Release/Shrink UNDO tablespace

  

Sometimes there will be situations where we run out of DB storage due to huge undo growth and we wanted to shrink it to release some space for other datafiles in the database and The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace

Create New UNDOTBS2 tablespace

SQL> create undo tablespace UNDOTBS2 datafile '+DATAC1' size 500M;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;

System altered.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS1                       OFFLINE                  12

UNDOTBS2                       ONLINE                   10

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE.

Check any segments other than OFFLINE status using below

SQL>  select status,segment_name from dba_rollback_segs where status not in ('OFFLINE') and tablespace_name='UNDOTBS1';

no rows selected

Since there are no segments then we can easily drop the tablespace and release space

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS2                       ONLINE                   10

UNDOTBS3                       OFFLINE                  22

Now reset to same tablespace by creating same tablespace again

SQL> create undo tablespace UNDOTBS1 datafile '+DATAC1' size 500M;

Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE =UNDOTBS1 SCOPE=BOTH;

System altered.

SQL>  select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

TABLESPACE_NAME                STATUS             COUNT(*)

------------------------------ ---------------- ----------

SYSTEM                         ONLINE                    1

UNDOTBS1                       ONLINE                   15

UNDOTBS2                       OFFLINE                  10

Sometimes dropping the old tablespace may give ORA-30013: undo tablespace '%s' is currently in use. This error indicates you must wait for the undo tablespace to become unavailable. In other words, you must wait for existing transaction to commit or rollback.   

Also, According to ORACLE DOC:

On some platforms, disk space is not freed to the OS until the database is restarted.  The disk space will remain "allocated" from the OS perspective until the database restart.

expdp ORA-01775: looping chain of synonyms

While doing export on my 12.1 database i get below error

oracledev.domain.com:REPODB:/home/oracle $ expdp directory=STATSDIR dumpfile=REPODB_Triggers.dmp logfile=exp_REPODB_Triggers.log include=TRIGGER metrics=y schemas= Workers

Export: Release 12.1.0.2.0 - Production on Wed Oct 23 10:48:44 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-01775: looping chain of synonyms


FIX:


SQL> Select owner, object_name, object_type, status   from dba_objects  where object_name like '%SYS_EXPORT%';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE             STATUS
----------------------- -------
PUBLIC
SYS_EXPORT_SCHEMA_01
SYNONYM                 VALID

SYSTEM
SYS_EXPORT_TABLE_01
TABLE                   VALID

connect / as sysdba
drop public synonym sys_export_schema_01;

Now rerun export again

(OR)

If you dont want to drop the table now then give some job name ( job_name=<JOB_NAME>" ) and run the export 

Referrence:
DataPump Import Results In ORA-39001 Invalid Argument Value ORA-1775 Looping Chain Of Synonyms (Doc ID 459151.1)

IMPDP Job Hanging On STATISTICS/MARKER

DB Version : 12.1
OS Version : RHEL 6

While doing import for one of the Env my impdp job just waiting at STATISTICS/MARKER step and taking very very long though i'm doing small set of data import, and if i check the JOB status and it shows job is 100% Done and there is no movement at all even in DB Alert log

Impdp job at “Processing object type SCHEMA_EXPORT/STATISTICS/MARKER”

After doing some research online and according to oracle looks like this is Due to the BUG and below is the work around/Solutions

Data pump Import Job Is Hanging On STATISTICS/MARKER (Doc ID 2135702.1)