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

一些常用的計劃腳本(2)

2013-11-13 15:59:52  來源: Oracle 

  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          :
  PHONE_NUMBER         :
  FAX_COUNTRY          :
  FAX_AREA           :
  FAX_NUMBER          :
  MOBILE_NO           :
  ZIP              :
  ADDRESS            : Robbins Rd
  CITY             : Columbus
  
  #######################################################
  show_space 用戶模式下查看對象空間使用情況
  create or replace
  procedure show_space
  ( p_segname in varchar
  p_owner  in varchar default user
  p_type  in varchar default TABLE
  p_partition in varchar default NULL )
  authid current_user
  as
  l_free_blks         number;
  
  l_total_blocks       number;
  l_total_bytes        number;
  l_unused_blocks       number;
  l_unused_bytes       number;
  l_LastUsedExtFileId     number;
  l_LastUsedExtBlockId    number;
  l_LAST_USED_BLOCK      number;
  procedure p( p_label in varchar p_num in number )
  is
  begin
  dbms_outputput_line( rpad(p_label) ||
  p_num );
  end;
  begin
  for x in ( select tablespace_name
  from user_tablespaces
  where tablespace_name = ( select tablespace_name
  from user_segments
  where segment_type = p_type
  and segment_name = p_segname
  and SEGMENT_SPACE_MANAGEMENT <> AUTO )
  )
  loop
  dbms_spacefree_blocks
  ( segment_owner   => p_owner
  segment_name   => p_segname
  segment_type   => p_type
  partition_name  => p_partition
  freelist_group_id =>
  free_blks     => l_free_blks );
  end loop;
  
  dbms_spaceunused_space
  ( segment_owner   => p_owner
  segment_name   => p_segname
  segment_type   => p_type
  partition_name  => p_partition
  total_blocks   => l_total_blocks
  total_bytes    => l_total_bytes
  unused_blocks   => l_unused_blocks
  unused_bytes   => l_unused_bytes
  LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId
  LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId
  LAST_USED_BLOCK => l_LAST_USED_BLOCK );
  
  p( Free Blocks l_free_blks );
  p( Total Blocks l_total_blocks );
  p( Total Bytes l_total_bytes );
  p( Total MBytes trunc(l_total_bytes//) );
  p( Unused Blocks l_unused_blocks );
  p( Unused Bytes l_unused_bytes );
  p( Last Used Ext FileId l_LastUsedExtFileId );
  p( Last Used Ext BlockId l_LastUsedExtBlockId );
  p( Last Used Block l_LAST_USED_BLOCK );
  end;
  
  #######################################################################
  moveallsql move表及rebuild 索引
  set echo off
  
  column order_col noprint
  column order_col noprint
  
  set heading off
  set verify off
  set feedback off
  set echo off
  
  spool move_build_tpcrmsql
  !date
  select decode( segment_type TABLE
  segment_name table_name ) order_col
  decode( segment_type TABLE ) order_col
  alter || segment_type || || segment_name ||
  decode( segment_type TABLE move; rebuild; )
  from user_segments
  (select table_name index_name from user_indexes )
  where segment_type in ( TABLE INDEX )
  and segment_name = index_name (+)
  and tablespace_name in
  (自己選擇的表空間)
  order by
  /
  !date
  spool off
  
  set heading on
  set verify on
  set feedback on
  set echo on
  
  REM UNCOMMENT TO AUTO RUN the generated commands
  REM ELSE edit move_buildsql modify as needed and run it
From:http://tw.wingwit.com/Article/program/Oracle/201311/17641.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.