Wednesday, August 11, 2010

What happened to your sequence/view/procedures during the "imp" if ignore=y

Basically they will follow the same rule that if already exists, the imp process will skip them. There is no "replace" keyword to be used.

For example:

"CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREM"
"ENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE"
"CREATE FORCE VIEW "DONGHUA"."V" ("VERSION") AS "
"select 'v2' version from dual"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = PLSQL"
"CREATE procedure p as begin dbms_output.put_line('v2');end;"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = NONE"
"ALTER PROCEDURE "P" COMPILE REUSE SETTINGS TIMESTAMP '2010-08-11:22:52:46'"

The side-effect for the sequence is the cache will be cleared even it's skipped.



SQL> select * from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
S 1 1.0000E+28 1 N N 20
21


SQL> CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
2 ;
CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select s.nextval from dual;

NEXTVAL
----------
21


SQL> CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
2 ;
CREATE SEQUENCE "S" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> select s.nextval from dual;

NEXTVAL
----------
41