General Database Script
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…
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,…
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 #…
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…
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 {} ;
— 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’;
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 ;
