Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Frederic.

Asked: August 08, 2000 - 12:20 pm UTC

Last updated: March 10, 2020 - 2:30 pm UTC

Version: 7.3.4.4

Viewed 100K+ times! This question is

You Asked

I have a tablespace with a few datafiles.
I have droped lots of tables in this tablespace and i just want to keep only one datafile on this tablespace.
I know the name of the only datafile witch contains datas so i no more need the others datafiles.
How can i do ?

I try to regenerate the controlfiles, it works but i can see some lines with "MISSINGXXX" in the v$datafile view so i look for another way(s) to do that because it doesn't seem very "clean".

I know that it's possible to do that with Import/Export (Export, Drop Tablespace, Import) but it's a big manipulation.

Do you have a solution to drop an empty datafile easily and properly ?

and Tom said...

No, datafiles are not meant to be dropped once added. Here is a support note with some methods you can use. (Doing an alter database OFFLINE DROP is not a method either).


Before we start with detailed explanations of the process involved, please note that Oracle does not provide an interface for dropping datafiles in the same way that you could drop a schema object such as a table, a view, a user, etc. Once you make a datafile part of a tablespace, the datafile CANNOT be
removed, although we can use some workarounds.

Article-ID: <Note:111316.1>
Circulation: PUBLISHED (EXTERNAL)
Folder: server.Rdbms.DBA.Admin
Topic: Managing Controlfiles and Datafiles
Title: How to 'DROP' a Datafile from a Tablespace
Document-Type: BULLETIN
How to 'DROP' a Datafile from a Tablespace
==========================================

PURPOSE
-------

This note explains how a datafile can be removed from a database.

Since there can be confusion as to how a datafile can be dropped because of the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains what steps are needed to delete a datafile and in contrast, what the OFFLINE DROP command is for.


SCOPE & APPLICATION
-------------------

There are two situations where people may want to 'remove' a datafile from a tablespace:

1. You've just mistakenly added a file to a tablespace, or perhaps you made the file much larger than intended and now want to remove it.

2. You are involved in a recovery scenario and the database won't startbecause a datafile is missing.

This article is meant to discuss circumstance 1 above. There are other articles that discuss recovery scenarios where a database cannot be brought online due to missing datafiles. Please see the 'Related Documents' section at the bottom of this article.

How to 'Drop' a Datafile
-----------------------------

Before we start with detailed explanations of the process involved, please note that Oracle does not provide an interface for dropping datafiles in the same way that you could drop a schema object such as a table, a view, a user, etc. Once you make a datafile part of a tablespace, the datafile CANNOT be
removed, although we can use some workarounds.

Before performing certain operations such as taking tablespaces/datafiles offline, and trying to drop them, ensure you have a full backup.

If the datafile you wish to remove is the only datafile in that tablespace, simply drop the entire tablespace using:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

You can confirm how many datafiles make up a tablespace by running the following query:

select file_name, tablespace_name
from dba_data_files
where tablespace_name ='<name of tablespace>';

The DROP TABLESPACE command removes the tablespace, the datafile, and the tablespace's contents from the data dictionary. Oracle will no longer have
access to ANY object that was contained in this tablespace. The physical datafile must then be removed using an operating system command (Oracle NEVER physically removes any datafiles). Depending on which platform you try this on, you may not be able to physically delete the datafile until Oracle is completely shut down (For example, on Windows NT, you may have
to shutdown Oracle AND stop the associated service before the operating system will allow you to delete the file - in some cases, file locks are still held by Oracle)

If you have more than one datafile in the tablespace, and you do NOT need the information contained in that tablespace, or if you can easily recreate the information in this tablespace, then use the same command as above:

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

Again, this will remove the tablespace, the datafiles, and the tablespace's contents from the data dictionary. Oracle will no longer have access to ANY object that was contained in this tablespace. You can then use CREATE TABLESPACE and re-import the appropriate objects back into the tablespace.

If you have more than one datafile in the tablespace and you wish to keep the objects that reside in the other datafile(s) which are part of this tablespace, then you must export all the objects inside the affected tablespace. Gather information on the current datafiles within the tablespace by running this query:

select file_name, tablespace_name
from dba_data_files
where tablespace_name ='<name of tablespace>';

Make sure you specify the tablespace name in capital letters.

In order to allow you to identify which objects are inside the affected tablespace for the purposes of running your export, use the following query:

select owner,segment_name,segment_type
from dba_segments
where tablespace_name='<name of tablespace>'

Now, export all the objects that you wish to keep.

Once the export is done, issue the DROP TABLESPACE tablespace INCLUDING CONTENTS.

Note that this PERMANENTLY removes all objects in this tablespace.Delete the datafiles belonging to this tablespace using the operating system (see the comment above about possible problems in doing this)Recreate the tablespace with the datafile(s) desired, then import the objects into that tablespace. (This may have to be done at the table level,depending on how the tablespace was organized.)

NOTE:
The ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace.

If you are running in archivelog mode, you can also use:

ALTER DATABASE DATAFILE <datafile name> OFFLINE; instead of OFFLINE DROP.Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between
the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.


If you do not wish to follow any of these procedures, there are other things that can be done besides dropping the tablespace.

--If the reason you wanted to drop the file is because you mistakenly created the file of the wrong size, then consider using the RESIZE command.
See 'Related Documents' below.

--If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be rebuilt to exclude the incorrect datafile.




Rating

  (94 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Prashant Jhaveri, March 30, 2001 - 4:55 pm UTC


How to drop a datafile ?

Alison Tonner, July 19, 2001 - 7:58 am UTC

This was a very helpful document, I have just performed the procedure to merge two very large datafiles (98% fragmented and only a small % used) into one smaller datafile.

Worked a treat....

Good information

Andre Araujo, July 19, 2001 - 2:04 pm UTC

This information was very useful to me , I really solved my problem and doubt.

Thank You,
Andre

How to drop a datafile

Ana, July 23, 2001 - 10:43 am UTC

Very helpful

Dropping datafiles

Anil, August 23, 2001 - 2:15 am UTC

Very good information. Cleared lot of doubts

Jase, August 28, 2001 - 8:55 pm UTC

Excellent, fixed my problem

Usefull???

Petr Bily, Josef Vesely, November 24, 2001 - 5:56 pm UTC

It has not been useful for us, we still can't drop the datafile. We can not undestand how other people can tell that this is very useful for them. Why do we have to drop our all other fine datafiles in tablespace if one is demaged???

Tom Kyte
November 24, 2001 - 7:34 pm UTC

If one file is damaged -- you must restore it from a backup. What use would be there in dropping a damaged file? It contains bits and pieces from perhaps many tables.

Once added, a file is part of a tablespace forever until you drop the tablespace. If it becomes "damaged" that is where backup and recovery kicks in. Consider a tablespace to be a data structure -- that file is part of a larger data structure. You know, like if you have a linked list in a program -- you cannot just "drop" the middle of the list -- the two ends point to garbage. Same with dropping a datafile.

If one file is damaged -- all of the other files in your tablespace are in fact NOT FINE, they rely on that file just as much as that file relied on them.

follow up "Usefull?"

Petr Bily, Josef Vesely, November 24, 2001 - 11:15 pm UTC

Let us illustrate why it could be useful to have ability to drop datafile: We have lost 1 datafile that cannot be restored from backup. This file contains partitions that are spread accross many other tablespaces. We cann't drop a datafile. DROP TABLESPACE returned en error ORA-14404. ALTER TABLE DROP PARTITION on partitions in demaged datafile returned error ORA-00376. The suggested command on Metalink for this situation ALTER TABLE ECHANGE PARTITION failed as well. The result is that due to 1 datafile lost we loose all database! Now we are in the position that we have to export all database without one demaged datafile and drop all objects/tablespaces and recreate it insted of importing only one tablespace (optimally datafile).

Tom Kyte
November 25, 2001 - 11:19 am UTC

No, you will not lose the entire database.  Think about what you must do.

You have a tablespace that is HOPELESSLY unrecoverable because you lost a datafile in it and YOU have no backup.

What we must do is remove this tablespace.  You will be responsible for recovering any lost data (we cannot help you, you don't have any backups).  We can cut out this partition easily.  You only need to drop it.  

Consider:



ops$tkyte@ORA817DEV.US.ORACLE.COM> create tablespace will_be_corrupted
  2  datafile '/tmp/will_be_corrupted.dbf' size 1m
  3  /
Tablespace created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE TABLE t
  2  ( x  int, y varchar2(5), z date )
  3  PARTITION BY RANGE (x) (
  4    PARTITION PART_95 VALUES LESS THAN (1996) tablespace users,
  5    PARTITION PART_96 VALUES LESS THAN (1997) tablespace users,
  6    PARTITION PART_97 VALUES LESS THAN (1998) tablespace users,
  7    PARTITION PART_98 VALUES LESS THAN (1999) tablespace will_be_corrupted
  8  )
  9  /
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1995, 'aaa', sysdate );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1996, 'bbb', sysdate );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1997, 'ccc', sysdate );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1998, 'ddd', sysdate );
1 row created.

<b>Ok, so I have a partitioned table with one partition in a tablespace we will "mess up".  So, I connect as sysdba and kill the instance:</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> connect / as sysdba
Connected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> shutdown abort 
ORACLE instance shut down.

ops$tkyte@ORA817DEV.US.ORACLE.COM> !rm -f /tmp/will_be_corrupted.dbf

<b>that assures us the file will be "corrupted", its gone.</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> startup
ORACLE instance started.

Total System Global Area  238829728 bytes
Fixed Size                    73888 bytes
Variable Size             142336000 bytes
Database Buffers           90112000 bytes
Redo Buffers                6307840 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/tmp/will_be_corrupted.dbf'

<b>ok, now what -- well, since you said you have no backup, we can simply:</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter database datafile '/tmp/will_be_corrupted.dbf' offline drop;
Database altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter database open;
Database altered.

<b>database is up, but we still have a problem -- the table isn't 100% yet</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> connect /
Connected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t drop partition part_98;
Table altered.

<b>very easy, remove the affected partition...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop tablespace will_be_corrupted;
Tablespace dropped.

<b>tablespace drops easily...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;

         X Y     Z
---------- ----- ---------
      1995 aaa   25-NOV-01
      1996 bbb   25-NOV-01
      1997 ccc   25-NOV-01

<b>and all of the data we can save is in fact saved.  That tablespace is gone -- the entire thing (regardless of how many files you had in it -- the entire tablespace is wiped out) but we don't lose the database by any means</b>
 

Dam Good Ans.

Parag Mehta, November 25, 2001 - 6:43 am UTC


follow up (Usefull?)

Petr Bily, Josef Vesely, November 26, 2001 - 6:20 am UTC

Your testcase works well as is. But if you have an index on partitioned table (CREATE INDEX T_1 on T(x) LOCAL TABLESPACE ind) you receive the error ORA-00376 as we did. Before issuing ALTER TABLE DROP PARTITION you have to drop all indexes on it (bug?). You are right, whole database is not lost. Thanks a lot.

database segments.

Venkateswara Reddy, March 04, 2002 - 12:56 pm UTC

Hi Tom

Thanks for your continuous support. I have successfully solved my problem. Here some doubt regarding support note issue ( some part).

You have mentioned before droping tablespace get the information about the objects which is stored in the particulare tablespace data in segments.

I have issued a query in my database
select file_name, tablespace_name
from dba_data_files
where tablespace_name ='HRMSDEMO';

I got only 25 Rows ( Tables). But I am sure my HRMSTEST user i have mapped to HRMSDEMO tablespace. If Issue
Select * from tab on HRMSTEST.
I got 112 objects including views. Wheere are my tables are stored. I am littelbit confuse. Pls clarify.

Thanks in advance.

Reddy

Tom Kyte
March 04, 2002 - 3:41 pm UTC

select * from user_segments where tablespace_name = 'HRMSDEMO';

you got the list of files, you wanted the list of segments (tables, indexes, etc).

DataFiles / Control Files

Santhosh, March 04, 2002 - 10:31 pm UTC

Was just going thru your answers...

Have a doubt...

1. Can the DataFiles or Control Files in a Database can be edited using some Text editors / some other editors ? (If the user has access to them)
2. Can these changes be saved ? If Yes or No, explain me why.
3. Also, what security is Oracle providing to safeguard these Data & Control Files (Other than the usual back-ups) ?

Thanks.

Tom Kyte
March 05, 2002 - 8:13 am UTC

1) NO

2) NO, because you cannot edit these files.

3) You need to read the concepts guide from start to finish:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/toc.htm <code>

do that and remember say about 10% of it and you'll already know more then most people do about Oracle.



Adding datafiles OPS

Irfan, March 05, 2002 - 3:22 am UTC

Hi Tom,
This solution was very nice and descriptive. We have OPS 7.3.4. We would like to add data file (raw device) to one of our tablespace to increase the size. Can you suggest this is what exactly I have to use

ALTER TABLESPACE TBSPNAME ADD DATAFILE '/dev/rdsk/c3t3dds1' SIZE 400M.

Is it necessary to bring the tablespace offline before issuing this command. Please advise any other alternate solution if.

Thanks

Tom Kyte
March 05, 2002 - 8:17 am UTC

Refer to your OS guide for specific details on raw partitions on your platform. You may have to add some device overhead (typically 2 blocks or so) to the raw partition.

Yes, that would be the command --given the device is properly configured. No, the tablespace need not be offline.

As OPS in 73 requires really good DBA interaction to run smoothly -- I would hope there is an experience DBA around that would be able to answer this as well.

dropping datafile.

Rajeev Grover, April 24, 2003 - 6:05 am UTC

How to exclude a missing file entry from a control file for which no physical datafile or its backup is present.

Tom Kyte
April 24, 2003 - 8:23 am UTC

did you read the backup and recovery guide which is where I would go for such an in depth question in order to try and find out for myself first?

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96572/recoscenarios.htm#11689 <code>

did you read through the above stuff?

your question is vague enough as to be unanswerable in a "safe" manner.

dropping datafile

Rajiv, April 25, 2003 - 5:49 am UTC

Thanks Tom for reference. It was useful.I conclude that, shall I create a new file belonging to that tablespace and rename the missing one to new one.

why is it not possible to drop datafile?

P, April 26, 2003 - 3:00 pm UTC

hi tom,
i know that it is not possible to drop a datafile once added...could you tell us why i cannot drop it...what is the technical reason...what is going on behind the screen which doesnot allow us to drop it?
thank you for all your help!
P

Tom Kyte
April 26, 2003 - 3:18 pm UTC

it becomes an integral part of the tablespace. In order to drop it -- you need to get rid of that entire tablespace.

Say a tablespace is a glass.
Datafiles are water.

You add a cup of water to the glass.

Later you add another cup of water. Now -- why can't you remove that last cup of water? They are all mixed together, all the same.

You can shrink the amount of water ( like you can resize a file within limits) but you cannot get rid of the "first cup" of water or the "second cup".



:)

Sravan, April 27, 2003 - 7:42 pm UTC

... interesting answer :-)

coool

Abdul Wahab Ansari, April 30, 2003 - 3:44 am UTC

The way you cleared the concept of datafiles and control files was absolutely excellent and helpful.

Help, Help, Help

Riaz Shahid, May 08, 2003 - 3:21 pm UTC

Dear Tom !

I am running my database in NonArchivelog mode. One of datafiles of tablespace RBS got corrupted (as shown below). So i want to drop the rbs tablespace and re-create it but i am unable to do so as shown below:


C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production

SVRMGR> connect / as sysdba
Connected.
SVRMGR> shutdown abort;
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 178436124 bytes
Fixed Size 75804 bytes
Variable Size 104882176 bytes
Database Buffers 73400320 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> select count(*) from cr.gstt05;
COUNT(*)
----------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter database datafile 'E:\ORACLE\ORADATA\STARR\RBS01.DBF' offline;
Statement processed.
SVRMGR> alter tablespace rbs offline;
alter tablespace rbs offline
*
ORA-01539: tablespace 'RBS' is not online
SVRMGR> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace
SVRMGR> alter tablespace rbs online
2> ;
alter tablespace rbs online
*
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter rollback segment rbs0 offline;
alter rollback segment rbs0 offline
*
ORA-01598: rollback segment 'RBS0' is not online

Note that i tried the media recovery but it asks for archived logs (that are not available since database was running in Noarchivelog mode). Please advise what should o do in order to re-create the tablespace RBS (and its datafiles). I have no physical backup for this database. However, i've logical backup for this database.

(I am using 8.1.7.2.1 on Windows 2000 Server)

Waiting for your expert advice...

Riaz

Datafile Size 8.1.7.4

Sriram, October 14, 2003 - 11:56 am UTC

Tom,
Is there a limit on the size of a single Datafile in a tablespace on Windows 2000 Server? I have read somewhere that it is 2GB but i'm not sure about it. If so, what the reason for the limit?

Thanks,
Sriram

Tom Kyte
October 14, 2003 - 12:47 pm UTC

there is a limit, it goes beyond 2gig - depends on your os support. they impose the limit here. generally it is around 64/128 gig depending on OS and all.

metalink has the supported configurations somewhere...

Resize the datafile

S.Darshan, October 15, 2003 - 1:48 am UTC

Dear Mr.Tom,

Thanks for your example regarding Tablespaces and Datafiles, Particularly

"Say a tablespace is a glass.
Datafiles are water."

I have a 6GB size of Tablespace called Archive with Datafiles Archive01.dbf(2GB), Archive02.dbf(2GB) and Archive03.dbf(2GB). I have a table1, table2, table3 in Archive Tablespace. After some time we truncated the table2& table3. Now free space for Archive tablespace is 4GB. I want to resize the tablespace size to 2GB or 2.5 GB by using
ALTER DATABASE DATAFILE '/u01/data/Archive03.dbf' RESIZE 1024M;

But I can not able to resize the datafile. How can I resize ? Can you help me?

With best regards
S.Darshan

Tom Kyte
October 15, 2003 - 7:51 am UTC

well -- the files can only be shrunk to their "current high water mark"

In each of those files, you'll find bits of Table1 -- its extents. Suppose the three files look like this:

(from 0m to 2gig in size -- f is "free" and U is used by table1)


0m 512m 1024m 1536m 2048m
ffffUUUffffUffffffffffffUffffffffffffffffffffffff file 1
fffffffffffffffffffffffffffffffffUUffffffffffffff file 2
ffffffffffffffffffffffffffffffffffffffffffffffffU file 3


Now, file 1 can be shrunk to 1gig in size -- there is nothing used beyond 1gig in that file.

file 2 can only be shrunk to 1.5 gig -- there is data at the 1.5 gig mark in that file. Even though that file only has 2 used bits -- they are way in the middle

file 3, with the least data, will be the biggest file. It cannot be shrunk at all in this example.


Now, if you are using LMTS -- you can try "alter table table1 MOVE" -- that'll tend to reorg the table and put all of the bits at the "front" of a file. Then you can shrink perhaps (remember to rebuild indexes after moving a table like that!)


If you search for

maxshrink

on this site you find a script that will tell you how small ever datafile in your database can currently be shrunk to

How to drop a datafile

Amit J, October 15, 2003 - 9:33 am UTC

The answere to the question regd. Data file where too good. It has improved my knoledge regarding the relation between Tablespace & Datafile to a very high extent.

A reader, October 15, 2003 - 2:18 pm UTC


Great helpful

Raghunath, January 05, 2004 - 4:01 am UTC

Really very informative. cleard my doubts.

How to drop a datafile ?

Linda, February 06, 2004 - 7:19 pm UTC

Saved my tailend! This does not seem to be documented anywhere else.

Difference between offline and offline drop.

Alvin, February 29, 2004 - 9:13 pm UTC

As per oracle 8i documents

---8<---

OFFLINE
Specify OFFLINE to take the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.



DROP takes a datafile offline when the database is in NOARCHIVELOG mode.

---8<---

Say i've a tablespace named 't' it has 2 datafiles t1 and t2. When t1 becomes corrupted and i need to recover it i would :

1. alter database datafile '/folder/t1' offline
2. replace os file from backup
3. perform recovery
4. bring it online

When do we use offline drop ? As per cut and pasted document above :

1. Does that mean that 'offline drop' is used on databases that are in noarchivelog mode ??

2. And we use 'offline' only when we are in archivelog mode ?

Tom Kyte
March 01, 2004 - 7:08 am UTC

offline drop will let you "cut the file out of the database - taking the tablespace with it". It lets you remove the thing that is broken in noarchivelog mode (or in archivelog mode when you have no backups you can use)

In OMF corresponding db files are not deleting...

Ashiq Shamsudeen A, March 01, 2004 - 7:40 am UTC

Hi Tom,

I've created OMF say test tablespace and assigned it scott as default tablespace. Then i created a table on it.

When i issue "drop tablespace test including contents and datafiles" ,its not deleting the the corresponding db files. What's the reason for not deleting the db files. See ..

sys@SHAM> create tablespace test;

Tablespace created.

sys@SHAM> sho parameters db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string e:\ashiq\ocp9i


sys@SHAM> alter user scott default tablespace test;

User altered.

Then i connected to scott and created a table to use newly created tablespace.

scott@SHAM> create table emp1 as select * from emp;

Table created.

scott@SHAM> select table_name ,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ---------------
BONUS SYSTEM
DEPT SYSTEM
EMP SYSTEM
EMP1 TEST
SALGRADE SYSTEM

5 rows selected.

So, now emp1 uses test tablespace..

then i got connected to sys and drop the new tablespace using the clause "including contents and datafiles"

sys@SHAM> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


sys@SHAM> drop tablespace test including contents and datafiles;

Tablespace dropped.

So to verify whether it deleted the corresponding db files in OS,

sys@SHAM> host

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

E:\oracle\ora92\bin>cd \

E:\>cd ashiq\o*

E:\ashiq\ocp9i>dir
Volume in drive E has no label.
Volume Serial Number is 34FE-64C8

Directory of E:\ashiq\ocp9i

03/01/2004 05:58p <DIR> .
03/01/2004 05:58p <DIR> ..
03/01/2004 06:04p 104,865,792 O1_MF_TEST_046C3900_.DBF
1 File(s) 104,865,792 bytes
2 Dir(s) 16,003,325,952 bytes free

E:\ashiq\ocp9i>exit

See still the db file exists there. Why it is so?

Oracle 9.2.0 running on W2k professional.

Tom Kyte
March 01, 2004 - 8:56 am UTC

I cannot reproduce (the and datafiles is not needed with OMF actually, it'll do that by default)

ops$tkyte@ORA920PC> create tablespace test;
 
Tablespace created.
 
ops$tkyte@ORA920PC> column file_name new_val f
ops$tkyte@ORA920PC> select file_name from dba_data_files where tablespace_name = 'TEST';
 
FILE_NAME
------------------------------
/d02/oradata/ora920/o1_mf_test
_046hl0fk_.dbf
 
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create table t ( x int ) tablespace test;
 
Table created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
 
 
ops$tkyte@ORA920PC> host ls -l &f
-rw-rw----    1 ora920   ora920   104865792 Mar  1 08:44 /d02/oradata/ora920/o1_mf_test_046hl0fk_.dbf
 
ops$tkyte@ORA920PC> drop tablespace test including contents and datafiles;
 
Tablespace dropped.
 
ops$tkyte@ORA920PC> host ls -l &f
ls: /d02/oradata/ora920/o1_mf_test_046hl0fk_.dbf: No such file or directory



I had the same response on my 9201 on windows running under vmware as well. 

One little question regarding documentation-links;

Victor, March 01, 2004 - 7:43 am UTC

Hi tom,

I've got a little question about the links you post here to Oracle-documentation, e.g.

</code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96572/recoscenarios.htm#11689

when I click this, or others like it, I always end up at 

http://otn.oracle.com/documentation/index.html <code>

Eventually i'm able to find the document you mean by looking at the URL, but still..
Has this something to do with a re-organisation of the Oracle-site?
Oh yeah, ofcourse I'm registered with Oracle, so that's not the problem.

Not really looking for a "solution" for this thingie, just wanted to let you know..

Greetings,
Victor Oosterbaan


Tom Kyte
March 01, 2004 - 8:47 am UTC

yes, they moved all of the links on me unfortunately -- every single one of them.

not deleting in W2k professional and Windows 2003 server...

Ashiq Shamsudeen A, March 02, 2004 - 2:32 am UTC

Hi tom,

  I tried in W2k professional, windows 2003 server and Sun SPARC 9 (in all machine db is oracle 920) , but in windows machine its not deleting the corresponding db files and in sun its doing. First i'll show in W2k machine

SQL> connect / as sysdba
Connected.
SQL> sho parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      e:\ashiq\ocp9i

SQL> create tablespace test datafile size 100m, size 100m;

Tablespace created.
  
   Then i got into OS to check

SQL> host

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

E:\oracle\ora92\bin>cd \

E:\>cd ashiq\ocp9i

E:\ashiq\ocp9i>dir
 Volume in drive E has no label.
 Volume Serial Number is 34FE-64C8

 Directory of E:\ashiq\ocp9i

03/02/2004  12:43p      <DIR>          .
03/02/2004  12:43p      <DIR>          ..
03/02/2004  12:43p         104,865,792 O1_MF_TEST_048F0100_.DBF
03/02/2004  12:43p         104,865,792 O1_MF_TEST_048F0700_.DBF
               2 File(s)    209,731,584 bytes
               2 Dir(s)  15,898,402,816 bytes free

E:\ashiq\ocp9i>exit

  I got connected to scott and created a table ..

scott@SHAM> conn scott/tiger@sham
Connected.

scott@SHAM> create table ashiq (name varchar2(10)) tablespace test;

Table created.

scott@SHAM> insert into ashiq values ('ashiq');

1 row created.

scott@SHAM> commit;

Commit complete.

  then I dropped the tablespace test 

SQL> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> host


Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

E:\oracle\ora92\bin>cd \

E:\>cd ashiq\ocp9i

E:\ashiq\ocp9i>dir
 Volume in drive E has no label.
 Volume Serial Number is 34FE-64C8

 Directory of E:\ashiq\ocp9i

03/02/2004  12:45p      <DIR>          .
03/02/2004  12:45p      <DIR>          ..
03/02/2004  12:45p         104,865,792 O1_MF_TEST_048F0100_.DBF
               1 File(s)    104,865,792 bytes
               2 Dir(s)  16,003,264,512 bytes free

E:\ashiq\ocp9i>exit

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> 

   Its deleting only one db file ,not all. Other one i've to remove it manually.

  Any reason for this ?

  I did the same thing in SUN machine , its doing what it has supposed to do. See..

SQL> connect / as sysdba
Connected.

SQL> sho parameters db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /oracle/oracle/ashiq/ocp9i

SQL> create tablespace test datafile size 100m, size 100m;

Tablespace created.

SQL> host
$ ls -las
total 409892
   2 drwxr-xr-x   2 oracle   dba          512 Mar  2 12:30 .
   2 drwxr-xr-x   3 oracle   dba          512 Mar  2 10:52 ..
   0 -rw-r--r--   1 oracle   dba            0 Mar  2 11:06 a
204944 -rw-r-----   1 oracle   dba      104865792 Mar  2 12:31 o1_mf_test_049k6fmc_.dbf
204944 -rw-r-----   1 oracle   dba      104865792 Mar  2 12:31 o1_mf_test_049k6jm6_.dbf
$ exit

 Then i got into another user and created a table and assigned test tablespace to it.

SQL> create table ashiq (name varchar2(10)) tablespace test;

Table created.

SQL> insert into ashiq values('ashiq');

1 row created.

SQL> commit;

Commit complete.

SQL> select tablespace_name, table_name from user_tables
  2  where table_name = 'ASHIQ';

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
TEST                           ASHIQ

 Then i dropped the tablespace

SQL> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> host
$ ls -las
total 4
   2 drwxr-xr-x   2 oracle   dba          512 Mar  2 12:32 .
   2 drwxr-xr-x   3 oracle   dba          512 Mar  2 10:52 ..
   0 -rw-r--r--   1 oracle   dba            0 Mar  2 11:06 a
$ exit

   Its deleting the corresponding the db files. 

 SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> 

  What is the problem in Windows ,when its doing fine in SUN ?
 

Tom Kyte
March 02, 2004 - 8:06 am UTC

I love that last sentence :) but I won't touch it....


I cannot reproduce on my vmware system -- I'll have to refer you to support for this one:



SQL*Plus: Release 9.2.0.1.0 - Production on Tue Mar 2 08:05:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


tkyte@ORA9IR2W>
tkyte@ORA9IR2W>
tkyte@ORA9IR2W> create tablespace test
2 datafile size 1m, size 1m;

Tablespace created.

tkyte@ORA9IR2W>
tkyte@ORA9IR2W> column file_name new_val f1
tkyte@ORA9IR2W> select file_name
2 from (
3 select file_name, row_number() over (order by file_name) rn
4 from dba_data_files
5 where tablespace_name = 'TEST'
6 )
7 where rn=1;

FILE_NAME
------------------------------
E:\ORACLE\ORADATA\ORA9IR2W\O1_
MF_TEST_0491ON00_.DBF


tkyte@ORA9IR2W>
tkyte@ORA9IR2W> column file_name new_val f2
tkyte@ORA9IR2W> select file_name
2 from (
3 select file_name, row_number() over (order by file_name) rn
4 from dba_data_files
5 where tablespace_name = 'TEST'
6 )
7 where rn=2;

FILE_NAME
------------------------------
E:\ORACLE\ORADATA\ORA9IR2W\O1_
MF_TEST_0491ON01_.DBF


tkyte@ORA9IR2W>
tkyte@ORA9IR2W> host dir &f1 &f2
Volume in drive E is New Volume
Volume Serial Number is C8B4-71EF

Directory of E:\ORACLE\ORADATA\ORA9IR2W

03/02/2004 08:05 AM 1,056,768 O1_MF_TEST_0491ON00_.DBF

Directory of E:\ORACLE\ORADATA\ORA9IR2W

03/02/2004 08:05 AM 1,056,768 O1_MF_TEST_0491ON01_.DBF
2 File(s) 2,113,536 bytes
0 Dir(s) 1,047,625,728 bytes free

tkyte@ORA9IR2W>
tkyte@ORA9IR2W> create table t ( x int ) tablespace test;

Table created.

tkyte@ORA9IR2W>
tkyte@ORA9IR2W> drop tablespace test;
drop tablespace test
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option


tkyte@ORA9IR2W> host dir &f1 &f2
Volume in drive E is New Volume
Volume Serial Number is C8B4-71EF

Directory of E:\ORACLE\ORADATA\ORA9IR2W

03/02/2004 08:05 AM 1,056,768 O1_MF_TEST_0491ON00_.DBF

Directory of E:\ORACLE\ORADATA\ORA9IR2W

03/02/2004 08:05 AM 1,056,768 O1_MF_TEST_0491ON01_.DBF
2 File(s) 2,113,536 bytes
0 Dir(s) 1,047,625,728 bytes free

tkyte@ORA9IR2W> drop tablespace test including contents and datafiles;

Tablespace dropped.

tkyte@ORA9IR2W> host dir &f1 &f2
Volume in drive E is New Volume
Volume Serial Number is C8B4-71EF

Directory of E:\ORACLE\ORADATA\ORA9IR2W


Directory of E:\ORACLE\ORADATA\ORA9IR2W

File Not Found

Just small query

A reader, April 02, 2004 - 8:53 am UTC

Hi Tom,

Just small query :

What is the difference between

ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP and ALTER DATABASE DATAFILE <datafile name> OFFLINE

regards

Tom Kyte
April 02, 2004 - 10:21 am UTC

offline drop shouldn't be used unless you know what you are doing (as the tablespace containing that file is basically "toast" at that oint)


the other one is "safe". you can online it again.

Strange thing ?

YenYangg, May 26, 2004 - 1:11 am UTC

I was testing on my home PC. Here is what I did. Pls tell me why it happened ?
On Oracle 9.1 on Windows,

Step 1:
Create Temporary Tablespace USERTemp
TempFile 'c:\oracle\usertempfile' size 100M;

Step 2:
Alter User YenYang Temporary Tablespace USERTemp;

Step 3:
Select UserName, Temporary_Tablespace From DBA_USERS;
Result I got, YenYang and USERTemp

Step 4:
DROP Tablespace USERTemp;

Step 5:
Select UserName, Temporary_Tablespace From DBA_USERS;
Result I got was same, YenYang and USERTemp
Why its showing USERTemp ???

Step 6:
DROP TABLESPACE USERTemp
I got an error saying USERTemp does not exists

Step 7:
I queried the DBA_TABLESPACES but I could not see the USERTemp

Step 8:
I shutdown the DB and restarted

Executed Step 5 : Select UserName, Temporary_Tablespace From DBA_USERS;
But I got the same result, For USER = YenYang its showing the USERTemp. Why its showing USERTemp ???

I tried sorting and creating temporary table. It worked fine without any problem.

I created Temporary tablespace USERTemp again. I could able to create it.

My question is why is DBA_USERS showing USERTemp in Temporary_Tablespace column when it was not existsing ?

Then I tried with Non-Temporary tablespace (I mean permanent tablespace). I found the same behaviour.

I hope you understood my question.

Thanks



Tom Kyte
May 26, 2004 - 8:05 am UTC

what else would it, could it, should it be showing exactly?



The temporary tablespace you assigned to the use was dropped.  That does not change the fact that the user was assigned to it, they would just recieve an error upon "needing it".  

This is the way it has always worked.  If you use DEFAULT TEMPORARY tablespaces, you'll find it works differently.

ops$tkyte@ORA9IR2> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
 
 


but otherwise, the temporary tablespace name in dba_users is just "a name".  It is data we will use at runtime to find out what tablespace should be used.  If you dropped it, user gets runtime error.  If it exists, fine, we'll use it. 

Sorry Im not convinced

YenYang, May 27, 2004 - 1:07 am UTC

Sorry Tom I gave rating = 1 because Im NOT Convienced with your reply. Its misleading as the dba_users shows but the tablespaces does not exists

Tom Kyte
May 27, 2004 - 9:01 am UTC

what do you need to be convinced about?  You do not believe your own eyes????  You observe a behaviour and do not believe it is happening?

it is the behaviour of Oracle forever, it has always worked that way.  


o You can drop a temporary tablespace
o As long as it is not the default temporary tables
o Even if a user has been assigned that as their temporary tablespace
o just like you can drop a users "default" tablespace 


all it means is that when Oracle needs temp space, it goes to 'dba users' and asks what is the name.  It gets a name and if that name exists -- it uses it, else it fails the statement.  

Just like when Oracle gets a create without a tablespace, it goes to 'dba users' and asks what is the default tablespace for this user.  It gets a name and if that name exists -- it uses it, else it fails the statement.

ops$tkyte@ORA9IR2> create tablespace ttt;
 
Tablespace created.
 
ops$tkyte@ORA9IR2> alter user ops$tkyte default tablespace ttt;
 
User altered.
 
ops$tkyte@ORA9IR2> drop tablespace ttt;
 
Tablespace dropped.
 
ops$tkyte@ORA9IR2> create table testing ( x int );
create table testing ( x int )
*
ERROR at line 1:
ORA-00959: tablespace 'TTT' does not exist
 


If you don't like the answer - fine, but all I can do is decribe what happens and what is expected to happen.  I cannot tell you want you thing you "want" to hear. 

A Query on Datafiles

Praveen.B, June 04, 2004 - 1:29 pm UTC

Hi Tom,
I went through the discussion right from scrach.I was extremely useful.My problem is vey much related to this .I have a tablesapce(MKINX)which has 4 datafiles each one in seperated disk.They all store Indexes

NAME FILE#
------------------------------------
mkinxdb1index.dbf 16
mkinxdb2index.dbf 17
mkinxdb3index.dbf 18
mkinxdb4index.dbf 19

Now my mkinxdb1index.dbf is corrupted :-( itrs gives follwing error whenever a CREATE INDEX is done on this table space.

ERROR at line 1:
ORA-00376: file 16 cannot be read at this time
ORA-01110: data file 16: '/usr10/oracle/CRAFTINDEX/mkinxdb1index.dbf'

My doubts are

1)Why does it access mkinxdb1index.dbf and not other DBF's .

2)Can i anyway make a setting such that mkinxdb1index.dbf is not accesed by the CREATE INDEX ststement.

3) Can i change the FILE ID(FILE#) number ???

I tried making the datafile offline .After that when i checked v$datafile view i found STATUS in RECOVER and not as OFFLINE.

To can u give me a solution

Tom Kyte
June 04, 2004 - 2:16 pm UTC

it is not corrupt -- it is unavailable.

make it available. correct the situation. perform a restore and recovery if need be.

1) because that file was taken away from us by you or someone at the operating system level.

2) by fixing the problem, yes. By doing a restore and recovery or just making the file available once again.

3) no.



A reader, June 04, 2004 - 6:00 pm UTC

Tom,

I was going through this article at
</code> http://www.dbasupport.com/oracle/ora9i/rman.shtml <code>

<quote>
Data File Recovery: The first database had lost three index data files. We restored the control file from the latest backup. Then we restored the particular data files that reported as corrupt. The last step was to recover the database. The recover database checked for the archive logs in the disk and if it was not available, it automatically restored the archive logs that were needed. We had developed our own recovery scripts and made sure we had tested them with the different scenarios. So all we did was run those scripts, sit back and monitor the log files.
</quote>

Tom to recover datafiles, i dont think restoring controlfile from backup is nessary, as I have successfully recovered datafiles without restoring controlfile from backup, can you please provide your feedback on this.

Thanks,
Rahul.

Tom Kyte
June 04, 2004 - 9:05 pm UTC

as i was reading that -- i was myself wondering "why the HECK would anyone restore a controlfile in response to losing three datafiles?" maybe to make recovery more challenging - but that would be about it.

restoring the control file from the last backup -- hmm, well, I think they are "not saying the truth". restoring old controlfiles would muck things up big time.

A small clearance

Praveen.B, June 05, 2004 - 5:52 am UTC

Hi Tom,
Oops a bit of confusion !!.I will come back to my question.I have a tablespace containing 4 datafiles.When i issue a CREATE INDEX or any schema creation into that tablespace what is the procees or architecture follwed by Oracle (ie) Which datafile does oracle access does it go according to the FILE NO(FILE#) sequential order or does it travel through all the datafiles defined in the table space ? What is its hierarchy?

Regards,
Praveen


Tom Kyte
June 05, 2004 - 9:22 am UTC

why does it matter? a file once added is going to be there forever and ever. Whether we use it randomly or in some prescribe fashion -- doesn't matter from a recovery perspective.

we tend to go round robin - file1, file2, file3, file4, file1....... when allocating space (there is an exception with system allocated LMT's but only for the 64k extents -- then it goes back into round robin)

A reader, June 05, 2004 - 9:22 am UTC

Tom,

Thanks for the feedback. I think articles and information like these are responsible for circulating incorrect info to users.

Thanks
Rahul.

Problems all the way

Praveen,Chennai, June 08, 2004 - 10:52 am UTC

Hi Tom,
I dropped my Table space(with 4 datafiles) completely and now i have recreated it(with all 4 datafiles) .During recreation i did one change .I changed one of the location of datafile from /usr10 to /usr11.When i startup the database it gives following error.This file shown in the ERROR i not the one i created and it does not belong to this TABLESPACE too.Can u help? what should i do ?

ORA-01157: cannot identify/lock data file 27 - see DBWR trace file
ORA-01110: data file 27: '/usr10/oracle/CRAFTDATA/CRF5OPDB8.DBF'


Tom Kyte
June 08, 2004 - 12:53 pm UTC

you'd need to show us the commands you used to drop the tablespace. I've a feeling you didn't.

check out your alert log - these commands would have been recorded in there, let us know what you did and in what order.

A reader, June 08, 2004 - 12:34 pm UTC

Tom,

The recovery article at dbasupport.com that i was referrring has been modified.

Thanks
Rahul.



Tom Kyte
June 08, 2004 - 1:27 pm UTC

excellent -- glad to hear the loop got completed. I make mistakes here too (typing too fast) and am more than willing to correct them (actually, I leave them there generally but with clarifications)

Q on datafile

YenYang, June 21, 2004 - 5:50 am UTC

is there anyway to find out whether an datafile is empty ?if an tablespace has 2 datafiles then if i want to findout whether an datafile is empty ?


Tom Kyte
June 21, 2004 - 8:36 am UTC

query dba_extents and see if there are any extents in it.

but - even if it is empty, that file will be part of the database until you drop the tablespace -- do NOT even attempt to drop the datafile, you'll just end up having to put it back. You can shrink it (search for maxshrink on this site) but you cannot remove it (10g with ASM lets you add and remove storage on the fly, but a file -- once added -- is part of the database)

A reader, July 16, 2004 - 9:05 am UTC

If the file doesn't have any objects, then you can re-size the datafile to smaller size. But can't drop it from the database.





Tom Kyte
July 16, 2004 - 11:29 am UTC

thanks for echoing exactly what i said right above this review?

A By-watcher

A reader, July 16, 2004 - 7:21 pm UTC


Reclaiming the space

Yogesh, July 26, 2004 - 5:16 am UTC

I had one TBS with 2 data files, 1000 MB and 1500 MB appx. Even after dropping this TBS, the physical volumes are not reflecting the new free space ...

What could be the problem?

Tom Kyte
July 26, 2004 - 7:29 am UTC

are you using unix? if so, erasing a file doesn't erase a file -- it just unlinks directory entries.

since opening/closing files is very expensive, many times a process will open a file and keep it open until it must close it for whatever reason.

it would seem some process (perhaps a dedicated server that queried that data) has the file still open and even though you cannot "see" the file via ls, it still exists.

tools such as "lsof" are useful for seeing who has what opened. If that process is made to close the file, the space will be "released" (the last process to close the file allows the OS to free the space)

Drop tablespace

Yogesh, July 26, 2004 - 6:35 am UTC

I checked the physical volume.. the files which are part of this TBS are still there .. that is the reason why it is not reflecting the free space ... Do I have to remove these files manually ?

Is there any way by which we can even drop the datafiles when we drop the tablespace ?

Oracle 8.0.4



Tom Kyte
July 26, 2004 - 7:36 am UTC

oh, even easier answer then. I naturally assumed you were using oracle managed files or the "including contents and datafiles" clause.

just erase them if you are sure you no longer need them.

In 8.0, you had to "do it yourself"

In 9i you have "including contents and datafiles" you can add to the drop tablespace -- or use OMF (whereby you do not name the files when you create the tablespace, we do and we manage them and drop them when you drop the tablespace)

A reader, July 28, 2004 - 9:36 am UTC

Restart the database. It will clear.

Tom Kyte
July 28, 2004 - 1:06 pm UTC

not in their case -- the files needed to be erased.

shutting down a database is a horrible thing to do.

Atanu Majumdar, July 30, 2004 - 7:51 am UTC

Very interesting....and enightening too. Thanks!

Recovery of Rollback datafile

Jay, October 13, 2004 - 2:51 pm UTC

Dear Tom !

I am running my database in NonArchivelog mode. One of datafiles of tablespace
RBS got corrupted (as shown below). So i want to drop the rbs tablespace and
re-create it but i am unable to do so as shown below:


C:\>svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production

SVRMGR> connect / as sysdba
Connected.
SVRMGR> shutdown abort;
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 178436124 bytes
Fixed Size 75804 bytes
Variable Size 104882176 bytes
Database Buffers 73400320 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SVRMGR> select count(*) from cr.gstt05;
COUNT(*)
----------
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter database datafile 'E:\ORACLE\ORADATA\STARR\RBS01.DBF' offline;
Statement processed.
SVRMGR> alter tablespace rbs offline;
alter tablespace rbs offline
*
ORA-01539: tablespace 'RBS' is not online
SVRMGR> drop tablespace rbs including contents;
drop tablespace rbs including contents
*
ORA-01548: active rollback segment 'RBS0' found, terminate dropping tablespace
SVRMGR> alter tablespace rbs online
2> ;
alter tablespace rbs online
*
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'E:\ORACLE\ORADATA\STARR\RBS01.DBF'
SVRMGR> alter rollback segment rbs0 offline;
alter rollback segment rbs0 offline
*
ORA-01598: rollback segment 'RBS0' is not online

Note that i tried the media recovery but it asks for archived logs (that are not
available since database was running in Noarchivelog mode). Please advise what
should o do in order to re-create the tablespace RBS (and its datafiles). I have
no physical backup for this database. However, i've logical backup for this
database.

Waiting for your expert advice...

(I am using 8.0.5 on Solaris)




Tom Kyte
October 13, 2004 - 3:30 pm UTC

contact support.

they might be able to help you get some of the data out -- but you are going to be rebuilding this database (might make you seriously consider using archivelog mode, in noarchivelog mode the only thing I am 100% certain of is that you will lose data someday. Not "may" but "will" lose data)




phil, November 16, 2004 - 11:18 pm UTC

hi Tom,

this has to be such a no brainer, but I look and I look but cannot find an answer - sorry

10g Windows

The oracle doco says ..

"If you add new datafiles to a tablespace and do not fully specify the filenames, Oracle creates the datafiles in the default directory of the database server"


whet is the "default directory of the database server" , where is this set.

I have just installed 10g and when I create a tablespace from the command prompt the datafiles are created in the c:\windows\system32 directory when I do not specificy a path .. if I do it through the browser oem , then the directory defaults to an ora data directory that I expect. So why the windoze directory ??

14:49:56 sys@GHT10> CREATE TABLESPACE test LOGGING DATAFILE 'test.dbf'
14:50:14 2 SIZE 5M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED EXTENT
14:50:14 3 MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO BLO
CKSIZE 8K;

Tablespace created.

14:50:20 sys@GHT10> column file_name format a40
14:56:30 sys@GHT10> column tablespace format a15
14:56:30 sys@GHT10> column bytes format 999,999,999
14:56:30 sys@GHT10>
14:56:30 sys@GHT10> select
14:56:30 2 file_name,
14:56:30 3 t.tablespace_name tablespace,
14:56:30 4 bytes
14:56:30 5 from
14:56:30 6 dba_data_files d,
14:56:30 7 dba_tablespaces t
14:56:30 8 where
14:56:30 9 t.tablespace_name = d.tablespace_name ;

FILE_NAME TABLESPACE BYTES
---------------------------------------- --------------- ------------
C:\ORACLE\ORADATA10\GHT10\USERS01.DBF USERS 5,242,880
C:\ORACLE\ORADATA10\GHT10\SYSAUX01.DBF SYSAUX 230,686,720
C:\ORACLE\ORADATA10\GHT10\UNDOTBS01.DBF UNDOTBS1 36,700,160
C:\ORACLE\ORADATA10\GHT10\SYSTEM01.DBF SYSTEM 471,859,200
C:\ORACLE\ORADATA10\GHT10\EXAMPLE01.DBF EXAMPLE 157,286,400
C:\WINDOWS\SYSTEM32\BIC_DATA01.DBF BIC_DATA01 23,068,672
C:\WINDOWS\SYSTEM32\TEST.DBF TEST 5,242,880


Tom Kyte
November 16, 2004 - 11:49 pm UTC

it is the "current working directory"

just like if you fired up a cmd.exe window and edited a file -- it would be created "there"

that is the current working directory apparently when you started the database via the "service" -- a uniquely windows "feature"

I would recommend never "not qualifying the filename" -- if you are not wanting to qualify file names -- just leave the entire filename out of it and use oracle managed files (you control where they go at least)

how about changing location of control files when they are managed using Oracle Managed Files (OMF)?

Nik Malenovic, November 17, 2004 - 3:57 pm UTC

Tom,

I've searched asktom.oracle.com, I've searched the complete 10g documentation, and I've googled, in that order, but to no avail: I can't change directory locations for OMF managed control files, log files, and DBF files in control files backed to a trace file. Here's what I've done so far.

After renaming directory locations of control files, log files, and DBF files, as expected, I got (after setting remote_login_passwordfile=NONE and using a pfile startup parameter):


SQL> startup pfile='U:\mme\oracle\admin\M4UDEV\pfile\init.ora.1082004203425'
ORACLE instance started.
...
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'U:\MME\ORACLE\INSTANCE\M4UDEV\DATAFILE\O1_MF_SYSTEM_0S08XFSO_.DBF'


I've read this thread and realized I had to:


SQL> alter database backup controlfile to trace;


then I've edited the trace file and came up with something like this:


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "M4UDEV" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 454
LOGFILE
  GROUP 1 (
    'U:\mme\oracle\oradata\M4UDEV\ONLINELOG\O1_MF_1_0S08WDF7_.LOG'
  ) SIZE 100M,
  GROUP 2 (
    'U:\mme\oracle\oradata\M4UDEV\ONLINELOG\O1_MF_2_0S08WSV9_.LOG'
  ) SIZE 100M,
  GROUP 3 (
    'U:\mme\oracle\oradata\M4UDEV\ONLINELOG\O1_MF_3_0S08X6PV_.LOG'
  ) SIZE 100M
-- STANDBY LOGFILE
DATAFILE
  'U:\mme\oracle\oradata\M4UDEV\DATAFILE\O1_MF_SYSTEM_0S08XFSO_.DBF',
  ...
  'U:\mme\oracle\oradata\M4UDEV\DATAFILE\O1_MF_USERS_0S0996WL_.DBF'
CHARACTER SET WE8MSWIN1252
;


So, I happilly ran:


SQL> startup nomount pfile='U:\mme\oracle\admin\M4UDEV\pfile\init.ora.1082004203425'
ORACLE instance started.
...
SQL> CREATE CONTROLFILE REUSE DATABASE "M4UDEV" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  ...
 79  CHARACTER SET WE8MSWIN1252
 80  ;
CREATE CONTROLFILE REUSE DATABASE "M4UDEV" NORESETLOGS NOARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01276: Cannot add file U:\MME\ORACLE\ORADATA\M4UDEV\CONTROLFILE\O1_MF_0S08W334_.CTL.  File has an Oracle Managed Files file name.




what gives? I can't move OMF managed control files? how do I move them to a different directory then? as always, looking for a few good words of your wisdom.


I guess I could get slapped with "don't move your files" but then again I had them scattered in multiple locations across multiple drives and I am trying to consolidate onto a single drive and a single location - and no snickering about database files located on a single resource bottleneck called 'single slow drive'.

And yes, I know I could get it back in working state, do "exp", drop database, recreate to my liking, and "imp" but then again I like to do things the hard way. The easy way is for Toms of the World.


thanks,


Nik
 

Tom Kyte
November 17, 2004 - 4:37 pm UTC

if you move them, they are not omf anymore. omf is omf, oracle managed, you told us the n places to put them -- and we do.

so, to move them, take control of them -- rename them (at the os level). so they are not OMF named...

A reader, December 15, 2004 - 1:17 pm UTC

Tom,

What should be done to move a datafile from one volume (directory) to another volume in unix? Can we do it online?

Tom Kyte
December 15, 2004 - 6:06 pm UTC

with files you have to do it offline.


alter tablespace T offline;
move the file
alter database to rename the file
alter tablespace T online;

rename datafile

whizkid, April 22, 2005 - 11:33 am UTC

Tom,

We were trying to 2 move datafile (1 relating to proddata tablespace and other relating to prodindx tablespace) from one partition to another in the same machine. there were two tablespace involved, proddata and prodindx. proddata tablespace was offlined. file was moved to the new location. the other file relating to prodindx was alos moved. but the tablespace was not offlined. then tried to rename file and it gave error. so moved the indexfile back to the
original location and then offlined and onlined the tablespace and it is working properly. but for the datafile, the first time when we tried to rename the file it said that it already exists. 

SQL> alter database rename file '/data3/oradata/DWH/PRODDATA05.dbf' to
'/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf';
alter database rename file '/data3/oradata/DWH/PRODDATA05.dbf' to
'/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf'
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8
ORA-01511: error in renaming log/data files
ORA-01523: cannot rename data file to
'/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf' - file already part of database 

there are two entreis in v$datafile for the same file. one pointing to the new location and one pointing to the old location.

Problem is with one file PRODDATA05.dbf

Original Location: /data3/oradata/DWH
Proposed Location: /data1/oracle/DWH/PROD/DATA/
Current Location: /data3/oradata/DWH

dba_data_files show two location:

/data3/oradata/DWH/PRODDATA05.dbf
/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf

copied the file from current location to proposed location
created another copy of the file in the original location with name PRODDATA05_N.dbf
[oracle@inp1dwh01 DWH]$ cp PRODDATA05.dbf PRODDATA05_N.dbf
[oracle@inp1dwh01 DWH]$ sp1
SQL*Plus: Release 9.2.0.6.0 - Production on Fri Apr 22 18:16:16 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> alter database rename file '/data3/oradata/DWH/PRODDATA05.dbf' to
'/data3/oradata/DWH/PRODDATA05_N.dbf';
Database altered.
Elapsed: 00:00:00.05
SQL> select file_name from dba_data_files; (--------editied-------)
FILE_NAME
/data3/oradata/DWH/PRODDATA05_N.dbf
/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf
30 rows selected.
Elapsed: 00:00:00.02
SQL> alter tablespace proddata online;
alter tablespace proddata online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8
ORA-01122: database file 15 failed verification check
ORA-01110: data file 15: '/data1/oracle/DWH/PROD/DATA/PRODDATA05.dbf'
ORA-01251: Unknown File Header Version read for file number 15
Elapsed: 00:00:00.06

So there's a catch 22 kind of situation. We have both the old file location and new file location in the data dictionary. We cannot bring the tablespace online because of that.

We have raise a iTar for this. Am hoping that you will be able to help us out with this. 

Tom Kyte
April 22, 2005 - 12:38 pm UTC

sorry -- i'm not going to touch this one. You might be in "recovery mode" here or "drop tablespace and rebuild indexes".

problem is -- some processes had the index file open -- you moved it (which copied it and unlinked it). those processes STILL HAD the old copy opened! You put the file "back" -- but those other processes may have written to the old file. You now have two of them but when the processes actually do close the index file, their writes go away....

work with support and be ready to restore from backup.

Just for my understanding....

whizkid, April 22, 2005 - 12:54 pm UTC

Ok... but what I'm not able to understand is that I offlined the data tablespace and here the problem is with a datafile in the data tablespace only. I moved the index file without offlining the tablespace. But after restoring it back and following the process, I was able to rename the datafile and successfully online the index tablespace.

The data tablespace... i had offlined it.. then how???

Tom Kyte
April 22, 2005 - 1:11 pm UTC

i'm not sure what you did -- please utilize support -- as I said, I won't touch this one here. You need to get all of the details into a tar and work it from there.

How to drop a datafile

Hossein Alaei Bavil, May 08, 2005 - 4:36 am UTC

you Said:

"The DROP TABLESPACE command removes the tablespace, the datafile, and the
tablespace's contents from the data dictionary. Oracle will no longer have
access to ANY object that was contained in this tablespace. The physical
datafile must then be removed using an operating system command (Oracle NEVER
physically removes any datafiles). Depending on which platform you try this
on, you may not be able to physically delete the datafile until Oracle is
completely shut down (For example, on Windows NT, you may have
to shutdown Oracle AND stop the associated service before the operating system
will allow you to delete the file - in some cases, file locks are still held by
Oracle) "
i used following commands with Sys user connection
CREATE TABLESPACE APPDATA LOGGING DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\APPDATA1.ORA'
SIZE 256M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE 512M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
DROP TABLESPACE APPDATA INCLUDING CONTENTS AND DATAFILES ;
create or replace directory TEST_DB
as 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TEST\';
exec utl_file.fremove('TEST_DB','appdata1.ora');

with no more need to shutdown database!


Tom Kyte
May 08, 2005 - 8:13 am UTC

and sometimes you will -- you didn't actually have other sessions trying to use the files.

A reader, May 26, 2005 - 9:32 am UTC

Hi Tom,

I want to drop the default constraint on a column, is there a direct command?

I am looking for something like this:

ALTER TABLE <TAB> MODIFY COLUMN <col> DROP DEFAULT;

Thanks.

Tom Kyte
May 26, 2005 - 10:03 am UTC

you set the default to NULL, there is no way to drop a "default", you can only set it back to the ultimate and original default of NULL.

but -- what the heck does this have to do with dropping a datafile?

Why could be the cause of this?

A reader, June 30, 2005 - 6:31 am UTC

Hi Tom,
As per your followup above:
with files you have to do it offline.


alter tablespace T offline;
move the file
alter database to rename the file
alter tablespace T online; 
__________
I did, select * from dba_segments where tablespace_name ='TEST';
no rows selected
SQL> alter tablespace TEST offline normal;
session just hangs...
PS: I am using Oracle 9.2 

Tom Kyte
June 30, 2005 - 9:43 am UTC

use another session and see what you are waiting on or doing.

how to know is any oracle process is using a datafile

Pravesh Karthik from India, July 18, 2005 - 11:01 am UTC

Tom,

I had a scenario, where in the mount point became 100%. Some how BMC Patrol did not detect that. I need to move some files to different mount point. Is there any way, how i can know, that this datafile is not being used by any oracle process for updates/writes/deletes?

Since for moving we need to make it readonly and then offline to rename it. Please let us know about this.

Thanks for your consideration
Pravesh Karthik

Tom Kyte
July 18, 2005 - 11:11 am UTC

depends on the OS.

google for lsof -- ls open files.

Reusing datafiles

A reader, July 19, 2005 - 3:21 pm UTC

Hello,
On Windoze you said that Oracle MAY still keep file locks after dropping a tablespace. So....
My question is can I drop a tablespace ( which removes from data dictionary the info) then recreate a tablespace with the same name and reuse the SAME datafile names used by the tablespace that I just dropped?

Thank you for your time



Tom Kyte
July 19, 2005 - 5:56 pm UTC

you should be able to, I have not tested that theory.

Allocation of space in offline datafiles

Tony, September 20, 2005 - 6:21 pm UTC

Typically, I am reading this the day AFTER I made my mistake, rather than the day before, but I have two follow up questions:

1) Can I assume that oracle will still attempt to allocate space in an offline datafile when there are free extents in online datafiles in the same tablespace? You can see where I am going: take a newly created datafile offline and leave it offline forever. Guess that doesn't work or you would have said so, but just to be clear.

2) You said in one response that the difference between OFFLINE DROP and OFFLINE was that with the former your datafile is unrecoverable. Fortunately for me I was able to easily recover yesterday after issuing the former command. What the docs. suggest is that you must supply DROP with NOARCHIVELOG mode as then you may very well be in trouble if you wait too long. However, reading between the lines I gather that for ARCHIVELOG (that's me) the semantics are the same. I am just curious. After reading this note I will not be trying this command again in a hurry :-)

Tom Kyte
September 21, 2005 - 1:42 pm UTC

if you offline drop, don't hold your breath getting the data back - you might get really lucky, but likely not. (eg: understand what you are typing before you hit the enter key)


not sure where you are going with #1, having an offline datafile permanently in a tablespace? why

AT LAST, MY PROBLEM IS SOLVED!

Doris, September 21, 2005 - 6:23 pm UTC

Thank you for this solution. I am an Oracle student. My instructor is away on a 2-wk vacation, and no one else at this school knows enough dba to help me. I've been googling ORA-01110 to locate an answer to my error message, but yours is the only site with advice I could use. I just completed Chapter 14 (ASM) of the Administration Workshop II textbook; and as I tried to begin Chapter 15, I couldn't open the database even though at the end of Ch 14, all the tablespaces and their contents (including the datafile Oracle couldn't identify) were DROPPED. At last, I can continue and finish the book in time for my instructor's return. Again, thank you.

Permanently offlining

Tony, September 22, 2005 - 4:40 pm UTC

Subsequent to my last post I reviewed the 10g R2 documentation which explains that you really can drop and delete an empty datafile for the first time. It confirms that OFFLINE and OFFLINE DROP are synonymous when in archivelog mode. I guess that that was true in previous versions too?

As to why I want to take an empty datafile offline permanently, the original question back in 2000 was "Do you have a solution to drop an empty datafile easily and properly ?". If I am working with a version of oracle prior to 10g R2 but my tablespace is an LMT, then taking an empty datafile offline and then deleting the OS file seems like the next best thing. Sure, the data dictionary has an annoying extra row, but since the free space bit map has gone with the file, the server will surely not attempt to allocate space there? But confirmation from an expert would be nice!

P.S. As an aside, I have confirmation from the author that there is a "mistake" in the 10g R2 document, specifically that you *can* delete the *last* datafile in a tablespace, but you cannot delete the *first* (original) datafile. This confusing wording will apparently be fixed next time round.

Tom Kyte
September 22, 2005 - 9:34 pm UTC

10gr2 has a new feature for this yes. if the file is truly "empty" you can drop it safely.

you should just shrink the file as small as possible and live with it or rebuild that tablespace.

(the first datafile is never truly empty)

Delete OFFLINE LMT

Tony, September 23, 2005 - 12:43 pm UTC

This will be my last post on this point I promise.

Is it actually unsafe to delete an empty offline LMT datafile (pre 10gR2), and if so why? Or is it just one of those things that shouldn't be committed to in writing :-)

Tom Kyte
September 23, 2005 - 8:37 pm UTC

once added to a datebase, a datafile is part of that database forever - prior to 10gr2.....

period.

Dropping a Tablespace with missing datafile

temi, October 07, 2005 - 4:46 pm UTC


Very Very Veryyyyyyyyyyy helpful. I spent 8 hrs trying to figure out how to drop a tablespace if I already deleted the datafile by mistake.
I kept getting the error message: cannot identify/lock data file 8 - see DBWR trace file.
I stumbled on the website by chance and that was the end of my 8 hrs struggle. oracle forum/website was absolutely of no help to resolve the issue.

Thanks Tom!

Tom Kyte
October 08, 2005 - 8:48 am UTC

(this is an oracle forum/website!)

so, that last statement isn't exactly accurate ;)

erase datafile by commad "Create tablespace" ???

Claude Quès, October 28, 2005 - 10:29 am UTC

Hello .. Claude

Is it possible to erase a file (datafile) with a command oracle "Create tablespace" ?

Is it possible to overwrite an existing tablespace with this same command?

Is it possible to overwrite a datafile with this command "Create tablespace" without the clause "REUSE" ?

Why ?
It occurred with a database 10G (9.0.1.5.0) on a disk resource NFS under Aix5L ( 5200-06 ) .

Here is the command :
"create tablespace tbs_rco datafile '/SemU3/dbf/ctaexp/rco_sem/rco_sem_1.dat' size 4939776 autoextend on next 1M maxsize unlimited;"

The command destroyed the existing datafile of an existing tablespace "tbs_rco" place on a NFS-system-files ?

and immédiatetly, the database was corrupted at once .


Why? Is there a known bug?


Tom Kyte
October 28, 2005 - 1:18 pm UTC

create tablespace, without reuse, should not reuse an existing file, no.

MORE for "erase datafile by commad "Create tablespace" ??? "

CQU, October 31, 2005 - 5:01 am UTC

Hi Tom,

In Oracle10g, is it possible to remove a datafile with Oracle command ?

Exact situation is :
On a 8.1.7 database i have a TBS : DAT1_RCO with datafile : '/SemU3/dbf/XXX/rco_yyy/rco_yyy_1.dat'
the database is on server 1 ==> AIX 4.3.3

On server 2 (AIX 5L), i have another database (10g), i have just tried to create à TBS (with REUSE condition) : TBS_RCO with the same file_name : '/SemU3/dbf/XXX/rco_yyy/rco_yyy_1.dat'.
(I create a dynamic script to realise that.. but i have forgotten to modify file_name).

Unfortunately, there was (since the morning) a NFS share named : SemU3 which permit to have access on the first file on Server1.

Consequence : The "create tbs" order executed on the 10g database failed with ORA-00604 (datafile on 8.1.7 database was probably used during operation),
but The most serious : datafile on Server1 disapeared !!!!!

I try to know how it's possible my datafile disapeared whereas I have just try to create a TBS on another database ...

however, I tought that ORACLE NEVER remove file, just OS command could do that ...

What do you think about that ?
Thank in advance


Tom Kyte
October 31, 2005 - 5:45 am UTC

what sort of NFS are you using here? Something certified like Netapp filers?

RE:MORE for "erase datafile by commad "Create tablespace" ??? "

CQU, November 02, 2005 - 4:30 am UTC

Hi Tom,

Sort of NFS i'm using is : NFS V2 client from AIX5L (ML05) to AIX 4.3.3 server (ML09).




Tom Kyte
November 03, 2005 - 4:32 am UTC

that would not be advisable at all - you cannot use NFS like that with a database - all kinds of problems. Only certified storage partners like NetApp work (with very special NFS implementations)

do not in general use NFS, it is not a supported file system - for any database (you cannot be sure the writes actually happen)

RE:RE:MORE for "erase datafile by commad "Create tablespace" ??? "

CQU, November 03, 2005 - 1:45 pm UTC

Hi Tom,

Thank a lot for your patience.

Share NFS isn't used for our DATABASE ...

Well, i just ask my first question :

how it's possible my datafile disapeared when i execute "create tablespace command" ???

Do you have already encountered such a problem ?

Is there any ORACLE's command which can remove physically my datafile ?

thank.


Tom Kyte
November 04, 2005 - 2:46 am UTC

you just said above you were using datafiles on NFS??

....
Unfortunately, there was (since the morning) a NFS share named : SemU3 which
permit to have access on the first file on Server1.
..........




yes, there are commands that remove datafiles.
no, you have not shown me that you are using those commands (drop tablesspace with a datafile clause)

RE:RE:RE:MORE for "erase datafile by commad "Create tablespace" ??? "

CQU, November 04, 2005 - 3:47 am UTC

Hi Tom,

When i have said NFS Share permits to have an access on the datafile, i wanted to say that my datafile was visible through this share ...
In fact, this share is used to exchange files....

It's just a share between two servers ....

I never execute drop tablespace command with datafile clause ... my only executed command was "create tablespace" with a filename which exists on another server and on another database version.

This command failed with ORA-00604 and datafile disapeared on the second server ...

Oracle Bug ? Another explanation ?

Thanks.


Tom Kyte
November 04, 2005 - 4:00 am UTC

sorry, I'll ask you to work with support - I'm very confused here - you say it is a share between two servers - but that nfs wasn't part of the issue - support will be able to collect the information needed to figure out what really happened.

You say "with a filename which exists on
another server and on another database version." which tells me "this is all about NFS" - the file was *shared*, by *NFS*, on *two servers* and the databases in question were on two different servers, accessing an NFS MOUNT POINT.

It could be as easy as someone on the other server did an erase at about the same time.


the create tablespace command with reuse would have corrupted the file regardless of whether it erased it or not - so it is somewhat a moot point - the file would have been destroyed either way.

alter database offline drop datafile

doc, November 21, 2005 - 7:44 pm UTC

This article is not helpful. I saw it on Metalink, too. Oracle does not like offline drop, either, but I think it is the right solution for me. I have a 26 datafile 90.8GB USERS tablespace with nothing in it in Oracle8i on OpenVMS. SMON freaks out if you try to resize, drop tablespace, do anything. Performing the offline drop datafile serially (one by one) is the only solution. I tried the drop tablespace but they perform cold backups every night, and after the batch I have only 15 hours before the next backup and the drop fails. After dropping all datafiles, I intend to do the drop tablespace. Your thoughts?

Tom Kyte
November 22, 2005 - 8:07 am UTC

I think there is insufficient data here to answer. I don't know what your goal here is ultimately.

"SMON" freaking sounds like you have tens or hundreds of thousands of extents in this tablespace (free or otherwise). It sounds like space management (and not really anything to do with dropping datafiles - other than that will result in space management).

I don't know state you are in right now. Have you dropped the datafiles? What are you trying to do at this point? If the datafiles are gone, you can just offline the tablespace (doesn't consume any storage)

data files

Ragahv, December 09, 2005 - 8:19 am UTC

Hi Tom

Suppose there are two datafiles created in a tablespace. There were two data files assigned to this tablespace. Suppose the two data files are assigned 500 MB each. If you drop the tablespace automatically the data files are also get dropped. But, when you try to create a new tablespace and try to give the same data file name which we have given earlier it will ask "The name with that file is already existing. Do you want to use the same file or want to re-use it". The reason because, the data files are not deleted (and can be seen) in the oracle directory.

My question is when we have deleted the tablespace intentionally and we want to drop the data files, why Oracle keeps these files in the directory and doesn't delete them.

Thanks and Regards
Raghav

Tom Kyte
December 09, 2005 - 12:53 pm UTC

the datafiles won't get ERASED unless you

a) are using OMF (oracle managed files)
b) use the including contents AND datafiles clause on the drop tablespcae


so, either use a) or specify b)

ORA-02449 errors

Erik, February 03, 2006 - 4:49 pm UTC

Thanks Tom, very helpful.  But I get the following error when trying to drop the tablespace:

SQL> DROP TABLESPACE TAG_D0 INCLUDING CONTENTS AND DATAFILES;    
DROP TABLESPACE TAG_D0 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

OK, the error speaks for itself, but is there an easy way to disable all of them in one shot? 

Tom Kyte
February 03, 2006 - 5:29 pm UTC

not that I know of, no, you would need to drop the tables/cascade constraints first.

deleted data file

Erik, February 03, 2006 - 7:54 pm UTC

Thanks for you quick response Tom.  Do you know, is there a way to recover from a deleted (from disk) datafile?  When I try to drop the tablespace I get the following error:

QL> drop tablespace tag_d0 including contents and datafiles;
drop tablespace tag_d0 including contents and datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 30
ORA-01110: data file 30: '/SAN/u10/oracle/oradata/prod8/TAG_D0_18.DBF'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


SQL>  

Tom Kyte
February 06, 2006 - 12:03 am UTC

if you just want to get rid of the tablespace, you can use offline drop.

ops$ora10gr2@ORA10GR2> drop tablespace test_drop including contents and datafiles;
drop tablespace test_drop including contents and datafiles
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/tmp/test_drop2.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


ops$ora10gr2@ORA10GR2> alter database datafile '/tmp/test_drop2.dbf' offline drop;

Database altered.

ops$ora10gr2@ORA10GR2> drop tablespace test_drop including contents and datafiles;

Tablespace dropped.

 

deleted data file

Erik, February 03, 2006 - 8:15 pm UTC

Helps to do a little legwork before asking.  And I always scowl about those who don't!!!

SQL> alter database datafile '/SAN/u10/oracle/oradata/prod8/TAG_D0_18.DBF'
  2  offline drop;

Database altered.

SQL> drop tablespace tag_d0 including contents and datafiles;

Tablespace dropped.

SQL> 

So that's when the drop datafile comes in handy. 

making data files offline

Raghav, February 13, 2006 - 7:59 am UTC

Hi Tom

If there are two data files attached to a single tablespace, can we make one of the data files offline? If yes, what is the procedure to do the same?
I am asking this because, when I try to off line a data file, entire tablespace has gone offline.

Thanks and Regards
Raghav


Tom Kyte
February 13, 2006 - 8:56 am UTC

yes you can, but in general - it'll pretty much take the tablespace for all intents and purposes with it.

note: must be in archivelog mode for this.


pretty much anything that is in the file will be unaccessible remember:


ops$tkyte@ORA9IR2> create tablespace offline_demo
  2  datafile '/tmp/od1.dbf' size 1m reuse, '/tmp/od2.dbf' size 1m reuse;
 
Tablespace created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table od1 (x int ) tablespace offline_demo;
 
Table created.
 
ops$tkyte@ORA9IR2> alter database datafile '/tmp/od1.dbf' offline;
 
Database altered.
 
ops$tkyte@ORA9IR2> create table od2 (x int ) tablespace offline_demo;
 
Table created.
 
ops$tkyte@ORA9IR2> recover datafile '/tmp/od1.dbf';
Media recovery complete.
ops$tkyte@ORA9IR2> alter database datafile '/tmp/od1.dbf' online;
 
Database altered.
 
ops$tkyte@ORA9IR2> insert into od1 values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into od2 values ( 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select tablespace_name, status from dba_tablespaces where tablespace_name = 'OFFLINE_DEMO'
  2  union all
  3  select name, status from v$datafile where name like '/tmp/od_.dbf'
  4  union all
  5  select segment_name, to_char(file_id) from dba_extents where segment_name in ( 'OD1', 'OD2' );
 
TABLESPACE_NAME      STATUS
-------------------- ----------------------------------------
OFFLINE_DEMO         ONLINE
/tmp/od1.dbf         ONLINE
/tmp/od2.dbf         ONLINE
OD1                  9
OD2                  10
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter database datafile '/tmp/od1.dbf' offline;
 
Database altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select tablespace_name, status from dba_tablespaces where tablespace_name = 'OFFLINE_DEMO'
  2  union all
  3  select name, status from v$datafile where name like '/tmp/od_.dbf'
  4  union all
  5  select segment_name, to_char(file_id) from dba_extents where segment_name in ( 'OD1', 'OD2' );
 
TABLESPACE_NAME      STATUS
-------------------- ----------------------------------------
OFFLINE_DEMO         ONLINE
/tmp/od1.dbf         RECOVER
/tmp/od2.dbf         ONLINE
OD2                  10
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from od1;
select * from od1
              *
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: '/tmp/od1.dbf'
 
 
ops$tkyte@ORA9IR2> select * from od2;
 
         X
----------
         1
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop tablespace offline_demo including contents and datafiles;
 
Tablespace dropped.
 
 

data files offline

Raghav, February 14, 2006 - 7:23 am UTC

Hi Tom

Is the procedure you have explained is the feature of 9i or it is there in earlier versions too like 8i, 
why because, I have tested this option in 8i (test environment) and I have strucked up in the 4th step. The following is the result.

create tablespace offline_demo
datafile '/tmp/od1.dbf' size 1m reuse, '/tmp/od2.dbf' size 1m reuse;
 
Tablespace created.
 

create table od1 (x int ) tablespace offline_demo;
 
Table created.
 
alter database datafile '/tmp/od1.dbf' offline;
 
Database altered.
 
SQL> create table od2 (x int ) tablespace offline_demo;
create table od2 (x int ) tablespace offline_demo
*
ERROR at line 1:
ORA-00376: file 14 cannot be read at this time
ORA-01110: data file 14: 'E:\TMP\OD1.DBF'

Thanks and Regards
Raghav 

Tom Kyte
February 14, 2006 - 8:20 am UTC

I got lucky - did you see what my goal was - create a table in file 1, create a table in file 2. show if the table is in file 2 and we offline file 1 - it is OK.

You can come up with other ways to do this (I was using locally managed tablespaces, probably has something to do with it).

My goal - simply to show what would happen if a table is contained in whole or part in the offline datafile versus something that was not.


You can just create a table almost 1m extents. That'll force t1 in to file 1 and t2 into file 2

Drops

Tim, February 21, 2006 - 11:52 pm UTC

First, 9i lets you drop a column then 10g R2 lets you drop a datafile.

What is next?

Tom Kyte
February 22, 2006 - 8:36 am UTC

don't forget dropping a database :) dbca

or in 10g

Greg, February 22, 2006 - 5:56 pm UTC

You can take the easy way out and "drop database;".
See:
</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8009.htm#i2157988 <code>

delete datafiles

Thai, July 17, 2006 - 3:43 pm UTC

Hi,
Ur information is upto the point.Thanks.
Also i found that once i dropped a tablespace including contents and tried to delete the datafiles in Win Xp operating system i could do that with the DB open.Is it not inconsistent with ur info on shutting the DB and Stopping the services to delete those physical files or is it b'coz of variation in OS.

Thanks in advance
Thai

Buggered UNDO tablespace

Elizabeth, August 03, 2006 - 10:27 pm UTC

Hi Tom

I added a datafile to the UNDO tablespace and later OFFLINED it while the database was up.

alter tablespace UNDOTBS1 add datafile '/dev/vx/rdsk/rac-dg/fusiondb_raw_undotbs1_3_5g' size 5120m;

After a day:

alter database datafile '/dev/vx/rdsk/rac-dg/fusiondb_raw_undotbs1_3_5g' offline; 

2 hours after the offline command, the STATUS, of the datafile in dba_data_files is AVAILABLE and I could use all my tables.  4 hours later I cannot insert or delete from the tables. Selects still work. I can't truncate or drop the tables either.  I don't have a backup from before the offline command.  I can't make the file online either.

SQL> alter database datafile '/dev/vx/rdsk/rac-dg/fusiondb_raw_undotbs1_3_5g' online;
alter database datafile '/dev/vx/rdsk/rac-dg/fusiondb_raw_undotbs1_3_5g' online
*
ERROR at line 1:
ORA-01113: file 21 needs media recovery
ORA-01110: data file 21: '/dev/vx/rdsk/rac-dg/fusiondb_raw_undotbs1_3_5g'

I don't need the data from the tables.  How do I get over this corruption ?

Thanks 

Tom Kyte
August 04, 2006 - 7:45 am UTC

please utilize support - this is clearly something for "support" - don't mess around here (not time to test ideas).



Greg from Greenville, NC

Greg Moseley, August 10, 2006 - 9:12 am UTC

Our DB uses raw datafiles. We created a raw logical volume to add to a tablespace, but inadvertently added the datafile with an incorrect name. A new cooked datafile residing in ohome/dbs was created/added to the tablespace. Now our backup script is failing when it attempts to backup the tablespace and encounters the cooked file. Is there a way to change/convert a cooked file into raw? Or do we need to export/drop/import to get rid of the cooked file? We resized/shrunk the cooked file to prevent being used.

Tom Kyte
August 10, 2006 - 9:42 am UTC

the method of moving a file to a raw partition depends on the OS - please utilize support if you wish to move the file (you may have to use different options to dd to move the file to raw - skipping over a couple of OS blocks or not)

I would not drop/export/import in ANY CASE.

Rather, you could:

a) create new tablespace on raw
b) alter table T move new_tablespace/alter index I rebuild new_tablespace
c) drop old tablespace
d) optionally rename new tablespace (if you have a release of oracle that does tablespace renames)

never export/import with a drop to move something.

Greg from Greenville NC

Greg Moseley, August 10, 2006 - 10:19 am UTC

Thank you for the quick response.

We're on Oracle 8.1.7, AIX 5.

I'll do the dd research to determine if doable. Ideally, I'd like to convert cooked to raw, relocate the file and rename the datafile if possible.

Thanks.

Tom Kyte
August 10, 2006 - 10:47 am UTC

I don't know about AIX, you'll want to use metalink.

deleted OS file

Mahomed, January 05, 2007 - 12:54 pm UTC

Hi Tom,

We have a tablespace that has a file which is offline and possibly dropped. I can see the tablespace file when querying dba_data_files. However, the OS file is missing. As a consequence the hot backup is not working. My question is can I re-create this datafile and add it to the tablespace or do I have to do what has been suggested above , i.e export the objects, drop the tablespace, remove the OS files, re-create the tablespace and import?

Thanks
Tom Kyte
January 06, 2007 - 8:36 am UTC

the hot backup is working, maybe YOUR SCRIPT is not functioning properly, but you can certainly hot backup if you wanted to.

If you lost that file, you'll need to get what you can out of that tablespace, and then drop it.

If you have been doing proper backups however, you have the option of restoring that datafile from your backup and applying your redo to it. Since you are obviously in archive log mode, and you have backups - why would you not just do media recovery on it?

delete OS file - followup

Mahomed, January 08, 2007 - 5:36 am UTC

Hi Tom,

Someone created a file and then decided to delete it after having offline dropping it, I assume. So now, no file exists and no backup for it either. In this case, I think I may have to export all objects from tablespace, drop tablespace including contents and datafiles, re-create tablespace and import the objects. Unless there is an easier way. The file is not being used as :
select segment_name, owner from dba_extents where file_id=23
shows no rows



Dropping a tablespace - checks

Arjun, February 02, 2007 - 11:47 am UTC

9i (Release 2) on HP-UX
Hi Tom,
I was just wondering about a check list before removing an "empty" tablespace..that had segments in it:

Make sure the tablespace is not in DBA_SEGMENTS table.
Make sure the tablespace is not a default tablespace for some user or some user has specific quota on it..

then run drop tablespace (including contents and datafiles) command..

any thing additional you want to add?
Tom Kyte
February 02, 2007 - 1:39 pm UTC

... an "empty" tablespace..that had segments in it: ...

oxymoron there... if it has segments, it is not empty.

that check list may or may not be appropriate depending on what you want to do.

I for one would not really care if it was someones 'default' tablespace or they had a quota on it. I really wouldn't.

And if the tablespace has segments in it, as you say it does (but somehow is empty??), it will be in dba_segments - so you would never drop it.

"I see" said the blind man...

Arjun, February 02, 2007 - 1:58 pm UTC

True..if it is not in DBA_SEGMENTS..it is empty..



Example of use of a offline drop datafile

Orlando, May 16, 2010 - 9:34 am UTC

Hi Tom!
I'm now working in a database that has a problem where offlne drop datafiles can be useful.
Database is working fine, tablespace in use is 60GB with many datafiles, I don't intend to drop it - tbspace.
2 datafiles were added and something happened, and now I have in dba_data_files:

FILE_ID FILE_NAME RELATIVE_FNO BYTES USER_BYTES ONLINE_
---------- -------------------------------------------------- ------------ ---------- ---------- -------
33 /u02/oragen76/oradata/ccadm04.ora.ora 33 RECOVER
36 /u02/oragen76/oradata/ccadm05.ora 36 5368709120 5368643584 ONLINE
34 /u02/oragen76/oradata/ccadm05.ora.ora 34 RECOVER

These '.ora.ora' files don't exist in the OS.
I'm already working with support, and I hope I can keep my tablespace and get rid of these files.



Tom Kyte
May 24, 2010 - 7:25 am UTC

if they contained data - probably not. You'd need what we refer to as "backups" for that.

your best bet would be to create a new empty tablespace, copy out what you can and then drop this one.

60gb is a drop in a bucket, not very large, you should have available space for that, if not - it is not that expensive (like maybe $40) to get something temporary to hold it.

restore offline dropped datafiles in standby db

RV, September 29, 2010 - 12:49 am UTC

Tom,

I have a scenario -

We created a phy standby of a database (20TB) on to a different machine - half of the tablespaces were readonly (10TB). So we restored and recovered skip readonly and placed the standby in managed recovery mode after offline dropping the readonly files. Now how to restore the remaining offline dropped readonly datafiles to switchover? v$datafile shows those files in recover status on standby db.

Thanks,
RV

CASCADE CONSTRAINTS option (9iR2)

P, January 28, 2011 - 2:19 pm UTC

Tom's Followup February 3, 2006 - 5pm Central time zone:
"not that I know of, no, you would need to drop the tables/cascade constraints first"

to the question:

"SQL> DROP TABLESPACE TAG_D0 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TAG_D0 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

OK, the error speaks for itself, but is there an easy way to disable all of them in one shot?"

From the Manual:

"To drop a tablespace, use the DROP TABLESPACE statement. The following statement drops the users tablespace, including the segments in the tablespace:

DROP TABLESPACE users INCLUDING CONTENTS;

If the tablespace is empty (does not contain any tables, views, or other structures), you do not need to specify the INCLUDING CONTENTS option. Use the CASCADE CONSTRAINTS option to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace."

Tom Kyte
February 01, 2011 - 3:27 pm UTC

see, learn something new every day :)
thanks!

Accidentally delete a data file

Everton Arantes, February 17, 2011 - 5:55 pm UTC

Hi Tom,

will delete the  file  after  accidentally  "F_ORA9_027_DAT.ORA" I can  not  access  the  database. Could you  help  me  solve  this issue.


SQL> shutdown abort
InstÔncia ORACLE desativada.
SQL> startup
InstÔncia ORACLE iniciada.

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Banco de dados montado.
ORA-01157: nÒo Ú possÝvel identificar/bloquear arquivo de dados 187 - consulte
arquivo de anßlise DBWR
ORA-01110: 187 do arquivo de dados:
'E:\DATABASES\ORACLE\92\DAT\F_ORA9_027_DAT.ORA'


SQL> alter database recover datafile 187;
alter database recover datafile 187
*
ERRO na linha 1:
ORA-00283: sessÒo de recuperaþÒo cancelada devido a erros
ORA-01110: 187 do arquivo de dados:
'E:\DATABASES\ORACLE\92\DAT\F_ORA9_027_DAT.ORA'
ORA-01157: nÒo Ú possÝvel identificar/bloquear arquivo de dados 187 - consulte
arquivo de anßlise DBWR
ORA-01110: 187 do arquivo de dados:
'E:\DATABASES\ORACLE\92\DAT\F_ORA9_027_DAT.ORA'


SQL*Plus: Release 9.2.0.1.0 - Production on Seg Fev 7 21:53:47 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Informe o nome do usußrio: system
Informe a senha:
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Tom Kyte
February 17, 2011 - 6:10 pm UTC

do you have any backups

do you need this data or do you just want to lose it (FOREVER) and open the database

do not expect me to reply immediately - I'm not support and it is dinner time ;)

Datafile offline in noarchivelog mode

CHAKRA, August 08, 2011 - 6:02 am UTC

Hi Tom,

Can you pls go through the below points and tell the difference between them.

Irrespective of database mode (archive or no archive),

1) When I put a datafile in offline and bring back to online, it requires recovery from the redo.

2) When I put a tablespace in offline and bring back to online, it doesn't ask recovery from the redo.

Tom Kyte
August 13, 2011 - 3:27 pm UTC

1) false, you can only offline normal or offline drop in noarchivelog mode. If you offline normal - we checkpoint and then offline it, no redo will be needed. If you offline drop - the file is gone no redo will be needed.

2) correct, it won't ask for redo since it will have checkpointed it first - it will need no redo recovery.

Create tablespace using exisiting datafile

karady, October 25, 2011 - 3:53 pm UTC

Tom -

I was playing with a new tool against development database .
By that , I accidentally deleted a tablespace that had sys.aud$ and sys.fga_log$ ( I moved to these tables using dbms_audit_mgmt.set_audit_trail_location few weeks back) .

The datafile did not get deleted ( thank goodness) , but the tablespace ( in the dictiontary got deleted) .
This database is in noarchivelog and do not have any physical backups .

I am on 11.2.0.2 on Windows .

Is it possible to create a tablespace with the existing data file ? . ( ie , need access to the existing data )


... Contents from alert log ... .
.....
....


DROP TABLESPACE AUDIT_TRAIL_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
WARNING: Cannot delete file D:\ORADATA\DBDEV\AUD01.DBF
Errors in file C:\APP\ORACLE\diag\rdbms\DBDEV\DBDEV\trace\DBDEV_ora_6224.trc:
ORA-01265: Unable to delete DATA D:\ORADATA\DBDEV\AUD01.DBF
ORA-27056: could not delete file
OSD-04024: Unable to delete file.
O/S-Error: (OS 32) The process cannot access the file because it is being used by another process.
Completed: DROP TABLESPACE AUDIT_TRAIL_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

Tom Kyte
October 25, 2011 - 4:17 pm UTC

nope, that data is lost and gone forever. It cannot be reattached to that database.

How will restore drop datafile?

Anil Rajak, May 05, 2016 - 10:55 am UTC

Hi,
I am new in oracle DBA and I created tablespace, datafile, table. I dropped it. now I tried lot of methods and read more articles but till now I am confuse about restore *.dbf file. could you tell me how to solve this problem.
thanks,
Anil
Connor McDonald
May 05, 2016 - 3:08 pm UTC

If the datafile is gone from your filesystem, you need to restore it from backup.

http://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89368

If you haven't got a backup, then your file is gone...

Dropping a datafile offline

Karishma Arora, October 31, 2017 - 6:45 am UTC

Good stuff on dropping a datafile with recovery perspective.

I have the same problem

S., November 29, 2018 - 10:18 pm UTC

Thanks for the great response, I’ve learnt a lot from this.

I have an Oracle 10g database running in HP UX 11i. I was trying to migrate my database to a new server but cloning failed due to a (corrupted?) datafile of size 0 in “recover” state. I was thinking I could simply drop this datafile from its Tablespace and move on. Is it by any means possible? I mean, the database doesn’t even use that datafile any longer, its last access date was in 2016, while all the other Datafiles have the same last access date earlier this week, before I shut down the database for cloning. What can I do?
Connor McDonald
November 30, 2018 - 6:19 am UTC

Create a new tablespace.
Move all objects from the current tablespace to the new one.
Then you can drop the old one.

Recover Dropped Tablespace

Davi, March 09, 2020 - 9:22 am UTC

Hi there,

I want to ask about tablespace.
I have Oracle Database 10g.
Previously I have a problem with database size, then I do some step from internet to drop the tablespace (with contents but not include the datafile). I don't know if it will remove the tablespace from the database.
I still have the datafile that previously connected to that tablespace but I don't have the back up for recovery.

Is that mean all the previous data I have has completely gone?
Is there any way to recover the data with creating new tablespace or configure new database?
I really need help.
Really appreciate if you can help to resolve this issue or give me more explanation.

Thank you in advance.

Best Regards,
Davi
Connor McDonald
March 10, 2020 - 2:30 pm UTC

Without a backup you are out of luck.

When you dropped the tablespace, what we have lost is the dictionary contents of that tablespace. So we no longer know what is in the datafile, even if the contents are still in the datafile.

We don't know the object names, the sizes etc etc. That is all lost.

For Davi

A reader, March 11, 2020 - 5:56 am UTC

Sorry to hear the dilemma that you are in.. lesson learnt don't just blindly implement every solution on the internet. As these may (most likely) destructive.

Anyway, by backup do you mean the backup of the tablespace? 
If you have archives and DB backup maybe you can perform a TSPITR or a DB restore on *another* server before the time you dropped the tablespace.

If this is a Prod host then *backups* shud be available. If non-prod check if the same exists on higher or lower envs and then try to get an idea of the contents.

Hope it helps!

Cheers

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.