How to import only sequences?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to import only sequences?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to import only sequences?

    Hi folks,

    I'm trying to import only sequences from dump file using Import Utility.
    I ran the following command:
    Code:
    imp system/pwd file=exp.dmp log=/tmp/imp_seq.log from user=USER1 touser=USER2 rows=n grants=n indexes=n constraints=n ignore=y
    The problem is that I got errors of "CREATE TYPE ... already exists"
    Why the "ignore=y" does not work in this case?
    How to overcome this problem?

    Thanks in advance,
    Nir

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    http://download-west.oracle.com/docs...02.htm#1005473

    Drop the sequences before importing
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi,

    I've already read the IGNORE=Y in the documentation.
    The option ignore=Y does not work when there are TYPES in the dump file.

    Any more suggestions?

    Thanks in advance,
    Nir

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    What about creating the sequences with dynamic sql?

    Code:
    SELECT  'DROP SEQUENCE '    || sequence_name  || ';' || CHR(10) ||
            'SELECT '           ||
          '''CREATE SEQUENCE '  || sequence_name ||
            ' INCREMENT BY '    || increment_by  ||
            ' START WITH ''||'  || sequence_name ||  '.NEXTVAL || ''' ||
            ' MINVALUE '        || MIN_VALUE     ||
            ' MAXVALUE '        || MAX_VALUE     ||
              CASE cycle_flag WHEN 'N' THEN ' NOCYCLE ' ELSE ' CYCLE ' END ||
              CASE order_flag WHEN 'N' THEN ' NOORDER ' ELSE ' ORDER ' END ||
            ' CACHE ' || CACHE_SIZE || ';'' FROM dual;' dyn_seq
      FROM user_sequences
     ORDER BY sequence_name;
    this space intentionally left blank

  5. #5
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi gandolf989,

    I know that I can create the sequences by dynamic sql.
    I wanted to check if anyone experienced with importing sequences by exp/imp.

    Thanks anyway!

    Regards,
    Nir

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    What do you mean ignore doesnt work. The import halts when it encounters an error?

  7. #7
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    The import does not halt,but all the errors invoke and appear on the screan.
    The question is why import does not ignore when the schema contains types?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if ignore does not work your import would halt

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by nir_s
    Hi folks,

    I'm trying to import only sequences from dump file using Import Utility.
    I ran the following command:
    Code:
    imp system/pwd file=exp.dmp log=/tmp/imp_seq.log from user=USER1 touser=USER2 rows=n grants=n indexes=n constraints=n ignore=y
    The problem is that I got errors of "CREATE TYPE ... already exists"
    Why the "ignore=y" does not work in this case?
    How to overcome this problem?

    Thanks in advance,
    Nir
    If the user defined OBJECT TYPE exists, then imp will report an error even.
    The error may look like:
    IMP-00061: Warning: Object type "USR1"."POINTTYPE" already exists with a different identifier
    "CREATE TYPE "POINTTYPE" TIMESTAMP '2005-12-28:16:42:11' OID '08FF481AE1B5C0"
    "30E0435AA83432C030' as object ( x number , y number) ;"
    Import terminated successfully with warnings.

    It seems to be a bug in 9i.
    Workaround is drop object type before imp.

    Tamil

  10. #10
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi Tamil,

    I'm running on 10.1.0.3 ,so the bug probably wasn't fixed
    I'll try to drop the TYPES before the import.

    Thanks!
    Nir

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