RE: Using QUERY in export

From: Hallas, John, Tech Dev <John.Hallas_at_gb.vodafone.co.uk>
Date: Thu, 2 Jun 2005 15:50:26 +0100
Message-ID: <1C6E45ADB2EC324F9553E468ABFE0F63030F0B44@UKWMXM04>

Example is here

Seems to work fine for me. This is what I did (8.1.7.4, also works on 9.2.0.2).

SQL> create user fred identified by fred default tablespace tools temporary tabl
espace temp quota unlimited on tools;

User created.

SQL> grant create session,create table to fred;

Grant succeeded.

SQL> conn fred/fred
Connected.

SQL> create table t1 (c1 number, c2 varchar2(10));

Table created.

SQL> create table t2 (c1 number);

Table created.

SQL> insert into t1 values (1,'a');

1 row created.

SQL> insert into t1 values (2,'b');

1 row created.

SQL> insert into t2 values (1);

1 row created.

SQL> commit;

Commit complete.

/u00/oracle/alan > cat exp.par

userid=3D"/ as sysdba"
file=3Dfred.dmp
log=3Dfred.log
tables=3D(fred.t1)
query=3D"where fred.t1.c1 in (select c1 from fred.t2)"

/u00/oracle > exp parfile=3Dexp.par

Export: Release 8.1.7.4.0 - Production on Wed Oct 8 11:54:47 2003

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

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in WE8PC850 character set and WE8PC850 NCHAR character set

About to export specified tables via Conventional Path ... Current user changed to FRED

. . exporting table                             T1          1 rows

exported
Export terminated successfully without warnings.

SQL> delete t1 where c1 =3D 1;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

        C1 C2
---------- ----------

         2 b

1 row selected.

/u00/oracle/alan > imp \'/ as sysdba\' file=3Dfred.dmp full=3Dy =
ignore=3Dy=20

Import: Release 8.1.7.4.0 - Production on Wed Oct 8 11:56:22 2003

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

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production With the Partitioning option
JServer Release 8.1.7.4.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path import done in WE8PC850 character set and WE8PC850 NCHAR character set

. importing SYS's objects into SYS
. importing FRED's objects into FRED
. . importing table                           "T1"          1 rows

imported
Import terminated successfully without warnings.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 02 2005 - 11:07:36 CDT