Re: export data out of Oracle DB

L. Tseng wrote:
>
> Hi,
>
> Does anyone know if there is a utility that can export/import data from/to a table
> using some criteria like in a where clause? Oracle's export/import utility can only
> export/import ALL the data from/to a table and they don't work with a view.
> For example I want to export part of a 10000 row table basing on a timestamp colume,
> how can this be done?
>
> Thanks,
>
> Leslie

Hi,

You can create another table that has exactly the same structure. Then you can export it.
You can create any table according your requirement ( adding columns, joins etc...)
Here is an example:

SQL> select * from emp;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- ---------

     7369 SMITH      CLERK          7902 17-DEC-80      
800                  20
     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600      
300        30
     7521 WARD       SALESMAN       7698 22-FEB-81      1250      
500        30
     7566 JONES      MANAGER        7839 02-APR-81     
2975                  20
     7654 MARTIN     SALESMAN       7698 28-SEP-81      1250     
1400        30
     7698 BLAKE      MANAGER        7839 01-MAY-81     
2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81     
2450                  10
     7788 SCOTT      ANALYST        7566 19-APR-87     
3000                  20
     7839 KING       PRESIDENT           17-NOV-81     
5000                  10
     7844 TURNER     SALESMAN       7698 08-SEP-81      1500        
0        30
     7876 ADAMS      CLERK          7788 23-MAY-87     
1100                  20
     7900 JAMES      CLERK          7698 03-DEC-81      
950                  30
     7902 FORD       ANALYST        7566 03-DEC-81     
3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82     
1300                  10

14 rows selected.

SQL> create table temp_emp_table_for_export as   2 select * from emp where sal > 1500;

Table created.

SQL> select * from temp_emp_table_for_export;

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- --------- --------- ---------

     7499 ALLEN      SALESMAN       7698 20-FEB-81      1600      
300        30
     7566 JONES      MANAGER        7839 02-APR-81     
2975                  20
     7698 BLAKE      MANAGER        7839 01-MAY-81     
2850                  30
     7782 CLARK      MANAGER        7839 09-JUN-81     
2450                  10
     7788 SCOTT      ANALYST        7566 19-APR-87     
3000                  20
     7839 KING       PRESIDENT           17-NOV-81     
5000                  10
     7902 FORD       ANALYST        7566 03-DEC-81     
3000                  20

7 rows selected.

SQL> Hakan Eren
InfoPower International Inc.
ERP/MRPII/DBMS Consulting and Software

Phone:905-507-2100
Fax:905-507-2108
E-mail:hakan_at_infopwr.com

       prabir_at_infopwr.com Received on Mon Jul 14 1997 - 00:00:00 CDT