-
DB files
Gals/girls.
I messed up and I hope there's some ancient DBA secret that I don't know about to fix it, hence the question before the community.
Here's the scenario....
I'm in my database cleaning up some tablespaces that are no longer required. Since Oracle won't remove the datafile(s) associated with the tablespace, once you remove the tablespace you have to go into the OS and rm the datafile, and that's exactly what I was doing. However... during my datafile removal I got a little to presumptuous and deleted a datafile that was still being used by an existing tablespace. Even though the datafile I deleted was going to get blown away, I did the removal backwards therefore causing the tablespace to freak out. Now, since the datafile is gone, Oracle won't let me blow the tablespace away because he can't get a lock on the datafile.
Does anyone know of a way to remedy this situation other than backing the controlfile to trace, editing the file to remove said tablespace and associated datafile(s), shutdown the database, blow away existing controlfiles, crank database back up with edited controlfile?
Thanks!
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
That's a classic "media failure", so you could restore the file . . . though the control file method could be quicker.
In 9i you can drop a ts INCLUDING CONTENTS AND DATAFILES
Of course, some OS don't let you delete a file that's in use . . . .
-
Re: DB files
Originally posted by OracleDoc
Now, since the datafile is gone, Oracle won't let me blow the tablespace away because he can't get a lock on the datafile.
r u sure ??
Code:
sys@DELDB.WORLD> create tablespace del_me datafile '/perfdb2/oradata/DELDB/del_me_01.dbf' size 10M;
sys@DELDB.WORLD> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/perfdb2/oradata/DELDB/system01.dbf
/perfdb2/oradata/DELDB/undotbs01.dbf
/perfdb2/oradata/DELDB/indx01.dbf
/perfdb2/oradata/DELDB/tools01.dbf
/perfdb2/oradata/DELDB/users01.dbf
/perfdb2/oradata/DELDB/del_me_01.dbf
6 rows selected.
oracle(DELDB):/perfdb2/oradata/DELDB>
> ls
del_me_01.dbf redo01.log redo03.log temp01.dbf undotbs01.dbf
indx01.dbf redo02.log system01.dbf tools01.dbf users01.dbf
oracle(DELDB):/perfdb2/oradata/DELDB>
> mv del_me_01.dbf del_me_01.dbf.old
oracle(DELDB):/perfdb2/oradata/DELDB>
> ls
del_me_01.dbf.old redo01.log redo03.log temp01.dbf undotbs01.dbf
indx01.dbf redo02.log system01.dbf tools01.dbf users01.dbf
sys@DELDB.WORLD> drop tablespace del_me;
Tablespace dropped.
sys@DELDB.WORLD> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/perfdb2/oradata/DELDB/system01.dbf
/perfdb2/oradata/DELDB/undotbs01.dbf
/perfdb2/oradata/DELDB/indx01.dbf
/perfdb2/oradata/DELDB/tools01.dbf
/perfdb2/oradata/DELDB/users01.dbf
sys@DELDB.WORLD>
This was on Unix... Oracle9i
Amar
"There is a difference between knowing the path and walking the path."
-
What i will do in this case is
1.Recreate the user tablespace.If all the objects in the tablespace can be re-created (recent export is available, tables can be re-populated using scripts or through SQL*Loader)Then, offline drop the datafile, drop the tablespace with including contents option.
2.Restore file from backup and do a media recovery.Then u can drop the tablespace as per the normal procedure.
Another useful link is
http://metalink.oracle.com/metalink/...atabase_id=NOT
Last edited by akhadar; 07-13-2004 at 04:47 AM.
-
LOL good Lord akhadar that's a lot of work...
Ok, I forgot to mention it's 8i and on Sun. Adewri, I did exactly that and it still wouldn't do drop it. I'm think'n it's because I'm on 8i.
Oracle it's not just a database it's a lifestyle!
--------------
BTW....You need to get a girlfriend who's last name isn't .jpg
-
alter database datafile 'XXX' offline drop;
drop tablespace xxx;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|