Fragmented_Tables_List in HTML FORMAT : col spoolname new_value spoolname select ‘Fragmented_tables’||to_char(sysdate, ‘YYYYMONDD’)||’.html’ spoolname from dual; spool ‘&spoolname’ set pagesize 200 set markup html on WITH STAT_SPACE AS( SELECT OWNER, TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, NUM_ROWS*AVG_ROW_LEN/1024/1024/1024 tbl_STAT_SPACE FROM DBA_TABLES WHERE OWNER=’SCOTT’), SEG_SPACE AS ( SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE,… Read More
Below are different kind of problems where you have recovery issues in standby. Before you take any decision check with your colleagues. 1) Check on source : SQL> select dest_id,error from v$archive_dest_status; 2) To know the redo apply rate : set linesize 500col item format… Read More
set linesize 140 pagesize 100select resource_name,current_utilization,limit_value from gv$resource_limit order by 1; Sample Output:
Check existing retention and current snapshot interval. set linesize 300 select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) snapshot_interval, extract( day from retention) *24*60+extract( hour from retention) *60+extract( minute from retention ) retention_interval, topnsql from dba_hist_wr_control; Below query is… Read More
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
