Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

Temporary Tablespace is Empty

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.
  • 4
  • 3
1 Solution
slightwv (䄆 Netminder) Commented:
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';
patriotpacerAuthor Commented:
>> 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.
slightwv (䄆 Netminder) Commented:
>>Brings back nothing

Then you need to create a temp tablespace:

Creating a Locally Managed Temporary Tablespace
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

patriotpacerAuthor Commented:
Why am I getting this when I just run a query to see a package contents?
slightwv (䄆 Netminder) Commented:
My guess is dbms_metadata creates temporary objects internally.

Some objects cannot be created in a permanent tablespace.
patriotpacerAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>> 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now