Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Transportable tablespaces within the same database

Re: Transportable tablespaces within the same database

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Wed, 13 Jun 2007 10:18:03 -0500
Message-ID: <466ffe6d$0$12034$88260bb3@free.teranews.com>


jms.news_at_gmail.com wrote:

> On Jun 13, 9:46 am, "jms.n..._at_gmail.com" <jms.n..._at_gmail.com> wrote:

>> On Jun 12, 10:54 am, "jms.n..._at_gmail.com" <jms.n..._at_gmail.com> wrote:
>>
>>
>>
>>> On Jun 9, 3:12 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>>>> "J" <n..._at_noone.org> a écrit dans le message denews: 466a2d19$0$17156$afc38..._at_news.optusnet.com.au...
>>>> | "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>>>> |
>>>> | >
>>>> | > <jms.n..._at_gmail.com> a crit dans le message de news:
>>>> | > 1181278817.166749.65..._at_i13g2000prf.googlegroups.com...
>>>> | > | Instead of transporting a tablespace between 2 databases .... can one
>>>> | > | transport the tablespace to the same database to essentially "restore"
>>>> | > | a tablespace to the state when the datafiles were copied ?
>>>> | > |
>>>> | > | e.g.: Assuming I have only one user that owns objects in the
>>>> | > | tablespace(s):
>>>> | > |
>>>> | >
>>>> | > And the purpose is?
>>>> |
>>>> | You did not read the first part of the post ? The OP says to "restore" a
>>>> | tablespace to the state when the datafiles were copied.
>>>> I meant what is the purpose to use TTS instead of "rename".
>>> 1) Tablespace rename only works with Oracle10g.
>>> 2) Even if I was using 10g, you still have to use a transportable
>>> tablespace, dont you ?
>>> jms
>> I was finally able to import the tablespace within the same database,
>> using the steps I outlined in the OP. However, I also needed to drop
>> the tablespaces before doing the import.
>>
>> I did get a few errors though about:
>>
>> IMP-00093: Inconsistency between dumpfile constraint definition for
>> table XXX with columns ("yyyy" )
>> IMP-00003: ORACLE error 1925 encountered
>> ORA-01925: maximum of 30 enabled roles exceeded
>> IMP-00000: Import terminated unsuccessfully
>>
>> ... which I ignored.
>>
>> However, the problem that I am facing now is that all of the user's
>> sequences were NOT imported!, whereas if I do an expport/import for
>> the user, the sequences are imported. Aren't sequences stored in the
>> user's default tablespace ?
> 
> Not only the sequences ... but the views as well.
> 
> 

Transportable Tablespaces (TTS) will only copy user's objects that are actually stored in those tablespaces. So you'll get the tables, indexes, etc. You can query DBA_SEGMENTS to see which objects will be in the tablespace you are transporting.

Transportable Tablespaces will not let you move objects that exist in the Data Dictionary, i.e. views, sequences, packages, procedures.

Even though the definition for the trigger exists in the Data Dictionary, you can get triggers transported with TTS as they will be tied directly to the tables being transported. And you can copy constraints as well for the same reason.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com
Received on Wed Jun 13 2007 - 10:18:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US