熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

導出oracle數據庫對象--同義詞,系列,視圖

2013-11-13 15:34:18  來源: Oracle 

  腳本可分兩部分:
  
  同義詞
  
  :synonymcmd:
  
  sqlplus dxsq/teledoone@jnnew @synonymsql
  
  synonymsql:
  
  create table tmp_user_synonym as select create synonym ||synonym_name||chr()||for||chr()||TABLE_OWNER||||TABLE_NAME||chr()||/ text from user_synonyms;
  set heading off feedback off termout off
  set pagesize
  set linesize
  set long
  col tt format a
  spool d:\bat\synonymlog;
  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 varchar());
  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 ||ttSEQUENCE_NAME);
  insert into tmp_user_sequence values(prompt);
  insert into tmp_user_sequence values(create sequence || || ttSEQUENCE_NAME);
  insert into tmp_user_sequence values(minvalue || || ttMIN_VALUE );
  insert into tmp_user_sequence values(MAXVALUE || || ttMAX_VALUE);
  insert into tmp_user_sequence values(start with || ||ttLAST_NUMBER);
  insert into tmp_user_sequence values(increment by || || ttINCREMENT_BY);
  insert into tmp_user_sequence values(cache || || to_char(ttCACHE_SIZE));
  if ttCYCLE_FLAG=Y then
  insert into tmp_user_sequence values(cycle);
  end if;
  if ttORDER_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 A;
  spool d:\bat\user_sequenceslog
  select text from tmp_user_sequence;
  spool off;
  exit;
From:http://tw.wingwit.com/Article/program/Oracle/201311/16952.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.