SQL Script

Adjust Sequences

Updated : 4-August-1998
Version : 1.0

Description

This script will drop and re-create sequences based upon the value of a sequence from another database. The script prompt for the database name, system password of the source database and the owner of the sequences which to drop and re-create. This script is useful for initialising sequences of a new database from a master database.

Parameters

&dblink - The SQL*Net v2 TNS Entry of the source database
& passw - The system password of the remote database
&seq_own - The sequence owner to drop and re-create the sequences.

SQL Source

set serveroutput on size 1000000
set verify off

ACCEPT dblink PROMPT 'Enter the name of the remote database : '
ACCEPT passw  PROMPT 'Enter system password : ' hide
ACCEPT seq_own PROMPT 'Enter value for Sequence Owner : '

spool adjseq.lst

drop database link &dblink;
create database link &dblink 
connect to system identified by &passw using '&dblink';

declare

cursor seq1 ( p_owner all_sequences.sequence_owner%TYPE ) is 
   select SEQUENCE_NAME 
        , MIN_VALUE             
        , MAX_VALUE            
        , INCREMENT_BY        
        , decode(CYCLE_FLAG ,'Y','CYCLE','N','NOCYCLE') cycle_fl
        , decode(ORDER_FLAG ,'Y','ORDER','N','NOORDER') order_fl
        , 20 cache_size
        , LAST_NUMBER            
   from all_sequences@&&dblink
   where sequence_owner= p_owner 
   and last_number <> 1; 
cursor seq2 ( p_owner all_sequences.sequence_owner%TYPE
            , p_name  all_sequences.sequence_name%TYPE ) is
   select 'FOUND'
   from all_sequences
   where sequence_owner= p_owner
   and   sequence_name= p_name;
seq2_rec seq2%ROWTYPE;

sql_stmt1 VARCHAR2(2000);
sql_stmt2 VARCHAR2(2000);
sql_stmt3 VARCHAR2(2000);

tab_owner VARCHAR2(30);

p_cursor_id INTEGER;
p_exec      INTEGER;

begin

p_cursor_id := dbms_sql.open_cursor;

   tab_owner:='&seq_own';

   for seq1_rec in seq1 (tab_owner) loop

/* DROP THE SEQUENCE IF IT ALREADY EXISTS */

     open seq2 ( tab_owner, seq1_rec.sequence_name );
     fetch seq2 into seq2_rec;
     if (seq2%FOUND) then
        sql_stmt1 := 'DROP SEQUENCE '||tab_owner||'.'||seq1_rec.sequence_name;
        dbms_sql.parse(p_cursor_id,sql_stmt1,1);
        p_exec := dbms_sql.execute (p_cursor_id); 
        dbms_output.put_line (sql_stmt1);
     end if;
     close seq2;

     sql_stmt2 := 'CREATE SEQUENCE '||tab_owner||'.'||seq1_rec.sequence_name
                  ||' MINVALUE '
                  ||seq1_rec.min_value||' MAXVALUE '||seq1_rec.max_value
                  ||' INCREMENT BY '||seq1_rec.increment_by||' START WITH '
                  ||seq1_rec.last_number||' CACHE '||seq1_rec.cache_size
                  ||' '||seq1_rec.order_fl||' '||seq1_rec.cycle_fl ;
/*     sql_stmt3 := 'GRANT SELECT ON '||seq1_rec.sequence_name    */
/*                  ||' TO public'; */

     dbms_sql.parse(p_cursor_id,sql_stmt2,1);
     p_exec := dbms_sql.execute (p_cursor_id); 
     dbms_output.put_line (sql_stmt2);

     dbms_sql.parse(p_cursor_id,sql_stmt3,1);
     p_exec := dbms_sql.execute (p_cursor_id); 
     dbms_output.put_line (sql_stmt3);

   end loop;

dbms_sql.close_cursor(p_cursor_id);
end;
/

drop database link &&dblink;

PROMPT Checking on sequences ...

select sequence_name,last_number from dba_sequences
where sequence_owner = UPPER('&seq_owner')
/