Re: Indexes on Global Temporary Tables

From: freek <f.d.h_at_pandora.be>
Date: Mon, 22 Sep 2003 23:18:55 GMT
Message-ID: <zrLbb.34663$WQ6.1583175@phobos.telenet-ops.be>

Sally wrote:

> My indexes created on Global Temporary Tables are being stored
> (according to sys.pluggable_set_check) in the system tablespace. The
> user creating these tables has no quota in the system tablespace. The
> system tablespace is not a default for the user.
>
> This causes problems with transportable tablespace exports, I am
> having to drop the temporary tables before I can do them.
>
> Oracle say this is not a bug :
>
>
> Has anybody else come across this, I can't find anything here, on Tom
> or with Oracle.
>
> I'm failing to see why it isn't a bug, could it fill the system
> tablespace, or is it just a marker and no "actual" space is used?
>
> Any information to enlighten me would be a great help.
>
> Thanks
>
> Sal.

Sal,

Normally all data will be stored in a temporary tablespace. If you query user_tables and user_indexes for the temporary table and its index, you will notice that the tablespace name is blank.

I think you can prove this by creating a global temporary table with a char column and insert some data in it and then query v$sort_usage. After that you do the same, but with an index on the table. With the index the table should take up more space in temp then without.

SQL> select * from v$version;

BANNER

---

Personal Oracle9i Release 9.0.1.1.1 - Production PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production NLSRTL Version 9.0.1.1.1 - Production

SQL> create global temporary table test

   2 ( testje char(2000))
   3 /

Table created.

SQL> create index testje_i on test(testje);

Index created.

SQL> select table_name, tablespace_name from user_tables where table_name = 'TEST';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------

TEST SQL> select index_name, tablespace_name from user_indexes where index_name = 'TESTJE_I';

INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------

TESTJE_I SQL> insert into test (testje)

   2 select object_name from all_objects    3 where rownum <= 200;

200 rows created.

SQL> select username, tablespace, blocks from v$sort_usage;

USERNAME                       TABLESPACE                          BLOCKS
------------------------------ ------------------------------- ----------
FREEK                          TEMP                                   256
FREEK                          TEMP                                   256

SQL> commit;

Commit complete.

SQL> select username, tablespace, blocks from v$sort_usage;

no rows selected

SQL> drop index testje_i;

Index dropped.

SQL> insert into test (testje)

   2 select object_name from all_objects    3 where rownum <= 200;

200 rows created.

SQL> select username, tablespace, blocks from v$sort_usage;

USERNAME                       TABLESPACE                          BLOCKS
------------------------------ ------------------------------- ----------
FREEK                          TEMP                                   256

greetings

Freek Received on Mon Sep 22 2003 - 18:18:55 CDT