No question at this time
DBA Top 10
1 A. Kavsek 8700
2 B. Vroman 5900
3 M. Cadot 5400
4 P. Wisse 4800
5 T. Boles 3700
6 J. Schnackenberg 2100
7 . Lauri 1500
8 R. Pattyn 700
9 T. P 600
9 J. PĂ©ran 600
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48409
Total active users1518
Act. users last 24h5
Act. users last hour0
Registered user hits last week284
Registered user hits last month997
Go up

objects with no tablespace
Next thread: Rac 11.2.0.4.3 to Stand alone
Prev thread: Copying a partitioned table and all of its global/local indexes

Message Score Author Date
Hi all, Encounter this table below with no tabl...... sze jie koh Sep 08, 2015, 06:41
There are many reason why there is no tablespace...... Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts Michel Cadot Sep 08, 2015, 07:24
Hi, Is it partitioned? ... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts BenBart BartBen Sep 08, 2015, 09:02
This is one of the cases I mentioned, with IOT, ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 08, 2015, 09:13
Hi Michel, Sorry for the late reply. I check t...... sze jie koh Sep 14, 2015, 03:21
If this is an IOT the name of the segment is not...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 14, 2015, 07:21
For instance, remember an IOT is an index: <pre...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 14, 2015, 08:36
Hi Michel, I realize that the table is actually...... sze jie koh Sep 15, 2015, 12:03
A GTT is just a definition until someone uses it...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Sep 15, 2015, 12:18
Hi Michel, Thanks for your reply. Can i chec...... sze jie koh Sep 16, 2015, 14:49
Also, related objects such as indexes created on...... sze jie koh Sep 16, 2015, 14:52
All of them do not exist until some rows are ins...... Michel Cadot Sep 16, 2015, 15:34

Follow up by mail Click here


Subject: objects with no tablespace
Author: sze jie koh, Singapore
Date: Sep 08, 2015, 06:41, 1444 days ago
Os info: linux
Oracle info: 11gr2
Message: Hi all,

Encounter this table below with no tablespace allocated.


select owner, table_name, tablespace_name from
dba_tables where table_name = 'SYS_TEMP_FBT';
FHKJAVA SYS_TEMP_FBT NULL

desc fhkjava.SYS_TEMP_FBT
Name Null Type
----------- ---- ------------
SCHEMA VARCHAR2(32)
OBJECT_NAME VARCHAR2(32)
OBJECT# NUMBER
RID UROWID()
ACTION CHAR(1)


Any idea why ?

Regards,
Noob
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 08, 2015, 07:24, 1444 days ago
Score:   Score: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 PtsScore: 700 Pts
Message:
There are many reason why there is no tablespace in dba_tables.
Forget this column in this view, it is a design error.
The tablespace is not a property of the table but of the associated segment(s) (note the "s"). So search it in dba_segments.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: BenBart BartBen, Philippines
Date: Sep 08, 2015, 09:02, 1444 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

Is it partitioned?

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 08, 2015, 09:13, 1444 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
This is one of the cases I mentioned, with IOT, temporary tables...

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: sze jie koh, Singapore
Date: Sep 14, 2015, 03:21, 1438 days ago
Message: Hi Michel,

Sorry for the late reply.
I check the segment view but don't find any tablespace information also, in fact, no related rows.


SQL> select tablespace_name from dba_segments where segment_name = 'SYS_TEMP_FBT';

no rows selected

SQL> select object_type from dba_objects where object_name = 'SYS_TEMP_FBT';

OBJECT_TYPE
-------------------
TABLE


How do I check if a table is created as deferred segment ?

Regards,
Noob
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 14, 2015, 07:21, 1438 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
If this is an IOT the name of the segment is not the same than the name of the table.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 14, 2015, 08:36, 1438 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
For instance, remember an IOT is an index:
SQL> create table iot (id int primary key, val varchar2(10)) organization index;


Table created.

SQL> select iot_type, tablespace_name from user_tables where table_name='IOT';
IOT_TYPE TABLESPACE_NAME
------------ ------------------------------
IOT

1 row selected.

SQL> select index_name from user_indexes where table_name='IOT';
INDEX_NAME
------------------------------
SYS_IOT_TOP_161293

1 row selected.

SQL> select tablespace_name, bytes from user_segments where segment_name='SYS_IOT_TOP_161293';
TABLESPACE_NAME BYTES
------------------------------ ----------
TS_D01 65536

1 row selected.


Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: sze jie koh, Singapore
Date: Sep 15, 2015, 12:03, 1437 days ago
Message: Hi Michel,

I realize that the table is actually a GLOBAL TEMPORARY table.

Does it resides in the TEMP tablespace then ? - if so, why isn't it show in any v$ tables ?

Regards,
Noob
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 15, 2015, 12:18, 1437 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
A GTT is just a definition until someone uses it.
Then, depending on the required space, it is in the session memory or in the temporary tablespace of the user using it.
So a GTT can be, at the same time, in multiple copies in different tablespaces.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: sze jie koh, Singapore
Date: Sep 16, 2015, 14:49, 1436 days ago
Message: Hi Michel,

Thanks for your reply.

Can i check with you that there is no way that we are able to see or verify where the tablespace of the temporary table is (even if it is in temp) right ?

Also, even if space are allocated , it will not be in the DBA_SEGMENTS too as well.

Am i right ?

Regards,
Noob
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: sze jie koh, Singapore
Date: Sep 16, 2015, 14:52, 1436 days ago
Message:
Also, related objects such as indexes created on the
temporary table, does not belong to any tablespace as well.

(it might be allocated from the temporary tablespace, but where it is actually located not be shown anywhere in the v$ tables even if rows/spaces are inserted/allocated)

Am i right ?

Regards,
Noob
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: objects with no tablespace
Author: Michel Cadot, France
Date: Sep 16, 2015, 15:34, 1436 days ago
Message:
All of them do not exist until some rows are inserted in table.
Then all of them are allocated in the user temporary tablespace.
There is no dictionary view that gives you any information about space allocated for gtt and its related objects.
v$sort_usage contains information about how much is allocated in the temporary tablespaces but does not make any distinction about the usage, gtt or other thing.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here