Transportable tablespaces within the same database

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):

1) alter tablespace ts1 read_only;
alter tablespace ts2 read_only;

2) EXP TRANSPORT_TABLESPACE=y TABLESPACES=(ts1,ts2)
TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=tsexpdat.dmp

3) Copy the datafiles of tablespaces ts1 and ts2 to a different
location.

4) Drop all objects owned by user

5) IMP TRANSPORT_TABLESPACE=y FILE=tsexpdat.dmp
DATAFILES=('/db/datafile1,'/db/datafile2')
TABLESPACES=(ts1,ts2) TTS_OWNERS=(joe)

6) ALTER TABLESPACE ts1 READ WRITE
ALTER TABLESPACE ts2 READ WRITE

<jms.news@gmail.com> schreef in bericht
news:1181278817.166749.65340@i13g2000prf.googlegro ups.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):
>
> 1) alter tablespace ts1 read_only;
> alter tablespace ts2 read_only;
>
> 2) EXP TRANSPORT_TABLESPACE=y TABLESPACES=(ts1,ts2)
> TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=tsexpdat.dmp
>
> 3) Copy the datafiles of tablespaces ts1 and ts2 to a different
> location.
>
> 4) Drop all objects owned by user
>
> 5) IMP TRANSPORT_TABLESPACE=y FILE=tsexpdat.dmp
> DATAFILES=('/db/datafile1,'/db/datafile2')
> TABLESPACES=(ts1,ts2) TTS_OWNERS=(joe)
>
> 6) ALTER TABLESPACE ts1 READ WRITE
> ALTER TABLESPACE ts2 READ WRITE
>

Yes one can!

<jms.news@gmail.com> a écrit dans le message de news: 1181278817.166749.65340@i13g2000prf.googlegroups.c om...
| 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):
|
| 1) alter tablespace ts1 read_only;
| alter tablespace ts2 read_only;
|
| 2) EXP TRANSPORT_TABLESPACE=y TABLESPACES=(ts1,ts2)
| TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=tsexpdat.dmp
|
| 3) Copy the datafiles of tablespaces ts1 and ts2 to a different
| location.
|
| 4) Drop all objects owned by user
|
| 5) IMP TRANSPORT_TABLESPACE=y FILE=tsexpdat.dmp
| DATAFILES=('/db/datafile1,'/db/datafile2')
| TABLESPACES=(ts1,ts2) TTS_OWNERS=(joe)
|
| 6) ALTER TABLESPACE ts1 READ WRITE
| ALTER TABLESPACE ts2 READ WRITE
|

And the purpose is?

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> wrote:

>
> <jms.news@gmail.com> a écrit dans le message de news:
> 1181278817.166749.65340@i13g2000prf.googlegroups.c om...
> | 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.

>
> Regards
> Michel Cadot

"J" <noone@noone.org> a écrit dans le message de news: 466a2d19$0$17156$afc38c87@news.optusnet.com.au...
| "Michel Cadot" <micadot{at}altern{dot}org> wrote:
|
| >
| > <jms.news@gmail.com> a crit dans le message de news:
| > 1181278817.166749.65340@i13g2000prf.googlegroups.c om...
| > | 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".

|
| >
| > Regards
| > Michel Cadot
|
|

On Jun 9, 3:12 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "J" <noone@noone.org> a écrit dans le message denews: 466a2d19$0$17156$afc38c87@news.optusnet.com.au...
> | "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> |
> | >
> | > <jms.news@gmail.com> a crit dans le message de news:
> | > 1181278817.166749.65340@i13g2000prf.googlegroups.c om...
> | > | 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

On Jun 12, 10:54 am, "jms.n...@gmail.com" <jms.n...@gmail.com> wrote:
> On Jun 9, 3:12 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
>
>
> > "J" <n...@noone.org> a écrit dans le message denews: 466a2d19$0$17156$afc38...@news.optusnet.com.au...
> > | "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> > |
> > | >
> > | > <jms.n...@gmail.com> a crit dans le message de news:
> > | > 1181278817.166749.65...@i13g2000prf.googlegroups.c om...
> > | > | Instead of transporting a tablespace between 2 databases .... canone
> > | > | 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 ?

On Jun 13, 9:46 am, "jms.n...@gmail.com" <jms.n...@gmail.com> wrote:
> On Jun 12, 10:54 am, "jms.n...@gmail.com" <jms.n...@gmail.com> wrote:
>
>
>
> > On Jun 9, 3:12 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>
> > > "J" <n...@noone.org> a écrit dans le message denews: 466a2d19$0$17156$afc38...@news.optusnet.com.au...
> > > | "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> > > |
> > > | >
> > > | > <jms.n...@gmail.com> a crit dans le message de news:
> > > | > 1181278817.166749.65...@i13g2000prf.googlegroups.c om...
> > > | > | 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.

jms.news@gmail.com wrote:
> On Jun 13, 9:46 am, "jms.n...@gmail.com" <jms.n...@gmail.com> wrote:
>> On Jun 12, 10:54 am, "jms.n...@gmail.com" <jms.n...@gmail.com> wrote:
>>
>>
>>
>>> On Jun 9, 3:12 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>>>> "J" <n...@noone.org> a écrit dans le message denews: 466a2d19$0$17156$afc38...@news.optusnet.com.au...
>>>> | "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>>>> |
>>>> | >
>>>> | > <jms.n...@gmail.com> a crit dans le message de news:
>>>> | > 1181278817.166749.65...@i13g2000prf.googlegroups.c om...
>>>> | > | 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@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