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_eventchr(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_statusinteger;
l_descTbl dbms_sql.desc_tab;
l_colCntnumber;
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
