Creating a table from a query using a different tablespace (Oracle SQL)

Question

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;

Bare

Answer 1

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;

Madeline

Well that was certainly easy. Thanks, I had a feeling it was something simple like that! – Bare