7

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following:

CREATE UNDO TABLESPACE UNDOTBS20140508  DATAFILE '+DATA/....../UNDOTBS20140508' SIZE 20G;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES;

When trying to drop the UNDOTBSX02 it show the following error:

Error starting at line : 13 in command -
DROP TABLESPACE UNDOTBSX02 INCLUDING CONTENTS AND DATAFILES
Error report -
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 57
ORA-30013: undo tablespace 'UNDOTBSX02' is currently in use
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I checked UNDOTBSX02 for any pending transaction and it is empty. So how to drop it?

Note : I am not Oracle DBA, but I have to do this task.

migrated from stackoverflow.com May 14 '14 at 11:22

This question came from our site for professional and enthusiast programmers.

  • Hello, How do you know there is no transaction using the old undo tbs? Maybe you have to just wait for undo retention. Could you do a restart of the database? – eliatou May 14 '14 at 10:02
  • As @eliatou says, probably a retention problem. oracleflash.com/32/… sums it up – Philᵀᴹ May 14 '14 at 14:28
  • No any connected session SELECT a.name,b.status , d.username , d.sid , d.serial# FROM v$rollname a,v$rollstat b, transaction c , v$session d WHERE a.usn = b.usn AND a.usn = c.xidusn AND c.ses_addr = d.saddr AND a.name IN ( SELECT segment_name FROM dba_segments WHERE tablespace_name = 'UNDOTBSX02'); – Mohammad Jolani May 14 '14 at 14:33
  • ok, no more transaction. Which retention time ? SQL> sho parameter UNDO_RETENTION. Could you restart your database? – eliatou May 14 '14 at 15:15
  • You could shutdown db, start it up in restricted mode and then try to drop old tablespace. – Raj May 15 '14 at 11:27
3

I tried the above suggestions, but did not work, such as select SQL did not get results and I still cannot drop the undo table space. The fundamental issue is that the old undo table space is still registered in Oracle as its undo, therefore we need to change it to the new undo table space.

Here are what I did and it worked:

  1. create a new UNDO table space:
CREATE SMALLFILE UNDO TABLESPACE "UNDO" 
DATAFILE '+DATA/t2/datafile/undo_01.dbf' SIZE 10G 
REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 10G;
  1. Switch the new UNDO to the "official" undo table space:
alter system set undo_tablespace='UNDO' scope=both;
  1. drop the old undo table space:
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

hope this helps.

  • This is the same what the original poster tried, and may fail with the same error as long as there is active undo in the original tablespace. – Balazs Papp Mar 13 '16 at 19:31
  • Not quite. Dave added the option of scope=both to his alter system statement, which might change the way oracle reacts to the change. – hot2use Nov 15 '16 at 7:54
  • 1
    The scope=both should not change the behaviour as scope=both is the default. You have to explicitly state scope=spfile or scope=memory if you want the change the parameter in only one location. – jmk Nov 15 '16 at 13:18
1

the error message mean the tablespace is in use, and since the undo tablespace is critical tablespace you need to shut it down first by using:

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN ( 
          SELECT segment_name
          FROM dba_segments 
          WHERE tablespace_name = 'UNDOTBSX02'
         ); 

then kill the SID for the undo tablespace by using

alter system kill session 'SID,serial'; --change the values of sid and serial by the ones  that being retrived from the previouse command

now you should be able to drop it.

  • For the select Statement it returns empty result. – Mohammad Jolani May 20 '14 at 19:38
  • In this case you need to wait until there is no pending transaction then restart instant. and drop it – Ahmad Abuhasna May 20 '14 at 19:49
1

You don't want to start dropping connections. How do you know what those connections are doing, and what might break if those connections are dropped.

You are better off waiting for sessions to start using the new tablespace. Eventually, the old undo tablespace will be not longer be in use. At that time you can drop it. If you need disk space you can try shrinking the undo tablespace, but that might not work. You can also get downtime to shutdown the database, bring up the database in restrict mode, then drop the old undo tablespace, that shutdown and startup the database unrestricted.

1

The answers provided by previous posters are all correct regarding the syntax to create a new UNDO tablespace and to switch system to the new UNDO tablespace.

The Oracle documentation states that the following statement will switch to the new UNDO tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = <name of new UNDO TS>;

e.g. for @Mohammad Jolani:

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS20140508;

However there are some restrictions to this switching which are documented as:

The switch operation does not wait for transactions in the old undo tablespace to commit. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace. An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment).

So according to A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. you would have to wait until Oracle has fully switched the current UNDO tablespace completely.

Try querying the status of the tablespace with :

select file#, ts#, status, bytes, substr(name, 0, 30) from v$datafile;

You might find that your old UNDO tablespace is still being used or in the PENDING OFFLINE state.

You could also try switching the UNDO tablespaces back and forth with:

ALTER SYSTEM SET UNDO_TABLESPACE = '';
An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment). If the parameter value for UNDO TABLESPACE is set to '' (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care, because if there is no undo tablespace available, the SYSTEM rollback segment is used. This causes ORA-01552 errors to be issued for any attempts to write non-SYSTEM related undo to the SYSTEM rollback segment.

You can not force the switching unless you shut down your database. If Oracle thinks it still needs the old UNDO TS then so be it. Your system will already be writing to the new UNDO TS so there is no need to force anything unless you have special requirements.

0

Saw this link for solution. It worked for me!

Corresponding rollback segments need to be offline before you can drop the tablespace.

Also, restarting the database is required before new parameter values take effect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.