Script to Disable constraints
The below script is to Disable constraints, SET PAGES 0 SET TRIMSPOOL ON SET LINES 200 set echo off set feedb off set trimspool on set verify off col…
Read MoreScript to Enable constraints
The below script is to Enable constraints, SET PAGES 0 SET TRIMSPOOL ON SET LINES 200 set echo off set feedb off set trimspool on set verify off col…
Read MoreScript to enable Archivelog and Flashback
The below script is to enable Archivelog and Flashback, set echo off set feedb off set heading off set verify off set trimspool on set lines 200 set define…
Read MoreScript to disable the plan baseline
The below script is to disable the plan baseline. # @(#) # @(#) ==================================================================== # @(#) Disable the SQL plan baseline # @(#) # @(#) # @(#) Format: disable_plan_baseline.sql…
Read MoreScript to enable the plan baseline
The below script is to enable the plan baseline # @(#) # @(#) ==================================================================== # @(#) Change SQL plan baseline attribute (set fixed to yes) # @(#) # @(#)…
Read MoreScript to gather stats the customs schemas
The below script is to gather stats the customs schemas. SET SERVEROUTPUT ON SIZE 1000000 set feedb off set trimspool on spool tmp_gather_schstats_${ORACLE_SID}.sql DECLARE CURSOR c_tm IS select…
Read MoreScript to identify ASM diskgroup space
set serveroutput on declare v_diskgroup_name varchar2(50); v_total_space number(12); v_free_space number(12); v_pct_free number(6,3); begin for diskgroup_rec in (select name from v$asm_diskgroup) loop — — Get the total space for the current…
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 MoreORA-46267 While initializing cleanup in audit table
BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 12 /* hours */); END; / ERROR at line 1: ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line…
Read More