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

advertisements

_____________________________________________________________________________________________________________________

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.

_____________________________________________________________________________________________________________________

Website Stats