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

一些 schema 的常用腳本歸類介紹

2013-11-13 15:26:42  來源: Oracle 

  getcodesql 獲得某個存儲過程函數代碼腳本
  set feedback off
  set heading off
  set termout off
  set linesize
  set trimspool on
  set verify off
  spool &sql
  prompt set define off
  select decode( type||||to_char(linefm)
          PACKAGE BODY /||chr()
          null) ||
      decode(linecreate or replace ) ||
      text text
   from user_source
  where name = upper(&&)
  order by type line;
  prompt /
  prompt set define on
  spool off
  set feedback on
  set heading on
  set termout on
  set linesize
  
  ############################################################
              getallcodesql 獲得所以存儲過程函數代碼腳本
  set termout off
  set heading off
  set feedback off
  set linesize
  spool xtmpxsql
  select @getcode || object_name
  from user_objects
  where object_type in ( PROCEDURE FUNCTION PACKAGE )
  /
  spool off
  spool getallcode_INSTALLsql
  select @ || object_name
  from user_objects
  where object_type in ( PROCEDURE FUNCTION PACKAGE )
  /
  spool off
  set heading on
  set feedback on
  set linesize
  set termout on
  @xtmpxsql
  ############################################################
              getaviewsql 獲得某個視圖腳本
  set heading off
  set feedback off
  set linesize
  set trimspool on
  set verify off
  set termout off
  set embedded on
  set long
  
  column column_name format a
  column text format a
  
  spool &sql
  prompt create or replace view & (
  select decode(column_id) || column_name column_name
  from user_tab_columns
  where table_name = upper(&)
  order by column_id
  /
  prompt ) as
  select text
  from user_views
  where view_name = upper(&)
  /
  prompt /
  spool off
  
  set heading on
  set feedback on
  set verify on
  set termout on
  
  ############################################################
              getallviewsql 獲得所有視圖腳本
  set heading off
  set feedback off
  set linesize
  set trimspool on
  set verify off
  set termout off
  set embedded on
  
  spool tmpsql
  select @getaview || view_name
  from user_views
  /
  spool off
  
  set termout on
  set heading on
  set feedback on
  set verify on
  @tmp
  
  
  ##########################################################
              gettrigsql 獲得觸發器腳本
  set heading off
  set feedback off
  set linesize
  set trimspool on
  set verify off
  set termout off
  set embedded on
  
  spool &sql
  select
  create or replace trigger ||
            trigger_name||||chr()||
            decode(substr(trigger_type)
            AAFTERBBEFOREIINSTEAD OF)||
            CHR()||
        triggering_event||chr()||
        ON||table_owner||||
        table_name||||chr()||
        decode(instr(trigger_typeEACH ROW)null
                     FOR EACH ROW)||chr()
   trigger_body
  from user_triggers
  where trigger_name = upper(&)
  /
  prompt /
  
  spool off
  set verify on
  set feedback on
  set termout on
  set heading on
  
  #################################################################
                      analyzesql 分析某用戶下的表及索引(大表將以評估的方式分析)
                      
  set serveroutput on size
  declare
   v_per number();
   v_start number := dbms_utilityget_time;
   v_end  number;
  begin
  
  for rec in (select segment_namesegment_typeceil(sum(bytes)//) segment_size
   from user_segments group by segment_namesegment_type)
  loop
  
    if recsegment_type = INDEX then
       dbms_statsgather_index_stats(ownname=>??? 自己改一下
                      INDNAME=>recsegment_name
                     );
  
      dbms_outputput_line(recsegment_name|| ||recsegment_size||m ||ceil((dbms_utilityget_time v_start)/)||s);
       v_start := dbms_utilityget_time;
  
    elsif recsegment_type = TABLE then
      case when recsegment_size < then
        v_per := ;
         when recsegment_size < then
        v_per := ;
      else
        v_per := ;
      end case;
         dbms_statsgather_table_stats(OWNNAME=>???
            TABNAME=>recsegment_name
            ESTIMATE_PERCENT=>v_per
            METHOD_OPT=>FOR ALL INDEXED COLUMNS);
  
       dbms_outputput_line(recsegment_name|| ||recsegment_size||m ||ceil((dbms_utilityget_time v_start)/)||s);
       v_start := dbms_utilityget_time;
  
    end if;
  end loop;
  end;
  /                    
  
  ###############################################################
               print_table 縱向顯示一行
              
              
  create or replace
  procedure print_table( p_query in varchar )
  AUTHID CURRENT_USER
  is
    l_theCursor   integer default dbms_sqlopen_cursor;
    l_columnValue  varchar();
    l_status    integer;
    l_descTbl    dbms_sqldesc_tab;
    l_colCnt    number;
  begin
    dbms_sqlparse( l_theCursor p_query dbms_sqlnative );
    dbms_sqldescribe_columns( l_theCursor l_colCnt l_descTbl);
  
    for i in l_colCnt loop
      dbms_sqldefine_column(l_theCursor i l_columnValue );
    end loop;
  
    l_status := dbms_sqlexecute(l_theCursor);
  
    while ( dbms_sqlfetch_rows(l_theCursor) > ) loop
      for i in l_colCnt loop
        lumn_value( l_theCursor i l_columnValue );
        dbms_outputput_line( rpad( l_descTbl(l_name )
                   || : ||
                   l_columnValue );
      end loop;
      dbms_outputput_line( ); 注意如果輸出的行比較多的話要加大dbms_outputenable(值)
    end loop;
  exception
    when others then
      dbms_sqlclose_cursor( l_theCursor );
      RAISE;
  end;
  /
  
  grant execute on print_table to public;
  
  該腳本是實現橫向改成縱向顯示
  例如:
  一行記錄顯示如下:
  ADMIN_MEMBER_ID        : dealexpress
  VIEW_NAME           : Deal Express
  BUSINESS_TYPE         :
  FIRST_NAME          : Tim
  LAST_NAME           : Horton
  JOB_TITLE           :
  PROVINCE           : Wisconsin
  COUNTRY            : US
  PHONE_COUNTRY         :
  PHONE_AREA          :

From:http://tw.wingwit.com/Article/program/Oracle/201311/16718.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.