【IT168 服务器学院】 一些常用的计划脚本
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;
/
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
PHONE_NUMBER : 9528
FAX_COUNTRY : 1920
FAX_AREA : 623
FAX_NUMBER : 9528
MOBILE_NO :
ZIP : 53925
ADDRESS : 215 Robbins Rd
CITY : Columbus
##########################
show_space --用户模式下查看对象空间使用情况
create or replace
procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default ''TABLE'',
p_partition in varchar2 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 varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,''.'') ||
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_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks );
end loop;
dbms_space.unused_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/1024/1024) );
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;
##################################
moveall.sql --move表及rebuild 索引
set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set verify off
set feedback off
set echo off
spool move_build_tpcrm.sql
!date
select decode( segment_type, ''TABLE'',
segment_name, table_name ) order_col1,
decode( segment_type, ''TABLE'', 1, 2 ) order_col2,
''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 1, 2
/
!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_build.sql, modify as needed and run it
