OraFAQ Forum: Server Administration » How to delete datafile if Tablespace contains 2 datafiles

Home » RDBMS Server » Server Administration » How to delete datafile if Tablespace contains 2 datafiles

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

How to delete datafile if Tablespace contains 2 datafiles [message #137885]

Sun, 18 September 2005 09:44

dnvs_praveen
Messages: 47
Registered: July 2005
Location: bangalore

Member

Hi

I know that my tablespace is having 2 datafiles.

If i give the statement 'alter database datafile '/home/oracle/newdb2/scotttss.dbf' offline drop' still datafile is presenting in the location itself and also when i give the statement 'select tablespace_name, file_name from dba_data_files' i can able to see the filename.

If i want to delete this datafile from dictionary and also from disk, then what statement i should give.

Before giving how to check in this particular datafile, any data presented or not?

Praveen

Report message to a moderator

Re: How to delete datafile if Tablespace contains 2 datafiles [message #137891 is a reply to message #137885]

Sun, 18 September 2005 12:27

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

You cannot - you need to drop the entire tablespace. After that, delete the files from disk manually.

Best regards.

Frank

Report message to a moderator

Re: How to delete datafile if Tablespace contains 2 datafiles [message #137892 is a reply to message #137891]

Sun, 18 September 2005 12:30

dnvs_praveen
Messages: 47
Registered: July 2005
Location: bangalore

Member

How to find in a particular datafile what tables data is existing?

What is the query to find it? Plz let me know

Praveen

Report message to a moderator

Re: How to delete datafile if Tablespace contains 2 datafiles [message #137897 is a reply to message #137892]

Sun, 18 September 2005 13:08

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Query the dba_extents view. Example:

SQL> SELECT file_name FROM dba_extents e, dba_data_files f
  2  WHERE segment_name = 'DUAL'
  3    AND f.file_id = e.file_id
  4  /

FILE_NAME
--------------------------------------------------------------------------------
/dev/rfb_system_01

Best regards.

Frank

Report message to a moderator

Re: How to delete datafile if Tablespace contains 2 datafiles [message #140080 is a reply to message #137885]

Fri, 30 September 2005 14:15

AnalystParth
Messages: 15
Registered: September 2005
Location: India

Junior Member

Praveen, unfortunately dropping datafile in oracle is a messy affair.

Not only will it let you drop it, but depending upon the oracle version you can notice some wierd behaviours such as:
1. Losing data along with the datafile.
2. Tables trying to extend into the already dropped datafile and throwing out an error.
3. applications going into either a hang or dumping internal errors intermittently.

The typical recommended procedure, like Frank mentioned is to drop and recreate the tablespace. But since you may not want to lose the information in the tablespace before dropping it, take an export backup of the tablespace info, drop the tablespace, recreate tablespace and import back.

--Parth

Report message to a moderator

Previous Topic:	Installing Problem with Oracle 8i.1.7
Next Topic:	ORA-03113: end-of-file on communication channel

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 25 08:28:29 CST 2025