OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Regarding Datafile

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

Regarding Datafile [message #161570]

Mon, 06 March 2006 01:41

frank.svs
Messages: 162
Registered: February 2006

Senior Member

Hi ,
How to drop a datafile of a particular tablespace.

I've a tablespace USERS for which it has 2 datafiles associated with iti.e users01.dbf and users02.dbf . Now i want to drop
users02.dbf datafile. i said,

SQL>alter database datafile 11 offline drop;
Database Altered.

But physically it is not removed and moreover the entry is still exists in the DICTionary tables. Why.What could be reason behind it. I want to drop that datafile at OS level also.

I am working on windows OS and Oracle 9.2.0.1.0 version

Thank Q.

Report message to a moderator

Re: Regarding Datafile [message #161575 is a reply to message #161570]

Mon, 06 March 2006 02:05

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You need to drop the tablespace.
OFFLINE DROP is just a step before dropping the tablespace.
You directly drop the tablespace including contents and datafiles
or
Do it step by step. (OFFLINE drop of datafiles and then drop tablespace). Even with a tablespace drop, not all information is completely removed from all the dictionary views/tables.
Please see here
http://www.orafaq.com/forum/t/59814/42800/

Report message to a moderator

Re: Regarding Datafile [message #161580 is a reply to message #161575]

Mon, 06 March 2006 02:19

girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)

Senior Member

Another useful article:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:515222144417

--Girish

Report message to a moderator

Previous Topic:	unlimited tablespace
Next Topic:	user creation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 14:16:51 CDT 2024