我写的几个脚本，大家看看
REM
REM display tables or indexes on very top n bocks
REM
set linesize 80
set pagesize 40
SET ECHO OFF
ttitle -
center'Segment Extent Summary'skip 2
col ownr format a8heading 'Owner'justify c
col type format a8heading 'Type' justify c trunc
col name format a20 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#'justify c
col fiid format9990 heading 'File#'justify c
col blid format 999,990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c
select
owner ownr,
segment_namename,
segment_typetype,
extent_id exid,
file_id fiid,
block_idblid,
blocksblks
from
dba_extents
where file_id = 1
and (block_id + &top_blocks) >(
select max(block_id) from dba_extents where file_id=1)
order by block_id
/
REM
REM produce reorganize sql commands of top tables
REM want they are be moved to lower free space
select
'alter '||segment_type||' '|| owner||'.'||segment_name
||decode(segment_type,'TABLE',' move;',
'INDEX', 'rebuild;','unknown')
from
dba_extents
where file_id = 1
and (block_id + &top_blocks) >(
select max(block_id) from dba_extents where file_id=1)
order by block_id
rem
rem display map of datafiles
rem
set serveroutput on size 20000
set linesize 80
declare
cursor c_map(p_file_id number) is
(select file_id,block_id,blocks,'X' used
from dba_extents where file_id=p_file_id
union
select file_id,block_id,blocks,'=' used
from dba_free_space where file_id=p_file_id
) order by 1,2,3;
cursor c_file is
select file_name,file_id,blocks/400bsize from dba_data_files;
j number :=1;
begin
for r_file in c_file
loop
dbms_output.put_line('Map of '||r_file.file_name);
for r_map in c_map(r_file.file_id)
loop
for iin 1..r_map.blocks/r_file.bsize
loop
dbms_output.put(R_MAP.USED);
if j>=80 then
j :=1;dbms_output.new_line;
else
j := j+1;
end if;
end loop;
end loop;
dbms_output.new_line;j:=1;
end loop;
end;
/
rem
rem display detail map of special datafile
rem
set serveroutput on size 999999
set linesize 80
rem ACCEPT p_file_id NUMBER PROMPT 'Pleas input File Number:'
declare
cursor c_map is
(select file_id,block_id,blocks,'X' used
from dba_extents where file_id=&p_file_id
union
select file_id,block_id,blocks,'=' used
from dba_free_space where file_id=&p_file_id
) order by 1,2,3;
j number :=1;
bsize number;
begin
select blocks/(80*80)into bsize from dba_data_files where file_id=&p_file_id;
--that is display in 80 lines with linesize of 80
for r_map in c_map
loop
for iin 1..r_map.blocks/bsize
loop
dbms_output.put(R_MAP.USED);
if j>=80 then
j :=1;dbms_output.new_line;
else
j := j+1;
end if;
end loop;
end loop;
end;
/
|
