Home » RDBMS Server » Server Administration » how to drop datafile (10.2.0.1)
how to drop datafile [message #555636] Fri, 25 May 2012 22:56 Go to next message
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
Re: how to drop datafile [message #555637 is a reply to message #555636] Fri, 25 May 2012 23:21 Go to previous messageGo to next message
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
Re: how to drop datafile [message #555638 is a reply to message #555636] Fri, 25 May 2012 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again PLEASE read the documentation BEFORE using a command and posting a question here.

Regards
Michel
Re: how to drop datafile [message #555655 is a reply to message #555638] Sat, 26 May 2012 10:25 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks,
BlackSwan and Michel!
Re: how to drop datafile [message #559221 is a reply to message #555655] Fri, 29 June 2012 21:08 Go to previous messageGo to next message
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
Re: how to drop datafile [message #559224 is a reply to message #559221] Sat, 30 June 2012 00:22 Go to previous message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Would like to know, how to find the size of individual folder in ASM 10g !
Next Topic: roles and permission
Goto Forum:
  


Current Time: Tue Mar 19 06:34:58 CDT 2024