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

  • Adrian Iriarte: Saludos, Que buen articulo, casi siempre al momento de querer implementar soluciones para el...
  • Patricia: Hi Wissem, is there a workaround for this? I mean I want to use the sqlnet.authentication_services =none,...
  • Brian: I created the cdb manually using the instruction, but then I tried to create pdb within in, it failed. have...
  • Farmer Johnson: Hi Wissem. How did you avoid the error you had previsouly encountered: “sudo opatch auto...
  • Héctor Moro: Querido Nelson; Después de entrevistar a mas de 50 profesionales de primer nivel en Uruguay, al fin te...
  • What is NO2 Maximus: I genuinely apрreciated your site! You shoulԁ update it wiϳth new info!
  • Nav: Indeed helpful, to the point article..
  • Koti: Can you please explain all the sections of AWR report in RAC. I mean to ask output produced by awrgrpt.sql...
  • Himanshu: Nice post.can you please comment on this Why RMAN restored the DATA_REORTING datafie to +DATA diskgroup?
  • Enrique: Great post.
  • gabriel: what version of sqldeveloper support plugable database?
  • Kal: Quality – thanks…
  • wissem: Well , like I mentioned in the post, delete service and add service using IP.
  • Kelly: were you able to solve this? Thanks!
  • wissem: Hello, You can buy the book from PakT: http://www.packtpub.com/oracle -data-guard-11gr2-administr...
  • goutham: hi i want to buy this book.could you please let me know how can i buy this book. i am in Malaysia. thanks...
  • cesar Lopez: Hola me gustaria saber tu punto de vista de MYSQl vs ORACLE para una sistema de Nomina de unos 1500...
  • wissem: Check my previous article in English; Oracle 12c: RMAN recover Table http://www.oracle-class.com/?p =2866
  • NILESH: Sir, English please
  • Ramakrishna: I want step by step installation of Hyper-table in windows and also usage of Hyper-table like data...