Select from System Package DBMS_METADATA Failed: ORA-25153: Temporary Tablespace is Empty, ORA-06512


Error Description:
Select from system packages getting failed with following error
SQL> select dbms_metadata.get_ddl ('TABLE', 'DUAL','SYS') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_METADATA", line 4018

ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

Solution Description:
The error is because the temporary tablespace is empty. The temporary tablespace (default TEMP) might have created but no datafiles would have been added to the tablespace. Verify the temp tablespace details using following queries and add temporary files to the temporary tablespace.

Check the temp tablespace name
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME LIKE 'TEMP%';

TABLESPACE_NAME
------------------------------
TEMP

Check whether any tempfile has been already added.
SQL> SELECT * FROM DBA_TEMP_FILES;

no rows selected

Add temporary file to the temp tablespace.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/TESTDB/temp01.dbf' size 500m reuse;

Tablespace altered.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google