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.
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.
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
Im not sure about your archive mode but try this one
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
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?