11g New Features

11g New Features

 

  ADR = Automatic Diagnostic Repository

 

*in 11g user_dump_dest,core_dump_dest,background_dump_dest are replace by diagnostic_dest

 

Sub-dirs under diagnostic_dest  

         Alert rdbms incidents

 

  Alert log file in two format

  Log.xml

  Alert_<SID>.log

 

  adrci Utility  to see the content of alert log or incident details

  $adrci

  Adrci>show alert

  Adrci>help

  Adrci>show incident

 

Changes in SGA & PGA memory management

 

Memory related parameter

  Memory_max_target (this is the upper limit for sga and pga combined)

  Memory_target (this is the actual size of sga and pga combined)

 

In 11g by default password is case sensitive, to disable it set the following parameter to false          

Sec_case_sensitive_logon=false;

 

To make a table read only/ read write

SQL>alter table emp read only;

SQL>alter table emp read write;

SQL>select  table_name,read_only from user_tables where table_name='EMP';

 

Table compression

SQL>alter table emp compress for all operations;

SQL>alter table emp nocompress;

SQL>select table_name ,compression from user_tables where table_name=’EMP’;

 

Making index invisible/visible

SQL>alter index ind_emp invisible;

SQL>alter index ind_emp invisible;

SQL>select index_name,visibility from user_indexes where index_name=’IND_EMP’;

 

Shrinking temporary tablespace

SQL>alter tablespace temp shrink space keep 100m;

 

Pending and publish statistics

By default statistics is published immediately so we use dbms_stats package to change default behaviour

SQL>exec dbms_stats.set_table_prefs('DEMO','SALES','PUBLISH','FALSE');

SQL> select dbms_stats.get_prefs('PUBLISH', 'DEMO', 'SALES' ) FROM DUAL;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('DEMO','SALES');

SQL>SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

To see the pending stats

SQL> SELECT TABLE_NAME,PARTITION_NAME ,LAST_ANALYZED  FROM DBA_TAB_PENDING_STAT;

Then we have to test the stats 

SQL> alter session set optimizer_use_pending_statistics=true;

 

Check that stats is working fine or not if working fine then publish;

SQL> EXEC DBMS_STATS.PUBLISH_PENDING_STATS ('DEMO','SALES');

SQL> SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME='SALES';

SQL> SELECT COUNT(*) FROM DBA_TAB_PENDING_STATS;

SQL>  alter session set optimizer_use_pending_statistics=FALSE;

Flashback archive 

Creatng Flashback Archive 

SQL>create flashback archive flash_arc tablespace <tablespace_name> quota 1g Retention 1 year ;

SQL>alter table u1.emp flashback archive flash_arc;

Sql> flashback table emp to timestamp to_timestamp(’16:12:10 02-NOV-2010’ ,’hh24:mi:ss dd-mon-yyyy);