exp/imp at table level
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: exp/imp at table level

  1. #1
    Join Date
    Mar 2008
    Posts
    21

    exp/imp at table level

    Hello,

    Here is the situation. I have schema name aaa and schema name bbb. Both of them in same 8i database. But i need to do export/import at table level. Schema bbb has tabled called macadd and so does schema aaa has same tablename macadd. But i need to take an export of macadd( from schema bbb) to the already existing schema aaa.

    But here is the twist. Schema aaa's table macadd already have data in it. So my question when i take an export from bbb. And import that into schema aaa table. Will it insert the data from the begin or just add to the exiting data.

    Well i need to keep the data in schema aaa tables and just add the new data from schema bbb tables ??? any idea how ??? And the fact that We as DBA's in the company dont load data thru sqlldr. Which would have been a lot of help in this situation. So only way i been told is so do exp/imp. Thanks in Advance. Thanks

  2. #2
    Join Date
    Dec 2007
    Posts
    55
    Import by default append the data into already existing rows as long as you have primary/unique constraints enabled,data will not get duplicated.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ...this is getting scary man.

    I don't even want to know why you don't use SQL*Loader...

    I don't even want to start thinking about why you think SQL*Loader would help you to move data from a table in schema AAA to a table in schema BBB of the same instance.

    You don't even need Exp/Imp. Don't you know you car select from a table in one schema and insert in a table sitting in a different one?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    why don't you go for copy command ?

    It's an alternative to the IMP and EXP commands that lets you copy data between two SQL*Net connected databases.

    you can CREATE a new table, REPLACE an existing table, INSERT values to an existing table or APPEND values to an existing table.

    A typical example of the COPY command in use would be;

    SQL> copy from scott/tiger @ORCL92 -
    to scott/tiger @ORCL92-
    create new_emp –
    using select * from emp;

    Here is an example of a COPY command that copies only two columns from the source table, and copies only those rows in which the value of DEPTNO is 30:

    SQL> COPY FROM SCOTT/TIGER@BOSTONDB -
    > REPLACE EMPCOPY2 -
    > USING SELECT ENAME, SAL -
    > FROM EMPCOPY -
    > WHERE DEPTNO = 30

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gopu_g
    ...lets you copy data between two SQL*Net connected databases.
    It doesn't surprises me anymore, one more time you didn't read the original post -- poster wants to move data from schema A to schema B in the same Oracle instance
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by PAVB
    It doesn't surprises me anymore, one more time you didn't read the original post -- poster wants to move data from schema A to schema B in the same Oracle instance
    So what.... ?

    SQL> copy from gopu/gopu@orcl to gopudba/gopudba@orcl create x using select * from x;

    Array fetch/bind size is 15. (arraysize is 15)
    Will commit when done. (copycommit is 0)
    Maximum long size is 80. (long is 80)

    Table X created.

    5 rows selected from gopu@orcl.
    5 rows inserted into X.
    5 rows committed into X at gopudba@orcl.

    SQL> insert into x values('b',6);

    1 row created.

    SQL> commit;

    SQL> copy from gopu/gopu@orcl to gopudba/gopudba@orcl append x using select * from x where id=6;


    Array fetch/bind size is 15. (arraysize is 15)
    Will commit when done. (copycommit is 0)
    Maximum long size is 80. (long is 80)
    1 rows selected from gopu@orcl.
    1 rows inserted into X.
    1 rows committed into X at gopudba@orcl.

    SQL> conn gopudba/gopudba
    Connected.
    SQL> select * from x;

    NAME ID
    ---------- ----------
    g 1
    f 2
    h 3
    j 4
    a 5
    b 6
    Last edited by gopu_g; 04-15-2008 at 04:53 AM.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gopu_g
    So what.... ?
    I know, you can also kill an ant with a nuke

    Now it is official, I'm giving up on you.

    Have a nice life and please try to stay away from databases
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by PAVB
    I know, you can also kill an ant with a nuke

    Now it is official, I'm giving up on you.

    Have a nice life and please try to stay away from databases
    post the answer... and correct me

  9. #9
    Join Date
    Dec 2007
    Posts
    55
    post the answer... and correct me
    Today 05:55 AM
    What PAVB meant is grant select on that table tabA to schemaB and do insert into tabB select * from scehmaA.tabA

  10. #10
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by monto1
    What PAVB meant is grant select on that table tabA to schemaB and do insert into tabB select * from scehmaA.tabA
    Thanks man...

    @PAVB Sorry for trouble with you

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