User Tools

Site Tools


database:oracle:drop-tablespace

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