Hi,
Any proposals how to reset schema used for testing purposes every night ?

Idea is to clear db from test cases made during working day.

Does it go like this - and what are alternative ways (tools ?) to do it:
-exp a dmp when db is clear
-at nighttime run a .sh script to remove that testing schema
-imp that schema from dmp

BR, Timo

On 20 Mar, 08:24, "timo" <timo.ta...@luukku.com> wrote:
> Hi,
> Any proposals how to reset schema used for testing purposes every night ?
>
> Idea is to clear db from test cases made during working day.
>
> Does it go like this - and what are alternative ways (tools ?) to do it:
> -exp a dmp when db is clear
> -at nighttime run a .sh script to remove that testing schema
> -imp that schema from dmp
>
> BR, Timo

Oracle version?

"gazzag" <gareth@jamms.org> wrote in message
news:d2a497bb-b801-4e8a-afad-a4e9fd653c0c@i12g2000prf.googlegroups.com...
> On 20 Mar, 08:24, "timo" <timo.ta...@luukku.com> wrote:
>> Hi,
>> Any proposals how to reset schema used for testing purposes every night ?
>>
>> Idea is to clear db from test cases made during working day.
>>
>> Does it go like this - and what are alternative ways (tools ?) to do it:
>> -exp a dmp when db is clear
>> -at nighttime run a .sh script to remove that testing schema
>> -imp that schema from dmp
>>
>> BR, Timo
>
> Oracle version?

10.2; linux

On 20 Mar, 11:09, "timo" <timo.ta...@luukku.com> wrote:
>
> 10.2; linux

I have certainly done as you have suggested and it works quite well.
Namely:

- drop and recreate the schema
- import into the schema

Another method would be:

- truncate tables in target schema
- repopulate target schema from source schema using either INSERT
over a database link or INSERT from <source_schema>.<table_name>
etc...

HTH

-g

On Mar 20, 1:24*am, "timo" <timo.ta...@luukku.com> wrote:
> Hi,
> Any proposals how to reset schema used for testing purposes every night ?
>
> Idea is to clear db from test cases made during working day.
>
> Does it go like this - and what are alternative ways (tools ?) to do it:
> -exp a dmp when db is clear
> -at nighttime run a .sh script to remove that testing schema
> -imp that schema from dmp
>
> BR, Timo

You might look into transportable tablespaces and rman options,
depending on whether you are wanting to refresh from live data or some
stable data set. Remember rman can restore tablespaces to a point in
time. I've heard some people are using flashback.

It all really depends what you are doing. You should have some test
data that includes bad data and data that comes near and goes over
functional design limits, and some that reflects actual data
distributions and values.

I'm always wishing I had time to do it right...

jg
--
@home.com is bogus.
awk 'BEGIN { q="'"'"'";print "Never say can"q"t."; exit }'

gazzag wrote:
> On 20 Mar, 11:09, "timo" <timo.ta...@luukku.com> wrote:
>> 10.2; linux
>
> I have certainly done as you have suggested and it works quite well.
> Namely:
>
> - drop and recreate the schema
> - import into the schema
>
> Another method would be:
>
> - truncate tables in target schema
> - repopulate target schema from source schema using either INSERT
> over a database link or INSERT from <source_schema>.<table_name>
> etc...
>
> HTH
>
> -g
Yet another idea, suited for large amounts of data (import would
take too long):
Use transportable tablespaces - all you import then, is the
metadata - you just copy the datafile(s) with all data from
another location.

Works for data only; if you have any source code that should
be reset, this does not work

--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

joel garry wrote:

>
> You might look into transportable tablespaces and rman options,
You beat me to it
--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up

"timo" <timo.talja@luukku.com> schreef in bericht
news:gbpEj.36$2f2.28@read4.inet.fi...
> Hi,
> Any proposals how to reset schema used for testing purposes every night ?
>
> Idea is to clear db from test cases made during working day.
>
> Does it go like this - and what are alternative ways (tools ?) to do it:
> -exp a dmp when db is clear
> -at nighttime run a .sh script to remove that testing schema
> -imp that schema from dmp
>
> BR, Timo
>
>

Here's another option: use vmware, take a snapshot; do what ever you like
and return to your snapshot!

Shakespeare

On Mar 20, 4:24 am, "timo" <timo.ta...@luukku.com> wrote:
> Hi,
> Any proposals how to reset schema used for testing purposes every night ?
>
> Idea is to clear db from test cases made during working day.
>
> Does it go like this - and what are alternative ways (tools ?) to do it:
> -exp a dmp when db is clear
> -at nighttime run a .sh script to remove that testing schema
> -imp that schema from dmp
>
> BR, Timo

Hi Timo,

In addition to what others have posted, lookup flashback database.

HTH,

Steve