Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
LOB-Segment of global temporary table

Hi,
I've created a global temporary table with a LOB column:
CREATE GLOBAL TEMPORARY TABLE "TMP_SRC_MAIL_FILES"
( "VOLID" NUMBER NOT NULL ENABLE,
"CSNAP#" NUMBER NOT NULL ENABLE,
"LSNAP#" NUMBER,
"PATHNAME" VARCHAR2(3072 BYTE) NOT NULL ENABLE,
"ITEM" VARCHAR2(768 BYTE) NOT NULL ENABLE,
"PATHTYPE" NUMBER(*,0) NOT NULL ENABLE,
"FILEDATA" BLOB) on commit preserve rows;
Querying USER_INDEXES Returns the following result:
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_IL0000475836C00007$$ LOB USER TMP_SRC_WORDBFD_FILES SYSTEM
I'm astonished that the index of the BLOB is created in the SYSTEM tablespace (and not in the default-tablespace of the owner of the table).
Furthermore querying USER_SEGMENTS returns no result for the above index.
I've tried to move the LOB of the temporary table to another tablespace - but this is not possible for global temporary tables.
My questions now are:
=> are always the indexs of LOB columns of global temporary table stored in the SYSTEM tablespace?
=> is there any possibility to move the data of thesxe LOB columns to another tablespace (Default tablespace of the owner of the global temporary table?)
Rgds
JH
Best Answer
-
Looking at the definition of DBA_INDEXES, I think the property column of the sys.ind$ table hasn't had the bit set to show that it's an index associated with a temporary table; as a result the query reports tablespace 0 (system) rather than decoding a zero to a null.
You could raise an SR with Oracle if you felt that this reporting error was sufficiently important.
Regards
Jonathan Lewis
Answers
-
Which version of the database - it may make a difference.
Bear in mind that whatever the query against dba_indexes says the index, when instantiated by a user, will HAVE to be in a proper temporary tablespace - so my first guess would be that your query was returning the wrong result.
I just ran your creation script and inserted a row into the global temporary table, then queried v$sort_usage - my userid is test_user, my temporary tablespace is called temp:
SQL> select * from V$sort_usage;
USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID
------------------------------ ------------------------------ ---------------- ----------- ---------------- ---------- -------------
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TEST_USER TEST_USER 000000009F6BC560 1527 000000009833B138 1029988163 9babjv8yq8ru3
TEMP TEMPORARY DATA 201 41856 1 128 1
TEST_USER TEST_USER 000000009F6BC560 1527 000000009833B138 1029988163 9babjv8yq8ru3
TEMP TEMPORARY LOB_DATA 201 41728 1 128 1
TEST_USER TEST_USER 000000009F6BC560 1527 000000009833B138 1029988163 9babjv8yq8ru3
TEMP TEMPORARY INDEX 201 41600 1 128 1
3 rows selected.
The lob index has been created in the TEMP tablespace, even though I (like you) found that user_indexes reported the definition as belonging to the SYSTEM tablespace.
Regards
Jonathan Lewis
-
Hi Jonathan,
thanks for your reply - we are on Oracle Database 10g Release 10.2.0.5.0 - 64bit Production.
Rgds
JH
-
It's a reporting nuisance, and it's still the same in 11.2.0.4 and 12.1.0.2 - but when you put data into the table the index information does actually go into your temporary tablespace.
In fact, in 11g you can specify a (temporary) tablespace for GTTs so that a given GTT will always be instantiated in the same place regardless of the temporary tablespace associated with the user, and if you do this the table, the lob data and the lob index will all go in that tablespace, and the lob index will report that tablespace name in user_indexes.
Regards
Jonathan Lewis
-
Therefore as a matter of fact the index of the LOB is stored in the SYSTEM tablespace, isn't it?
Not nice since there's no way to change that in Oracle release 10r2.
From my point of view no user objects should be stored in the SYSTEM tablespace.
Rgds
JH
-
No, the index of the LOB is NOT stored in the SYSTEM tablespace:
From my previous reply:
"the index information does actually go into your temporary tablespace"
From my first reply:
TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TEMP TEMPORARY INDEX 201 41600 1 128 1
-
..but querying USER_INDEXES returns that the index of the LOB is stored in the SYSTEM tablespace. What is causing this misleading Information?
Thx
JH
-
Looking at the definition of DBA_INDEXES, I think the property column of the sys.ind$ table hasn't had the bit set to show that it's an index associated with a temporary table; as a result the query reports tablespace 0 (system) rather than decoding a zero to a null.
You could raise an SR with Oracle if you felt that this reporting error was sufficiently important.
Regards
Jonathan Lewis