===================
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;
