OraFAQ Forum: Server Administration » how to drop datafile

Home » RDBMS Server » Server Administration » how to drop datafile (10.2.0.1)

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

how to drop datafile [message #555636]

Fri, 25 May 2012 22:56

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
I can not drop datafile in a tablespace,how can i do?


SQL> Alter Database Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf'
Offline  2  
  3  /

Database altered.

SQL> Alter Tablespace tps_hxl
Drop Datafile '/u02/app/oracle/oradata/oracl/hxl06.dbf';  2  
Alter Tablespace tps_hxl
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

Report message to a moderator

Re: how to drop datafile [message #555637 is a reply to message #555636]

Fri, 25 May 2012 23:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

bcm@bcm-laptop:~$ oerr ora 3264
03264, 00000, "cannot drop offline datafile of locally managed tablespace"
// *Cause: Trying to drop offline datafile in lmts
// *Action: Try to drop file afetr making it online

Report message to a moderator

Re: how to drop datafile [message #555638 is a reply to message #555636]

Fri, 25 May 2012 23:50

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

Senior Member
Account Moderator

Once again PLEASE read the documentation BEFORE using a command and posting a question here.

Regards
Michel

Report message to a moderator

Re: how to drop datafile [message #555655 is a reply to message #555638]

Sat, 26 May 2012 10:25

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks,
BlackSwan and Michel!

Report message to a moderator

Re: how to drop datafile [message #559221 is a reply to message #555655]

Fri, 29 June 2012 21:08

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

You can drop "Empty" datafiles with the following.

ECSCDAD3 > create tablespace alan datafile '/u02/app/oracle/oradata/CSCDAD/alan_01.dbf' size 1m;

Tablespace created.

ECSCDAD3 > alter tablespace alan add datafile '/u02/app/oracle/oradata/CSCDAD/alan_02.dbf' size 1m;

Tablespace altered.

ECSCDAD3 > alter tablespace alan drop datafile '/u02/app/oracle/oradata/CSCDAD/alan_02.dbf';

Tablespace altered.

And you can drop empty tablespaces with the following.

ECSCDAD3 > drop tablespace alan;

Tablespace dropped.

You can tell which tablespaces are empty from dba_segments and you can tell which datafiles from empty from joining DBA_DATA_FILES.file_id with DBA_EXTENTS.file_id

Report message to a moderator

Re: how to drop datafile [message #559224 is a reply to message #559221]

Sat, 30 June 2012 00:22

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

Senior Member
Account Moderator

Please do NOT feed the lazy.
The purpose of this site is to help people learn NOT to spoonfeed and give basic solutions.
If you want you can provide a link to the documentation.

Regards
Michel

Report message to a moderator

Previous Topic:	Would like to know, how to find the size of individual folder in ASM 10g !
Next Topic:	roles and permission

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:47:19 CST 2024