This discussion is archived
3 Replies Latest reply: Nov 4, 2012 11:07 PM by myOra_help RSS

imp records of One schema into another schema of same table.

leen Newbie
Currently Being Moderated
I had done following steps,

schemas(toy,toys)

1) i open the session of toy schema

First i taken backup of table

create table bck20121103_himan as select * from himan;

Backup table is created.

After taking the Backup table

delete himan;(deleting the records)

2) i log in to another session(toys)

exp toys/toys@orcl file=20121103TOYs.DMP TABLES=(HIMAN) /* Particular table is taken*/

3) i log in to toy schema

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) INDEXES=N IGNORE=Y

i tried the above statement it taken so much of time..

Later i tried

I log in to toy session

i rename the table with other name.

later i imported

imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y FULL=Y

it's successfully imported..

kindly correct me if its wrong or any other steps is their ..
  • 1. Re: imp records of One schema into another schema of same table.
    Varma Explorer
    Currently Being Moderated
    Hi,

    Follow the below steps:

    1)Take the backup of table using export utility.

    Exp toy/toy@orcl file=20121103TOYs.DMP TABLES=toys.HIMAN;

    2)Import it using imp utility.

    imp toy/toy@orcl file=20121103TOYs.DMP TABLES=HIMAN;

    FULL =(Y/N) Indicates whether EXPORT should export the entire database.
  • 2. Re: imp records of One schema into another schema of same table.
    javeedkaleem dba Newbie
    Currently Being Moderated
    as per your first method.

    you took backup
    you deleted not commited.

    took export from toys and imported into toys without fromuser & touser

    after correction.
    ============
    you mentioned

    you took backup
    you deleted toy.himen (again not commited)

    exported toy.himen
    imported toy.himen

    check you might have got the duplicate records also

    when you delete any records its a dml operation which does not auto commit you need to commit it.

    hope you got the point.

    Regards
    Kaleem.
  • 3. Re: imp records of One schema into another schema of same table.
    myOra_help Journeyer
    Currently Being Moderated
    Because you have not commited old trnsaction, thats why your import is waiting for old transaction to complete.
    Commit the old transaction, i.e. "delete himan;(deleting the records)"

    After export, try below for importing data in toy schema from toys

    imp toy/toy@orcl file=<dump file name> TABLES=(HIMAN) IGNORE=Y fromuser=toys touser=toy

    Try to use EXPDP/IMPDP data pumps instead of EXP/IMP as they provides much flexibilty.

    Hope it helps you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points