Sequence

sequence rebuild
+++++++++++


select 'drop sequence ' || sequence_name || ';' from user_sequences;


select 'select dbms_metadata.get_ddl('||'''SEQUENCE'''||','''||SEQUENCE_NAME||''','''||SEQUENCE_OWNER||''') FROM DUAL ;' FROM all_sequences where sequence_owner='ODSADMIN'';

select 'select dbms_metadata.get_ddl('||"'SEQUENCE"'||',"'||SEQUENCE_NAME||","||SEQUENCE_OWNER||"’) FROM DUAL ;' FROM all_sequences where sequence_owner='ORADMIN’ and sequence_name like 'MKT_SHOPPING_SEQ';



Create sequence
+++++++++++

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;' from all_sequences where sequence_owner='ODSADMIN';
#!/usr/bin/ksh
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




TOD1DB_get_sequences.ksh


sqlplus /nolog <<EOF
connect / as sysdba

set lines 188
set trimspool on
set pages 0

spool create_sequences.lst

select 'CREATE SEQUENCE ODSADMIN.'||sequence_name||' START WITH '||last_number||' MAXVALUE 999999999999
999999999999999 MINVALUE 1 NOCYCLE NOCACHE ORDER;'
from all_sequences where sequence_owner='ODSADMIN';

EOF


if [ -f create_sequences.sql ]; then
        rm create_sequences.sql
fi

cat TOD1DB_get_sequences_header.txt > create_sequences.sql

cat create_sequences.lst |grep -v selected |grep -v SQL |grep -v "  2  from all_" >> create_sequences.
sql

echo "" >> create_sequences.sql
echo "quit" >> create_sequences.sql





2)TOD1DB_get_sequences_header.txt

connect / as sysdba

set time on  timing on  echo on pagesize 20000 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select NAME from V$DATABASE;


3) drop sequence


SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
spool drop_sequences_r.sql
select 'drop sequence '||sequence_owner||'.'||sequence_name||';'
from dba_sequences
where sequence_owner=upper('&l_schema') ;

spool off



4)grant prilvege




SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS where owner='ODSADMIN';