Re: Indexes on Global Temporary Tables

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f70293c$0$256$ed9e5944_at_reading.news.pipex.net>...
> "sally" <sally_al2003_at_yahoo.co.uk> wrote in message
> news:df28fc2a.0309230209.24f852e4_at_posting.google.com...
> > Thank you,
> >
> > So the data is only going into temp (the default temporary tablespace).
> >
> > So it's just pluggable that has a problem and shows that it belongs to
> 'system'.
> >
> > Does anybody have any clues why it might be this way?
>
> I haven't done any great research into GTT internals, and nor have I seen
> any. However it would seem to me that a temp ts is a candidate for
> recreating rather than transporting since it should contain no permanent
> data. A GTT is no different in this regard.

Yes I agree but I'm not trying to transport 'TEMP' I'm transporting 'MYTABLESPACE'. SQL>select owner, object_type, status, temporary from dba_objects
> where object_name like 'TM2262%'

AUSER                      TABLE             VALID  Y
AUSER                      INDEX             VALID  Y

I have 2 temporary objects, a GTT and an index created on it.

SQL>select owner, table_name, tablespace_name, temporary from dba_tables

   >where table_name like 'TM2262%'

AUSER TM2262 Y SQL>select owner, index_name, tablespace_name, temporary from dba_indexes
> where index_name like 'TM2262%'

AUSER TM2262_T_IDX Y So you see I have a temporary table and a temporary index in which dba_tables and dba_indexes thinks there is no tablespace

On a v_$sort_usage they do show as being in temp.

SQL>select obj1_name, obj1_type, ts1_name,
> obj2_name, obj2_type, ts2_name , reason
>from pluggable_set_check
>where obj2_name like 'TM2262%' or obj1_name like 'TM2262%'

TM2262 TABLE SYSTEM
TM2262_T_IDX INDEX MYTABLESPACE
Tables and associated indexes not fully contained in the pluggable set

Now the Tablespace I am transporting is 'mytablespace'.

The Default Tablespace for AUSER is MYTABLESPACE. The Default temp tablespace for AUSER is TEMP.

So why should the GTT show as in the system tablespace?

The work around is to delete all temporary tables before doing a Transportable export but it's just annoying that I don't understand why pluggable is showing what it is. Received on Wed Sep 24 2003 - 05:07:02 CDT