Bobby, October 10, 2015 - 1:57 am UTC
Thanks Tom for taking time for answering.
I think it's my mistake not to point out first in my question that these table's are not temporary tables, which I did check for.
They are ordinary tables, segment created and have data.
October 10, 2015 - 3:56 am UTC
SQL> create table PARTAB ( x int, y int )
2 partition by list (x )
3 ( partition p1 values (10),
4 partition p2 values (20));
Table created.
SQL> select tablespace_name from user_tables where table_name = 'PARTAB';
TABLESPACE_NAME
--------------------
A reader, October 10, 2015 - 7:30 am UTC
No..the tables are ordinary tables...no temporary, not partitioned, not clustered etc...
select table_name,partitioned,temporary,iot_name, secondary,cluster_owner, dropped from dba_tables where owner = 'XXXXX' and tablespace_name is null;
and the result is below
TABLE_NAME PAR T IOT_NAME S CLUSTER_OWNER DRO
------------------------------ --- - ------------------------------ - ------------------------------ ---
TABLE 1 NO N N NO
TABLE 2 NO N N NO
October 10, 2015 - 10:09 am UTC
How about you send us this :-)
select * from dba_tables where owner = 'XXXXX' and tablespace_name is null;
Bobby, October 12, 2015 - 12:10 pm UTC
Hi,
Please find the result of the following query attached.
select * from dba_tables where owner = 'XXXXX' and tablespace_name is NULL
OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -------- ---------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- ---------------
LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ FLASH_C CELL_FL ROW_MOVE GLO USE DURATION SKIP_COR MON
--- - ---------- ---------- ------------ ---------- ---------- ----------- ------------------------- ------------------- ---------- ---------- ----- -------- ----------- --------- --- ------------ - - --- ------- ------- ------- -------- --- --- --------------- -------- ---
CLUSTER_OWNER DEPENDEN COMPRESS COMPRESS_FOR DRO REA SEG RESULT_
------------------------------ -------- -------- ------------ --- --- --- -------
XXXXX TABLE 1 VALID 0 0 0 0
N 2398 0 0 17 0 1 1 N ENABLED 2398 28-JUL-15 NO IOT N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO DISABLED YES
DISABLED DISABLED BASIC NO NO YES DEFAULT
XXXXX TABLE 2 VALID 0 0 0 0
N 446 0 0 33 0 1 1 N ENABLED 446 11-OCT-15 NO IOT N N NO DEFAULT DEFAULT DEFAULT DISABLED YES NO DISABLED YES
DISABLED DISABLED BASIC NO NO YES DEFAULT
2 rows selected.
October 13, 2015 - 1:34 am UTC
IOT !!!
The table is an index
SQL> create table T ( x int primary key )
2 organization index
3 /
Table created.
SQL>
SQL> select table_name, tablespace_name from user_tables
2 where table_name = 'T';
TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------
T
SQL>
SQL> select index_name, tablespace_name from user_indexes
2 where table_name = 'T';
INDEX_NAME TABLESPACE_NAME
------------------------------ --------------------
SYS_IOT_TOP_110308 USERS
External Tables?
Rajeshwaran, Jeyabal, October 12, 2015 - 2:08 pm UTC
May be it could be an External Tables?
rajesh@ORA11G> CREATE TABLE TRACE_FILE
2 ( X NUMBER,
3 Y VARCHAR2(4000)
4 )
5 ORGANIZATION EXTERNAL
6 ( TYPE ORACLE_LOADER
7 DEFAULT DIRECTORY TKDIR
8 ACCESS PARAMETERS
9 ( RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
10 PREPROCESSOR 'ETL_TOOLS_DIR':'tkprof.bat'
11 NOBADFILE
12 NOLOGFILE
13 NODISCARDFILE
14 READSIZE 1048576
15 FIELDS LDRTRIM
16 REJECT ROWS WITH ALL NULL FIELDS
17 (
18 X RECNUM,
19 Y POSITION(1:4000)
20 )
21 )
22 LOCATION
23 ( '' )
24 )
25 REJECT LIMIT UNLIMITED
26 /
Table created.
rajesh@ORA11G> select t1.table_name, t2.tablespace_name
2 from user_external_tables t1,
3 user_tables t2
4 where t1.table_name = t2.table_name ;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TRACE_FILE
EMP_LOAD_EXT
2 rows selected.
rajesh@ORA11G>
Bobby, October 12, 2015 - 4:50 pm UTC
No Rajesh, they are not external tables.
Deferred Segment Creation?
Arian, October 12, 2015 - 7:39 pm UTC
Hi,
the query results are not very clear. But is the row_count 0 for these tables. In that case, can it be related to Deferred_segment_creation?
Unfortunately, I don't have a database handy to verify the results.
dbms_metadata.get_ddl ?
Rajeshwaran, Jeyabal, October 13, 2015 - 1:28 am UTC
How about this? can you post the result set for this query ?
select dbms_metadata.get_ddl('TABLE','<YOUR_TABLE_NAME>') from dual;
Bobby, October 13, 2015 - 7:52 pm UTC
Hi,
@Aarin, the tables do have data, correct me if my understanding is wrong but and as far as I know the parameter you refering to "Deferred_segment_creation" will not create a segment before data is created, but that does not mean that it would not assign the tablespace or you could be able to create a table without no tablespace if this is a normal table..i.e not temporary or external etc. There is no relation between Deferred_segment_creation and tablesspace (yes, when segment is created, it would be created in the tablespace alloacted for segment/table
@Rajesh, below is the result for metadata.
SQL> select dbms_metadata.get_ddl('TABLE','MY_TABLE') from dual;
ERROR:
ORA-31603: object "MY_TABLE" of type TABLE not found in schema "ORACLE"
ORA-06512: at "SYS.DBMS_METADATA", line 5805
ORA-06512: at "SYS.DBMS_METADATA", line 8344
ORA-06512: at line 1
Note : The actual table name has been chaned to MY_TABLE here.
Thanks
Bobby, October 16, 2015 - 3:54 pm UTC
Yes, they are IOTs.
But to my confusion, I would expect IOT_NAME column from DBA_TABLES to be populated for IOT tables to indicate that tables are IOT, tables which I checked and was empty.
Hoever, IOT_TYPE column is populated for those tables in DBA_TABLES with value "IOT" which I missed to see.
It's resolved for me now...thanks for your help.
Bobby, October 16, 2015 - 3:58 pm UTC
Yes, they are IOTs.
But to my confusion, I would expect IOT_NAME column from DBA_TABLES to be populated for IOT tables to indicate that tables are IOT, tables which I checked and was empty.
Hoever, IOT_TYPE column is populated for those tables in DBA_TABLES with value "IOT" which I missed to see.
It's resolved for me now...thanks for your help.
question
A reader, March 20, 2017 - 2:37 pm UTC
please tell me in which cases one should or may use DEFERRED_SEGMENT_CREATION = TRUE / FALSE ?
please elaborate few cases.
March 22, 2017 - 1:37 am UTC
RMAN duplicate command fails ....
Dmitry, January 22, 2018 - 7:36 am UTC
Hi, we have s similar issue with tables which have sub partitions. The tablespace name for the tables is empty.
select tablespace_name from dba_tables where owner='RISKDW_PROD' and table_name='PNL_ADJUSTMENT_VALUE_BY_FOLIO';
TABLESPACE_NAME
------------------------------
1 row selected.
However when we do a database duplication suing RMAN skipping related tables/tablespaces, it fails error:
DUPLICATE TARGET DATABASE TO LDBFPR01 SKIP TABLESPACE FPESSP01:PNL_NOBACKUP_DATA,FPESSP01:PNL_DATA,FPESSP01:VAR_TOTAL,FPESSP01:VAR_STRESS FROM ACTIVE DATABASE nofilenamecheck ;
datafile 277 switched to datafile copy
input datafile copy RECID=187 STAMP=964888213 file name=+DATA01/LDBFPR01/5D12A21043A22311E0534B1D1E0A06E9/DATAFILE/incore3_data.263.964887945
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Executing: alter database enable block change tracking
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
contents of Memory Script:
{
sql clone "alter pluggable database all open";
}
executing Memory Script
sql statement: alter pluggable database all open
Dropping offline and skipped tablespaces
Executing: drop tablespace "VAR_TOTAL" including contents cascade constraints
Executing: drop tablespace "VAR_STRESS" including contents cascade constraints
Executing: drop tablespace "PNL_NOBACKUP_DATA" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/08/2018 16:32:36
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-14407: partitioned table contains subpartitions in a different tablespace
January 23, 2018 - 2:07 am UTC
What is the DDL for PNL_ADJUSTMENT_VALUE_BY_FOLIO?
Non existing tablespace
Michiel Stubbe, November 27, 2018 - 4:32 pm UTC
Hi,
We are having more or less the same issue.
It's a partitioned table.
The base table is allocated to tablespace A1
The partitions are allocated to tablespace A2
Tablespace A1 does not exist. (probably dropped after this table was created)
If you look in dba_tables, the tablespace_name is empty.
If you look at the ddl generated with dbms_metadata.get_ddl it shows the tablespace A1 there. There is no segment created for this table as all the partitions contain the data and they are in a different tablespace A2.
We are hitting this issue when migrating database version (12.1 to 18) with datapump. Is there another way besides (dbms_metadata.get_ddl) to find objects that belong to non existing databases?
Thanks for your time!
November 28, 2018 - 1:03 am UTC
Here's an example
SQL> create table t ( x int ) tablespace demo
2 partition by list ( x )
3 ( partition p1 values (1) tablespace users,
4 partition p2 values (2) tablespace largets
5 );
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL> insert into t values(2);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> drop tablespace demo including contents and datafiles;
Tablespace dropped.
SQL> desc t
Name Null? Type
----------------------------------------------------------------- -------- ----------------
X NUMBER(38)
SQL> select tablespace_name from user_tables
2 where table_name = 'T';
TABLESPACE_NAME
------------------------
SQL> select def_tablespace_name
2 from user_part_tables
3 where table_name = 'T';
DEF_TABLESPACE_NAME
------------------------------
DEMO
so for completeness you could do something like
SQL> with all_possible_ts as
2 (
3 select tablespace_name from DBA_LOBS union all
4 select tablespace_name from DBA_CLUSTERS union all
5 select tablespace_name from DBA_INDEXES union all
6 select tablespace_name from DBA_ROLLBACK_SEGS union all
7 select tablespace_name from DBA_TABLES union all
8 select tablespace_name from DBA_OBJECT_TABLES union all
9 select DEF_TABLESPACE_NAME from DBA_PART_TABLES union all
10 select DEF_TABLESPACE_NAME from DBA_PART_INDEXES union all
11 select tablespace_name from DBA_TAB_PARTITIONS union all
12 select tablespace_name from DBA_IND_PARTITIONS union all
13 select tablespace_name from DBA_TAB_SUBPARTITIONS union all
14 select tablespace_name from DBA_IND_SUBPARTITIONS union all
15 select DEF_TABLESPACE_NAME from DBA_PART_LOBS union all
16 select tablespace_name from DBA_LOB_PARTITIONS union all
17 select tablespace_name from DBA_LOB_SUBPARTITIONS union all
18 select tablespace_name from DBA_SUBPARTITION_TEMPLATES union all
19 select tablespace_name from DBA_LOB_TEMPLATES union all
20 select tablespace_name from DBA_SEGMENTS union all
21 select tablespace_name from DBA_EXTENTS union all
22 select tablespace_name from DBA_UNDO_EXTENTS
23 )
24 select tablespace_name from all_possible_ts
25 minus
26 select tablespace_name from DBA_TABLESPACES;
TABLESPACE_NAME
--------------------
DEMO