导出oracle数据库对象---同义词,系列,视图

发布: 2007-7-02 11:08 | 作者: admin | 来源: | 查看: 10次 | 进入软件测试论坛讨论

脚本可分两部分:
同义词
1:synonym.cmd:
sqlplus dxsq/teledoone@jnnew @synonym.sql
2.synonym.sql:
create table tmp_user_synonym as select @#create synonym @#||synonym_name||chr(13)||@#for@#||chr(13)||TABLE_OWNER||@#.@#||TABLE_NAME||chr(13)||@#/@# text from user_synonyms;
set heading off feedback off termout off
set pagesize 0
set linesize 100
set long 100000
col tt format a
spool d:\bat ynonym.log;
select text tt from tmp_user_synonym;
spool off;
drop table tmp_user_synonym;
exit;
大家可以以一反三,系列的写法可以参考下面:

drop table tmp_user_sequence;
create table tmp_user_sequence (text varchar2(4000));
declare
begin
for tt in (select * from user_sequences) loop
     insert into tmp_user_sequence values(@#prompt@#);
     insert into tmp_user_sequence values(@#prompt create sequence @#||tt.SEQUENCE_NAME);
     insert into tmp_user_sequence values(@#prompt@#);
     insert into tmp_user_sequence values(@#create sequence@# || @# @#|| tt.SEQUENCE_NAME);
     insert into tmp_user_sequence values(@#minvalue@# ||@# @#|| tt.MIN_VALUE );
     insert into tmp_user_sequence values(@#MAXVALUE@# ||@# @#|| tt.MAX_VALUE);
     insert into tmp_user_sequence values(@#start with @# || @# @#||tt.LAST_NUMBER);
     insert into tmp_user_sequence values(@#increment by@# ||@# @#|| tt.INCREMENT_BY);
     insert into tmp_user_sequence values(@#cache@# ||@# @#|| to_char(tt.CACHE_SIZE));
     if tt.CYCLE_FLAG=@#Y@# then
       insert into tmp_user_sequence values(@#cycle@#);
     end if;
     if tt.ORDER_FLAG=@#Y@# then
       insert into tmp_user_sequence values(@#order@#);
     end if;
     insert into tmp_user_sequence values(@#/@#);
     insert into tmp_user_sequence values(@#@#);
end loop;
commit;
end;
/
set heading off feedback off termout off;
column text format A100;
spool d:\bat\user_sequences.log
select text from tmp_user_sequence;
spool off;
exit;