Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dropping large tablespace and datafiles

erskwestMay 12 2009 — edited May 13 2009
Oracle 9.2.0.4, AIX 5.2

As part of some database reorg work, I need to drop a large tablespace (approx. 40 GB) and its datafiles (20 files @ 2 GB per). The process I have used in testing is:

SQLPLUS> alter tablespace XXXXX offline;
SQLPLUS> drop tablespace XXXXX including contents and datafiles;

Because of the size of the tablespace, the drop command is taking about 4 1/2 hours to complete.

My question is - what is the consequence of doing the following?

SQLPLUS> alter tablespace XXXXX offline;
AIX> rm /*/oradata/dbxx/XXXXX*.dbf
SQLPLUS> drop tablespace XXXXX including contents and datafiles;

I have done some testing with this, and although Oracle fusses about the datafiles being missing, all of the error messages in the alert log appear to be related to the delete part of the command - ORA-27037 and ORA-01259. I received a completed result to the command, and it let me recreate the tablespace with new datafiles and parameters. Doing it this way, the drop only takes a few minutes.

Are there any problems that I am missing?

Thanks in advance,
Kathleen
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 10 2009
Added on May 12 2009
5 comments
1,438 views