Oracle Flashback Query: Recovering at the Row Level
In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 4:25…
Read MoreScript to find the session events
col sql format a35 col username format a20 col child format 999 col secs format 9999 col machine format a12 col event format a25 col state format a10 select /*+…
Read MoreScript to check temp used by session
set pagesize 10000 set linesize 133 column tablespace format a20 heading ‘Tablespace Name’ column segfile# format 9,999 heading ‘File|ID’ column spid format 9,999 heading ‘Unix|ID’ column segblk# format 999,999,999 heading…
Read MoreFinding Top sqls ordered by elapsed time in a period of time
set head on column module format a20 set lines 1000 pages 1000 prompt SQL Ordered by Elapsed time /*FIND THE SNAP ID*/ set pages 0 col begin_interval_time for a30 col…
Read MorePerforming Expdp/Impdp in Autonomous datawarehouse using SQLDEVELOPER
Click here to Download
Read MoreMaterialized view refresh
MVIEW LOG =========== col log_owner for a30 col master for a40 col log_table for a40 col last_purge_date for a30 col last_purge_status for 9999999 select log_owner,master,log_table,last_purge_date,last_purge_status from dba_mview_logs; MVIEW LOG SIZE…
Read MoreESTIMATE INDEX SIZE BEFORE CREATION
GATHER STATS FOR TABLE exec dbms_stats.gather_table_stats (ownname=>’&Owner’,tabname=>’&Table_name’,estimate_percent=>100,block_sample=>true,method_opt=>’FOR ALL COLUMNS size 254′); ESTIMATE INDEX SIZE set serveroutput on declare l_used_bytes number; l_alloc_bytes number; begin dbms_space.create_index_cost ( ddl => ‘create index test_indx…
Read More