18 August, 2019

Blog Series on 12cR1 RAC

A series of posts in 2017 on 12cR1 RAC :

1.  1. Grid  Infrastructure Install  (Dec-16)

2.  2. Convert AdminManaged Database to PolicyManaged   (Dec-16)

3.  3. Convert PolicyManaged Database back to AdminManaged   (Jan-17)

4.  4. Adding a Disk of a Different Size  (Jan-17)

5.  5. Relocating OCR and VoteDisk  (Jan-17)

6.  6. Running the Cluster Verification Utility  (Feb-17)

7.  7. OCR Commands  (Mar-17)

8.  8a. Setting Up Single Instance DG Standby for RAC  (Mar-17)

9.  8b. DUPLICATE DATABASE FOR STANDBY  (Mar-17)

10.  8c. Ignorable "Errors" During the DUPLICATE  (Mar-17)

11.  8d. Registering the two databases in DataGuard Broker  (Mar-17)

12.  8e. Redo Shipping and Apply (RAC to nonRAC)  (Apr-17)

13.  8f. Accessing Data in the PDB in the Standby  (Apr-17)

14.  8g. Switchover from RAC Primary to SingleInstance Standby  (Apr-17)

15.  8h. DataGuard Switchover (RAC to nonRAC) messages  (Apr-17)

16.  8i. Switchback from SingleInstance to RAC  (Apr-17)

17.  9. Adding a Service to a PDB in RAC  (Apr-17)

18.  10. Video on Database Startup  (May-17)



Basic Replication -- 2a : Elements for creating a Materialized View

The CREATE MATERIALIZED VIEW statement is documented here.  It can look quite complex so I am presenting only the important elements here.  In this post, I begin with only the basic elements.

(EDIT: These SQL operations, queries and results were in a 19c Database)

First, I recreate the SOURCE_TABLE poperly, with a Primary Key :

SQL> drop table source_table;

Table dropped.

SQL> create table source_table
  2  (id  number not null,
  3   data_element_1 varchar2(15),
  4   data_element_2 varchar2(15),
  5   date_col date)
  6  /

Table created.

SQL> create unique index source_table_pk
  2  on source_table(id);

Index created.

SQL> alter table source_table
  2  add constraint source_table_pk
  3  primary key (id)
  4  /

Table altered.

SQL>


Then I create a Materialized View Log on SOURCE_TABLE.  This will capture all DML against this table and will be read by the target Materialized View to identify "changed" rows at every refresh.

SQL> create materialized view log on source_table;

Materialized view log created.

SQL>


I then identify the objects that were created.

SQL> select object_id, object_name, object_type
  2  from user_objects
  3  where created > sysdate-1
  4  order by object_id
  5  /

 OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -----------------------
     73055 SOURCE_TABLE                   TABLE
     73056 SOURCE_TABLE_PK                INDEX
     73057 MLOG$_SOURCE_TABLE             TABLE
     73058 RUPD$_SOURCE_TABLE             TABLE
     73059 I_MLOG$_SOURCE_TABLE           INDEX

SQL>
SQL> desc mlog$_source_table;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 SNAPTIME$$                                                                        DATE
 DMLTYPE$$                                                                         VARCHAR2(1)
 OLD_NEW$$                                                                         VARCHAR2(1)
 CHANGE_VECTOR$$                                                                   RAW(255)
 XID$$                                                                             NUMBER

SQL> desc rupd$_source_table;
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 ID                                                                                NUMBER
 DMLTYPE$$                                                                         VARCHAR2(1)
 SNAPID                                                                            NUMBER(38)
 CHANGE_VECTOR$$                                                                   RAW(255)

SQL>


Interesting that the "CREATE MATERIAIZED VIEW LOG" statement created 3 database objects.

What happens after I perform DML on the SOURCE_TABLE ?

SQL> insert into source_table
  2  values (1,'First','One',sysdate);

1 row created.

SQL> insert into source_table
  2  values (2,'Second','Two',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> delete source_table
  2  where id=2
  3  /

1 row deleted.

SQL>
SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
     XID$$
----------
         1 01-JAN-00 I N
FE
2.8158E+14

         2 01-JAN-00 I N
FE
2.8158E+14

         2 01-JAN-00 D O
00
2.5334E+15


SQL>
SQL> select * from rupd$_source_table;

no rows selected

SQL>


So the MLOG$_SOURCE_TABLE is the log that captures 2 INSERT statements and 1 DELETE statement.  (OR is it 2 INSERT *rows* and 1 DELETE *row* ??)
We don't know what the RUPD$_SOURCE_TABLE captures yet.

Let me create a Materialized View and then query MLOG$_SOURCE_TABLE (which is the "MV Log")

SQL> create materialized view
  2  mv_of_source
  3  refresh fast on demand
  4  as select * from source_table
  5  /

Materialized view created.

SQL> select * from mv_of_source
  2  /

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         1 First           One             18-AUG-19

SQL>
SQL> select * from mlog$_source_table;

no rows selected

SQL>


So, the CREATE MATERIALIZED VIEW statement has also done a cleanup of the MV Log entries with a SNAPTIME$ older than when it was created.

Let me insert two new rows and then refresh the Materialized View and check the MV Log again.

SQL> insert into source_table
  2  values (3,'Third','Three',sysdate);

1 row created.

SQL> insert into source_table
  2  values (4,'Fourth','Four',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

        ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
     XID$$
----------
         3 01-JAN-00 I N
FE
1.6889E+15

         4 01-JAN-00 I N
FE
1.6889E+15


SQL>
SQL> execute dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from mv_of_source;

        ID DATA_ELEMENT_1  DATA_ELEMENT_2  DATE_COL
---------- --------------- --------------- ---------
         1 First           One             18-AUG-19
         3 Third           Three           18-AUG-19
         4 Fourth          Four            18-AUG-19

SQL>


So, the 2 single-row INSERTs did create two entries in the MV Log and the REFRESH of the Materialized View did a cleanup of those two entries.

I haven't yet explored :
a.  UPDATEs
b. Multi-Row Operations

16 August, 2019

Basic Replication -- 1 : Introduction

Basic Replication, starting with Read Only Snapshots has been available in Oracle since  V7.   This was doable with the "CREATE SNAPSHOT" command.

In 8i, the term was changed from "Snapshot" to "Materialized View"  and the "CREATE MATERIALIZED VIEW" command was introduced, while "CREATE SNAPSHOT" was still supported.

Just as CREATE SNAPSHOT is still available in 19c,  DBMS_SNAPSHOT.REFRESH is also available.


























Not that I recommend that you use CREATE SNAPSHOT and DBMS_SNAPSHOT anymore.  DBAs and Developers should have been using CREATE MATERIALIZED VIEW and DBMS_REFRESH since 8i.

In the next few blog posts (this will be a very short series) I will explore Basic Replication.  Let me know if you want to see it in 11.2 and 12c as well.


EDIT :  As I had only presented a screen-shot earlier, I am pasting in the commands from an SQLPlus session here :

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Aug 18 16:19:21 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Enter user-name: hemant/hemant@orclpdb1
Last Successful login time: Sun Aug 18 2019 16:17:48 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> create table source_table as select * from dba_objects;

Table created.

SQL> create snapshot mv_of_source as select * from source_table;

Materialized view created.

SQL> select count(*) from source_table;

  COUNT(*)
----------
     72355

SQL> select count(*) from mv_of_source;

  COUNT(*)
----------
     72355

SQL> insert into source_table select * from source_table;

72355 rows created.

SQL> dbms_snapshot.refresh('MV_OF_SOURCE');
SP2-0734: unknown command beginning "dbms_snaps..." - rest of line ignored.
SQL> execute dbms_snapshot.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> select  count(*) from mv_of_source;

  COUNT(*)
----------
    144710

SQL> select object_id, object_type from user_objects where object_name = 'MV_OF_SOURCE';

 OBJECT_ID OBJECT_TYPE
---------- -----------------------
     73051 TABLE
     73052 MATERIALIZED VIEW

SQL> drop materialized view mv_of_source;

Materialized view dropped.

SQL> select object_id, object_type from user_objects where object_name = 'MV_OF_SOURCE';

no rows selected

SQL>


As you can see, even if I use the "CREATE SNAPSHOT" command, the data dictionary shows that it is a Materialized View.  OBJECT_ID 73051 is the actual table created by Oracle to "store" the rows of the Materialized View while 73052 is the definition of the Materialized View.



08 August, 2019

2million PageViews


This blog has now achieved 2million PageViews :




(The "drop" at the end is the count for only the first week of August 2019).

Although this blog began in December 2006, the PageViews counts start with 8,176 in July 2010.  So, effectively, this blog has had 2million PageViews in 9years.

The first 1million PageViews were achieved in March 2015.

Unfortunately, the rate at which I have been publishing has declined since 2017 (36 posts in 2017, 30 in 2018 and only 8 so far this year).  I apologise for this.  Hopefully, I should be able to add more posts in the coming months.




07 May, 2019

Partitioning -- 16 : Hybrid Partitioning

Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 WIDGET_CLASS_ID                                    VARCHAR2(5)
 WIDGET_ID                                          VARCHAR2(32)
 WIDGET_NAME                                        VARCHAR2(32)
 WIDGET_DESCRIPTION                                 VARCHAR2(128)

SQL>
SQL> l
  1  select table_name, partitioning_type, partition_count
  2  from user_part_tables
  3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME                       PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST                     LIST                    3

SQL>
SQL> l
  1  select partition_name,high_value, num_rows
  2  from user_tab_partitions
  3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME   HIGH_VALUE         NUM_ROWS
---------------- ---------------- ----------
P_A              'A'                    1520
P_B              'B'                     520
P_C              'C'                     119

SQL>


Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$


So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
  1  alter table widgets_list
  2  add external partition attributes (
  3  type oracle_loader
  4  default directory acquired_company
  5  access parameters (
  6  fields terminated by ','
  7  (widget_class_id, widget_id, widget_name, widget_description)
  8  )
  9* )
SQL> /

Table altered.

SQL>
SQL> l
  1  alter table widgets_list
  2  add partition P_ACQ_CO values ('X')
  3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','WIDGETS_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> l
  1  select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'WIDGETS_LIST'
  4* order by partition_position
SQL> /

PARTITION_NAME                   HIGH_VALUE         NUM_ROWS
-------------------------------- ---------------- ----------
P_A                              'A'                    1520
P_B                              'B'                     520
P_C                              'C'                     119
P_ACQ_CO                         'X'                       3

SQL>
SQL> l
  1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID                        WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X'          'ABCXX2'                         'The1'
'cddfdaxx'

'X'          'XXD2'                           'The2'
'dda3'

'X'          'XRC34'                          'The3'
'ff33355312'


SQL>


The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
  1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID  WIDGET_ID                        WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X                ABCXX2                           The1
cddfdaxx

X                XXD2                             The2
dda3

X                XRC34                            The3
ff33355312


SQL>


So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

08 April, 2019

Partitioning -- 15 : Online Modification of Partitioning Type (Strategy)

Oracle 18c introduces the ability to convert a Partitioned Table from one Type to another -- e.g. from Hash Partitioning to Range Partitioning.  This is effectively a change of the Partitioning strategy for a table without actually having to manually rebuild the table.

I start with a Hash Partitioned Table.

SQL> create table customers(customer_id number, customer_name varchar2(200), customer_city_code number)
  2  partition by hash (customer_id) partitions 4;

Table created.

SQL> select partitioning_type from user_part_tables
  2  where table_name = 'CUSTOMERS'
  3  /

PARTITION
---------
HASH

SQL> select partition_name from user_tab_partitions
  2  where table_name = 'CUSTOMERS'
  3  /

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P221
SYS_P222
SYS_P223
SYS_P224

SQL>
SQL> insert into  customers
  2  select dbms_random.value(1,1000001), dbms_random.string('X',25), mod(rownum,5)
  3  from dual
  4  connect by level < 1000001
  5  /

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
  2  from user_tab_partitions
  3  where table_name = 'CUSTOMERS'
  4  /

PARTITION_NAME     NUM_ROWS
---------------- ----------
SYS_P221             250090
SYS_P222             249563
SYS_P223             250018
SYS_P224             250329

SQL>


I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.

SQL> alter table customers
  2  modify
  3  partition by range (customer_id)
  4  (partition P_100K values less than (100001),
  5   partition P_200K values less than (200001),
  6   partition P_300K values less than (300001),
  7   partition P_400K values less than (400001),
  8   partition P_500K values less than (500001),
  9   partition P_600K values less than (600001),
 10   partition P_700K values less than (700001),
 11   partition P_800K values less than (800001),
 12   partition P_900K values less than (900001),
 13   partition P_1MIL values less than (1000001),
 14   partition P_2MIL values less than (2000001),
 15   partition P_MAXVALUE values less than (MAXVALUE))
 16  online;

Table altered.

SQL>
SQL> select partitioning_type
  2  from user_part_tables
  3  where table_name = 'CUSTOMERS'
  4  /

PARTITION
---------
RANGE

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> col high_value format a12
SQL> select partition_name, high_value, num_rows
  2  from user_tab_partitions
  3  where table_name = 'CUSTOMERS'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE     NUM_ROWS
---------------- ------------ ----------
P_100K           100001           100116
P_200K           200001            99604
P_300K           300001            99941
P_400K           400001           100048
P_500K           500001            99841
P_600K           600001            99920
P_700K           700001           100081
P_800K           800001           100024
P_900K           900001           100123
P_1MIL           1000001          100302
P_2MIL           2000001               0
P_MAXVALUE       MAXVALUE              0

12 rows selected.

SQL>


The Hash Partitioned Table is now converted to a Range Partitioned Table.  The number of Partitions has been changed.  And the operation was performed online with the ONLINE keyword added to the ALTER TABLE ... statement.  The UPDATE INDEXES clauses can also be used to update existing Indexes on the Table.





25 March, 2019

Partitioning -- 14 : Converting a non-Partitioned Table to a Partitioned Table

Pre-12cRelease2, there were only three methods to convert a non-Partitioned Table to a Partitioned Table

(a) Create a new, empty, Partitioned Table and copy (using INSERT .... AS SELECT ... ) all the data from the non-Partitioned Table to the new, Partitioned Table (and subsequently rename the new Partitioned Table after renaming or dropping the old non-Partitioned Table)

(b) Create a new, empty, Partitioned Table and use EXCHANGE PARTITION to switch the non-Partitioned Table into the Partitioned Table (and then run subsequent SPLIT PARTITION or ADD PARTITION commands as needed to create the additional Partitions)

(c) Create an interim Partitioned Table and use DBMS_REDEFINITION to do an online copy of the data to the interim Partitioned Table and automatically switch the name at the end


12.2 introduced the ability to use ALTER TABLE  ... MODIFY PARTITION ... to convert a non-Partitioned Table to a Partitioned Table

I start with a non-Partitioned Table :

SQL> select table_name, partitioned
  2  from user_tables
  3  where table_name = 'SALES_DATA_NONPARTITIONED'
  4  /

TABLE_NAME                     PAR
------------------------------ ---
SALES_DATA_NONPARTITIONED      NO

SQL> select index_name, uniqueness, partitioned
  2  from user_indexes
  3  where table_name = 'SALES_DATA_NONPARTITIONED'
  4  /

INDEX_NAME                     UNIQUENES PAR
------------------------------ --------- ---
SALES_DATA_UK                  UNIQUE  NO

SQL> 


I then convert it to a Range-Partitioned Table.

SQL> alter table sales_data_nonpartitioned
  2  modify 
  3  partition by range (sale_date)
  4  (
  5  partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
  6  partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),
  7  partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
  8  partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
  9  partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY')),
 10  partition p_MAXVALUE values less than (MAXVALUE) 
 11  )
 12  online 
 13  update indexes
 14  /

Table altered.

SQL> 
SQL> alter table sales_data_nonpartitioned rename to sales_data;

Table altered.

SQL>
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES_DATA'
  4  order by partition_position
  5  /

PARTITION_NAME   HIGH_VALUE
---------------- --------------------------
P_2015           TO_DATE(' 2016-01-01 00:00
P_2016           TO_DATE(' 2017-01-01 00:00
P_2017           TO_DATE(' 2018-01-01 00:00
P_2018           TO_DATE(' 2019-01-01 00:00
P_2019           TO_DATE(' 2020-01-01 00:00
P_MAXVALUE       MAXVALUE

6 rows selected.

SQL> 
SQL> select index_name, partitioned, uniqueness, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  /

INDEX_NAME                     PAR UNIQUENES STATUS
------------------------------ --- --------- --------
SALES_DATA_UK                  NO  UNIQUE    VALID

SQL> 


The SALES_DATA_NONPARTITIONED was converted to a Range Partitioned Table.  If I didn't have to rename the table (e.g. if the table name was actually, properly SALES_DATA only), then there would be no need to lock the table as the RENAME command does.



20 March, 2019

Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS
------------------------------ --- --------
SALES_DATA_PK                  NO  VALID
SALES_DATA_LCL_NDX_1           YES N/A

SQL> 


I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL>
SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    YES
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 


The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
  1  select owner, job_name, last_start_date, next_run_Date
  2  from dba_scheduler_jobs
  3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

OWNER
---------------------------------------------------------------------------
JOB_NAME
---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SYS
PMO_DEFERRED_GIDX_MAINT_JOB
20-MAR-19 10.18.51.215433 AM UTC
21-MAR-19 02.00.00.223589 AM UTC


SQL> !date
Wed Mar 20 20:05:24 SGT 2019

SQL> 


So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
  2  from user_indexes
  3  where table_name = 'SALES_DATA'
  4  order by 2,1
  5  /

INDEX_NAME                     PAR STATUS   ORP
------------------------------ --- -------- ---
SALES_DATA_PK                  NO  VALID    NO
SALES_DATA_LCL_NDX_1           YES N/A      NO

SQL> 


Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.


Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.