Space Management Scripts

Check index fragmentation status for a schema

prompt — Drop and create temporary table to hold stats… drop table my_index_stats / create table my_index_stats ( index_name varchar2(30), height number(8), del_lf_rows number(8), distinct_keys number(8), rows_per_key number(10,2), blks_gets_per_access number(10,2) ) / prompt — Save script which we will later use to populate the above table… insert into my_index_stats select NAME, HEIGHT, DEL_LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,…

Check whether a tablespace is fragmented and show fragmentation type

TTI “Tablespace Fragmentation Details” SELECT dfsc.tablespace_name tablespace_name, DECODE ( dfsc.percent_extents_coalesced, 100, (DECODE ( GREATEST ((SELECT COUNT (1) FROM dba_free_space dfs WHERE dfs.tablespace_name = dfsc.tablespace_name), 1), 1, ‘No Frag’, ‘Bubble Frag’ ) ), ‘Possible Honey Comb Frag’ ) fragmentation_status FROM dba_free_space_coalesced dfsc ORDER BY dfsc.tablespace_name; TTI off

List tables with high water mark not equal to used blocks

set verify off column owner format a10 column alcblks heading ‘Allocated|Blocks’ just c column usdblks heading ‘Used|Blocks’ just c column hgwtr heading ‘High|Water’ just c break on owner skip page select a.owner, a.table_name, b.blocks alcblks, a.blocks usdblks, (b.blocks-a.empty_blocks-1) hgwtr from dba_tables a, dba_segments b where a.table_name=b.segment_name and a.owner=b.owner and a.owner not in(‘SYS’,’SYSTEM’) and a.blocks (b.blocks-a.empty_blocks-1)…

List segments with more than 200 Meg of free DB Blocks

set serveroutput on size 40000 declare total_blocks number; total_bytes number; unused_blocks number; unused_bytes number; last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_pct number; begin for c1 in (select owner, segment_type, segment_name from sys.dba_segments where owner not in (‘SYS’, ‘SYSTEM’, ‘PUBLIC’) and segment_type not like ‘%LOB%’) loop — dbms_output.put_line(‘Check space for ‘||c1.segment_type||’: ‘|| — c1.owner||’.’||c1.segment_name||’…’); dbms_space.unused_space(c1.owner, c1.segment_name,…

Show database growth in Meg per month for the last year

set pagesize 50000 tti “Database growth per month for last year” select to_char(creation_time, ‘RRRR Month’) “Month”, sum(bytes)/1024/1024 “Growth in Meg” from sys.v_$datafile where creation_time > SYSDATE-365 group by to_char(creation_time, ‘RRRR Month’) / tti off

Oracle segment sizing recommendations

prompt Database block size: select to_number(value) “Block size in bytes” from sys.v_$parameter where name = ‘db_block_size’ / prompt Max number of possible extents (if not set to UNLIMITED) prompt is db_block_size/16-7 select to_number(value)/16-7 “MaxExtents” from sys.v_$parameter where name = ‘db_block_size’ / prompt The recommended min extent size is a multiple of prompt db_block_size * db_file_multiblock_read_count….

List objects in the SYSTEM tablespace that doesn’t belong to SYS or SYSTEM

select * from sys.dba_segments where owner not in (‘PUBLIC’, ‘SYS’, ‘SYSTEM’) and tablespace_name = ‘SYSTEM’ /