Subscriber Count


Subscribe to Posts by Email


12c Database : Asynchronous index maintainence

Earlier to 12c, when an partition is truncated with update global index clause the operation will take longer to complete since it has to perform the operation on index also to remove corresponding keys in index partition/index and then rebuild entire partition. But that is history,

Asynchronous global index maintainence is the feature to clear […]

12c Database : Interval Partitioning enhancements – 2

From 12c, interval partitions has been enhanced to use reference partitioning, With this you can create a parent/child table with references in them using interval partitioning.

The following is the test case.


12c Database : Reference Partitioning Enhancement – Part 1

From 12c, you can truncate partitions from a referenced partition with cascade option.

The following is the test case.

Create table EMPLOYEE ( EMPLOYEE_ID number primary key, EMPLOYEE_NAME varchar(25), JOINING_DATE date) PARTITION BY RANGE (JOINING_DATE)( PARTITION P_2013_06 VALUES LESS THAN (to_date('30-06-2013','dd-mm-yyyy')) NOCOMPRESS, PARTITION P_2013_07 VALUES LESS THAN (to_date('31-07-2013','dd-mm-yyyy')) NOCOMPRESS, PARTITION P_2013_08 VALUES LESS THAN (to_date('31-08-2013','dd-mm-yyyy')) […]

12c Database : New DBMS_PART package to cleanup the partition maintenance activities

In 12c, Oracle introduced package to manage the global indexes orphan entry clean up and clearing up the online partition movement stuff. Here it is PROCEDURE CLEANUP_GIDX- To clean up the global indexes , this runs daily at 2.00AM via scheduler job automatically PROCEDURE CLEANUP_GIDX_INTERNAL – To clean up the internal tables PROCEDURE CLEANUP_ONLINE_OP – […]

12c Database : Multiple Partitions maintainance in one go

Before to 12c, if you want to maintain partitions you will need to execute that many alter statements partition commands that many times.

For example to add p7,p8 partitions, you have to use

SQL> alter table test_tbl add partition p7 values less than (7) SQL> alter table test_tbl add partition p8 values less than […]

12c Database : Partial Indexing on partition/sub partitions tables

Before to 12c, it is not possible to create a index for certain partitions or for the partitions you want.

From 12c onwards, you can create the indexes (global or local) for specific partitions (not to all partitions) or for specific sub partitions (not to all sub partitions). This means indexes will be created […]

12c Database : Online clause for partition movement

From 12c onwards, partitions movement has been extended with "online clause", along with this global index are maintained during partition maintenance , hence manual rebuild of index is not required any more. Before proceeding with the test, here are some restriction as listed in documentation, ONLINE clause can’t be specified ?for tables owned by SYS. […]

12c Database : Default Values enhancements

Prior to 12c, if the developer wants to create a sequential ID number generator , he/she has to use a sequence and then a trigger or reference that seq value in the insert statement.

From 12c Onwards, this has been enhanced by providing the sequence.nextval in the table definition itself.

Further it even enhanced that […]

Quick Question: Finding fragmentation of a table having clob.

A user has posted me "how to find the fragmentation for a table that contains CLOB", ironically all the queries and other stuff will lead to confusion or no more useful.

Honestly, to answer this question I have searched many things to clear up, only Jonathan has a clear cut demonstration and answer for […]

12c Database : New Background Processes

New Background Processes that has been introduced from 12c, compared with Database.


12 C Process Name AQPC AQ Process Coordinator ARSn ASM Recovery Slave Process BWnn Database Writer Process FENC Fence Monitor Process IPC0 IPC Service Background Process LDDn Global Enqueue Service Daemon Helper Slave LGnn Log Writer Worker LREG Listener Registration Process […]