To reclaim the free space

Method 1.
To reclaim the free space you can Move the table to the tablespace that it currently belongs, ie. Doing nothing but just moving it to itself.

This can be only done if it is an LMT (locally managed tablespace), we tend to move the data to the "front" of the file (allowing you to shrink the file)
____ ____ ____ ____
If you are not using LMTs then try 'alter tablespace tblspc-name coalesce'


Method 2

1) delete million rows from table A

2) create table B as select * from table A

3) truncate table A

4) insert into table A select * from table B

5) drop table B

6) rebuild indexes


1) alter table (tablename) move;
2) rebuild all the UNUSABLE indexes on the table.

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