DBA CHECK LIST

12) DBA CHECK LIST

--------------------------- A.    Daily Procedures  -----------------------------
-------------------------   1.    Free.sql
--   To verify free space in tablespaces --   Minimum amount of free space --   document your thresholds: --   = 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 ;

----------------------     2.    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;

--SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED,C.BYTES FREE,round((B.BYTES*100)/A.BYTES) "% USED",round((C.BYTES*100)/A.BYTES) "% FREE"
--FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
--WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
--------------------------------------- 3.    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 ;
-------------------------------------   4.    nr_extents.sql
---- nr_extents.sql -- To find out any object reaching   -- 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

--------------------------------5.    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 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.    Nightly Procedures

--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.    Weekly Procedures
-- 1.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 -- 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 ---- 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 all_tab_columns@&my_db_link  -- second environment
WHERE owner = '&OWNER2'
order by table_name, column_name

--8.    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 user_objects@&my_db_link

----VI.    References
----
----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