This content has been marked as final. Show 57 replies
I can not OFFLINE undots1
SQL> ALTER TABLESPACE undotbs1 OFFLINE; Tablespace altered. SQL> select tablespace_name||' '||file_name||' '||status from dba_data_files; TABLESPACE_NAME||''||FILE_NAME||''||STATUS -------------------------------------------------------------------------------- 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.
You need to switch to your second undo tablespace before you go and drop the first.1 person found this helpful
Take a look at the following:
alter system set undo_tablespace = undotbs2 scope=both ;
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.
Lol....I hear you!!1 person found this helpful
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 :(
Please help ...Thanks
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'
Edited by: yxes2013 on 5.5.2013 4:40
Can you try killing and restarting the job, Are you resuming the job anyway ? Reason being its using old undo1 person found this helpful
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?
Im not sure about your archive mode but try this one1 person found this helpful
ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;
Then drop the tablespace:
DROP TABLESPACE INCLUDING CONTENTS;
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.
Alter database datafile 'filename' offline drop;
Alter database open;
drop tablespace ;
Edited by: Karan on May 5, 2013 5:39 PM
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?
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.
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?
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
No you can have only 1 undo tablespace online, either undo1 or undo2 but not both
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?