|
-
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
"Greatest Rewards come only with Greatest Commitments!"
-
----------------------------------
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
"Greatest Rewards come only with Greatest Commitments!"
-
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
"Greatest Rewards come only with Greatest Commitments!"
-
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 .
siva prakash
DBA
-
====
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.
siva prakash
DBA
-
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...
"Greatest Rewards come only with Greatest Commitments!"
-
I am not sure what's so hard to understand, even the note from Metalink is pasted here and it's clear stating
Code:
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.
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 there
Code:
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
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 tablespace
Try it yourself
-
Good Explanation Pando !!
-
oh sm ... drop tablespace tbsname including contents and datafiles;
ok?
or ...
alter database datafile tbsname resize 1m; 
F.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote