1 2 3 4 Previous Next 57 Replies Latest reply on May 6, 2013 7:19 AM by Nicolas.Gasparotto


      Hi all,

      I want to shrink my UNDOTS1 since it got grown so big.

      I created anothe UNDOTS2 so I can drop the first.

      But I got confused which to drop because I did not know I got 2 UNDOTS1?
      SQL> select tablespace_name||' '||file_name from dba_data_files;
      UNDOTBS1 /u02/oradata/oracle/undo/undo02.dbf
      UNDOTBS2 /free/edmp/undotbs02.dbf
      USERS /u01/app/oracle/oradata/edmp/users01.dbf
      UNDOTBS1 /u01/app/oracle/oradata/edmp/undotbs01.dbf
      SYSAUX /u01/app/oracle/oradata/edmp/sysaux01.dbf
      SYSTEM /u01/app/oracle/oradata/edmp/system01.dbf
      DATA_EDMP /u02/oradata/edmp/data_edmp.dbf
      Is it possible to have 2 UNDO TABLESPACES for UNDO?
      How do I drop it or which undo is droppable?

        • 1. Re: UNDO CONFUSIONS
          I can not OFFLINE undots1
          SQL>  ALTER TABLESPACE undotbs1 OFFLINE;
          Tablespace altered.
          SQL>  select tablespace_name||' '||file_name||' '||status from dba_data_files;
          UNDOTBS1 /u02/oradata/oracle/undo/undo02.dbf AVAILABLE
          UNDOTBS2 /free/edmp/undotbs02.dbf AVAILABLE
          USERS /u01/app/oracle/oradata/edmp/users01.dbf AVAILABLE
          UNDOTBS1 /u01/app/oracle/oradata/edmp/undotbs01.dbf AVAILABLE
          SYSAUX /u01/app/oracle/oradata/edmp/sysaux01.dbf AVAILABLE
          SYSTEM /u01/app/oracle/oradata/edmp/system01.dbf AVAILABLE
          DATA_EDMP /u02/oradata/edmp/data_edmp.dbf AVAILABLE
          7 rows selected.
          • 2. Re: UNDO CONFUSIONS
            You need to switch to your second undo tablespace before you go and drop the first.
            alter system set undo_tablespace = undotbs2 scope=both ;
            Take a look at the following:


            Note the following:
            An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.
            The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
            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.
            1 person found this helpful
            • 3. Re: UNDO CONFUSIONS
              John Stegeman
              Take a look at the following:
              Good luck with that suggestion.
              1 person found this helpful
              • 4. Re: UNDO CONFUSIONS
                Lol....I hear you!!
                1 person found this helpful
                • 5. Re: UNDO CONFUSIONS
                  Hi all,

                  When I alter the undo to >alter system set undo_tablespace = UNDOTBS2 ;
                  Then ALTER TABLESPACE undotbs1 OFFLINE;
                  My undotbs01 got error and
                  My expdp got error now :(
                  ORA-31626: job does not exist
                  ORA-31637: cannot create job SYS_EXPORT_SCHEMA_02 for user SYSTEM
                  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
                  ORA-06512: at "SYS.KUPV$FT_INT", line 798
                  ORA-39080: failed to create queues "KUPC$C_1_20130505190314" and "KUPC$S_1_20130505190314" for Data Pump job
                  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
                  ORA-06512: at "SYS.KUPC$QUE_INT", line 1530
                  ORA-00376: file 3 cannot be read at this time
                  ORA-01110: data file 3: '/u01/app/oracle/oradata/edmp/undotbs01.dbf'
                  Please help ...Thanks

                  Edited by: yxes2013 on 5.5.2013 4:40
                  • 6. Re: UNDO CONFUSIONS
                    Can you try killing and restarting the job, Are you resuming the job anyway ? Reason being its using old undo
                    1 person found this helpful
                    • 7. Re: UNDO CONFUSIONS
                      I revert back the undo to the old one.
                      If I online again the UNDOTBS1 the exports works fine, but how can I drop it if expdp uses it?
                      • 8. Re: UNDO CONFUSIONS
                        Im not sure about your archive mode but try this one

                        ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;

                        Then drop the tablespace:


                        The above is for disasters when you dont have backup and you want to bring your critical files online... If you really dont need it, go for it

                        Do the follwing steps.
                        Shutdown Immediate;
                        Startup Mount;
                        Alter database datafile 'filename' offline drop;
                        Alter database open;
                        drop tablespace ;

                        Edited by: Karan on May 5, 2013 5:39 PM
                        1 person found this helpful
                        • 9. Re: UNDO CONFUSIONS
                          So if I had drop it after I offline the undotbs1...It shouLd have not been looked for by the EXPDP?

                          I am not in archive log mode, that is why I am afraid to offline drop it :(

                          Are you sure if I drop it, the expdp will not be looking for it? or do I need to bounced the database?
                          • 10. Re: UNDO CONFUSIONS
                            So why your database is not running in the archive log mode? Anyways, at any time, only one undo tablespace can stay online so except for the one which is set currently as the undo tablespace for the database , drop the other one.

                            • 11. Re: UNDO CONFUSIONS
                              Is there a way to shrink it only? i am afraid to drop it becuase it is not in archivelog mode.

                              I have 2 undo tablespace, UNDOTS1 (with 2 datafiles) and UNDOTS2 (with 1 datafile)

                              How do I know that a tablespace is created as undo?

                              Can I have them both online and switch back and forth with the other when one grown so big?
                              • 12. Re: UNDO CONFUSIONS
                                Any new/Fresh transaction or a new/Fresh job will surely use the new undo tablespace, and as above said only 1 undo tablespace can be active which is settled by undo_tablespace parameter
                                • 13. Re: UNDO CONFUSIONS
                                  No you can have only 1 undo tablespace online, either undo1 or undo2 but not both
                                  • 14. Re: UNDO CONFUSIONS
                                    Yes I got that, I have only 1 active which is UNDOTBS2.

                                    But why is that when I ran expdp ....it was looking for UNDOTBS1 which has offline datafiles? Do I need to bounce the database when switching undo tablepace?

                                    1 2 3 4 Previous Next