行文过长，建议跳转到原文观看。

出处：墨天轮《GaussDB T 的创建脚本之：initplsql.sql 创建统计信息和变更信息过程和任务》

GaussDB以及其他数据库专栏集锦（建议收藏学习）：https://www.modb.pro/db

在 GaussDB T 中，完成数据库创建的最后一个脚本是 initwsr.sql ，这是高斯数据库的 『AWR』。

DROP TABLE IF EXISTS WSR_EXCEPTION_LOG
/

CREATE TABLE WSR_EXCEPTION_LOG
(
CTIME DATE NOT NULL,
PROCEDURENAME VARCHAR(100) NOT NULL,
ERRORINFO VARCHAR(8000) NOT NULL
)
TABLESPACE SYSTEM
/

CREATE INDEX IX_EXCEPTION_LOG ON WSR_EXCEPTION_LOG(CTIME)
TABLESPACE SYSTEM
/

CREATE OR REPLACE PROCEDURE WSR$INSERT_ERRORLOG
(
STR_IN_PNAME IN VARCHAR,
STR_IN_ERRORINFO IN VARCHAR
)
AS
BEGIN
INSERT INTO WSR_EXCEPTION_LOG
( CTIME,
PROCEDURENAME,
ERRORINFO
)
VALUES( SYSDATE,
STR_IN_PNAME,
STR_IN_ERRORINFO
);
COMMIT;
END;
/

DECLARE
I_L_COUNT INT;
I_L_NUM INT := 0;
BEGIN
FOR ITEM IN (SELECT * FROM MY_JOBS WHERE WHAT IN (‘WSR$CREATE_SNAPSHOT();', 'WSR$CREATESNAPSHOT();′,′WSRDROP_SNAPSHOT_TIME();’, ‘WSR$CREATE_SESSION_SNAPSHOT();’)) LOOP
DBMS_JOB.BROKEN(ITEM.JOB, TRUE);
END LOOP;
COMMIT;

LOOP
    SELECT COUNT(*)
      INTO I_L_COUNT
      FROM MY_JOBS A, ADM_JOBS_RUNNING B
     WHERE WHAT IN ('WSR$CREATE_SNAPSHOT();', 'WSR$DROP_SNAPSHOT_TIME();', 'WSR$CREATE_SESSION_SNAPSHOT();')
       AND A.JOB = B.JOB;
    
    IF (I_L_COUNT > 0) THEN
        SLEEP(1);
        I_L_NUM := I_L_NUM + 1;
        
        IF (I_L_NUM > 15) THEN
            RAISE_APPLICATION_ERROR(-20000, 'CAN''T STOP WSR JOB!');
        END IF;
    ELSE
        EXIT;
    END IF;
END LOOP;

END;
/

DROP TABLE IF EXISTS WSR_CONTROL_BK
/

DECLARE
I_L_COUNT BINARY_INTEGER;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM SYS_TABLES
WHERE NAME = ‘WSR_CONTROL’
AND USER# = 0;

IF (I_L_COUNT > 0) THEN
    EXECUTE IMMEDIATE 'CREATE TABLE WSR_CONTROL_BK AS SELECT * FROM WSR_CONTROL';
END IF;

END;
/

DROP TABLE IF EXISTS WRM$_WR_CONTROL
/
DROP TABLE IF EXISTS WSR_CONTROL
/

CREATE TABLE WSR_CONTROL
(
DBID BINARY_INTEGER NOT NULL,
SNAP_INTERVAL INTERVAL DAY(5) TO SECOND(1) NOT NULL,
SNAPINT_NUM BINARY_INTEGER NOT NULL,
RETENTION INTERVAL DAY(5) TO SECOND(1) NOT NULL,
RETENTION_NUM BINARY_INTEGER NOT NULL,
MOST_RECENT_SNAP_ID BINARY_INTEGER,
MOST_RECENT_SNAP_TIME TIMESTAMP(3),
STATUS VARCHAR(3) NOT NULL,
MOST_RECENT_PURGE_TIME TIMESTAMP(3),
TOPNSQL BINARY_INTEGER NOT NULL,
LOG_DAYS BINARY_INTEGER DEFAULT 30 NOT NULL,
SESSION_STATUS VARCHAR(3) NOT NULL,
SESSION_INTERVAL BINARY_INTEGER NOT NULL
)
TABLESPACE SYSTEM
/

INSERT INTO WSR_CONTROL(DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION, RETENTION_NUM, STATUS, TOPNSQL, LOG_DAYS, SESSION_STATUS, SESSION_INTERVAL)
SELECT DBID, NUMTODSINTERVAL(30, ‘MINUTE’), 1800, NUMTODSINTERVAL(2, ‘DAY’), 2*86400, ‘Y’, 200, 30, ‘Y’, 30
FROM DV_DATABASE
/

CREATE OR REPLACE PROCEDURE WSR$CHECK_COLUMN
(
STR_IN_COLUMNNAME IN VARCHAR
)
AS
I_L_COUNT INTEGER;
BEGIN
SELECT COUNT(*)
INTO I_L_COUNT
FROM MY_TAB_COLUMNS
WHERE TABLE_NAME = ‘WSR_CONTROL_BK’
AND COLUMN_NAME = UPPER(STR_IN_COLUMNNAME);

IF (I_L_COUNT > 0) THEN
    EXECUTE IMMEDIATE 'UPDATE WSR_CONTROL SET ' || STR_IN_COLUMNNAME || ' = NVL((SELECT ' || STR_IN_COLUMNNAME || ' FROM WSR_CONTROL_BK), ' || STR_IN_COLUMNNAM