Export Tablespaces

From: FlameDance <FlameDance_at_gmx.de>
Date: Wed, 26 Nov 2003 13:02:01 +0100
Message-ID: <bq24nk$spk$03$1_at_news.t-online.com>

[Quoted] Hello everyone,

[Quoted] 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.

[Quoted] exp system/password_at_instance file=mydump full=y

[Quoted] 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] 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] 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.

For the record: sqlplus '/ as sysdba' works.

Stephan

---

First as documented in
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/tspaces.htm#5697

---

exp file=famtbl.dmp TRANSPORT_TABLESPACE=y TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y

Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:13:57 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Username: sys as sysdba
Password:

EXP-00056: ORACLE error 1034 encountered
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully


---------------------------------------------------


A couple of other attempts:


---------------------------------------------------

-bash-2.05b$ exp "'/ as sysdba'" file=famtbl.dmp TRANSPORT_TABLESPACE=y
TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y

Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:20:46 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

EXP-00056: ORACLE error 1034 encountered
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory

EXP-00005: all allowable logon attempts failed
EXP-00000: Export terminated unsuccessfully


---------------------------------------------------

-bash-2.05b$ exp '/ as sysdba' file=famtbl.dmp TRANSPORT_TABLESPACE=y
TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y LRM-00108: invalid positional parameter value 'as'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help EXP-00000: Export terminated unsuccessfully

---

exp 'sys/password as sysdba' file=famtbl.dmp TRANSPORT_TABLESPACE=y TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y

LRM-00108: invalid positional parameter value 'as'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help EXP-00000: Export terminated unsuccessfully

---

exp "'sys/password as sysdba'" file=famtbl.dmp TRANSPORT_TABLESPACE=y TABLESPACES=(tbla,tblb) TRIGGERS=y CONSTRAINTS=y GRANTS=y

Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:10:31 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

EXP-00056: ORACLE error 1034 encountered
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Linux Error: 2: No such file or directory EXP-00005: all allowable logon attempts failed EXP-00000: Export terminated unsuccessfully

---

exp "'sys/password_at_instance as sysdba'" file=famtbl.dmp TRANSPORT_TABLESPACE=y TABLESPACES=(FAMDAT,FAMIDX) TRIGGERS=y CONSTRAINTS=y GRANTS=y

Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:11:44 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

EXP-00056: ORACLE error 1017 encountered ORA-01017: invalid username/password; logon denied Username:

---

-bash-2.05b$ exp "'system/password_at_instance as sysdba'" file=famtbl.dmp
TRANSPORT_TABLESPACE=y TABLESPACES=(FAMDAT,FAMIDX) TRIGGERS=y CONSTRAINTS=y GRANTS=y
Export: Release 8.1.7.4.0 - Production on Wed Nov 26 13:12:07 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

EXP-00056: ORACLE error 1031 encountered ORA-01031: insufficient privileges Received on Wed Nov 26 2003 - 13:02:01 CET