Table not created in specific tablespace - Oracle

Question

I am a moderate user of Oracle and I had to create some of the tables in specified table space as shown below.

create table t_abc tablespace abc_tbspc as select * from abc;
create table t_xyz tablespace abc_tbspc as select * from xyz;

After running these through jobs (file containing around 5 tables to be created in a single tablespace), I could see that the table t_abc is created in abc_tbspc ; but the table t_xyz is assigned to null when I query the all_tables. Not sure why the 2nd table is not created in the specified tablespace even though there is abundant space in the table space.

Answer 1

TABLESPACE_NAME will be null for one of these reasons:

1. Temporary Temporary tables use a temporary tablespace.
2. Index Organized Index-organized tables store data in an index, not in a heap.
3. Partitioned Each partition could have a different tablespace, there is not necessarily one tablespace for the whole table.
4. External External tables do not store their data in a tablespace.

Your code should not meet one of the conditions above; did you leave out some details? I ran the query below to look for other cases where TABLESPACE_NAME is null but could not find any.

select *
from dba_tables
where tablespace_name is null
    and (temporary is null or temporary <> 'Y')              -- #1
    and (iot_type is null or iot_type <> 'IOT')              -- #2
    and (partitioned is null or partitioned <> 'YES')        -- #3
    and (owner, table_name) not in                           -- #4
        (select owner, table_name from dba_external_tables)