Hi - We have a small Oracle server used by three programmers.
We would like programmer working on Schema A to be able to backup the
schema (at T1) and subsequently (at T2) restore it for the purposes of
testing etc.

We would also like on customer sites when applying schema changes via
DDL to backup the structure of the affected schema before making the
changes.

We assumed both of these would be possible via exp (with ROWS=Y) and
imp (with IGNORE=Y, DESTROY=Y) but we find, on restore, we still have
the data inserted between T1 and T2.

As background - I know we could at least do the programmer thing by
using RMAN but we'd rather not give the programmers access to that if
we could avoid it; more background the data volumes are tiny.

Can anyone explain what's going wrong ?

thanks

R.

On Jul 14, 9:59*pm, northof40 <shearich...@gmail.com> wrote:
> Hi - We have a small Oracle server used by three programmers.
> We would like programmer working on Schema A to be able to backup the
> schema (at T1) and subsequently (at T2) restore it for the purposes of
> testing etc.
>
> We would also like on customer sites when applying schema changes via
> DDL to backup the structure of the affected schema before making the
> changes.
>
> We assumed both of these would be possible via exp (with ROWS=Y) and
> imp (with IGNORE=Y, DESTROY=Y) but we find, on restore, we still have
> the data inserted between T1 and T2.
>
> As background - I know we could at least do the programmer thing by
> using RMAN but we'd rather not give the programmers access to that if
> we could avoid it; more background the data volumes are tiny.
>
> Can anyone explain what's going wrong ?
>
> thanks
>
> R.

What do you mean by
>> we still have the data inserted between T1 and T2. <<

If you are running the import as user T1 and the T2 objects are being
imported to user T1 then use the fromuser= touser= parameters or impdp
equilivent to load T2 back into user T2. User T1 is probably going to
have to have the EXP_FULL_DATABASE role in order to export other
user's objects.

HTH -- Mark D Powell --

On Jul 15, 10:02*am, Mark D Powell <Mark.Pow...@eds.com> wrote:
> On Jul 14, 9:59*pm, northof40 <shearich...@gmail.com> wrote:
>
>
>
>
>
> > Hi - We have a small Oracle server used by three programmers.
> > We would like programmer working on Schema A to be able to backup the
> > schema (at T1) and subsequently (at T2) restore it for the purposes of
> > testing etc.
>
> > We would also like on customer sites when applying schema changes via
> > DDL to backup the structure of the affected schema before making the
> > changes.
>
> > We assumed both of these would be possible via exp (with ROWS=Y) and
> > imp (with IGNORE=Y, DESTROY=Y) but we find, on restore, we still have
> > the data inserted between T1 and T2.
>
> > As background - I know we could at least do the programmer thing by
> > using RMAN but we'd rather not give the programmers access to that if
> > we could avoid it; more background the data volumes are tiny.
>
> > Can anyone explain what's going wrong ?
>
> > thanks
>
> > R.
>
> What do you mean by
> *>> we still have the data inserted between T1 and T2. <<
>
> If you are running the import as user T1 and the T2 objects are being
> imported to user T1 then use the fromuser= touser= parameters or impdp
> equilivent to load T2 back into user T2. *User T1 is probably going to
> have to have the EXP_FULL_DATABASE role in order to export other
> user's objects.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

PS - I question if you should be using destroy=y. That is a very
dangerous parameter and unless you can guarentee that all sites that
run your code separate the user objects into dedicated tablespaces
could cause damage to their databases.

-- Mark D Powell --

Agreed on destroy = y--bad idea.

Have you thought about dropping a recreating T2 (perhaps after
exporting). This should all be shell (and/or batch) scriptable, to be
a fairly automated and consistent process.

Also have you thought about using FLASHBACK?

On 15 Jul, 15:02, Mark D Powell <Mark.Pow...@eds.com> wrote:
> If you are running the import as user T1 and the T2 objects are being
> imported to user T1 then use the fromuser= touser= parameters or impdp
> equilivent to load T2 back into user T2. *User T1 is probably going to
> have to have the EXP_FULL_DATABASE role in order to export other
> user's objects.
>
> HTH -- Mark D Powell --- Hide quoted text -
>
> - Show quoted text -

As I understand the OP, T1 and T2 are not schema names.

T1 = time of export of the schema in question
T2 = time of subsequent import of the schema in question

At T2, I would drop the schema in question, recreate it and then run
the import with FULL=Y.

HTH

-g