Q
Problem solve Get help with specific problems with your technologies, process and projects.

Receiving the ORA-01652 error with no specified tablespace

An Oracle user asks why they are receiving the ORA-01652 error but there is no tablespace specified in the error.

I started receiving the following errors:

RMAN> report schema;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: === ERROR MESSAGE STACK FOLLOWS ======
RMAN-00571: =====================================
RMAN-03002: failure of report command at 05/31/2008 10:39:03
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 05/31/2008 10:39:03
ORA-01652: unable to extend temp segment by in tablespace

I also get the same error when executing SHOW ALL.

The error does not specify the tablespace_name in "error". I also checked the RMAN Catalog db for any space related issues. I did not find one.

Typically, there are two types of temporary segments. One, the temporary segments in your TEMP tablespace. Two, segments that are marked as temporary during index rebuilds and later made permanent index segments. I would first start by ensuring that your TEMP tablespace has enough free space. If not, consider increasing the size of the TEMP tablespace. Otherwise, it will be some index creation step that does not have enough free space to complete. I'm guessing that the Recovery Catalog database does not have enough free space in the tablespace that holds the RC tables and indexes. However, to be sure, you can enable a 1652 trace. In your RC database, issue the following:

alter system set events '1652 trace name errorstack level 1';

When the ORA-1652 error is raised again, Oracle will create a trace file in the USER_DUMP_DEST directory. This trace file should show you which tablespace is short on space. To turn off this trace, issue:

alter system set events '1652 trace name context off';

While querying DBA_FREE_SPACE to see if your tablespace has enough free space, keep in mind that with Dictionary Managed Tablespaces, that free space may be fragmented too much, so as to be virtually unusable.

For more solutions to common Oracle errors, check out our Solving common Oracle errors guide.

Dig Deeper on Oracle database administration

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close