LOB-Segment of global temporary table

VivaLaVida!

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

Jonathan Lewis

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

Jonathan Lewis

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

VivaLaVida!

Hi Jonathan,

thanks for your reply - we are on Oracle Database 10g Release 10.2.0.5.0 - 64bit Production.

Rgds

JH

Jonathan Lewis

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

VivaLaVida!

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

Jonathan Lewis

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

VivaLaVida!

..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

Jonathan Lewis

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