Home » RDBMS Server » Server Utilities » How to Export data based on Join between 3 tables. (Windows XP, Oracle 10g)
How to Export data based on Join between 3 tables. [message #488658] Mon, 10 January 2011 06:33 Go to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Hi,


How to export a data, which is a join of three tables.

Will Export or dbms_datapump supports for above scenario.


Database: DB1
Tables: T1, T2 & T3
Select: t1.*,t2.*,t3.*
Join: t1.c1=t2.c1 and t2.c1=t3.c1


Thanks in Advance,

Ranjan
Re: How to Export data based on Join between 3 tables. [message #488660 is a reply to message #488658] Mon, 10 January 2011 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: How to Export data based on Join between 3 tables. [message #488726 is a reply to message #488660] Mon, 10 January 2011 13:24 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But you might try to create a materialized view as SELECT from those tables, and then export the materialized view.

Create a MV (connected as SCOTT):
SQL> create materialized view mv_dept_emp as
  2  select d.deptno, d.dname, d.loc, e.empno, e.ename, e.job, e.sal, e.mgr
  3  from dept d, emp e
  4  where d.deptno = e.deptno;

Materialized view created.

Export it:
SQL> $exp scott/Tiger tables=mv_dept_emp file=mde.dmp

Export: Release 10.2.0.1.0 - Production on Pon Sij 10 20:20:26 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                    MV_DEPT_EMP         14 rows exported
Export terminated successfully without warnings.

Import into MIKE's schema:
SQL> $imp mike/lion file=mde.dmp full=y

Import: Release 10.2.0.1.0 - Production on Pon Sij 10 20:21:44 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SCOTT, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into MIKE
. importing SCOTT's objects into MIKE
. . importing table                  "MV_DEPT_EMP"         14 rows imported
Import terminated successfully without warnings.

Check what we've done:
SQL> connect mike/lion
Connected.

Session altered.

SQL> select * from mv_dept_emp;

    DEPTNO DNAME          LOC                EMPNO ENAME      JOB              SAL        MGR
---------- -------------- ------------- ---------- ---------- --------- ---------- ----------
        20 RESEARCH       DALLAS              7369 SMITH      CLERK            800       7902
        30 SALES          CHICAGO             7499 ALLEN      SALESMAN        1600       7698
        30 SALES          CHICAGO             7521 WARD       SALESMAN        1250       7698
        20 RESEARCH       DALLAS              7566 JONES      MANAGER         2975       7839
        30 SALES          CHICAGO             7654 MARTIN     SALESMAN        1250       7698
        30 SALES          CHICAGO             7698 BLAKE      MANAGER         2850       7839
        10 ACCOUNTING     NEW YORK            7782 CLARK      MANAGER         2450       7839
        20 RESEARCH       DALLAS              7788 SCOTT      ANALYST         3000       7566
        10 ACCOUNTING     NEW YORK            7839 KING       PRESIDENT       5000
        30 SALES          CHICAGO             7844 TURNER     SALESMAN        1500       7698
        20 RESEARCH       DALLAS              7876 ADAMS      CLERK           1100       7788
        30 SALES          CHICAGO             7900 JAMES      CLERK            950       7698
        20 RESEARCH       DALLAS              7902 FORD       ANALYST         3000       7566
        10 ACCOUNTING     NEW YORK            7934 MILLER     CLERK           1300       7782

14 rows selected.
Previous Topic: how to export table structure as script in oracle in all schemas
Next Topic: import with buffer parameter
Goto Forum:
  


Current Time: Fri Mar 29 01:10:18 CDT 2024