Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

LOB-Segment of global temporary table

VivaLaVida!May 4 2016 — edited May 4 2016

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

This post has been answered by Jonathan Lewis on May 4 2016
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 1 2016
Added on May 4 2016
7 comments
760 views