Creating a Table from a Query Using a Different Tablespace (Oracle Sql)

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

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;

How can I create a copy of an Oracle table without copying the data?

Just use a where clause that won't select any rows:

create table xyz_new as select * from xyz where 1=0;

Limitations

The following things will not be copied to the new table:

• sequences
• triggers
• indexes
• some constraints may not be copied
• materialized view logs

This also does not handle partitions

---

How to get all tablespaces that a table belongs to in Oracle?

Ok, here it is:

The entire concept of "all the tablespaces that are assigned to a table" is a nullity. A table can belong to one and only one tablespace.

spo demo.log
select owner,
       segment_name,
       segment_type,
       tablespace_name
from dba_segments
order by owner,
         segment_name,
         segment_type,
         tablespace_name
;
spo off
edit demo.log

---

Related Topics