Re: Transportable Tablespaces and Temp Tables

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 08 Dec 2004 17:01:13 +1100
Message-ID: <41b698a8$0$9390$afc38c87@news.optusnet.com.au>

DA Morgan wrote:

> g3000 wrote:
> 

>> I am trying to transport all tablespaces that belong to an application.
>>
>> As far as the Oracle docs state, global temp tables are stored in the
>> data dictionary (system tablespace) then for each users session it is
>> created (on the fly Im assuming, a temp segment for the temp table) in
>> my temp tablespace. (which is what i want and not a problem)
>>
>> Problem is when I try to create a "self-containted" tablespace set to
>> transport all the tablespaces my schema uses.
>> Oracle has a pointer from the system tablespace pointing to another
>> tablespace that holds the primary key for the temp table. That causes
>> my tablespace set to not be self contained.
>>
>> Oracle does not allow you to transport the SYSTEM tablespace.
>>
>> Is there a way I can get around this without having to drop the temp
>> table primary key then execute DDL for my temporary tables and recreate
>> the pk's on tablespace set import?
>> Thank you in advance for your time
>> Carlton G

> 
> 
> Why would you want to transport the SYSTEM tablespace? Or the TEMP
> tablespace? Or even though you didn't mention it SYSAUX and UNDO
> tablespaces. Look at what TTS does.
> 

Did you actually bother to read his post?

I guess not, otherwise you would see that he DOES NOT want to transport SYSTEM. He's trying to create a self-contained transport set, but because he's got an index on a global temporary table, the set can't be self-contained *unless* SYSTEM is included... which it can't be, by design... so he's in a Catch-22 situation.

As usual, you've just leapt into the breach with a broadside, sweeping piece of snooty put-down, and not bothered to actually analyse his rather well-described problem.

To the OP: The only thing I can think of is to drop the primary key constraint (which you'd already thought of, and wanted to avoid, I realise). Dropping and re-creating isn't a major issue, of course, because there won't be any rows in the GTT, so the index won't take more than a second to re-create. I realise your issue is having to remember to create it, and *how* to recreate it (ie, what DDL to issue), but I honestly can't think of any other workaround.

Regards to the OP
HJR Received on Wed Dec 08 2004 - 00:01:13 CST