New Features in Oracle 12c

  1. Online renaming and relocation of datafile.

    SQL> alter database move datafile ‘location 1 (ASM/ NON-ASM)’ to ‘location 2 (ASM/ NON-ASM)’;

  • OVERWRITE

    SQL> alter database move datafile ‘location 1 (ASM/ NON-ASM)’ to ‘location 2 (ASM/ NON-ASM)’ REUSE;

     

  • KEEP

    SQL> alter database move datafile ‘location1(ASM/NON ASM)’ to ‘location2(ASM/NON ASM)’ KEEP;

  1. Putting multiple indexes on same column

    SQL> create index new_ind on table_name(col1, col2);

    SQL> create bitmap index new_ind1 on table _name(col1,col2) INVISIBLE;

  2. Creating invisible columns in a table that can be made visible later on.

    SQL> create

  3. Online migration of table partition or sub-partition

    SQL> ALTER table tname move partition / subpartition partition_name to tablespace tablespace_name

    SQL> alter table table_name move partition partition_name to tablespace tablespace_name update indexes online;

  4. DDL Logging

    Inint param is ENABLE_DDL_LOGGING

    It may be set TRUE or FALSE as required.

    Cd $ORACLE_BASE/diag/rdbms/dbname/log

    Is the location of ddl log.

  5. Temporary undo

    SQL> ALTER SESSION/ SYESTEM SET TEMP_UNDO_ENABLE=TRUE/ FALSE;

    The views and tables related are

  • v$tempundostat

  • v$undostat

  • DBA_HIST_UNDOSTAT

 

 

  1. Backup specific user privilege to sys backup

    …]$ rman target “user/password as sysbackup”

  2. SQL statement can be directly executed in RMAN without announcing “SQL ‘sql command’” syntax

    RMAN> SELECT * FROM YOUR_TABLE;

  3. Restricting PGA size.

    SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;

    To remove this limit set it to 0 and hard limit will be disabled.

  4. Table or Partition recovery in RMAN.

    RMAN> connect target username/pass as sysbackup

    RMAN> recover table user.table_name until time ‘time_stamp’

                 Auxiliary destination ‘/u01/tablerecovery’

                 Datapump destination ‘/u01/dpump’

                 Dumpfile table_name.dmp

    NOTABLEIMPORT-> Avoids table import automatically.

                        remap table ‘user.table_name’;

                        ‘user.new_table_name’; ->rename table

like0