DECLAREcount1 NUMBER := 0;total NUMBER := 0;CURSOR del_record_cur ISSELECT rowid FROM scott.emp WHERE empno<=20000;BEGINFOR rec IN del_record_cur LOOPDELETE FROM scott.emp WHERE rowid = rec.rowid;total := total + 1;count1 := count1 + 1;IF (count1 >= 1000) THEN COMMIT;count1 := 0;END IF;END LOOP;COMMIT;DBMS_OUTPUT.PUT_LINE(‘Deleted ‘ || total ||… Read More
Check Stats of Table : –example : ‘DEPT’,’EMP’,’SALGRADE’ set linesize 300 set pagesize 100 col owner for a15 col LAST_ANALYZED for a30 col table_name for a40 select OWNER,TABLE_NAME , NUM_ROWS , BLOCKS , EMPTY_BLOCKS , CHAIN_CNT, AVG_ROW_LEN CACHE, to_char(LAST_ANALYZED,’DD-MON-YYYY HH24:MI:SS’) LAST_ANALYZED from dba_tables where table_name… Read More
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(sys_context(‘USERENV’, ‘INSTANCE_NAME’), 17) current_instance FROM dual; SET TERMOUT ON; SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 256 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON… Read More
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
