import selected rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: import selected rows

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    import selected rows

    Hi

    Is there an option in the import utility to import selected rows from a table?

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Well, there is a 'QUERY=select statement' when doing an export, so you could do that and then the import would only contain the rows you require.

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    By adding constraint at the column level, you can import selected rows.

    SQL> create table t1 (id int);

    Table created.

    SQL> insert into t1 select rownum from dba_tables where rownum < 11 ;

    10 rows created.

    SQL> select * from t1;
    ID
    ----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10


    10 rows selected.

    SQL> commit;

    Commit complete.

    --- do the export
    $>exp / file=t1.dmp full=N tables=tamil.t1

    Export: Release 9.2.0.4.0 - Production on Mon Jun 7 12:35:03 2004

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

    About to export specified tables via Conventional Path ... Current user changed to TAMIL
    . . exporting table T1 10 rows exported
    Export terminated successfully without warnings.

    SQL> drop table t1;

    Table dropped.

    SQL> create table t1 (id int check (id < 6));

    Table created.

    --- do the import
    $>imp / file=t1.dmp fromuser=tamil touser=tamil tables=t1 ignore=y

    Import: Release 9.2.0.4.0 - Production on Mon Jun 7 12:37:06 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

    Export file created by EXPORT:V09.02.00 via conventional path import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set . importing TAMIL's objects into TAMIL
    . . importing table "T1"
    IMP-00019: row rejected due to ORACLE error 2290
    IMP-00003: ORACLE error 2290 encountered
    ORA-02290: check constraint (TAMIL.SYS_C0028917) violated Column 1 6
    IMP-00019: row rejected due to ORACLE error 2290
    IMP-00003: ORACLE error 2290 encountered
    ORA-02290: check constraint (TAMIL.SYS_C0028917) violated Column 1 7
    IMP-00019: row rejected due to ORACLE error 2290
    IMP-00003: ORACLE error 2290 encountered
    ORA-02290: check constraint (TAMIL.SYS_C0028917) violated Column 1 8
    IMP-00019: row rejected due to ORACLE error 2290
    IMP-00003: ORACLE error 2290 encountered
    ORA-02290: check constraint (TAMIL.SYS_C0028917) violated Column 1 9
    IMP-00019: row rejected due to ORACLE error 2290
    IMP-00003: ORACLE error 2290 encountered
    ORA-02290: check constraint (TAMIL.SYS_C0028917) violated
    Column 1 10 5 rows imported
    Import terminated successfully with warnings.


    SQL> select * from t1;

    ID ----------
    1
    2
    3
    4
    5
    SQL>


    Tamil
    Last edited by tamilselvan; 06-07-2004 at 01:51 PM.

  4. #4
    Join Date
    Jan 2000
    Posts
    387
    Hi

    I am awared that export can use a query. However, I have done an export (with the query) a couple of weeks ago and i want to retrieve some of the data into the database with an import with selected rows only, therefore is this possible??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width