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.

_____________________________________________________________________________________________________________________

Live Traffic Feed

A visitor from Utrecht viewed 'ORA-00059: maximum number of DB_FILES exceeded - F' 18 mins ago

A visitor from Oregon viewed 'Sql Query to Find and Reclaim the Fragmented Space' 22 mins ago

A visitor from Quito viewed 'Will IGNORE=Y Option Work with Data Pump IMPDP' 1 hr 31 mins ago

A visitor from Bryan viewed 'Expdp failed with ORA-39047 ORA-39047: Jobs of typ' 4 hrs 59 mins ago

A visitor from Warsaw viewed 'How to Exclude MLOG$ Materialized View Log Tables ' 6 hrs 26 mins ago

Real-time | Get Script | More Info