Oracle Database Administration

set pages 9999 set lines 210 col object_name for a30 col owner for a15 col lp for a8 col name for a30 col text for a120 select count(*) from dba_objects where status<>'VALID'; select owner, object_type, count(*) from dba_objects where status<>'VALID' group by owner,object_type order by 1,2; select owner, object_type, object_name, status from dba_objects where status<>'VALID' order by 1,2,3; --list which errors are causing invalids select owner,type,name,line||'/'||position lp,text from DBA_ERRORS where   owner = '&owner' and type = '&obj_type' order by owner,type,name,sequence;

Transfer oracle sql profiles between two databases Summary  Here is a simple guide to transfer sql profiles from one database to another. For better understanding assume that you want to transfer one sql profile from the 'source' database to the 'destination' database.  1. Connect to the source database as sysdba and grant the required privileges to the user which will own the staging table.  $ sqlplus "/ as sysdba" 2. Create the staging table. BEGIN DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROFILES_STGTAB'); END; / 3. Check the existing sql profiles at the source db and copy the desired to the staging table SELECT * FROM dba_sql_profiles ORDER BY created DESC; BEGIN DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_014c5ae7b1c80001', staging_table_name => 'SQL_PROFILES_STGTAB'); END; / SELECT * FROM SQL_PROFILES_STGTAB; 4. Copy the staging table S...

run the report find the hash value SET ECHO OFF SET MARKUP HTML ON SPOOL ON SPOOL planhash.html set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = 'gd1by1cgjj853' and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3; SPOOL OFF SET MARKUP HTML OFF SET ECHO ON Step -1- Create SQL Tuning Set BEGIN   DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'STS_TEST',description => 'SQL Tunin...

--Check the existing blocking sessions in database: set lines 130 pages 1000 col BLOCKING_STATUS for a100 select s1.INST_ID,s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 / --Check internal blocking sessions which are holding in background: set lines 200 set pages 1000 col event for a40 col obj for a30 select inst_id,sid,blocking_session BSID,blocking_instance BINST,sql_id,event,last_Call_et,wait_time_micro, (select object_name from dba_objects where object_id = row_wait_obj#) obj, decode(event,'enq: TX - row lock contention',dbms_rowid.ROWID_CREATE(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#),null) rid f...

Please refer http://gavinsoorma.com/2009/08/read-only-tablespace-restore-and-recovery/ READ ONLY Tablespace Restore and Recovery Keeping static or historical data in read only tablespaces is a good practice especially for data warehouse type environments. Using the RMAN SKIP READONLY command, we can reduce the backup window and overhead as well by excluding these read only tablespaces from the database backupsets. But we need to keep in mind that we need to take at least one backup of the tablespace after it has been made read only and thereafter we can use the SKIP READONLY command to exclude these tablespaces from the daily or weekly database backups. However, while doing a restore we need to use the CHECK READONLY keywords otherwise by default the read only tablespaces will not be restored and hence the recovery will also bypass these tablespaces. Subssequent attempts to open the database will fail. Let us illustrate the same with an example where we have ma...

CPU usage SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"  FROM v$sesstat st, v$statname sn, v$session s, v$process p WHERE sn.name = 'CPU used by this session' -- CPU  AND st.statistic# = sn.statistic# AND st.sid = s.sid AND s.paddr = p.addr AND s.last_call_et < 1800 -- active within last 1/2 hour AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours ORDER BY st.value; SET ECHO OFF COL HOURS          FORMAT 999,990.99 COL MESSAGE        FORMAT A22 COL OPNAME         FORMAT A16        HEA "OPERATION" COL PCT_COMPLETE   FORMAT 99.9        HEA "PCT"  COL SERIAL#        FORMAT 99999 COL SID            FORMAT 9999 COL STARTED        FORMAT A16        HEA "START TIME" COL TARG...

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner from gv$locked_object l, dba_objects o where l.object_id = o.object_id; select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner, 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session from v$locked_object l, dba_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid; select sid, serial# from gv$session where sid = 0000; alter sysetm kill session ''; select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program, s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value, q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time, o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec from v$lock l, v$session s, v$pro...