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

Question

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

Other issue is about the index. An index used in a temporary table will be the same used in a permanent table, if I convert it?

Answer 1

You can't 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 could get the DDL for the temporary table using the DBMS_METADATA package and manually edit the DDL to create the new permanent table.

Then you can create whatever indexes you would like 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 would like.