Datafile has been removed already , But I still can find its information in V$datafile
why?
Printable View
Datafile has been removed already , But I still can find its information in V$datafile
why?
Unless you offline and remove the datafile with a drop command the entry will remain in the data dictionary. Simply removing the datafile will not remove it association with the Oracle database.
Regards
would you please tell us how did you remove the datafile...means using what command??
as per my knowledge .
once created datafile cannot be dropped through normal sql syntax . tablespace associated with the datafile can be dropped, which removes the datafile from v$datafile.
alter database datafile 'xxx' offline drop;
Or if it is the only datafile in the tablespace,
Drop tablespace name including contents;
offlince drop a datafile will force you to drop entire tablespace
Pando, what do u mean by 'force to drop entire tablespace'? I agree that offline a datafile means that u may drop the tablespace at a later time and not that U will be forced to drop it.
after droping the datafile . i believe we cannot make it online
with a similar syntax . only option it leaves is to recover the database if it is in archivelog mode .
is this syntax valid prior to version 8 .
I think we moved from the actual question posed. The one line reply is:
"References to the dropped datafiles are not eliminated from views unless the tablespace in which they exist is recreated or dropped. "
Quote:
Originally posted by soniaarora
Pando, what do u mean by 'force to drop entire tablespace'? I agree that offline a datafile means that u may drop the tablespace at a later time and not that U will be forced to drop it.
may?
no, you are forced to, you cant never ever remove a datafile from a tablespace
Sorry Pando, I dont agree with u. Go thru this note in Metalink:
Bookmark Fixed font Go to End
Doc ID: Note:111316.1
Subject: How to 'DROP' a Datafile from a Tablespace
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 31-MAY-2000
Last Revision Date: 27-APR-2001
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 the
steps needed to delete a datafile and in contrast, and when the OFFLINE DROP
command is used.
SCOPE & APPLICATION
There are two situations where people may want to 'remove' a datafile from a
tablespace:
1. You have 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 start
because 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 from a Tablespace:
===========================================
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 TABLESPACEINCLUDING 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 ='';
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 TABLESPACEINCLUDING 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 ='';
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=''
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 DATAFILEOFFLINE 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 DATAFILEOFFLINE;
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
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.
RELATED DOCUMENTS
-----------------
[NOTE:30910.1] Recreating database objects
[NOTE:1013173.6] Recovering from a lost datafile in a USER tablespace
[NOTE:1013115.6] Recovering from a lost datafile in an INDEX tablespace
[NOTE:1013221.6] Recovering from a lost datafile in a ROLLBACK tablespace
[NOTE:1029252.6] How to resize a datafile
.
--------------------------------------------------------------------------------
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
NOTE:
The ALTER DATABASE DATAFILE 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
go through it again carefully
Pando,
This is exactly what I meant. It is not that after u offline a datafile, u HAVE to drop the tablespace. It is only that this is the workaround available to remove the datafile which is not required in the tablespace.
I think we have some missunderstanding here, are you saying you can remove a datafile from a tablespace? Or you are saying you cannot?
time for me to leave office lest I shud miss my bus. Shall resume on Monday. sorry
sonia is correct the only way to drop a datafile from db w/o dropping the ts is to offline drop it provided it is not the last file in the ts . it will not force you to drop the ts . This will also remove the entry from the data dictionary.
I do not agree, Pando is right here : you CANNOT drop a datafile w/o dropping the tablespace.
you can make the datafile offline, but it will still be on your HD, it won't be dropped !!
Pando is right, the file is simply marked as offline in the controlfile. As the document say this allows you to open the database without the controlfile trying to checkpoint the file.
The original post however is possible if running under Unix as it is possible to physically remove the datafile while the database is running but the entry will still appear as valid until the data within that file is accessed or the database is bounced.
Regards
We r not talking about dropping the file from HD but dropping it from the DB,and that is what we accomplish by this command. Pls note the command drop tablespace including contents also does not drop the datafiles from HD. Prior to 9i there was no way to remove the files from HD by firing DB commands . The files had to be physically removed from the HD
The original post states "Datafile has been removed already" surely this means it has been removed from the HD
Regards
I'm agree with the arguments given by Sonia and sm8728...truly valid arguments...drop offline will logically remove the file from the list...at the time of startup it will never ask for the dropped datafile for recovery...what does this mean...simply a case of dropping the datafile...
Sandy
----------------------------------
Originally by- pipo
I do not agree, Pando is right here : you CANNOT drop a datafile w/o dropping the tablespace.
you can make the datafile offline, but it will still be on your HD, it won't be dropped !!
------------------------------------
Even if you drop the tablespace the datafiles will still be in your HD. The only way you can remove a file from HD is by OS command only...
Sandy
yes, and AFTER having dropped the tablespace... if you wish to make it clean of course
pando
quote:
---------------------------------------------------------------------
Originally posted by soniaarora
Pando, what do u mean by 'force to drop entire tablespace'? I agree that offline a datafile means that u may drop the tablespace at a later time and not that U will be forced to drop it.
---------------------------------------------------------------------
may?
no, you are forced to, you cant never ever remove a datafile from a tablespace
-----------------------------------------------
Don’t be so stubborn
what will happen if my TS has more then one datafile...if the command will force me to drop entire tablespace...??
As I told earlier, when you offline drop the datafile oracle will never ask you for recovery of this datafile at the time of next startup...even if you drop this file physically from HD... what does this mean...
Logically I have dropped my datafile.
think about it...
Sandy
i support pando . we cannot ever drop a datafile . if u drop it u need to drop the tablespace as well . else u need a recovery to be performed .
OFFLINE takes 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.
Hope this makes u clear .
====
sandycrab
As I told earlier, when you offline drop the datafile oracle will never ask you for recovery of this datafile at the time of next startup...even if you drop this file physically from HD... what does this mean...
========
what do u mean by this . u want to leave the database in a recovery mode ???? it is not an ideal DBA practice as per my knowledge.
Quote:
Originally posted by prakashs43
i support pando . we cannot ever drop a datafile . if u drop it u need to drop the tablespace as well . else u need a recovery to be performed .
OFFLINE takes 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.
Hope this makes u clear .
after Offline drop[ing] a datafile Oracle will never ask you for recovery at the time of next startup even the datafile is been removed physically from the HD. I would never like to bring the datafile online as I've dropped it only because I don't want it any more.
How can you say U need to drop the TS as well. Plz let me know the case where I have more then one datafile in my TS. Try to do this in your experiment DB and you'll understand...
I am not sure what's so hard to understand, even the note from Metalink is pasted here and it's clear stating
When you offline drop a datafile that datafile is not removed from dictionary, when Oracle has to allocate extents it will still try to allocate extents thereCode:NOTE:
The ALTER DATABASE DATAFILE 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.
as you can see even you offline drop the datafile the file still exists in data dictionary. What does that mean? It means Oracle will try to use free extents in the drop file even it does not exist. What is the fix of this? Drop the tablespaceCode:
SQL> conn / as sysdba
Connected.
SQL> create tablespace dropme
2 datafile 'D:\ORACLE\ORADATA\DEV901\DROPME01.DBF' size 5M,
3 'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' size 5M
4 extent management dictionary
5 minimum extent 128k
6 default storage (initial 128k
7 next 128k
8 pctincrease 0);
Tablespace created.
SQL> create table objects tablespace dropme as select * from dba_objects;
Table created.
SQL> select * from fet$ where file# in (11, 12);
TS# FILE# BLOCK# LENGTH
---------- ---------- ---------- ----------
15 12 450 831
15 11 482 799
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 105672596 bytes
Fixed Size 282516 bytes
Variable Size 71303168 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
SQL> alter database datafile 'D:\ORACLE\ORADATA\DEV901\DROPME02.DBF' offline drop
Database altered.
SQL> alter database open;
Database altered.
SQL> select * from fet$ where file# in (11, 12);
TS# FILE# BLOCK# LENGTH
---------- ---------- ---------- ----------
15 12 450 831
15 11 482 799
SQL> select file#, tablespace_name, name, b.status
2 from dba_data_files a, v$datafile b
3 where a.file_id = b.file#;
FILE# TABLESPACE_NAME NAME STATUS
---------- ------------------------------ -------------------------------------------------- -------
1 SYSTEM D:\ORACLE\ORADATA\DEV901\SYSTEM01.DBF SYSTEM
2 UNDOTBS D:\ORACLE\ORADATA\DEV901\UNDOTBS01.DBF ONLINE
3 CWMLITE D:\ORACLE\ORADATA\DEV901\CWMLITE01.DBF ONLINE
4 DRSYS D:\ORACLE\ORADATA\DEV901\DRSYS01.DBF ONLINE
5 EXAMPLE D:\ORACLE\ORADATA\DEV901\EXAMPLE01.DBF ONLINE
6 INDX D:\ORACLE\ORADATA\DEV901\INDX01.DBF ONLINE
7 TOOLS D:\ORACLE\ORADATA\DEV901\TOOLS01.DBF ONLINE
8 USERS D:\ORACLE\ORADATA\DEV901\USERS01.DBF ONLINE
9 OEM_REPOSITORY D:\ORACLE\ORADATA\DEV901\OEM_REPOSITORY.DBF ONLINE
10 DATA_SMALL D:\ORACLE\ORADATA\DEV901\DATA_SMALL01.DBF ONLINE
11 DROPME D:\ORACLE\ORADATA\DEV901\DROPME01.DBF ONLINE
12 DROPME D:\ORACLE\ORADATA\DEV901\DROPME02.DBF OFFLINE
SQL> drop table objects;
Table dropped.
SQL> select * from fet$ where file# in (11, 12);
TS# FILE# BLOCK# LENGTH
---------- ---------- ---------- ----------
15 12 450 831
15 11 386 32
15 11 482 799
15 12 386 32
15 12 258 32
15 11 418 32
15 12 418 32
15 11 450 32
15 11 258 32
15 11 290 32
15 12 290 32
15 11 322 32
15 12 322 32
15 11 354 32
15 12 354 32
15 11 130 32
15 12 130 32
15 11 162 32
15 12 162 32
15 11 194 32
15 12 194 32
15 11 226 32
15 12 226 32
15 11 2 32
15 12 2 32
15 11 34 32
15 12 34 32
15 11 66 32
15 12 66 32
15 11 98 32
15 12 98 32
Try it yourself
Good Explanation Pando !!
oh sm ... drop tablespace tbsname including contents and datafiles;
ok?
or ...
alter database datafile tbsname resize 1m; :) :)
F.
nope, you dont need to shutdown the database in NT to remove a datafile that has been logically removed from Oracle. You just query any of datafile related datadictionary views ater removing the datafile from Oracle using sql syntex. Your lock gets released and you can remove the file physically...Quote:
Originally posted by soniaarora
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.)
Steps on NT:
alter database drop <; --logical deletion of datafile
select * from dba_data_files; --this releases locks on datafile
remove the file physically..
This works...
-nagarjuna
nope.. We can use a workaround to remove the datafile from a tablespace.Quote:
Originally posted by soniaarora
Pando,
This is exactly what I meant. It is not that after u offline a datafile, u HAVE to drop the tablespace. It is only that this is the workaround available to remove the datafile which is not required in the tablespace.
Steps:
1. alter database drop datafile <>;
2. alter database backup controlfile to trace;
3. edit the controlfile creation script generated by the above command. Remove the entry of the datafile that we have dropped in step 1.
4. Now, start up the database in nomount state and recreate the control file.
5. Do the recovey if needed using current logfiles.
6. Open the database.
This works fine. But, we need some experiance to do this. Be, careful doing this..
-nagarjuna
sigh, so many PROFESSIONALs discussing such a MEANINGLESS topic!
It is so clear that you have to rebuild the tablespace after your offline the datafile.
Offline drop the datafile only allows you to open the database temporary so that you can export/move the tables in the tablespace and drop and recreate it later.
I think the metalink note has said it clearly.
nagarjuna:
Your way of removing that datafile is TOTALLY WRONG!
I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary
mmm...v$datafile is a controlfile view...Quote:
Originally posted by pando
I still dont understand what's so hard to understand? Even you remove it from controlfile it is still present in data dictionary
chao_ping > I agree that, this is a meaningless topic.. I posted that msg just to add spice to our DBA life. We get bored with daily routine works.. These kinda experiments gives some fun.. nothing much.. I do agree that my reply was meaningless..
Oracle use v$datafile (control flie) mostly in ALTER DATABASE OPEN(MOUNT) process.Quote:
Originally posted by nagarjuna
[mmm...v$datafile is a controlfile view...
[/B]
In most others situations oracle will use sys.ts$ and sys.file$ tables.
but records about datafile still present in dictioany tables and don't present in controlfile!!!
Very interesting situation...
THIS IS WRONG WAY.
[Edited by Shestakov on 09-07-2002 at 12:27 PM]
Pando is right, since you can`t control how data is writen to the files, you don`t know the contets of the file you are "droping".
A table may span over more than one datafile, for example, so there is no logic in droping a datafile, not in my eyes, anyway.
I am extremely sorry for that post. That was not the right way to do. That was really a stupidity. Some problem with my basics I guess..
soniaarora
you cannot remove a datafile from a tablespace, even the note you pasted said so
read it again and properly!
thank you