Go Premium for a chance to win a PS4. Enter to Win


Temporary Tablespace is Empty

Posted on 2013-06-21
Medium Priority
Last Modified: 2013-06-21
I'm getting an error.  I've researched it but still not sure what to do.

I run...

select dbms_metadata.get_ddl ('PACKAGE_BODY','<<package name>>') from dual

And I get...

ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
25153. 00000 -  "Temporary Tablespace is Empty"
*Cause:    An attempt was made to use space in a temporary tablespace with
           no files.
*Action:   Add files to the tablespace using ADD TEMPFILE command.
Question by:patriotpacer
  • 4
  • 3
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39266093
Looks like you don't have a temporary tablespace or it is offline.

Possible the user you are using doesn't have a default temp tablepsace.

 select tablespace_name, status from dba_temp_files;

select temporary_tablespace from dba_users where username='SOME_USERNAME';

Author Comment

ID: 39266105
>> select tablespace_name, status from dba_temp_files;

Brings back nothing

>>select temporary_tablespace from dba_users where username='SOME_USERNAME';

I ran with my username and get the result back of "temp"

BTW - this is the crap I hate about Oracle.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39266118
>>Brings back nothing

Then you need to create a temp tablespace:

Creating a Locally Managed Temporary Tablespace
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 39266143
Why am I getting this when I just run a query to see a package contents?
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39266157
My guess is dbms_metadata creates temporary objects internally.

Some objects cannot be created in a permanent tablespace.

Author Comment

ID: 39266175
Thx, slightwv.

I'll accept your answer, but I can't test it.  Says I have ""insufficient privileges."

Never thought this would be so hard.
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39266185
>> Says I have ""insufficient privileges."

You need to be SYS to create tablespaces.  If you don't have SYS access, have your DBA create it for you.

Any database created should have a TEMP tablespace.  Since yours doesn't, I would try my best to figure out why.  This can do bad things to your database...

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question