OFFINE DROP [message #464716] |
Fri, 09 July 2010 00:38 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
maniatniit
Messages: 49 Registered: June 2010 Location: Mumbai
|
Member |
![maniatniit@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
Hello All
I had Offline drop a tablespace
by following command
ALTER DATABSE DATAFILE 'D:\ORADATA\MANISH.dbf' offline drop;
physically the file is not there
when i checked logically
select tablespace_name, file_name from dba_data_files where tablespace_name='Manish';
the entry is there
can u suggest how to drop tablespace logically
Thanks,
|
|
|
Re: OFFINE DROP [message #464722 is a reply to message #464716] |
Fri, 09 July 2010 01:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Did your DROP command,
Quote:ALTER DATABSE DATAFILE 'D:\ORADATA\MANISH.dbf' offline drop; fail, because of the typing error?
|
|
|
|
Re: OFFINE DROP [message #464726 is a reply to message #464716] |
Fri, 09 July 2010 01:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:the entry is there
Expected behaviour.
Quote:can u suggest how to drop tablespace logically
Drop tablespace ...
Please read OraFAQ Forum Guide and don't use IM speak but use SQL*Plus and copy and paste your session instead of trying to explain what you did and saw.
Also always post your Oracle version (with 4 decimals).
Regards
Michel
[Updated on: Fri, 09 July 2010 01:29] Report message to a moderator
|
|
|
Re: OFFINE DROP [message #464741 is a reply to message #464726] |
Fri, 09 July 2010 02:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
maniatniit
Messages: 49 Registered: June 2010 Location: Mumbai
|
Member |
![maniatniit@gmail.com](/forum/theme/orafaq/images/google.png)
|
|
This is a Session
SQL> conn sys/ace123@orcl[/email] as sysdba;
Connected.
SQL> alter database datafile 'F:\ORADATA\TBS_ACEIND.dbf' OFFLINE DROP
2 /
Database altered.
SQL> connect SYS/ace123@ORCL[/email] as sysdba
Connected.
SQL> CREATE TABLESPACE TBS_ACEIND
2 DATAFILE 'F:\ORADATA\TBS_ACEIND.dbf' SIZE 800M
3 AUTOEXTEND ON NEXT 500K MAXSIZE UNLIMITED
4 ONLINE EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE TBS_ACEIND
*
ERROR at line 1:
ORA-01543: tablespace 'TBS_ACEIND' already exists
====================================================
Can u tell me sollution
Thanks
[Updated on: Fri, 09 July 2010 02:53] Report message to a moderator
|
|
|
|
|
Re: OFFINE DROP [message #464783 is a reply to message #464781] |
Fri, 09 July 2010 08:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
ORA-02429: cannot drop index used for enforcement of unique/primary key
*Cause: user attempted to drop an index that is being used as the
enforcement mechanism for unique or primary key.
*Action: drop the constraint instead of the index.
Maybe you should have a look at Error Messages before posting.
You must first drop the constraints that uses the indexes in your tablespace.
Quote:Also always post your Oracle version (with 4 decimals).
Regards
Michel
[Updated on: Fri, 09 July 2010 08:10] Report message to a moderator
|
|
|
|
Re: OFFINE DROP [message #464788 is a reply to message #464785] |
Fri, 09 July 2010 08:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:no Index is there as if there is no user.
I tend to trust Oracle.
Post
select segment_type, count(*) from dba_segments where tablespace_name = 'TBS_ACEIND' group by segment_type;
Regards
Michel
[Updated on: Fri, 09 July 2010 08:26] Report message to a moderator
|
|
|