Converting Gloabal Indexes into Local Indexes

Rem ******************************************
Rem IF THE INDEX IS PARTIONED, THEN IT'S LOCAL
Rem ******************************************

select index_type, partitioned, count(*) from dba_indexes group by index_type, partitioned;
select index_name, index_type, table_owner, table_name, partitioned from dba_indexes;
____________________________________________________________________


STEP 1. Drop CONSTRAINT

SQL> alter table P_NBSC_RES_ACCESS_HR drop constraint P_NBSC_RES_ACCESS_HR_PK;
------------------------------------------------------------------------------

STEP 2. Drop INDEX

SQL> DROP INDEX P_NBSC_RES_ACCESS_HR_PK;
----
Use the following command to verify if the index has been dropped.
----
SQL> select index_name, index_type, partitioned, table_name from dba_indexes where table_name like '%P_NBSC_RES_ACCESS_HR';

no rows selected
------------------------------------------------------------------------------------------------

Step 3. Create Local Index

SQL> CREATE INDEX P_NBSC_RES_ACCESS_HR_PK ON P_NBSC_RES_ACCESS_HR
(DATETIME, BSC, SEGMENT_ID) TABLESPACE NOK_P_NBSC_RES_ACCESS_AGG_I LOCAL NOLOGGING
PARALLEL;
----
Use the syntax given below to verfiy the type of index and its creation.
----
SQL> select index_name, index_type, partitioned, table_name from dba_indexes where table_name like '%P_NBSC_RES_ACCESS_HR';

INDEX_NAME                     INDEX_TYPE                  PAR TABLE_NAME
------------------------------ --------------------------- --- ------------------------------
P_NBSC_RES_ACCESS_HR_PK        NORMAL                      YES P_NBSC_RES_ACCESS_HR