Not able to drop tablespace [message #348174] |
Mon, 15 September 2008 21:14 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Hi Experts,
I created one tablespace.After creating tablspace I dropped the datafile on operating system.I was supposed to
execute drop tablespace including contents and datafiles.
Somehow I dropped the datafile prior to give this command.
Now I am not able to drop tablespace.
Can you give any idea how to drop the tablespace as I have to recreate tablespace
with same name.
Regards,
Varun Punj
|
|
|
|
Re: Not able to drop tablespace [message #348181 is a reply to message #348176] |
Mon, 15 September 2008 22:49 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
I created one tablespace.
After creating tablspace I dropped the datafile on operating system.
I was supposed to execute drop tablespace including contents and datafiles.
Somehow I dropped the datafile prior to give this command.
Now I am not able to drop tablespace.
Can you give any idea how to drop the tablespace
as I have to recreate tablespace with same name. .
Please let me know if I am
still missing something
|
|
|
Re: Not able to drop tablespace [message #348182 is a reply to message #348174] |
Mon, 15 September 2008 22:54 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Practice
Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.
If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:
SQL> insert into t2 values (t2_s.nextval, x1) returning id into :x2;
insert into t2 values (t2_s.nextval, x1) returning id into :x2
*
ERROR at line 1:
ORA-00984: column not allowed here
You choose to ignore posting guideline.
I choose to ignore your question.
You're On Your Own (YOYO)!
[Updated on: Mon, 15 September 2008 22:56] by Moderator Report message to a moderator
|
|
|
Re: Not able to drop tablespace [message #348188 is a reply to message #348182] |
Mon, 15 September 2008 23:26 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Hi,
I had given the following command to create tablespace:-
create tablespace sitecom_d01 datafile
'/data3/oradata/sc/sitecom_d01.dbf' reuse
autoexend on extent management local;
Then I deleted sitecom_d01.dbf from operating system.
Now I am trying to drop the tablespace and it is giving
me the following error:-
ORA-01116: error in opening database file 12
ORA-01110: data file 12: '/data3/oradata/sc/SITECOM_D01.dbf'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
How can I drop the tablespace now
Regards,
Varun Punj,
|
|
|
Re: Not able to drop tablespace [message #348190 is a reply to message #348174] |
Mon, 15 September 2008 23:32 |
varunvir
Messages: 389 Registered: November 2007
|
Senior Member |
|
|
Hi Ana,
I got the answer.I am posting what I tried:-
alter database datafile 'datafile path' offline drop
drop tablespace tablespace_name including contents. Regards,
Varun Punj
|
|
|
Re: Not able to drop tablespace [message #348191 is a reply to message #348174] |
Mon, 15 September 2008 23:36 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I am sorry to see that GOOGLE is broken for you.
Results 1 - 10 of about 8,880 for oracle drop tablespace file missing
HOW TO DROP TABLESPACE if datafile is missing [Archive] - dBforums Feb 10, 2004 ... [Archive] HOW TO DROP TABLESPACE if datafile is missing Oracle. ... HOW CAN I DROP TABLESPACE IF A DATAFILE IS MISSING ...
www.dbforums.com/archive/index.php/t-982598.html - 6k - Cached - Similar pages - Note this
|
|
|