|
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_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: |
