OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » OFFINE DROP (OFFINE DROP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

OFFINE DROP [message #464716]

Fri, 09 July 2010 00:38

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

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,

Report message to a moderator

Re: OFFINE DROP [message #464722 is a reply to message #464716]

Fri, 09 July 2010 01:22

John Watson
Messages: 8960
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?

Report message to a moderator

Re: OFFINE DROP [message #464725 is a reply to message #464722]

Fri, 09 July 2010 01:28

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

Hi
DATABASE ALTERED.

i had tried your QUERY nothng happened still tablespace is there

Report message to a moderator

Re: OFFINE DROP [message #464726 is a reply to message #464716]

Fri, 09 July 2010 01:29

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

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 #464746 is a reply to message #464741]

Fri, 09 July 2010 02:59

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

How "offlining" a file drops a tablespace?
Do as I said in my previous post: use "drop tablespace".

Regards
Michel

Report message to a moderator

Re: OFFINE DROP [message #464781 is a reply to message #464746]

Fri, 09 July 2010 07:58

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

Hi Micheal

SQL> conn sys/ace123@orcl as sysdba;

Connected.

SQL> drop tablespace TBS_ACEIND including contents and datafiles;

drop tablespace TBS_ACEIND including contents and datafiles

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key

===========================================================

when i use
drop tablespace command
the above error is thrown

Report message to a moderator

Re: OFFINE DROP [message #464783 is a reply to message #464781]

Fri, 09 July 2010 08:10

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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 #464785 is a reply to message #464783]

Fri, 09 July 2010 08:19

maniatniit
Messages: 49
Registered: June 2010
Location: Mumbai

Member

no Index is there as if there is no user.

my oracle version is 10.2.0.4.0.

Report message to a moderator

Re: OFFINE DROP [message #464788 is a reply to message #464785]

Fri, 09 July 2010 08:26

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Previous Topic:	Weird issue with ORA-30036
Next Topic:	Oracle Processes crashing on VMS -- Critical

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 13:06:22 CST 2024