Oracle 编程艺术-环境配置

--创建EMP表
CREATE TABLE EMP
(
? EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,
? SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2)
);
INSERT INTO EMP VALUES (7369, 'SMITH','CLERK',7902,TO_DATE('17-12-1980', 'DD-MM-YYYY'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN', 'SALESMAN', 7698,TO_DATE('20-02-1981', 'DD-MM-YYYY'),1600,300, 30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-02-1981', 'DD-MM-YYYY'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('02-04-1981', 'DD-MM-YYYY'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-09-1981', 'DD-MM-YYYY'),1250,1400, 30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('01-05-1981', 'DD-MM-YYYY'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('09-06-1981', 'DD-MM-YYYY'),2450,NULL, 10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('09-12-1982', 'DD-MM-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('08-09-1981', 'DD-MM-YYYY'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('12-01-1983', 'DD-MM-YYYY'),1100,NULL, 20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('03-12-1981', 'DD-MM-YYYY'),950,NULL, 30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('03-12-1981', 'DD-MM-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-01-1982', 'DD-MM-YYYY'),1300,NULL,10);
COMMIT;
--创—EPT表
CREATE TABLE DEPT
(
DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
--创建主键或约束

ALTER TABLE EMP ADD CONSTRAINT EMP_PK PRIMARY KEY(EMPNO);
ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY(DEPTNO);
ALTER TABLE EMP ADD CONSTRAINT EMP_FK_DEPT FOREIGN KEY(DEPTNO) REFERENCES DEPT;
ALTER TABLE EMP ADD CONSTRAINT EMP_FK_EMP FOREIGN KEY(MGR) REFERENCES EMP;
---------------------------------------------------------
--login.sql
--创建SQLPATH环境变量，指定login.sql所在文件夹
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1,decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on；
---------------------------------------------------------
--RUNSTATS 包
CREATE OR REPLACE VIEW STATS
AS SELECT 'STAT...' || A.NAME NAME, B.VALUE
FROM V$STATNAME A, V$MYSTAT B
WHERE A.STATISTIC# = B.STATISTIC#
UNION ALL
SELECT 'LATCH.' || NAME, GETS
FROM V$LATCH;

--临时表用于保留统计信息
CREATE GLOBAL TEMPORARY TABLE RUN_STATS
( RUNID VARCHAR2(15),
NAME VARCHAR2(80),
VALUE INT )
ON COMMIT PRESERVE ROWS;

--创建runstats包头
CREATE OR REPLACE PACKAGE RUNSTATS_PKG
?AS
?PROCEDURE RS_START;
?PROCEDURE RS_MIDDLE;
?PROCEDURE RS_STOP( P_DIFFERENCE_THRESHOLD IN NUMBER DEFAULT 0 );
?END;
?
-- runstats测试开始时调用RS_STAT（runstats开始）。
-- 正如你想象的，RS_MIDDLE会在测试之间调用。
-- 完成时调用RS_STOP，打印报告。
--参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息，
--然后打印一个报告，说明每次测试（每个方法）使用了多少资源，以及不同测试（不同方法）的结果之差

--创建包主体
CREATE OR REPLACE PACKAGE BODY RUNSTATS_PKG AS
? G_START NUMBER;
? G_RUN1? NUMBER;
? G_RUN2? NUMBER;
? PROCEDURE RS_START IS
? BEGIN
??? DELETE FROM RUN_STATS;
??? INSERT INTO RUN_STATS
????? SELECT 'BEFORE', STATS.* FROM STATS;
??? G_START := DBMS_UTILITY.GET_TIME;
? END;
? PROCEDURE RS_MIDDLE IS
? BEGIN
??? G_RUN1 := (DBMS_UTILITY.GET_TIME - G_START);
??? INSERT INTO RUN_STATS
????? SELECT 'AFTER 1', STATS.* FROM STATS;
??? G_START := DBMS_UTILITY.GET_TIME;
? END;
? PROCEDURE RS_STOP(P_DIFFERENCE_THRESHOLD IN NUMBER DEFAULT 0) IS
? BEGIN
??? INSERT INTO RUN_STATS
????? SELECT 'AFTER 2', STATS.* FROM STATS;
??? G_RUN2 := (DBMS_UTILITY.GET_TIME - G_START);
??? DBMS_OUTPUT.PUT_LINE('RUN1 RAN IN ' || G_RUN1 || ' HSECS');
??? DBMS_OUTPUT.PUT_LINE('RUN2 RAN IN ' || G_RUN2 || ' HSECS');
??? DBMS_OUTPUT.PUT_LINE('RUN 1 RAN IN ' ||
???????????????????????? ROUND(G_RUN1 / G_RUN2 * 100, 2) ||
???????????????????????? '% OF THE TIME');
??? DBMS_OUTPUT.PUT_LINE(CHR(9));
??? DBMS_OUTPUT.PUT_LINE(RPAD('NAME', 30) || LPAD('RUN1', 10) ||
???????????????????????? LPAD('RUN2', 10) || LPAD('DIFF', 10));
??? FOR X IN (SELECT RPAD(A.NAME, 30) ||
???????????????????? TO_CHAR(B.VALUE - A.VALUE, '999,999,999') ||
???????????????????? TO_CHAR(C.VALUE - B.VALUE, '999,999,999') ||
???????????????????? TO_CHAR(((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)),
???????????????????????????? '999,999,999') DATA
??????????????? FROM RUN_STATS A, RUN_STATS B, RUN_STATS C
?????????????? WHERE A.NAME = B.NAME
???????????????? AND B.NAME = C.NAME
???????????????? AND A.RUNID = 'BEFORE'
???????????????? AND B.RUNID = 'AFTER 1'
???????????????? AND C.RUNID = 'AFTER 2'
???????????????? AND (C.VALUE - A.VALUE) > 0
???????????????? AND ABS((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)) >
???????????????????? P_DIFFERENCE_THRESHOLD
?????????????? ORDER BY ABS((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE))) LOOP
????? DBMS_OUTPUT.PUT_LINE(X.DATA);
??? END LOOP;
??? DBMS_OUTPUT.PUT_LINE(CHR(9));
??? DBMS_OUTPUT.PUT_LINE('RUN1 LATCHES TOTAL VERSUS RUNS -- DIFFERENCE AND PCT');
??? DBMS_OUTPUT.PUT_LINE(CHR(9));
??? DBMS_OUTPUT.PUT_LINE(LPAD('RUN1', 10) || LPAD('RUN2', 10) ||
???????????????????????? LPAD('DIFF', 10) || LPAD('PCT', 8));
??? FOR X IN (SELECT TO_CHAR(RUN1, '999,999,999') ||
???????????????????? TO_CHAR(RUN2, '999,999,999') ||
???????????????????? TO_CHAR(DIFF, '999,999,999') ||
???????????????????? TO_CHAR(ROUND(RUN1 / RUN2 * 100, 2), '999.99') || '%' DATA
??????????????? FROM (SELECT SUM(B.VALUE - A.VALUE) RUN1,
???????????????????????????? SUM(C.VALUE - B.VALUE) RUN2,
???????????????????????????? SUM((C.VALUE - B.VALUE) - (B.VALUE - A.VALUE)) DIFF
??????????????????????? FROM RUN_STATS A, RUN_STATS B, RUN_STATS C
?????????????????????? WHERE A.NAME = B.NAME
???????????????????????? AND B.NAME = C.NAME
???????????????????????? AND A.RUNID = 'BEFORE'
???????????????????????? AND B.RUNID = 'AFTER 1'
???????????????????????? AND C.RUNID = 'AFTER 2'
???????????????????????? AND A.NAME LIKE 'LATCH%')) LOOP
????? DBMS_OUTPUT.PUT_LINE(X.DATA);
??? END LOOP;
? END;
END;
-----------------------------------------------------
--mystat.sql
SET ECHO OFF
SET VERIFY OFF
COLUMN VALUE NEW_VAL V
DEFINE S="&1"
SET AUTOTRACE OFF
SELECT A.NAME, B.VALUE
FROM V$STATNAME A, V$MYSTAT B
WHERE A.STATISTIC# = B.STATISTIC#
AND LOWER(A.NAME) LIKE '%' || LOWER('&S')||'%'
/
SET ECHO ON
--mystat2.sql
SET ECHO OFF
SET VERIFY OFF
SELECT A.NAME, B.VALUE V, TO_CHAR(B.VALUE-&V,'999,999,999,999') DIFF
FROM V$STATNAME A, V$MYSTAT B
WHERE A.STATISTIC# = B.STATISTIC#
AND LOWER(A.NAME) LIKE '%' || LOWER('&S')||'%'
/
SET ECHO ON
-------------------------------------------------------

---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;
? L_SEGMENT_SPACE_MGMT VARCHAR2(255);
? L_UNFORMATTED_BLOCKS NUMBER;
? L_UNFORMATTED_BYTES? NUMBER;
? L_FS1_BLOCKS???????? NUMBER;
? L_FS1_BYTES????????? NUMBER;
? L_FS2_BLOCKS???????? NUMBER;
? L_FS2_BYTES????????? NUMBER;
? L_FS3_BLOCKS???????? NUMBER;
? L_FS3_BYTES????????? NUMBER;
? L_FS4_BLOCKS???????? NUMBER;
? L_FS4_BYTES????????? NUMBER;
? L_FULL_BLOCKS??????? NUMBER;
? L_FULL_BYTES???????? NUMBER;
? PROCEDURE P(P_LABEL IN VARCHAR2, P_NUM IN NUMBER) IS
? BEGIN
??? DBMS_OUTPUT.PUT_LINE(RPAD(P_LABEL, 40, '.') ||
???????????????????????? TO_CHAR(P_NUM, '999,999,999,999'));
? END;
BEGIN
? -- THIS QUERY IS EXECUTED DYNAMICALLY IN ORDER TO ALLOW THIS PROCEDURE
? -- TO BE CREATED BY A USER WHO HAS ACCESS TO DBA_SEGMENTS/TABLESPACES
? -- VIA A ROLE AS IS CUSTOMARY.
? -- NOTE: AT RUNTIME, THE INVOKER MUST HAVE ACCESS TO THESE TWO
? -- VIEWS!
? -- THIS QUERY DETERMINES IF THE OBJECT IS AN ASSM OBJECT OR NOT
? BEGIN
??? EXECUTE IMMEDIATE 'SELECT TS.SEGMENT_SPACE_MANAGEMENT
FROM DBA_SEGMENTS SEG, DBA_TABLESPACES TS
WHERE SEG.SEGMENT_NAME = :P_SEGNAME
AND (:P_PARTITION IS NULL OR
SEG.PARTITION_NAME = :P_PARTITION)
AND SEG.OWNER = :P_OWNER
AND SEG.TABLESPACE_NAME = TS.TABLESPACE_NAME'
????? INTO L_SEGMENT_SPACSELECT TS.SEGMENT_SPACE_MANAGEMENT FROM DBA_SEGMENTS SEG, DBA_TABLESPACES TS WHERE SEG.SEGMENT_NAME = :P_SEGNAME AND (:P_PARTITION IS NULL OR SEG.PARTITION_NAME = :P_PARTITION) AND SEG.OWNER = :P_OWNER AND SEG.TABLESPACE_NAME = TS.TABLESPACE_NAMEE_MGMT
????? USING P_SEGNAME, P_PARTITION, P_PARTITION, P_OWNER;
? EXCEPTION
??? WHEN TOO_MANY_ROWS THEN
????? DBMS_OUTPUT.PUT_LINE('THIS MUST BE A PARTITIONED TABLE, USE P_PARTITION => ');
????? RETURN;
? END;
? -- IF THE OBJECT IS IN AN ASSM TABLESPACE, WE MUST USE THIS API
? -- CALL TO GET SPACE INFORMATION, OTHERWISE WE USE THE FREE_BLOCKS
? -- API FOR THE USER-MANAGED SEGMENTS
? IF L_SEGMENT_SPACE_MGMT = 'AUTO' THEN
??? DBMS_SPACE.SPACE_USAGE(P_OWNER,
?????????????????????????? P_SEGNAME,
?????????????????????????? P_TYPE,
?????????????????????????? L_UNFORMATTED_BLOCKS,
?????????????????????????? L_UNFORMATTED_BYTES,
?????????????????????????? L_FS1_BLOCKS,
?????????????????????????? L_FS1_BYTES,
?????????????????????????? L_FS2_BLOCKS,
?????????????????????????? L_FS2_BYTES,
?????????????????????????? L_FS3_BLOCKS,
?????????????????????????? L_FS3_BYTES,
?????????????????????????? L_FS4_BLOCKS,
?????????????????????????? L_FS4_BYTES,
?????????????????????????? L_FULL_BLOCKS,
?????????????????????????? L_FULL_BYTES,
?????????????????????????? P_PARTITION);
??? P('UNFORMATTED BLOCKS ', L_UNFORMATTED_BLOCKS);
??? P('FS1 BLOCKS (0-25) ', L_FS1_BLOCKS);
??? P('FS2 BLOCKS (25-50) ', L_FS2_BLOCKS);
??? P('FS3 BLOCKS (50-75) ', L_FS3_BLOCKS);
??? P('FS4 BLOCKS (75-100)', L_FS4_BLOCKS);
??? P('FULL BLOCKS ', L_FULL_BLOCKS);
? ELSE
??? DBMS_SPACE.FREE_BLOCKS(SEGMENT_OWNER???? => P_OWNER,
?????????????????????????? SEGMENT_NAME????? => P_SEGNAME,
?????????????????????????? SEGMENT_TYPE????? => P_TYPE,
?????????????????????????? FREELIST_GROUP_ID => 0,
?????????????????????????? FREE_BLKS???????? => L_FREE_BLKS);
??? P('FREE BLOCKS', L_FREE_BLKS);
? END IF;
? -- AND THEN THE UNUSED SPACE API CALL TO GET THE REST OF THE
? -- INFORMATION
? 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('TOTAL BLOCKS', L_TOTAL_BLOCKS);
? P('TOTAL BYTES', L_TOTAL_BYTES);
? P('TOTAL MBYTES', TRUNC(L_TOTAL_BYTES / 1024 / 1024));
? 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;
---------------------------------------------------
--创建big tables

CREATE TABLE BIG_TABLE
AS
SELECT ROWNUM ID, A.*
FROM ALL_OBJECTS A
WHERE 1=0;

ALTER TABLE BIG_TABLE NOLOGGING;

DECLARE
? L_CNT? NUMBER;
? L_ROWS NUMBER := &1;
BEGIN
? INSERT /*+ APPEND */
? INTO BIG_TABLE
??? SELECT ROWNUM, A.* FROM ALL_OBJECTS A;
? L_CNT := SQL%ROWCOUNT;
? COMMIT;
? WHILE (L_CNT < L_ROWS) LOOP
??? INSERT /*+ APPEND */
??? INTO BIG_TABLE
????? SELECT ROWNUM + L_CNT,
???????????? OWNER,
???????????? OBJECT_NAME,
???????????? SUBOBJECT_NAME,
???????????? OBJECT_ID,
???????????? DATA_OBJECT_ID,
???????????? OBJECT_TYPE,
???????????? CREATED,
???????????? LAST_DDL_TIME,
???????????? TIMESTAMP,
???????????? STATUS,
???????????? TEMPORARY,
???????????? GENERATED,
???????????? SECONDARY
??????? FROM BIG_TABLE
?????? WHERE ROWNUM <= L_ROWS - L_CNT;
??? L_CNT := L_CNT + SQL%ROWCOUNT;
??? COMMIT;
? END LOOP;
END;
/
--创建主键约束
ALTER TABLE BIG_TABLE ADD CONSTRAINT BIG_TABLE_PK PRIMARY KEY(ID);
--收集表的统计信息
BEGIN
? DBMS_STATS.GATHER_TABLE_STATS(OWNNAME??? => USER,
??????????????????????????????? TABNAME??? => 'BIG_TABLE',
??????????????????????????????? METHOD_OPT => 'FOR ALL INDEXED COLUMNS',
??????????????????????????????? CASCADE??? => TRUE);
END;
/