Oracle Database Administration

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...