How to import only sequences?

[nir_s]

Hi folks,

I'm trying to import only sequences from dump file using Import Utility.
I ran the following command:

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

[slimdave]

http://download-west.oracle.com/docs...02.htm#1005473

Drop the sequences before importing

[nir_s]

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

[gandolf989]

What about creating the sequences with dynamic sql?

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;

[nir_s]

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

[pando]

What do you mean ignore doesnt work. The import halts when it encounters an error?

[nir_s]

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?

[pando]

if ignore does not work your import would halt

[tamilselvan]

Hi folks,

I'm trying to import only sequences from dump file using Import Utility.
I ran the following command:

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

[nir_s]

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