What is the Asynchronous Global Index Maintenance introduced in Oracle 12.1?

Asynchronous Global Index Maintenance, also called Deferred Global Index Maintenance, means that Oracle will maintain global indexes during the nightly maintenance window, when you perform operations on a partitioned tables that use a global index. Oracle has introduced this in an effort to make online operations less disruptive.

Examples of such operations are

* DROP PARTITION
* TRUNCATE PARTITION
* MOVE PARTITION (although this operation is not listed in the Oracle 12.1 documentation)

The execution of the index maintenance is done through the PL/SQL program PMO_DEFERRED_GIDX_MAINT, which purpose is to clear orphan data from global indexes generated during partition maintenance operations.

The program can be verified through DBA_SCHEDULER_PROGRAMS:

SELECT OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED
FROM DBA_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	PROGRAM_NAME	PROGRAM_TYPE	PROGRAM_ACTION	ENABLED
SYS	PMO_DEFERRED_GIDX_MAINT	PLSQL_BLOCK	dbms_part.cleanup_gidx_internal( noop_okay_in => 1);	TRUE

It is executed trough the scheduler every night:

select schedule_name,repeat_interval
from DBA_SCHEDULER_SCHEDULES
where schedule_name='PMO_DEFERRED_GIDX_MAINT_SCHED';

SCHEDULE_NAME	REPEAT_INTERVAL
PMO_DEFERRED_GIDX_MAINT_SCHED	FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0

It is executed through the job PMO_DEFERRED_GIDX_MAINT_JOB:

SELECT owner,job_name,program_name,schedule_name,enabled,state,run_count,to_char(last_start_date,'dd.mm.yyyy hh24:mi') "start",stop_on_window_close
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	JOB_NAME	PROGRAM_NAME	SCHEDULE_NAME	ENABLED	STATE	RUN_COUNT	start	STOP_ON_WINDOW_CLOSE
SYS	PMO_DEFERRED_GIDX_MAINT_JOB	PMO_DEFERRED_GIDX_MAINT	PMO_DEFERRED_GIDX_MAINT_SCHED	TRUE	RUNNING	1328	10.12.2019 02:00	FALSE

If the job is not finished within a day, is it possible to run PMO_DEFERRED_GIDX_MAINT with parallelism, as specified in the Oracle Support Note "Is It Possible To Execute The Job PMO_DEFERRED_GIDX_MAINT_JOB With Parallel Degree? (Doc ID 2126370.1)".


An example from my own experience is that the following command

ALTER TABLE SALES
MOVE PARTITION P_2019_02 ONLINE
TABLESPACE DATA2
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Triggered the statement:

ALTER INDEX "SH"."SALES_IDX1" COALESCE CLEANUP;

I have found that I sometime need to stop an job that's running through the maintenance window, and into office hours.
In that case, I've used the procedure dbms_scheduler.stop_job, like this:

BEGIN
  DBMS_SCHEDULER.STOP_JOB('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'TRUE');
END;
/

If you need to find out which indexes that are due for coalescing, use this query:

SELECT owner,index_name,orphaned_entries
FROM   dba_indexes
where owner ='SCOTT'
and orphaned_entries='YES'
ORDER BY 1;

How to move a datafile online in Oracle 12.1

Starting with Oracle 12.1, you can move datafiles online.


Generate move-script:

select 'alter database move datafile ''' || file_name || ''' TO ''' || replace(file_name,'old_sid','new_sid') || ''';'
from dba_data_files;

Output will be:

alter database move datafile '/oradata/old_sid/system01.dbf' TO '/oradata/new_sid/system01.dbf';

How to move a partition online from Oracle 12.1 and onwards

From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:

ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:

select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	GB
SYS_SUBP102302	DATA2	8,9
SYS_SUBP102303	DATA2	2
SYS_SUBP102304	DATA2	0
SYS_SUBP120071	DATA2	7,9
SYS_SUBP120072	DATA	1,8
SYS_SUBP120073	DATA2	0
SYS_SUBP12401	DATA	129,5

Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"