通過SPA方式在Lugz0庫抓取SQL指令碼

---------------------------------------------------
--Step1: 建立名稱為STS_NAME的SQL_SET.
---------------------------------------------------
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'STS_NAME',
          DESCRIPTION => 'COMPLETE APPLICATION WORKLOAD',
          SQLSET_OWNER =>'DBMGR');
END;
/

---------------------------------------------------
--Step2: 初始載入當前資料庫中的SQL.
---------------------------------------------------

DECLARE
      STSCUR DBMS_SQLTUNE.SQLSET_CURSOR;
    BEGIN
      OPEN STSCUR FOR
        SELECT VALUE(P)
          FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('PARSING_SCHEMA_NAME <> ''SYS''')) P;
      -- POPULATE THE SQLSET
      DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=>'STS_NAME',
              POPULATE_CURSOR=>STSCUR,
              SQLSET_OWNER => 'DBMGR');
    END;
/

---------------------------------------------------
--Step3: 增量抓取資料庫中的SQL, 會連續抓取7天,每小時抓取一次
---------------------------------------------------
BEGIN
 DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(SQLSET_NAME=>'STS_NAME',
  TIME_LIMIT=>604800,
  REPEAT_INTERVAL=>3600,
  CAPTURE_OPTION=>'MERGE',
  CAPTURE_MODE =>DBMS_SQLTUNE.MODE_ACCUMULATE_STATS,
  BASIC_FILTER=> 'PARSING_SCHEMA_NAME<>''SYS''',
  SQLSET_OWNER => 'DBMGR');
END;
/

------------------------------------------------------
以下步驟要在增量抓取結束後執行,本次不要執行

---------------------------------------------------
--Step4: 增量抓取資料庫中的SQL, 會連續抓取7天,每小時抓取一次
---------------------------------------------------

--EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('STAGE_SQLSET','DBMGR');

--EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ('STS_NAME','DBMGR','STAGE_SQLSET','DBMGR');