Drop Obsolete Tablespaces and Datafiles

===================

General

===================

Drop Obsolete Tablespaces and Datafiles

In case table is partitioned, and a TBS is assigned to each partition, it might be that there are obsolete TBS which are assigned to already dropped partitions

Step 1 - Identify Obsolete Tablespaces

Step 2 - Drop Obsolete Tablespaces and Datafiles

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

===================

Code by Example

===================

Step 1 - Identify Obsolete Tablespaces

SELECT 'DROP TABLESPACE '||obsolete_tbs||' INCLUDING CONTENTS AND DATAFILES;'

FROM (

 SELECT DBA_DATA_FILES.tablespace_name as obsolete_tbs, 

        DBA_DATA_FILES.file_name as obsolete_data_file,

        ROUND(DBA_DATA_FILES.bytes/1024/1024) as Mb

 FROM (

  SELECT tablespace_name as tablespace_name 

    FROM DBA_DATA_FILES 

   WHERE 1=1

     AND file_name LIKE '%20%'

     AND tablespace_name NOT LIKE '%YEARLY%'

  MINUS 

  SELECT DISTINCT  tablespace_name 

   FROM DBA_SEGMENTS

 ) OBSOLETE_TBS_LIST,

   DBA_DATA_FILES

 WHERE OBSOLETE_TBS_LIST.tablespace_name =

       DBA_DATA_FILES.tablespace_name

);

Step 2 - Drop Obsolete Tablespaces and Datafiles

DROP TABLESPACE DWNG_REP_MONTHLY_201701 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE DWNG_REP_MONTHLY_201702 INCLUDING CONTENTS AND DATAFILES;

etc...

Step 3 - Identify datafiles that are assigned to non existing tablespaces.

SELECT * 

  FROM DBA_DATA_FILES 

 WHERE file_name LIKE '%DWNG_REP_MONTHLY_2016%' 

ORDER BY file_name;

ls -ltr | grep 2016

rw-r----- 1 oracle dba     2105344 Dec  6 08:43 DWNG_DAILY_TB_MONTHLY_201601_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:43 DWNG_DAILY_TB_MONTHLY_201602_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201603_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201605_1.dbf

-rw-r----- 1 oracle dba     2105344 Dec  6 08:44 DWNG_DAILY_TB_MONTHLY_201604_1.dbf

In case the files from OS do not exist in DB - simply from these files with OS command - as there were assigned to a dropped tablespace.

Error ORA-14405: partitioned index contains partitions in a different tablespace

In case of error ORA-14405 need to find which objects use the same tablespace.

DROP TABLESPACE DWNG_REP_MONTHLY_201901 INCLUDING CONTENTS AND DATAFILES

ERROR at line 1:

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT table_owner,

       table_name,

       partition_name,

       tablespace_name,

       DBA_TAB_PARTITIONS.*

  FROM DBA_TAB_PARTITIONS

 WHERE (table_owner, table_name) IN (

   SELECT table_owner, table_name

    FROM dba_tab_partitions DTB

   WHERE     DTB.tablespace_name = 'DWNG_REP_MONTHLY_201901'

     AND EXISTS

        (SELECT *

           FROM DBA_TAB_PARTITIONS DTB_INNER

          WHERE DTB.table_owner = DTB_INNER.table_owner

            AND DTB.table_name = DTB_INNER.table_name

            AND DTB_INNER.tablespace_name <>  'DWNG_REP_MONTHLY_201901'

    )

      GROUP BY table_owner, table_name)

ORDER BY 1, 2, partition_position;

SOME_USER REP_OUT_DAILY_SMS_TRANSACTIONS

SOME_USER REP_IN_DAILY_SMS_TRANSACTIONS

MY_USER   REP_OUT_DAILY_TRANSACTIONS

MY_USER   REP_IN_DAILY_DN_TRANSACTIONS

===================

Check Tablespace Usage

===================

SELECT 'TABLE' AS type, COUNT(*) AS segments

  FROM DBA_TABLES T

WHERE T.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL  

SELECT  'INDEX' AS type, COUNT(*) AS segments

  FROM DBA_INDEXES I

WHERE I.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL     

SELECT 'LOB' as type, COUNT(*) AS segments

  FROM DBA_LOBS L

WHERE L.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'TABLE_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_TAB_PARTITIONS TP

WHERE TP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'INDEX_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_IND_PARTITIONS IP

WHERE IP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106'

UNION ALL

SELECT 'LOB_PARTITIONS' as type, COUNT(*) as segments

  FROM DBA_LOB_PARTITIONS LP

WHERE LP.TABLESPACE_NAME = 'DWNG_REP_MONTHLY_202106';

ORA-14404 and ORA-14405 When dropping a partition

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14404: partitioned table contains partitions in a different tablespace

To solve this error, need to free move these objects to a different tablespace.

CREATE TABLESPACE CDROMETER_TEST_TBS DATAFILE '/oracle_db/db1/db_igt/cdrometer_test_data_01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 10000M;

To generate the SQL:

SELECT 'ALTER TABLE '||TABLE_OWNER ||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE NEW_TABLESPACE_NAME UPDATE INDEXES;' 

FROM DBA_TAB_PARTITIONS 

WHERE tablespace_name = 'CDROMETER_DATA_HIST_202107';

Actual SQL:

ALTER TABLE CDROMETER_TEST.CDROMETER_DATA_HISTORY MOVE PARTITION P_202107 TABLESPACE CDROMETER_TEST_TBS UPDATE INDEXES;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

ORA-14405: partitioned index contains partitions in a different tablespace

SELECT COUNT(*) FROM DBA_SEGMENTS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

  COUNT(*)

----------

         0

SELECT COUNT(*) FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

 

  COUNT(*)

----------

         3

To generate the SQL:

SELECT 'ALTER INDEX '||index_owner||'.'||index_name||' REBUILD PARTITION '||partition_name||' TABLESPACE CDROMETER_TEST_TBS;' FROM DBA_IND_PARTITIONS WHERE tablespace_name IN ('CDROMETER_DATA_HIST_202107');

ALTER INDEX CDROMETER_TEST.CDROMETER_DATA_HISTORY_A2 REBUILD PARTITION P_202107  TABLESPACE CDROMETER_TEST_TBS;

DROP TABLESPACE CDROMETER_DATA_HIST_202107 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped

Checks before dropping tablespace

SELECT * FROM DBA_SEGMENTS       WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_IND_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_TAB_PARTITIONS WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

SELECT * FROM DBA_DATA_FILES     WHERE tablespace_name LIKE 'CDROMETER_DATA_HIST_2021%'

DROP TABLESPACE CDROMETER_DATA_HIST_202109 INCLUDING CONTENTS AND DATAFILES;