# DROPPING TABLESPACE AFTER DATAFILES HAVE BEEN REMOVED
How to drop a tablespace when the datafile has already been removed from
the
OS....
SQL> select * from v$recover_file;
no rows selected
SQL> select * from dba_data_files where tablespace_name =
'SHP_SUMMARY_INDEX';
FILE_NAME
------------------------------------------------------------------------
----
--------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
--------- ------------------------------ --------- --------- ---------
/u11/oradata/whse/summary_index01.dbf
48 SHP_SUMMARY_INDEX 38797312 2368 AVAILABLE
SQL> ALTER DATABASE DATAFILE '/u11/oradata/whse/summary_index01.dbf'
OFFLINE
DROP;
Database altered.
SQL> ALTER TABLESPACE SHP_SUMMARY_INDEX OFFLINE;
ALTER TABLESPACE SHP_SUMMARY_INDEX OFFLINE
*
ERROR at line 1:
ORA-01191: file 48 is already offline - cannot do a normal offline
ORA-01110: data file 48: '/u11/oradata/whse/summary_index01.dbf'
SQL> ALTER TABLESPACE SHP_SUMMARY_INDEX OFFLINE IMMEDIATE;
Tablespace altered.
SQL> DROP TABLESPACE SHP_SUMMARY_INDEX;
Tablespace dropped.
SQL>
If the datafile is in recovery status, the instance will need to be
brought up in mount mode to
drop the datafile. Then open the database and drop the tablespace
1. From a server manager session stop the instance restart in mount
mode.
SVRMGRL> shutdown immediate
:
SVRMGRL> startup mount
2. From the server manager session, alter the datafiles offline and
drop.
SVRMGRL> alter database datafile '[full pathed datafile name]' offline
drop;
3. Open the database
SVRMGRL> alter database open;
4. Drop the tablespace
SVRMGRL> drop tablespace [target tablespace];
-----Original Message-----
From: Abdul Wahab Ansari
[mailto:oracledba-ezmlmshield-x64371139.[Email address protected]
Sent: Sunday, April 02, 2006 6:31 AM
To: LazyDBA Discussion
Subject: datafile dropped
Hello
One programmer misused his rights and dropped a datafile without
dropping the tablespace. How can we drop the tablespace
Now?
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
------------------------------------------------------------------------------
This email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else, unless expressly approved by the sender or an authorized addressee, is unauthorized.
If you are not the intended recipient, any disclosure, copying, distribution or any action omitted or taken in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email in error, please contact the sender, delete this e-mail and destroy all copies.
==============================================================================
Oracle LazyDBA home page