Re: Export Tablespaces

comments embedded
"FlameDance" <FlameDance_at_gmx.de> wrote in message news:bq24nk$spk$03$1_at_news.t-online.com...
> Hello everyone,
>
> under Oracle 8.1.7 and redhat linux 8 I want to create a daily copy of
> the production database (or a part of it) on the same machine for use as
> a test database.
>
> So far we have used
> exp user/password_at_instance
> and imported the tables but that doesn't suffice anymore, as we need
> procedures, functions, triggers, packages, etc too.
>
> exp system/password_at_instance file=mydump full=y

you only need to export the relevant user, there is a parameter to the exp utility for this.

>
> works (after giving the EXP_FULL_DATABASE role to system) but I've been
> warned that a full import will attempt to overwrite the production
> database instead of creating the test database.

[Quoted] [Quoted] import will import wherever you tell it to. The only way that you could overwrite production would be if the import script specified the production [Quoted] database as its destination. imp user/pass_at_testdb .......

>
> Another problem is that there is a huge table in the database that uses
> 1.7GB and that the Datafiles are only 2GB big. If the table get's
> fragmented over 2 datafiles, there's trouble ahead, I've been told (I
> haven't verified that yet).

[Quoted] if the table is spread over 2 datafiles then.... nothing much will happen. [Quoted] What will happen however if you export with compress=y and the table is larger than any single datafile is that there will be nowhere that the table [Quoted] can be created and import will fail. You should be using compress=n.

> So my idea is to export tablespaces and reuse them in the test database.
> That would avoid both problems (I hope) and even be faster than writing
> and reading export files.
>
> However, I can't seem to get it right, in various wording I get error
> messages. Whatever I try the exp utility throws error messages at me.
>
> For any hint for solution I'd be thankful.

[Quoted] [Quoted] Not withstanding my suggestions above your key problem is this one ORA-01034: ORACLE not available. You can check out the message at tahiti.oracle.com but I'll give you this one for free. Your database isn't started.

Niall Received on Wed Nov 26 2003 - 15:02:14 CET