Oracle DBA 日常維護手冊

ygzhou518發表於2012-06-15
Oracle
A. DAILY CHECKLIST
1. FREE.SQL
/* FORMATTED ON 2012/06/15 11:48 (FORMATTER PLUS V4.8.8) */
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
/* FORMATTED ON 2012/06/15 11:48 (FORMATTER PLUS V4.8.8) */
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
3. DAILY_01.SQL
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
BEGIN
   DBMS_UTILITY.ANALYZE_SCHEMA ('&OWNER', 'ESTIMATE', NULL, 5);
END;
/
4. NR_EXTENTS.SQL 
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
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
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
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 、WEEKLY CHECKLIST
1. NEXTEXT.SQL
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
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 DT.TABLESPACE_NAME, DS.SEGMENT_TYPE, DS.SEGMENT_NAME〕

2. EXISTEXT.SQL
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
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
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
SELECT TABLE_NAME
  FROM ALL_TABLES
 WHERE WNER = '&OWNER'
MINUS
SELECT TABLE_NAME
  FROM ALL_CONSTRAINTS
 WHERE WNER = '&&OWNER' AND CONSTRAINT_TYPE = 'P'

4. DISPK.SQL
/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, STATUS
  FROM ALL_CONSTRAINTS
 WHERE WNER = '&OWNER' AND STATUS = 'DISABLED' AND CONSTRAINT_TYPE = 'P'

5. NONUPK.SQL

/* FORMATTED ON 2012/06/15 11:49 (FORMATTER PLUS V4.8.8) */
SELECT INDEX_NAME, TABLE_NAME, UNIQUENESS
  FROM ALL_INDEXES
 WHERE INDEX_NAME LIKE '&PKNAME%'
   AND WNER = '&OWNER'
   AND UNIQUENESS = 'NONUNIQUE';
/* FORMATTED ON 2012/06/15 11:50 (FORMATTER PLUS V4.8.8) */
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

SELECT 'alter index ' || INDEX_NAME || ' rebuild ', 'tablespace INDEXES storage ( initial 256 K next 256 K ) ; '
FROM ALL_INDEXESWHERE ( TABLESPACE_NAME != 'INDEXES' OR NEXT_EXTENT != ( 256 * 1024 ) )AND WNER = 'OWNER' 
/

7. DATATYPE.SQL
/* FORMATTED ON 2012/06/15 11:51 (FORMATTER PLUS V4.8.8) */
SELECT   TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
         DATA_SCALE, NULLABLE
    FROM ALL_TAB_COLUMNS           -- FIRST ENVIRONMENT WHERE WNER = '&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 WNER = '&OWNER2'
ORDER BY TABLE_NAME, COLUMN_NAME

8. OBJ_COORD.SQL
/* FORMATTED ON 2012/06/15 11:51 (FORMATTER PLUS V4.8.8) */
SELECT OBJECT_NAME, OBJECT_TYPE
  FROM USER_OBJECTS
MINUS
SELECT OBJECT_NAME, OBJECT_TYPE
  FROM USER_OBJECTS@&MY_DB_LINK

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-732925/，如需轉載，請註明出處，否則將追究法律責任。

相關文章