oracle11数据备份之导出sequences

oracle11数据备份之导出sequences

1、将某个用户的全部sequence查询出来，并拼成创建语句。

Sql代码

select 'create sequence '||sequence_name||     
       ' minvalue '||min_value||     
       ' maxvalue '||max_value||     
       ' start with '||last_number||     
       ' increment by '||increment_by||     
       (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'   
from dba_sequences where sequence_owner='LIDONG'

注意：其中LIDONG，是需要导出sequence的用户，必须大写的说！并且使用该脚本的用户需要有访问dba_sequences的权限。

2、导出本用户的sequence

Sql代码

select 'create sequence '||sequence_name||    
       ' minvalue '||min_value||    
       ' maxvalue '||max_value||    
       ' start with '||last_number||    
       ' increment by '||increment_by||    
       (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'  
from user_sequences

3.删除某个用户的所有的sequence

declare owner_name varchar(20);  
    cursor cur1 is select sequence_name from user_sequences;  
    begin  
        owner_name := 'LIDONG';   
        for cur2 in cur1 loop  
            execute immediate 'drop sequence '||owner_name||'.'||cur2.sequence_name;  
        end loop;  
    end;