Home » RDBMS Server » Server Administration » Not able to drop tablespace (10.2.0.2)
Not able to drop tablespace [message #348174] Mon, 15 September 2008 21:14 Go to next message
varunvir
Messages: 363
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 #348176 is a reply to message #348174] Mon, 15 September 2008 21:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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


You choose to ignore posting guideline.
I choose to ignore your question.

You're On Your Own (YOYO)!
Re: Not able to drop tablespace [message #348181 is a reply to message #348176] Mon, 15 September 2008 22:49 Go to previous messageGo to next message
varunvir
Messages: 363
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 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 Go to previous messageGo to next message
varunvir
Messages: 363
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 Go to previous messageGo to next message
varunvir
Messages: 363
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 Go to previous message
BlackSwan
Messages: 25036
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

Previous Topic: Error when adding partitions to already partitioned table
Next Topic: ASM Migration
Goto Forum:
  


Current Time: Sat Dec 03 22:09:55 CST 2016

Total time taken to generate the page: 0.12883 seconds