Clever Geek Handbook

How to convert temporary table to permanent table in Oracle and vice versa

I would like to know what is the command to convert a temporary table to a permanent table in Oracle.

Another problem concerns the index. The index used in the temporary table will be the same used in the permanent table if I convert it?

+3


source to share


1 answer


You cannot convert a table from a temporary table to a permanent table.

You can create a new permanent table that matches the structure of the temporary table

CREATE TABLE new_permanent_table
AS
SELECT *
  FROM old_temporary_table
 WHERE 1=0;


Or you can get the DDL for a temporary table using a package DBMS_METADATA

and manually edit the DDL to create a new permanent table.

Then you can create any indexes you want to use on the new permanent table and drop the old temporary table. Once the old temporary table is dropped, you can rename the permanent table to use the name of the old temporary table if you like.

+6


source






More articles:


All Articles