Creating a table from a query using a different tablespace (Oracle SQL)
I want to create some tables in our database from some queries I have developed. I used the code below and it created the table and it works great.
The issue I am having is apparently it created the table using a different tablespace than the one we are supposed to use. Is there a way to specify that in code like what is below? Just a disclaimer, I am more of the end user of the data so I am not as tech savy.
CREATE TABLE new_permanent_table
AS
SELECT *
FROM old_temporary_table
WHERE amount<5000;
Answer
Assuming that you have a quota on the other tablespace, you should be able to just add the "TABLESPACE <tablespace name>" statement below your CREATE TABLE statement:
CREATE TABLE new_permanent_table
TABLESPACE other_tablespace
AS
SELECT *
FROM old_temporary_table
WHERE amount<5000;