getcode.sql --获得某个存储过程、包、函数代码脚本
set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100
############################################################
getallcode.sql --获得所以存储过程、包、函数代码脚本
set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL.sql
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql
############################################################
getaview.sql --获得某个视图脚本
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
set long 50000
column column_name format a1000
column text format a1000
spool &1..sql
prompt create or replace view &1 (
select decode(column_id,1,'',',') || column_name column_name
from user_tab_columns
where table_name = upper('&1')
order by column_id
/
prompt ) as
select text
from user_views
where view_name = upper('&1')
/
prompt /
spool off
set heading on
set feedback on
set verify on
set termout on
############################################################
getallview.sql --获得所有视图脚本
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
spool tmp.sql
select '@getaview ' || view_name
from user_views
/
spool off
set termout on
set heading on
set feedback on
set verify on
@tmp
##########################################################
gettrig.sql --获得触发器脚本
set heading off
set feedback off
set linesize 1000
set trimspool on
set verify off
set termout off
set embedded on
spool &1..sql
select
'create or replace trigger "'||
trigger_name||'"'||chr(10)||
decode(substr(trigger_type,1,1),
'A','AFTER','B',BEFORE','I','INSTEAD OF')||
CHR(10)||
triggering_event||chr(10)||
'ON"'||table_owner||'"."'||
table_name||'"'||chr(10)||
decode(instr(trigger_type,'EACH ROW'),0,null,
'FOR EACH ROW')||chr(10),
trigger_body
from user_triggers
where trigger_name = upper('&1')
/
prompt /
spool off
set verify on
set feedback on
set termout on
set heading on
#################################################################
analyze.sql --分析某用户下的表及索引（大表将以评估的方式分析）
set serveroutput on size 100000
declare
v_per number(3);
v_start number := dbms_utility.get_time;
v_end number;
begin
for rec in (select segment_name,segment_type,ceil(sum(bytes)/1024/1024) segment_size
from user_segments group by segment_name,segment_type)
loop
if rec.segment_type = 'INDEX' then
dbms_stats.gather_index_stats(ownname=>'???', --自己改一下
INDNAME=>rec.segment_name
);
-- dbms_output.put_line(rec.segment_name||' '||rec.segment_size||'m '||ceil((dbms_utility.get_time - v_start)/100)||'s');
v_start := dbms_utility.get_time;
elsif rec.segment_type = 'TABLE' then
case when rec.segment_size < 32 then
v_per := 100;
when rec.segment_size < 320 then
v_per := 10;
else
v_per := 1;
end case;
dbms_stats.gather_table_stats(OWNNAME=>'???',
TABNAME=>rec.segment_name,
ESTIMATE_PERCENT=>v_per,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
-- dbms_output.put_line(rec.segment_name||' '||rec.segment_size||'m '||ceil((dbms_utility.get_time - v_start)/100)||'s');
v_start := dbms_utility.get_time;
end if;
end loop;
end;
/
###############################################################
print_table --纵向显示一行
create or replace
procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' ); --注意如果输出的行比较多的话,要加大dbms_output.enable(值)
end loop;
exception
when others then
dbms_sql.close_cursor( l_theCursor );
RAISE;
end;
/
grant execute on print_table to public;
--该脚本是实现横向改成纵向显示
例如:
一行记录显示如下:
ADMIN_MEMBER_ID : dealexpress
VIEW_NAME : Deal Express
BUSINESS_TYPE : 5
FIRST_NAME : Tim
LAST_NAME : Horton
JOB_TITLE :
PROVINCE : Wisconsin
COUNTRY : US
PHONE_COUNTRY : 1920
PHONE_AREA : 623
