Category: Scripts

General Database Script

Datatypes, General DBA, Scripts

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 … Continue reading How to find average row length for a table?

Alex LimaLeave a comment
General DBA, Scripts, Tuning

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 … Continue reading How to find the IO stats of filesystem?

Alex LimaLeave a comment
General DBA, OEM - Grid Control, Scripts, Shell, trace, Unix

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 {} \; … Continue reading Job to delete log and trace files from OEM

Alex LimaLeave a comment
Scripts, Shell, SQL*Plus, trace, Unix

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 … Continue reading Clean up script with oratab

Alex LimaLeave a comment
General DBA, Scripts

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’; Continue reading How to compile/drop public synonym?

Alex LimaLeave a comment
General DBA, Scripts

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 ; Continue reading How to grant view all DDL in an Oracle database to unprivilege user

Alex LimaLeave a comment