select ‘restore archivelog from logseq ‘|| min(sequence#) || ‘ until logseq ‘ || max(sequence#) || ‘ thread ‘ || thread# || ‘;’ from gv$archived_log where completion_time between to_date(‘&start_date’,’DD-MON-YY HH24:MI:SS’) AND to_date(‘&end_date’,’DD-MON-YY HH24:MI:SS’) group by thread#; Input Required: Enter value for start_date: 02-OCT-16 Enter… Read More
Table Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘TABLE’,’&TABLE_NAME’,’&OWNER’) FROM DUAL; Procedure Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘PROCEDURE’,’&PROCEDURE_NAME’,’&OWNER’) FROM DUAL; Tablespace Metadata Definition : set pagesize 0 set long 90000 select dbms_metadata.get_ddl(‘TABLESPACE’,’&TABLESPACE_NAME’) FROM DUAL; Index Metadata Definition… Read More
Step 1 : Create Temporary Table : CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date); Step 2 : load table with 1,000,000 rows begin for i in 1 .. 1000 loop for j in 1 .. 1000 loop insert into unpar_table values… Read More
AWR Difference Report for NON RAC: @?/rdbms/admin/awrddrpt.sql AWR Difference Report for RAC : @?/rdbms/admin/awrddrpi.sql AWR Report for Non RAC : @?/rdbms/admin/awrrpt AWR Report for RAC : @?/rdbms/admin/awrrpti Global AWR RAC Report : @?/rdbms/admin/awrgrpt.sql ASH Report : @?/rdbms/admin/ashrpt.sql GLOBAL AWR RAC Differenct Report : @?/rdbms/admin/awrgdrpt.sql AWR… Read More
Instance Information : set linesize 300 select name||’ – ‘||instance_name||’@’||host_name||’ – ‘||DB_UNIQUE_NAME||’ – ‘||version||’ – ‘||open_mode||’ – ‘||to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) || ‘ – ‘|| database_role from v$database, v$instance; FRA Usage : set linesize 300 col file_type for a40 select * from V$FLASH_RECOVERY_AREA_USAGE; Check Restore Point :… Read More
Check List before DB is ready for Performance Test 1) Init Parameters properly configured or not.2) sessions Monitoring script.3) OS Watchers running or not.4) AWR Snapshots configured or not.5) CPU’s Count.6) SGA Size7) OEM (Grid) 8) Keep handy Monitoring Scripts (Check Load Test Monitoring in… Read More
If the Oracle Home is filled and the DB gets hanged, then the first thing we need to check is— Audit File / Network Logs / dump locations — Check the file system using the below commanddf -h | grep oracle (IF THE OUTPUT HAS 100% USAGE)… Read More
1. EXPLAIN PLAN FOR SELECT <STATEMENT> set linesize 300SELECT * FROM TABLE(dbms_xplan.display); 2. FROM MEMORY – Get the execution plan which is generated by CBO Use DBMS_XPLAN package:select * from table (dbms_xplan.display_cursor (‘&sqlid’) ) ; For the above, you need to have SQL ID. To… Read More
cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More
set linesize 200 set pagesize 500 spool /tmp/Oct29.html set markup html on set numwidth 40 select * from emp; set long 100000 select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off
