Home » RDBMS Server » Server Administration » how to offline datafile in no archive mode (11.2.0.1 windows xp)
how to offline datafile in no archive mode [message #529432] Mon, 31 October 2011 20:05 Go to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Dear all,
I want to drop a datafile in my test db which is in no archive mode,at first, i want to offline the datafile,but it failed,is there any way to do it?

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3237
Current log sequence           3239
SQL> Select file_name
  2  From Dba_Data_Files
  3  Where file_name='E:\ORACLE_DATA\USERS02.DBF';

FILE_NAME
--------------------------------------------------------------------------------
E:\ORACLE_DATA\USERS02.DBF

SQL> Alter Database Datafile 'E:\ORACLE_DATA\USERS02.DBF' Offline;
Alter Database Datafile 'E:\ORACLE_DATA\USERS02.DBF' Offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

Re: how to offline datafile in no archive mode [message #529433 is a reply to message #529432] Mon, 31 October 2011 20:31 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
01145, 00000, "offline immediate disallowed unless media recovery enabled"
// *Cause: ALTER TABLESPACE ... OFFLINE IMMEDIATE or ALTER DATABASE DATAFILE
//         ... OFFLINE is only allowed if database is in ARCHIVELOG mode.
// *Action:Take tablespace offline normally or shutdown abort. Reconsider your
//         backup strategy. You could do this if you were archiving your logs.
Re: how to offline datafile in no archive mode [message #529434 is a reply to message #529433] Mon, 31 October 2011 21:17 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
I have offline the tablespace USERS,but the datafile can not drop.
SQL> Alter Tablespace Users Offline;

Tablespace altered.

SQL> Alter Database Datafile 'E:\ORACLE_DATA\USERS02.DBF' Offline;

Database altered.

SQL> Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF'
  2  /
Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF'
*
ERROR at line 1:
ORA-03262: the file is non-empty

Re: how to offline datafile in no archive mode [message #529437 is a reply to message #529434] Mon, 31 October 2011 22:09 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
http://www.lmgtfy.com/?q=oracle+drop+offline+datafile

Is GOOGLE broken for YOU?
Re: how to offline datafile in no archive mode [message #529447 is a reply to message #529434] Tue, 01 November 2011 01:41 Go to previous messageGo to next message
halim
Messages: 99
Registered: September 2008
Location: Bangladesh
Member

see here
http://halimdba.blogspot.com/2011/09/how-to-skip-wrongly-deleted-datafile.html

regards
Halim
Re: how to offline datafile in no archive mode [message #529452 is a reply to message #529447] Tue, 01 November 2011 03:25 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
The datafile can not contain any objects,but it can not drop also.
SQL> Select file_id,file_name
  2  From Dba_Data_Files Where File_name='E:\ORACLE_DATA\USERS02.DBF';

   FILE_ID   FILE_NAME
------------------------------------------------
    7        E:\ORACLE_DATA\USERS02.DBF


SQL> Select Count(1)From dba_extents bb
  2  Where bb.file_id = 7;

  COUNT(1)
----------
         0

SQL> purge Recyclebin;

Recyclebin purged.

SQL> Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF';
Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF'
*
ERROR at line 1:
ORA-03262: the file is non-empty
Re: how to offline datafile in no archive mode [message #529453 is a reply to message #529452] Tue, 01 November 2011 03:36 Go to previous messageGo to next message
andy huang
Messages: 498
Registered: July 2011
Senior Member
hi,
A new issue is raise.
SQL> Purge Tablespace Users;

Tablespace purged.

SQL> Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF';
Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF'
*
ERROR at line 1:
ORA-01662: tablespace 'USERS' is non-empty and cannot be made temporary
Re: how to offline datafile in no archive mode [message #529487 is a reply to message #529453] Tue, 01 November 2011 07:58 Go to previous messageGo to next message
joy_division
Messages: 4485
Registered: February 2005
Location: East Coast USA
Senior Member
andy huang wrote on Tue, 01 November 2011 04:36
hi,
A new issue is raise.
SQL> Purge Tablespace Users;

Tablespace purged.

SQL> Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF';
Alter Tablespace Users Drop Datafile 'E:\ORACLE_DATA\USERS02.DBF'
*
ERROR at line 1:
ORA-01662: tablespace 'USERS' is non-empty and cannot be made temporary


Why can you not look up these errors yourself?
[localhost.localdomain] /home/zeppo> oerr ora 1662
01662, 00000, "tablespace '%s' is non-empty and cannot be made temporary"
// *Cause: Tried to convert a non-empty tablespace to a temporary tablespace
// *Action: To drop all the objects in the tablespace.
[localhost.localdomain] /home/zeppo>

Re: how to offline datafile in no archive mode [message #529565 is a reply to message #529487] Tue, 01 November 2011 20:38 Go to previous message
andy huang
Messages: 498
Registered: July 2011
Senior Member
Thanks!
Previous Topic: get script about create partition by auto
Next Topic: temporary tablespace issue
Goto Forum:
  


Current Time: Wed Jul 23 03:24:31 CDT 2014

Total time taken to generate the page: 0.05994 seconds