常用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)  

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.  

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  

SYS.DBMS_UTILITY.ANALYZE_SCHEMA ( '&OWNER','COMPUTE');  

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.  

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  

FROM ALL_TABLES  

WHERE OWNER = '&OWNER'  

MINUS  

FROM ALL_CONSTRAINTS  

WHERE OWNER = '&&OWNER'  

AND CONSTRAINT_TYPE = 'P'  

4. DISPK.SQL  

-- DISPK.SQL  

-- TO FIND OUT WHICH PRIMARY KEYS ARE DISABLED  

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, STATUS  

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.  

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  

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD '  

, 'TABLESPACE INDEXES STORAGE '   

|| ' ( INITIAL 256 K NEXT 256 K PCTINCREASE 0 ) ; '  

WHERE ( TABLESPACE_NAME != 'INDEXES'  

OR NEXT_EXTENT != ( 256 * 1024 )  

)  

AND OWNER = '&OWNER'  

/  

7. DATATYPE.SQL  

-- DATATYPE.SQL  

-- TO CHECK DATATYPE CONSISTENCY BETWEEN TWO ENVIRONMENTS  

SELECT  

TABLE_NAME,  

COLUMN_NAME,  

DATA_TYPE,  

DATA_LENGTH,  

DATA_PRECISION,  

DATA_SCALE,  

NULLABLE  

FROM ALL_TAB_COLUMNS -- FIRST ENVIRONMENT  

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  

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.