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;
--创建DEPT表
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;
/