DBA Hub

📋Steps in this guide1/3

How to Drop Empty Tablespace?

SQL> select 'ALTER TABLESPACE ' || name || ' OFFLINE;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from

oracle configurationintermediate
by OracleDba
13 views
1

Prepare Offline Statements :

Code/Command (click line numbers to comment):

1
SQL> select 'ALTER TABLESPACE ' || name || ' OFFLINE;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') order by 1;
2

Exclude Reserved Tablespaces

Code/Command (click line numbers to comment):

1
SQL> select 'ALTER TABLESPACE ' || name || ' OFFLINE;' stmt from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY') and name not in ('RESERVED_TBS1', 'RESERVED_TBS2') order by 1;
3

Check Status

Code/Command (click line numbers to comment):

1
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name in (select name tablespace_name from v$tablespace where ts# > 6 and name not in (select distinct tablespace_name from dba_segments) and name not in (select tablespace_name from dba_tablespaces where contents = 'TEMPORARY'));

Comments (0)

Please to add comments

No comments yet. Be the first to comment!