create table tmp_user_synonym as select 'create synonym 'synonym_namechr(13)'for'chr(13)TABLE_OWNER'.'TABLE_NAMEchr(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/synonym.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;
