OraFAQ Forum: Server Administration » how to offline datafile in no archive mode

Home » RDBMS Server » Server Administration » how to offline datafile in no archive mode (11.2.0.1 windows xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to offline datafile in no archive mode [message #529432]

Mon, 31 October 2011 20:05

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

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529433 is a reply to message #529432]

Mon, 31 October 2011 20:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529434 is a reply to message #529433]

Mon, 31 October 2011 21:17

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

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529437 is a reply to message #529434]

Mon, 31 October 2011 22:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.lmgtfy.com/?q=oracle+drop+offline+datafile

Is GOOGLE broken for YOU?

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529447 is a reply to message #529434]

Tue, 01 November 2011 01:41

halim
Messages: 100
Registered: September 2008

Senior Member

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

regards
Halim

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529452 is a reply to message #529447]

Tue, 01 November 2011 03:25

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

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529453 is a reply to message #529452]

Tue, 01 November 2011 03:36

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

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529487 is a reply to message #529453]

Tue, 01 November 2011 07:58

joy_division
Messages: 4963
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>

Report message to a moderator

Re: how to offline datafile in no archive mode [message #529565 is a reply to message #529487]

Tue, 01 November 2011 20:38

andy huang
Messages: 498
Registered: July 2011

Senior Member

Thanks!

Report message to a moderator

Previous Topic:	get script about create partition by auto
Next Topic:	temporary tablespace issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 00:57:34 CDT 2024