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.

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

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

Remove Old Backup Log Files

This script will delete files modified more than 14 days ago and have file name that begin with daily_backup , it will do recursive search within directories that exists in the directory /work/dba/backup/logs/

find /work/dba/backup/logs/ -name "daily_backup*" -mtime +14 -exec rm -f {} ;

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
;

How does one trace a session based on user

CREATE OR REPLACE TRIGGER sys.session_trace_on
– to be created by sys user
AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
ora_username VARCHAR2(30) DEFAULT NULL;
os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE audsid = userenv(’sessionid’)
– and upper(machine) like ‘ABC123′
and username = ‘EPCBATCH’;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename,v_program;
IF c1%FOUND THEN
DBMS_SESSION.set_sql_trace (TRUE);
END IF;
CLOSE c1;
END;
/

How to view session stats?

To see current stats of your session, one can run the following query.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic#;

To view session stats of another session one can run the query below passing the session id.
SELECT a.name, b.value FROM v$statname a, v$mystat b WHERE a.statistic# = b.statistic# and sid = &v_sid;

Trigger to monitor successful logins in the database

By having a logon trigger in a database one can monitor or audit OS user, programs, DB users, time users logging into a database. It could also be used the change a behavior of a session by altering user’s session’s based on certain criteria.

CREATE TABLE sys.logon_audit
(
logon_time DATE,
username VARCHAR2(30), — DB user
osuser VARCHAR2(30), — OS user
machine VARCHAR2(64), — machine
program VARCHAR2(48 )
) TABLESPACE dba_tbs;

CREATE OR REPLACE TRIGGER sys.connection_audit
AFTER LOGON ON database
DECLARE
– use sys_context for 9i and above
– for 8i use userenv(’sessionid’)
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session;
– WHERE audsid = sys_context(‘USERENV’,’sessionid’);
rec_c1 c1%ROWTYPE;

BEGIN
rec_c1 := NULL;

OPEN c1;
FETCH c1 INTO rec_c1;
– record will always be found, so need to check record not found
INSERT INTO user_audit(logon_time, username, osuser, machine, program)
VALUES (sysdate, user, rec_c1.osuser, rec_c1.machine, rec_c1.program);
CLOSE c1;
END;
/

Note: You may also implement a routine to delete data from the table as if the tablespace gets full users won’t be able to login.