Re: Importing Sequences to Oracle

From: <markp7832_at_my-deja.com>
Date: Wed, 08 Dec 1999 14:52:22 GMT
Message-ID: <82lrb4$7nu$1@nnrp1.deja.com>

In article <82k2j5$ib$1_at_nnrp1.deja.com>,   khandley_at_austin-hayne.com wrote:
> Is there anyway to import a sequence to a database?
>
> Thanks,
> Kevin
>

Oracle does not provide a means to export and import just a sequence; however you might be able to do a user export of the sequence owner and then follow this with a user import with ignore set to the default of 'n' so that all creates for existing objects fail. This will create all objects that were exported for the owner that do not exist in the target system, but the sequence will be set at the value it had in the original database which may not be what you want. If you follow this method you may want to do the export with rows=n to insure not copying test data to production.

I wrote a sequence code generator that you may find of use:

set echo off
rem
rem PL/SQL script to recreate sequences. rem
rem 19960730 Mark D. Powell Save code written as part of fix to SEQ$.
rem 19990810 Mark D. Powell Add grants on sequences rem
set serveroutput on size 65536
spool recre_seq.sql
declare
--

v_seq_name      varchar2(30) ;
v_seq_owner     varchar2(12) ;
v_increment     number       ;
v_maxvalue      number       ;
v_maxvalue_string varchar2(38) ;
v_minvalue      number       ;
v_minvalue_string varchar2(38) ;
v_cycle         varchar2(07) ;
v_cycle_string  varchar2(07) ;
v_cache         number       ;
v_cache_string  varchar2(12) ;
v_order         varchar2(07) ;
v_last          number       ;
v_ctr           number   := 0;

--
cursor c_seq is
 select substr(sequence_owner,1,12),

        sequence_name,
        nvl(min_value,0),
        nvl(max_value,0),
        nvl(increment_by,1),
        decode(cycle_flag,'Y','CYCLE','N','NOCYCLE','NOCYCLE'),
        decode(order_flag,'Y','ORDER','N','NOORDER','NOORDER'),
        cache_size,
        last_number

   from sys.dba_sequences
  where sequence_owner != 'SYS'
  order by sequence_owner, sequence_name ; --
begin
open c_seq ;
loop

 fetch c_seq into v_seq_owner,
                  v_seq_name,
                  v_minvalue,
                  v_maxvalue,
                  v_increment,
                  v_cycle,
                  v_order,
                  v_cache,
                  v_last ;

 exit when c_seq%notfound ;
 v_ctr := v_ctr + 1 ;
 if v_cache = 0 then

    v_cache_string := 'NOCACHE' ;
 else

    v_cache_string := 'CACHE '||v_cache ;  end if ;
 if v_minvalue = 0 then

    v_minvalue_string := 'NOMINVALUE' ;  else

    v_minvalue_string := 'MINVALUE '||to_char(v_minvalue) ;  end if ;
 if v_maxvalue = 0 then

    v_maxvalue_string := 'NOMAXVALUE' ;  else

    v_maxvalue_string := 'MAXVALUE '||to_char(v_maxvalue) ;  end if ;
 dbms_output.put_line('create sequence '||v_seq_owner||'.'||

                      v_seq_name) ;
 dbms_output.put_line('increment by '||v_increment);
 dbms_output.put_line('start with '||v_last) ;

 dbms_output.put_line
(' '||v_minvalue_string||' '||v_maxvalue_string||' '||

                      v_cycle||' '||v_cache_string||' '||v_order) ;

 dbms_output.put_line(';') ;
 dbms_output.put_line('rem') ;
 for c_grt in (select grantee

               from   sys.dba_tab_privs
               where  table_name = v_seq_name
               and    grantor    = v_seq_owner ) loop
               dbms_output.put_line('grant select
on '||v_seq_owner||'.'||
                          v_seq_name||' to '||c_grt.grantee||' ;') ;
              end loop ;

 dbms_output.put_line('rem') ;
end loop ;
 dbms_output.put_line('rem Number of sequences generated: '||v_ctr) ; end ;
/

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 08 1999 - 08:52:22 CST