database:oracle:drop-tablespace
Table of Contents
Completely drop a tablespace and all the associated data files
Check data files location
First, note all the datafiles associated with your tablespace:
SELECT DECODE(r, 1, tablespace_name, NULL) tablespace_name, file_name FROM (SELECT tablespace_name, file_name, rank() OVER (partition BY tablespace_name ORDER BY tablespace_name, file_name) r FROM dba_data_files ORDER BY tablespace_name, file_name )
Result:
The ST_SERVERLOG tablespace has 2 data files:
- /opt/oracle/oradata/ORACLE19C/datafile/o1_mf_st_serve_js7jhf9y_.dbf
- /opt/oracle/oradata/ORACLE19C/datafile/o1_mf_st_serve_js7jqjb3_.dbf
Remove tablespace
DROP TABLESPACE ST_SERVERLOG including contents;
This will not remove the data files from the disk, so the space will remain occupied.
Drop data files
Manually go on the disk and remove the associated data files. If those files are not in use by any tablespace, the Oracle will let you remove them.
database/oracle/drop-tablespace.txt · Last modified: 2023/07/04 19:36 by 127.0.0.1