Re: Selective export

From: Maxim <mdemenko_at_gmail.com>
Date: Mon, 9 May 2011 13:40:49 +0200
Message-ID: <BANLkTiknS+7gHy83-q9CT_AQ6NZm9r9msA_at_mail.gmail.com>

With datapump you can implement multiple QUERY parameter to achieve that, here a small example:

>expdp scott/tiger directory=dpdir \
> dumpfile=scott.dmp tables=\"EMP,DEPT\" \
> query=EMP:\"WHERE DEPTNO=10\" \
> QUERY=DEPT:\"WHERE DEPTNO IN \(10,20\)\"
Export: Release 11.2.0.2.0 - Production on Mon May 9 13:39:33 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dpdir dumpfile=scott.dmp tables="EMP,DEPT" query=EMP:"WHERE DEPTNO=10" QUERY=DEPT:"WHERE DEPTNO IN (10,20)"
Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.898 KB       2
rows
. . exported "SCOTT"."EMP"                               8.125 KB       3

rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

---

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:   /home/oracle/work/dp/scott.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 13:39:40

Best regards

Maxim

On Mon, May 9, 2011 at 12:16 PM, Abhishek Gurung < abhishek.gurung_at_hotmail.com> wrote:

> Hi
>
> Thanks for your reply.
> What I want is like this
> I need selected data from three tables not whole tables e.g
> select * from emp where empid = 1
> select * from dept where deptid = 2
> select * from countries where countryid = 3
>
> Regards
> Abhishek
> ------------------------------
> Date: Mon, 9 May 2011 08:09:45 +0100
> Subject: Re: Selective export
> From: nigel.cl.thomas_at_googlemail.com
> To: abhishek.gurung_at_hotmail.com
> CC: oracle-l_at_freelists.org
>
>
> (Taken from http://wiki.oracle.com/page/Oracle+export+and+import+)
>
> To export specific tables to disk:
>
> - Login to server which has an Oracle client
>
> exp SYSTEM/password FILE=expdat.dmp TABLES=(scott.emp,hr.countries)
>
> -the above command uses two users : scott and hr
>
> exp <user>/<password> FILE=scott.dmp TABLES=(emp,dept)
>
> the above is only for one user
>
> Note that in first case user doesn't have to be SYSTEM, but does need
> access to the tables being exported.
>
> HTH Nigel
>
> On 9 May 2011 07:50, Abhishek Gurung <abhishek.gurung_at_hotmail.com> wrote:
>
> Hi
>
> I want to export selective data from three tables in one dump.
> Can anyone suggest me how it can be done?
> If it is not possible can I combine the three dump files into one?
>
> Thankyou.
>
> Regards
> Abhishek
>
>
>

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

Received on Mon May 09 2011 - 06:40:49 CDT