Dropping large tablespace and datafiles
erskwestMay 12 2009 — edited May 13 2009Oracle 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