Re: extending tablespace TEMP

Hi,

>>If there was no data in the datafile - and thats a pretty big if - you might
>>get away with 'alter database datafile 'your file' offline drop;'

  I think most people are confused that we a data file is being added to a tablespace, it will remain with it forever. There is no easy such as the drop that you can use for views, tables, indexes etc.

  Another thing is the 'Alter database datafile offline drop' is meant for when you intend to drop the tablespace including contents. Simply offline drop without dropping the tablespace itself will cause Ora-01147 at startup.

  Here is my two lepta:

  If you are running in Noarchivelog mode

1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline drop
3. open the database - alter database open
4. check all objects belong to that tablespace: select owner, segment_name, segment_type from dba_segments where tablespace_name='tbs_name'
5. export out all the objects in that tablespace
6. drop the tablespace - drop tablespace tbs_name including contents
7. Delete the physical datafiles belonging to the tablespace
8. Recreate the tablespace, import back the objects

If you are running in Archivelog mode

1. mount the database - startup mount
2. drop the datafile - alter database datafile xxx offline (Note: the datafile is still part of the database and is marked only as offline in the controlfile. Just make sure you don't use the same data file name again)
3. Remove the physical data file at OS level
4. open the database - alter database open
5. At the right time, you can export the objects belong to this tablespace, drop the tablespace, create back the tablespace with appropriate datafiles and import the objects back.

Of course dropping a datafile Oracle 9i is a different story I believe.

Allan W. Tham
DBA Received on Tue Dec 18 2001 - 21:14:02 CST