Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Export Certain Columns from Table

    I need to create a dmp file that doesn't have the WHOLE table in it but only columns. This should be a dmp that can be imported to another table into another DB. Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    do you mean the table with no rows?

    Or do you mean only specific rows of the table??

    You can do both.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Sorry for not being more descriptive. I need to export a table and it's rows but only certain columns withing the table. Does that make better sense? Let's say that I have a table (EM) and it has 27 columns and 511 rows BUT I only want to export 16 columns of the table, can I do that and still get all the rows? Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    create a temporary table of the columns/rows you want.

    create table temp_columns3
    as
    select col1, col2, col3 from original_table;


    example:
    Code:
    12:56:17 kod:platform> create table temp_column
    12:56:24   2  as select org_id, cust_id from customer;
    
    Table created.
    
    Elapsed: 00:00:00.04
    
    12:57:20 kod:platform> desc temp_column
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ------------
     ORG_ID                                                NOT NULL VARCHAR2(30)
     CUST_ID                                               NOT NULL VARCHAR2(15)
    
    12:57:25 kod:platform> select count(*) from temp_column;
    
      COUNT(*)
    ----------
            84
    
    Elapsed: 00:00:00.00
    12:57:35 kod:platform>
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Thanks. How should I export the data so that a person at another location can import this table and it's data? Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Exporting Columns

    Hi
    I presume that exporting few columns from a table should have a neater solution. Duplicating the information is really not a good solution.

    I have another way of handling this scenario.
    Why dont u export the whole table, and let the other guy import the full table and then drop the unwanted columns, since this information would be going to new table at the other end.

    Let me know if you need some more help on this.
    Thanx and Regards
    Aruneesh

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I don't trust the company getting the data as it is employee information. I have created a temporary table by duplicating the information and now, I want to export. I know how to export but I need to make sure that the people importing don't have any troubles. Can someone help me export this so the person recieving the dmp file can import it with no problems. Thanks, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Columns

    Hi
    Does this information need to be appended to an exisiting table or a new table would be created.

    Another thing worth looking at is a CSV (comma seperated file). Then use the SQL Loader on the other end to load the information.

    Let me know if it helps.
    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    It will create a new table. CSV would be fine but I don't know the best method to get this done from Oracle. I was just going to exp from the command line but if you know a better or easier way, let me know. Thanks, Jeremy
    Nothing better than a good ride.

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Okay...how do I export just the tables needed instead of the whole DB? Thanks, Jeremy
    Nothing better than a good ride.

  11. #11
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Exporting tables

    Hi

    Use the following command

    exp / file=anyfilename.dmp tables=DBNAME.TABLENAME1 buffer = 1600000

    That should do the job for you.
    Thanx and Regards
    Aruneesh

  12. #12
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I get an error that the table or view does not exist. I know they're there and I've verified but this is the output when I run it:

    Code:
    C:\>exp pk1/pk1@PK1 file=StarPrecision.dmp tables=PK1.EM_TMP,PK1.ATX_TMP buffer=
    1600000
    
    Export: Release 9.0.1.3.1 - Production on Fri Jul 25 12:03:21 2003
    
    (c) Copyright 2001 Oracle Corporation.  All rights reserved.
    
    
    Connected to: Oracle8i Release 8.1.7.0.0 - Production
    JServer Release 8.1.7.0.0 - Production
    EXP-00056: ORACLE error 942 encountered
    ORA-00942: table or view does not exist
    EXP-00000: Export terminated unsuccessfully
    
    C:\>
    Got any ideas? Thanks, Jeremy
    Nothing better than a good ride.

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I also tried like this:

    Code:
    C:\>exp pk1/pk1@PK1 file=StarPrecision.dmp tables=EM_TMP,ATX_TMP buffer=
    1600000
    
    Export: Release 9.0.1.3.1 - Production on Fri Jul 25 12:03:21 2003
    
    (c) Copyright 2001 Oracle Corporation.  All rights reserved.
    
    
    Connected to: Oracle8i Release 8.1.7.0.0 - Production
    JServer Release 8.1.7.0.0 - Production
    EXP-00056: ORACLE error 942 encountered
    ORA-00942: table or view does not exist
    EXP-00000: Export terminated unsuccessfully
    
    C:\>
    but got the same results.
    Nothing better than a good ride.

  14. #14
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow use this

    Use this

    exp / file=StarPrecision.dmp tables=PK1.EM_TMP,PK1.ATX_TMP buffer=
    1600000

    without your extra stuff in there.

    Thanx and Regards
    Aruneesh

  15. #15
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Okay...now I get this:

    Code:
    C:\>exp / file=StarPrecision.dmp tables=PK1.EM_TMP,PK1.ATX_TMP buffer=1600000
    
    Export: Release 9.0.1.3.1 - Production on Fri Jul 25 12:10:05 2003
    
    (c) Copyright 2001 Oracle Corporation.  All rights reserved.
    
    
    EXP-00056: ORACLE error 12560 encountered
    ORA-12560: TNS:protocol adapter error
    EXP-00000: Export terminated unsuccessfully
    What's up with that? :lol:
    Nothing better than a good ride.

Posting Permissions

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