Answer:-
You cannot drop a datafile without open state of database .
so you have to offline the datafile .
in Archive log mode
--------------------
SQL>alter database datafile '.../test.dbf' offline ;
in noarchive log mode
---------------------
SQL>alter database datafile '.../test.dbf' offline drop ;
It does nothing more than taking offline the file.
DROP option is mandatory in NOARCHIVELOG mode and is ignored in ARCHIVELOG mode.
we take datafile with "offline drop" then we can't back datafile online.
but You can do it until the offline SCN goes out of the online redo logs.
So how long depends on your logs size and workload.
examples:-
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> alter database datafile 11 offline drop;
Database altered.
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database datafile 11 online;
Database altered.
Restrictions for Dropping Datafiles
---------------------------------------
The following are restrictions for dropping datafiles and tempfiles:
The database must be open.
If a datafile is not empty, it cannot be dropped.
If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
You cannot drop the first or only datafile in a tablespace.
This means that DROP DATAFILE cannot be used with a bigfile tablespace.
You cannot drop datafiles in a read-only tablespace.
You cannot drop datafiles in the SYSTEM tablespace.
If a datafile in a locally managed tablespace is offline, it cannot be dropped.
more...
No comments:
Post a Comment