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

ORA-14456

Hi. About this error, cannot rebuild an index on a temporary table.

This message is about a tbs in our schema that, generally, is not temporary. We think that somebody created that as temporary. So, there is a way to transform this tbs into a normal tbs? if there is data in it, this will be a problem?

Could somebody help?
0
Thiago_corporative
Asked:
Thiago_corporative
  • 5
  • 2
1 Solution
 
schwertnerCommented:
You can create a regular table so:

CREATE TABLE reg_table AS select * from temp_table;

DROP TABLE temp_table;
RENAME TABLE reg_table TO temp_table;

But also this could be caused by a good bug.
Investigate USER_TABLES for details.
0
 
schwertnerCommented:
alter table reg_table rename to temp_table;

0
 
Thiago_corporativeAuthor Commented:
sorry, I didn't explain very well... it says that our tablespace it's temporary not a table, sorry again... but it shouldn't be... it should be a regular tablespace... there is a way to transform a temporary tablespace in a normal tablespace?
0
Independent Software Vendors: 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!

 
Mark GeerlingsDatabase AdministratorCommented:
You are using the term "tbs" (which usually means: "tablespace" in Oracle) plus the term: "temporary table".  Oracle has both temporary tables (uusally called "global temporary tables") and temporary tablespaces, and they are two different things.  The ORA-14456 error happens with a temporary table, not with a "tbs" (or tablespace).

Temporary tables may have an index (or even indexes) just as permananet tables can, but Oracle does not support rebuilding an index on a temporary table, likely because the data in a temporary table is only there for a short duration anyway, so the overhead of rebuilding an index on that data would be wasted effort in most cases.

The index will be rebuilt anyway the next time that table is initialized.  This will happen either at commit time (if the temporary table was created with the default setting: "on commite delete rows") or the first time the table is used after a login.

The temporary table was created as a temporary table for some purpose.  Yes, you could consider dropping that and recreating it as a permament table.  But, that would like require some programming changes in programs that use the temporary table now.  And, it will add database overhead (slower performance) and could require some additional program changes to handle data concurrency, isolation of data per session, data retention, etc.
0
 
Mark GeerlingsDatabase AdministratorCommented:
"is [there] a way to transform a temporary tablespace in[to] a normal tablespace?"
No.

These are intentionally very different in Oracle.  I don't think you want to drop the temporary tablespace and replace it with a permanent table with the same name.
0
 
schwertnerCommented:
Temporary tablespaces are internally used by Oracle for operations that need additional RAM, but the RAM is fully occupied and there is no free space. The example is very popular - big sorting operation (big means with many data involved).
Tables can not be placed in temporary tablespaces.
0
 
schwertnerCommented:
You mention ORA-14456.
This means you run in problem with a table, not with tablespace.
0
 
schwertnerCommented:
Good luck!
0

Featured Post

Independent Software Vendors: 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!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now