gar-oracledba

Oracle Materialized Views Version 12.1.0.2 http://www.morganslibrary.org/reference/materialized_views.html

How to run DDL on busy objects ? Sometimes we always get resource too busy when DDL on a object. Here is how to use PL/SQL to do that. declare resource_busy exception; pragma exception_init (resource_busy,-54); begin loop begin execute immediate 'alter index INDEX_NAME noparallel'; exit; exception when resource_busy then dbms_lock.sleep(1); end; end loop; end; /

How-to show oracle parameters including hiddened ones 1 2 3 4 5 6 7 8 9 10 set pagesize 100 set linesize 150 col name format A45 col value format A20 col description format A70 select ksppinm name , ksppstvl value, ksppdesc description from x$ksppi x, x$ksppcv y where (x.indx = y.indx) order by name ;

Evaluate Oracle AWR (Automatic Workload Repository) 1. Operations 1.1 How to Install/Enable Set STATISTICS_LEVEL to TYPICAL, does not need bounce. Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14211/autostat.htm If we want to save every second ASH data to disk, set “_ash_disk_filter_ratio”=1. By default, it save 1 second snapshot out of 10 seconds. If we want to set AWR snapshot interval to 15 minutes, by default it’s 60 minutes: exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>15); If we want to set AWR data retention time to 90 days, by default it’s 7 days: exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>90*1440); 1.2 How to Disable In 11g, set CONTROL_MANAGEMENT_PACK_ACCESS = NONE as Oracle document said. But even with that, as my test in 11.2.0.2, Oracle will still create AWR snapshots. You can download the file dbmsnoawr.plb from Oracle DOC [ID 436386.1], and run follow 1 2 @dbmsnoawr.plb exec dbms_a