-- -- free.sql -- --to verify free space in tablespaces --minimum amount of free space --document your thresholds: --<tablespace_name> = <amount> m -- select tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) / (1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks from dba_free_space group by tablespace_name 1. space.sql -- -- space.sql -- -- to check free, pct_free, and allocated space within a tablespace -- -- 11/24/98 select tablespace_name, largest_free_chunk , nr_free_chunks, sum_alloc_blocks, sum_free_blocks , to_char(100*sum_free_blocks/sum_alloc_blocks, '09.99') || '%' as pct_free from ( select tablespace_name , sum(blocks) as sum_alloc_blocks from dba_data_files group by tablespace_name ) , ( select tablespace_name as fs_ts_name , max(blocks) as largest_free_chunk , count(blocks) as nr_free_chunks , sum(blocks) as sum_free_blocks from dba_free_space group by tablespace_name ) where tablespace_name = fs_ts_name 2. analyze5pct.sql -- -- analyze5pct.sql -- -- to analyze tables and indexes quickly, using a 5% sample size -- (do not use this script if you are performing the overnight
-- collection of volumetric data) -- -- 11/30/98 begin dbms_utility.analyze_schema ( '&owner', 'estimate', null, 5 ) ; end ; / 3. nr_extents.sql -- -- nr_extents.sql -- -- to find out any object reaching <threshold> -- extents, and manually upgrade it to allow unlimited -- max_extents (thus only objects we *expect* to be big -- are allowed to become big) -- -- 11/30/98 select e.owner, e.segment_type , e.segment_name , count(*) as nr_extents , s.max_extents , to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as mb from dba_extents e , dba_segments s where e.segment_name = s.segment_name group by e.owner, e.segment_type , e.segment_name , s.max_extents having count(*) > &threshold or ( ( s.max_extents - count(*) ) < &&threshold ) order by count(*) desc 4. spacebound.sql -- -- spacebound.sql -- -- to identify space-bound objects. if all is well, no rows are returned. -- if any space-bound objects are found, look at value of next extent -- size to figure out what happened. -- then use coalesce (alter tablespace <foo> coalesce;). -- lastly, add another datafile to the tablespace if needed. -- -- 11/30/98 select a.table_name, a.next_extent, a.tablespace_name from all_tables a, ( select tablespace_name, max(bytes) as big_chunk
from dba_free_space group by tablespace_name ) f where f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk b. 每晚处理程序 1. mk_volfact.sql -- -- mk_volfact.sql (only run this once to set it up; do not run it nightly!) -- -- -- table utl_vol_facts create table utl_vol_facts ( table_name varchar2(30), num_rows number, meas_dt date ) tablespace platab storage ( initial 128k next 128k pctincrease 0 minextents 1 maxextents unlimited ) / -- public synonym create public synonym utl_vol_facts for &owner..utl_vol_facts / -- grants for utl_vol_facts grant select on utl_vol_facts to public / 2. analyze_comp.sql -- -- analyze_comp.sql -- begin
sys.dbms_utility.analyze_schema ( '&owner','compute'); end ; / 3. pop_vol.sql -- -- pop_vol.sql -- insert into utl_vol_facts select table_name , nvl ( num_rows, 0) as num_rows , trunc ( last_analyzed ) as meas_dt from all_tables -- or just user_tables where owner in ('&owner') -- or a comma-separated list of owners / commit / c. 每周处理程序 1. nextext.sql -- -- nextext.sql -- -- to find tables that don't match the tablespace default for next extent. -- the implicit rule here is that every table in a given tablespace should -- use the exact same value for next, which should also be the tablespace's -- default value for next. -- -- this tells us what the setting for next is for these objects today. -- -- 11/30/98 select segment_name, segment_type, ds.next_extent as actual_next , dt.tablespace_name, dt.next_extent as default_next from dba_tablespaces dt, dba_segments ds where dt.tablespace_name = ds.tablespace_name and dt.next_extent !=ds.next_extent and ds.owner = upper ( '&owner' ) order by tablespace_name, segment_type, segment_name 2. existext.sql -- -- existext.sql --
-- to check existing extents -- -- this tells us how many of each object's extents differ in size from -- the tablespace's default size. if this report shows a lot of different -- sized extents, your free space is likely to become fragmented. if so, -- this tablespace is a candidate for reorganizing. -- -- 12/15/98 select segment_name, segment_type , count(*) as nr_exts , sum ( decode ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts , dt.tablespace_name, dt.next_extent as dflt_ext_size from dba_tablespaces dt, dba_extents dx where dt.tablespace_name = dx.tablespace_name and dx.owner = '&owner' group by segment_name, segment_type, dt.tablespace_name, dt.next_extent 3. no_pk.sql -- -- no_pk.sql -- -- to find tables without pk constraint -- -- 11/2/98 select table_name from all_tables where owner = '&owner' minus select table_name from all_constraints where owner = '&&owner' and constraint_type = 'p' 4. dispk.sql -- -- dispk.sql -- -- to find out which primary keys are disabled -- -- 11/30/98
select owner, constraint_name, table_name, status from all_constraints where owner = '&owner' and status = 'disabled’ and constraint_type = 'p' 5. nonupk.sql -- -- nonupk.sql -- -- to find tables with nonunique pk indexes. requires that pk names -- follow a naming convention. an alternative query follows that -- does not have this requirement, but runs more slowly. -- -- 11/2/98 select index_name, table_name, uniqueness from all_indexes where index_name like '&pkname%' and owner = '&owner' and uniqueness = 'nonunique' select c.constraint_name, i.tablespace_name, i.uniqueness from all_constraints c , all_indexes i where c.owner = upper ( '&owner' ) and i.uniqueness = 'nonunique' and c.constraint_type = 'p' and i.index_name = c.constraint_name 6. mkrebuild_idx.sql -- -- mkrebuild_idx.sql -- -- rebuild indexes to have correct storage parameters -- -- 11/2/98 select 'alter index ' || index_name || ' rebuild ' , 'tablespace indexes storage ' || ' ( initial 256 k next 256 k pctincrease 0 ) ; ' from all_indexes where ( tablespace_name != 'indexes' or next_extent != ( 256 * 1024 ) ) and owner = '&owner' / 7. datatype.sql
-- -- datatype.sql -- -- to check datatype consistency between two environments -- -- 11/30/98 select table_name, column_name, data_type, data_length, data_precision, data_scale, nullable from all_tab_columns -- first environment where owner = '&owner' minus select table_name, column_name, data_type, data_length, data_precision, data_scale, nullable from [email protected]&my_db_link -- second environment where owner = '&owner2' order by table_name, column_name 8. obj_coord.sql -- -- obj_coord.sql -- -- to find out any difference in objects between two instances -- -- 12/08/98 select object_name, object_type from user_objects minus select object_name, object_type from [email protected]&my_db_link
六. 参考文献 1. loney, kevin oracle8 dba handbook 2. cook, david database management from crisis to confidence [http://www.orapub.com/] 3. cox, thomas b. the database administration maturity model