常用SQL語句5

  DBA 日常维护程序 :
--FREE.SQL
--TO VERIFY FREE SPACE IN TABLESPACES?
--MINIMUM AMOUNT OF FREE SPACE?
--DOCUMENT YOUR THRESHOLDS:?
--=M??

SQL>SELECTTABLESPACE_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

SQL> COL TABLESPACE_NAME FORMAT A20
SQL>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   
-- 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 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 128 K  
NEXT 128 K  
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 ALL_TAB_COLUMNS@&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 USER_OBJECTS@&MY_DB_LINK

show_space 函数包用法 :
CREATE OR REPLACE PROCEDURE SHOW_SPACE(P_SEGNAME   IN VARCHAR2,
                                       P_OWNER     IN VARCHAR2 DEFAULT USER,
                                       P_TYPE      IN VARCHAR2 DEFAULT 'TABLE' ,
                                       P_PARTITION IN VARCHAR2 DEFAULT NULL) AS
 L_FREE_BLKS          NUMBER;
 L_TOTAL_BLOCKS       NUMBER;
 L_TOTAL_BYTES        NUMBER;
 L_UNUSED_BLOCKS      NUMBER;
 L_UNUSED_BYTES       NUMBER;
 L_LASTUSEDEXTFILEID NUMBER;
 L_LASTUSEDEXTBLOCKID NUMBER;
 L_LAST_USED_BLOCK    NUMBER;
 PROCEDURE P(P_LABEL IN VARCHAR2, P_NUM IN NUMBER) IS
 BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40 , '.' ) || P_NUM);
 END;
BEGIN
 DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER     => P_OWNER,
                         SEGMENT_NAME      => P_SEGNAME,
                         SEGMENT_TYPE      => P_TYPE,
                         PARTITION_NAME    => P_PARTITION,
                         FREELIST_GROUP_ID => 0 ,
                         FREE_BLKS         => L_FREE_BLKS);
 DBMS_SPACE.UNUSED_SPACE(SEGMENT_OWNER             => P_OWNER,
                          SEGMENT_NAME              => P_SEGNAME,
                          SEGMENT_TYPE              => P_TYPE,
                          PARTITION_NAME            => P_PARTITION,
                          TOTAL_BLOCKS              => L_TOTAL_BLOCKS,
                          TOTAL_BYTES               => L_TOTAL_BYTES,
                          UNUSED_BLOCKS             => L_UNUSED_BLOCKS,
                          UNUSED_BYTES              => L_UNUSED_BYTES,
                          LAST_USED_EXTENT_FILE_ID => L_LASTUSEDEXTFILEID,
                          LAST_USED_EXTENT_BLOCK_ID => L_LASTUSEDEXTBLOCKID,
                          LAST_USED_BLOCK           => L_LAST_USED_BLOCK);
 P( 'FREE BLOCKS' , L_FREE_BLKS);
 P( 'TOTAL BLOCKS' , L_TOTAL_BLOCKS);
 P( 'TOTAL BYTES' , L_TOTAL_BYTES);
 P( 'UNUSED BLOCKS' , L_UNUSED_BLOCKS);
 P( 'UNUSED BYTES' , L_UNUSED_BYTES);
 P( 'LAST USED EXT FILEID' , L_LASTUSEDEXTFILEID);
 P( 'LAST USED EXT BLOCKID' , L_LASTUSEDEXTBLOCKID);
 P( 'LAST USED BLOCK' , L_LAST_USED_BLOCK);
END;
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE SHOW_SPACE( 'DSF' );
FREE BLOCKS............................. 0
TOTAL BLOCKS............................ 128
TOTAL BYTES............................. 1048576
UNUSED BLOCKS........................... 127
UNUSED BYTES............................ 1040384
LAST USED EXT FILEID.................... 21
LAST USED EXT BLOCKID................... 9
LAST USED BLOCK......................... 1

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.