Database Administration (MOSC)

MOSC Banner

Drop tablespace and reuse datafile to new tablespace

edited Jan 17, 2020 10:10AM in Database Administration (MOSC) 17 commentsAnswered

Hi,

I plan to drop one of the empty tablespace and assign to new tablespace let call 'TBS_NEW'.

May i know how should i proceed.

Oracle 9i ( 9.2.0.6 ) version

SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files

  2  where TABLESPACE_NAME='YMS_TBS';

FILE_NAME                                          TABLESPACE_NAME

-------------------------------------------------- ---------------

/u00/oradata/ODSDWPD/yms_tbs_001.dbf               YMS_TBS

/u00/oradata/ODSDWPD/yms_tbs_002.dbf               YMS_TBS

/u00/oradata/ODSDWPD/yms_tbs_003.dbf               YMS_TBS

/u00/oradata/ODSDWPD/yms_tbs_004.dbf               YMS_TBS

/u00/oradata/ODSDWPD/yms_tbs_005.dbf               YMS_TBS

SQL>

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where TABLESPACE_NAME='YMS_TBS';

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center