|
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_statusinteger; l_descTbl dbms_sqldesc_tab; l_colCntnumber; begin dbms_sqlparse(l_theCursorp_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_typein varchar default TABLE p_partition in varchar default NULL ) authid current_user as l_free_blks number; l_total_blocksnumber; l_total_bytes number; l_unused_blocks number; l_unused_bytesnumber; l_LastUsedExtFileId number; l_LastUsedExtBlockIdnumber; 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; ####################################################################### moveallsqlmove表及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 |
