How to find average row length for a table?

Using the following PL/SQL code one can find average size of a row in a table, the following code samples the first 100 rows. It expects 2 parameters table owner and table_name.

DECLARE
l_vc2_table_owner VARCHAR2(30) := '&table_owner';
l_vc2_table_name VARCHAR2(30) := '&table_name';
/* sample number of rows */
l_nu_sample_rows NUMBER := 100;

/* loop through columns in the table */
CURSOR l_cur_columns IS
SELECT column_name, data_type FROM dba_tab_columns
WHERE owner = l_vc2_table_owner
AND table_name = l_vc2_table_name;
l_rec_columns l_cur_columns%ROWTYPE;
l_vc2_sql VARCHAR2(10000);
l_avg_row_size NUMBER(10,2);
BEGIN
l_vc2_sql := '';
OPEN l_cur_columns;
FETCH l_cur_columns INTO l_rec_columns;
/* loop through columns */
WHILE l_cur_columns%FOUND
LOOP
/* if LOB datatype use dbms_log.get_length to find length */
IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
ELSE
l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
END IF;
FETCH l_cur_columns INTO l_rec_columns;
IF l_cur_columns%FOUND THEN
l_vc2_sql := l_vc2_sql || ' + ';
END IF;
END LOOP;
IF l_vc2_sql IS NOT NULL THEN
l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' WHERE rownum < ' || l_nu_sample_rows;
EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
ELSE
dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
END IF;
END;
/
Output
Enter value for table_name: TEST_OBJECTS
old 3: l_vc2_table_name VARCHAR2(30) := ‘&table_name’;
new 3: l_vc2_table_name VARCHAR2(30) := ‘TEST_OBJECTS’;
SCOTT.TEST_OBJECTS average row length: 76.88

PL/SQL procedure successfully completed.

Advertisements

How to find the IO stats of filesystem?

Using v$filestat one can find the physical reads and writes to datafiles it also includes reads done by RMAN. So using this SQL one can find physical read and write on a filesystem. Note: The data reported is since the database started.

SQL> column filesystem format a40
SQL> SELECT substr(vdf.name, 1, instr(vdf.name, '/', -1)) filesystem, sum(vfs.phyrds) totalreads, sum(vfs.phywrts) totalwrts
FROM v$filestat vfs, v$datafile vdf
WHERE vfs.file# = vdf.file#
GROUP BY substr(vdf.name, 1, instr(vdf.name, '/', -1));

FILESYSTEM TOTALREADS TOTALWRTS
—————————————- ————— —————-
/u01/oradata/TEST/data01/ 33397136 1315151
/u02/oradata/TEST/idx01/ 71951 35720
….

Here is the description of the view V$FILESTAT and V$DATAFILE

Job to delete log and trace files from OEM

I use this script to clean up all the log and trace file from each host..  You can schedule a job in OEM to go and execute this in all hosts you want.

$cat /etc/oratab

ESRTSP:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
ESRTSS:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
RESTST:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
LISTENER_10g:/oracle/app/EPW/rdbms/v10203_ee_suse10:N
AGENT_10g:/oracle/app/EPW/agent/v10203_ee_suse10:N

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

###############################################
###############################################
##  ADD ALL INSTANCES TO THE FOR LOOK LIST   ##
###############################################
###############################################

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:’|egrep -v  ‘LISTENER|AGENT’`
do

find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

done

job-oem

Clean up script with oratab

A simple script to delete trace files and arc logs from remote location, based on oratab instances.

#!/bin/ksh

export PATH=/usr/bin:$PATH
export USERNAME=`whoami`
export HOSTNAME=`hostname`

########################################
#  Copy TNS Names file to local disk   #
########################################
/bin/ksh /work/dba/monora/scripts/update_local_tns.ksh > /tmp/update_local_tns.log 2>&1
#
#      Clean up Oracle Export logs
#
find /work/dba/logs -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /tmp -name ‘*${HOSTNAME}_*’ -mtime +7 -exec rm {} \;
find /work/dba/prod/backups -name ‘tmp_ora_${HOSTNAME}_*’ -mtime +3 -exec rm -r {} \;

for SID in `cat /etc/oratab|egrep ‘:N|:Y’|grep -v \*|cut -f1 -d’:’|egrep -v  ‘LISTENER|AGENT’`
do
find /oracle/EPW/${SID}/admin/udump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/cdump -name ‘core*’ -mtime +13 -exec rm -r {} \;
find /oracle/EPW/${SID}/admin/bdump -name ‘*.trc’ -mtime +13 -exec rm {} \;
find /oracle/EPW/${SID}/admin/adump -name ‘*.aud’ -mtime +13 -exec rm {} \;

## To delete ach logs from remote online location for dev/test/training env that are in arch log mode
find /oracle/EPW/arch/${SID} -name ‘${SID}*.arc’ -mtime +3 -exec rm {} \;
done

How to compile/drop public synonym?

— compile public synonym that are invalid
Select ‘alter public synonym ‘||object_name||’ compile;’
From dba_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

— drop public synonym that are invalid
Select ‘drop  public synonym ‘||object_name||’;’
From dba_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

How to grant view all DDL in an Oracle database to unprivilege user

SQL>grant select on dba_objects to <username>;

SQL>conn <username>/<password>

SET PAGESIZE 0
SET LONG 100000
SET LONGCHUNKSIZE 10000
SET LINESIZE 1000
–SET TERMINATOR ON
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE)
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = ‘PACKAGE’ AND OWNER = ‘POSITION_OWNER’
–SAME FOR OTHER OBJECTS E.G. TABLE, PROCEDURE, ETC
;