Oracle 12c: DBMS_PART Package: Clean up global indexes on partitioned tables

July 1st, 2013 | Posted in 12c, Blog | No Comments


1- DBMS_PART.CLEANUP_GIDX:

Maintenance operations on partiioned tables can leave global indexes pointing to non-existing data segments.
Oracle 12c comes with the new procedure called DBMS_PART.CLEANUP_GIDX. This procedure cleans up the global indexes and results in improvement in performance and storage management.

The mainteannce JOb is automatically run via SMON in asynchronous mode, and cleans up all global indexes;

The SYS.PMO_DEFERRED_GIDX_MAINT_JOB Job is responsible to clean up all global indexes.
This job is scheduled to run at 2:00 A.M. on a daily basis by default.
You can run this job at any time using DBMS_SCHEDULER.RUN_JOB if you want to proactively clean up the indexes.
You can also modify the job to run with a different schedule based on your specific requirements.
However, Oracle recommends that you do not drop the job.

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 
Connected as wissem
 
SQL> DESC DBMS_PART
Element               Type      
--------------------- --------- 
CLEANUP_GIDX          PROCEDURE 
CLEANUP_GIDX_INTERNAL PROCEDURE 
CLEANUP_ONLINE_OP     PROCEDURE 
 
SQL> desc DBMS_PART.CLEANUP_GIDX
Parameter      Type     Mode Default? 
-------------- -------- ---- -------- 
SCHEMA_NAME_IN VARCHAR2 IN   Y        
TABLE_NAME_IN  VARCHAR2 IN   Y        
 
SQL> 

2- DBMS_PART.CLEANUP_ONLINE_OP:

This procedure pro-actively cleans up online table partition move operations instead of waiting for the background process (SMON) to do so.

SQL> desc  DBMS_PART.CLEANUP_ONLINE_OP
Parameter      Type     Mode Default? 
-------------- -------- ---- -------- 
SCHEMA_NAME    VARCHAR2 IN   Y        
TABLE_NAME     VARCHAR2 IN   Y        
PARTITION_NAME VARCHAR2 IN   Y        
 
SQL> 

Notes:

– If schema_name, table_name and partition_name are specified, this cleans up the failed online move operation for the specified partition.

– If schema_name and table_name are specified, this cleans up all failed online move operations for all the partitions of the specified table.

– If only schema_name is specified, this cleans up all failed online move operations in the schema.

– If no arguments are provided, we cleans up all the failed online move operations in the system.

– All other cases raise ORA-20000 to inform the user of invalid inputs as arguments.

References ; Doc 12cR1



No Comments to “Oracle 12c: DBMS_PART Package: Clean up global indexes on partitioned tables”

There are no comments yet, add one below.


Leave a Comment





Polls

which oracle topic interests you most?

View Results

Loading ... Loading ...


Oracle Class Tweets


Recent Posts


Recent Comments

  • T Robinson: In regard to the comment “In our case, RMAN didn’t found anything corrupted.” Please notice...
  • iftikhar: if we backup in multiple backupsets and will be command for restoring for more than one backupsets ,
  • Abey: Very helpful article.Thanks a lot. Can we follow this on oracle DG enabled deployment also?
  • Mauricio Daher: Need help configuring SBT for cloud backups. Does not seem to work for 12c. Thanks! RMAN>...
  • Leszek K: *.db_create_file_dest=’+ DATA’ create database command – why do you use full datafile...
  • Prabhakar Kumar: Thanks buddy!!! It really helps to DBAs who are novice to 12c ASM feature.
  • Wubeshet: I am getting the ORA-07445 This morning on the trace file. The following is the symptom of the problem. It...
  • Lakshmi N: Hi Thanks for the post. Just curious, is there a way to restore to a point wher my db is in no archive...
  • Shivakkumar P: This is really very good article. Iam learning Oracle 12c I used this to replicate testing from...
  • Uday: Hi Wissem, Once we apply the level 1 incremental backup to datafile copy it is no longed required. My question...
  • avnish: Can I set up active duplicate database in 11g standard edition (11.2.0.1)
  • Jaya V: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • Jaya: hi, am having rman backup. i need to restore on it, from test instance. But the test instance name not same...
  • wissem: SQL Developer 4.0
  • oracleman consulting: great info thanks for sharing
  • Mike: Very nice article!! Thanks for sharing!!
  • yathish: I really appreciate if you can send me, if you have tutorial for 11g (11.2.0.1) RAC & ASM (prefer to be...
  • Farrukh Kamal: Fahad, I’m interesting in pursuing remote employment with Pythian. What tips would you suggest...
  • pavankumar: for the error it need to set env correctly http://orcl11gdba.blogspot.in/ 2014/08/asmcmd-08102-no-con...
  • wissem: Post result off; crsctl check has crsctl check crs

Oracle-Class Forums Latest Activity