3 Replies Latest reply: Nov 5, 2012 1:07 AM by myOra_help RSS

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

    leen
      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
          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
            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
              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.