|
|
| Line 1: |
Line 1: |
| | {{SQWareRepositoryPresSgbd|Oracle|schema|instances|ora}} | | {{SQWareRepositoryPresSgbd|Oracle|schema|instances|ora}} |
| | == Specific database content for Oracle == | | == Specific database content for Oracle == |
| − | {{SQWareRepositoryTables|tsqw_Repository|
| |
| − | <nowiki>Instance repository</nowiki>|
| |
| − | <pre>
| |
| − | /* Instance repository */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_Repository (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | VIRT_HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | USERNAME VARCHAR2 (60) NOT NULL,
| |
| − | PORT NUMBER NOT NULL,
| |
| − | COMMENTS VARCHAR2(500),
| |
| − | CONTACT VARCHAR2(100),
| |
| − | STATUS VARCHAR2(10) default 'ON',
| |
| − | CLIENT VARCHAR2 (60) default 'N.A',
| |
| − | UPD_DATE DATE default sysdate,
| |
| − | ENV CHAR(3)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_Repository for &&UserIndicator..tsqw_Repository;
| |
| − | grant all on &&UserIndicator..tsqw_Repository to &&UserIndicator._RMAJ;
| |
| − | grant all on &&UserIndicator..tsqw_Repository to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_Repository ADD (
| |
| − | CONSTRAINT isqw_Repository_pk PRIMARY KEY (ORACLE_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_RepositoryOther|
| |
| − | <nowiki>Other instances repository (for passive cluster node for example)</nowiki>|
| |
| − | <pre>
| |
| − | /* Other instances repository (for passive cluster node for example) */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_RepositoryOther (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | VIRT_HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | USERNAME VARCHAR2 (60) NOT NULL,
| |
| − | PORT NUMBER NOT NULL,
| |
| − | COMMENTS VARCHAR2(500),
| |
| − | CONTACT VARCHAR2(100),
| |
| − | STATUS VARCHAR2(10) default 'ON',
| |
| − | CLIENT VARCHAR2 (60) default 'N.A',
| |
| − | UPD_DATE DATE default sysdate,
| |
| − | ENV CHAR(3)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_RepositoryOther for &&UserIndicator..tsqw_RepositoryOther;
| |
| − | grant all on &&UserIndicator..tsqw_RepositoryOther to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_RepositoryOther to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_JobsExec|
| |
| − | <nowiki>Jobs execution monitoring</nowiki>|
| |
| − | <pre>
| |
| − | /* Jobs execution monitoring */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_JobsExec
| |
| − | (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | BEGINNING DATE NOT NULL,
| |
| − | SCRIPT CHAR(80) NOT NULL,
| |
| − | PARAMETERS VARCHAR2(400) NOT NULL,
| |
| − | END DATE,
| |
| − | DURATION CHAR(8 BYTE),
| |
| − | STATUS NUMBER(3)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_JobsExec ADD (
| |
| − | CONSTRAINT isqw_JobsExec_pk PRIMARY KEY (ORACLE_SID, BEGINNING, SCRIPT)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X);
| |
| − |
| |
| − | create public synonym tsqw_JobsExec for &&UserIndicator..tsqw_JobsExec;
| |
| − | grant all on &&UserIndicator..tsqw_JobsExec to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_JobsExec to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolBackups|
| |
| − | <nowiki>Monitoring backups</nowiki>|
| |
| − | <pre>
| |
| − | /* Monitoring backups */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolBackups (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | BEGINNING DATE NOT NULL,
| |
| − | BCK_TYPE VARCHAR2(50) NOT NULL,
| |
| − | TOOLS VARCHAR2(12) NOT NULL,
| |
| − | END DATE NOT NULL,
| |
| − | DURATION CHAR(8) NOT NULL,
| |
| − | STATUS NUMBER(3) DEFAULT 0,
| |
| − | SIZE_BCK NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolBackups for &&UserIndicator..tsqw_VolBackups;
| |
| − | grant all on &&UserIndicator..tsqw_VolBackups to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolBackups to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolBackups ADD (
| |
| − | CONSTRAINT isqw_VolBackups_pk PRIMARY KEY (ORACLE_SID,BEGINNING)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolBackupsExclude|
| |
| − | <nowiki>Exclusion of check backup</nowiki>|
| |
| − | <pre>
| |
| − | /* Exclusion of check backup */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolBackupsExclude
| |
| − | (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | DBANAME VARCHAR2(50) NOT NULL,
| |
| − | COMMENTS VARCHAR2(50) NOT NULL,
| |
| − | UPD_DATE DATE default trunc(sysdate)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D;
| |
| − |
| |
| − | create public synonym tsqw_VolBackupsExclude for &&UserIndicator..tsqw_VolBackupsExclude;
| |
| − | grant all on &&UserIndicator..tsqw_VolBackupsExclude to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolBackupsExclude to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolBackupsExclude ADD (
| |
| − | CONSTRAINT isqw_VolBackupsExclude_pk PRIMARY KEY (ORACLE_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X);
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolBackupsMonitoring|
| |
| − | <nowiki>Monitoring backups FS during backups</nowiki>|
| |
| − | <pre>
| |
| − | /* Monitoring backups FS during backups */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolBackupsMonitoring (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | BEGINNING DATE NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | SIZE_BCK NUMBER DEFAULT 0,
| |
| − | SIZE_FS NUMBER DEFAULT 0,
| |
| − | USED_FS NUMBER DEFAULT 0,
| |
| − | FREE_FS NUMBER DEFAULT 0,
| |
| − | RATE_FS NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolBackupsMonitoring for &&UserIndicator..tsqw_VolBackupsMonitoring;
| |
| − | grant all on &&UserIndicator..tsqw_VolBackupsMonitoring to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolBackupsMonitoring to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolBackupsMonitoring ADD (
| |
| − | CONSTRAINT isqw_VolBackupsMonitoring_pk PRIMARY KEY (ORACLE_SID,BEGINNING,GATHER_DATE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolTbs|
| |
| − | <nowiki>Volumetry of Tablespaces</nowiki>|
| |
| − | <pre>
| |
| − | /* Volumetry of Tablespaces */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolTbs (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | TABLESPACE_NAME VARCHAR2 (30) NOT NULL,
| |
| − | CONTENTS VARCHAR2 (9) NOT NULL,
| |
| − | SIZE_TBS NUMBER DEFAULT 0,
| |
| − | MAXSIZE NUMBER DEFAULT 0,
| |
| − | FREE NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolTbs for &&UserIndicator..tsqw_VolTbs;
| |
| − | grant all on &&UserIndicator..tsqw_VolTbs to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolTbs to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolTbs ADD (
| |
| − | CONSTRAINT isqw_VolTbs_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE, TABLESPACE_NAME)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolTbsThreshold|
| |
| − | <nowiki>Definition of dynamic thresholds specic for TBS</nowiki>|
| |
| − | <pre>
| |
| − | /* Definition of dynamic thresholds specic for TBS */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolTbsThreshold (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | TABLESPACE_NAME VARCHAR2 (30) NOT NULL,
| |
| − | THRESHOLD_C NUMBER NOT NULL,
| |
| − | THRESHOLD_W NUMBER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolTbsThreshold for &&UserIndicator..tsqw_VolTbsThreshold;
| |
| − | grant all on &&UserIndicator..tsqw_VolTbsThreshold to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolTbsThreshold to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolTbsThreshold ADD (
| |
| − | CONSTRAINT isqw_VolTbsThreshold_pk PRIMARY KEY (ORACLE_SID, TABLESPACE_NAME)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolTbsThresholdDef|
| |
| − | <nowiki>Definition of default dynamic thresholds for TBS</nowiki>|
| |
| − | <pre>
| |
| − | /* Definition of default dynamic thresholds for TBS */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolTbsThresholdDef (
| |
| − | SIZE_TBS NUMBER NOT NULL,
| |
| − | THRESHOLD_C NUMBER NOT NULL,
| |
| − | THRESHOLD_W NUMBER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolTbsThresholdDef for &&UserIndicator..tsqw_VolTbsThresholdDef;
| |
| − | grant all on &&UserIndicator..tsqw_VolTbsThresholdDef to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolTbsThresholdDef to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolTbsThresholdDef ADD (
| |
| − | CONSTRAINT isqw_VolTbsThresholdDef_pk PRIMARY KEY (SIZE_TBS)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | insert into tsqw_VolTbsThresholdDef (SIZE_TBS, THRESHOLD_C, THRESHOLD_W) values (2048, 90, 80);
| |
| − | insert into tsqw_VolTbsThresholdDef (SIZE_TBS, THRESHOLD_C, THRESHOLD_W) values (10240, 92, 85);
| |
| − | insert into tsqw_VolTbsThresholdDef (SIZE_TBS, THRESHOLD_C, THRESHOLD_W) values (20480, 95, 90);
| |
| − | insert into tsqw_VolTbsThresholdDef (SIZE_TBS, THRESHOLD_C, THRESHOLD_W) values (10240000000, 99, 97);
| |
| − | commit;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolDb|
| |
| − | <nowiki>Volumetry of Bases</nowiki>|
| |
| − | <pre>
| |
| − | /* Volumetry of Bases */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolDb (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | SIZE_DB NUMBER DEFAULT 0,
| |
| − | FREE NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolDb for &&UserIndicator..tsqw_VolDb;
| |
| − | grant all on &&UserIndicator..tsqw_VolDb to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolDb to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolDb ADD (
| |
| − | CONSTRAINT isqw_VolDb_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolArchlogs|
| |
| − | <nowiki>Table de suivi des archives logs</nowiki>|
| |
| − | <pre>
| |
| − | /* Table de suivi des archives logs */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolArchlogs (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | NB_ARCH NUMBER DEFAULT 0,
| |
| − | SIZE_ARCH NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolArchlogs for &&UserIndicator..tsqw_VolArchlogs;
| |
| − | grant all on &&UserIndicator..tsqw_VolArchlogs to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolArchlogs to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolArchlogs ADD (
| |
| − | CONSTRAINT isqw_VolArchlogs_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolSchema|
| |
| − | <nowiki>Volumetry of schemas</nowiki>|
| |
| − | <pre>
| |
| − | /* Volumetry of schemas */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolSchema (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | OWNER VARCHAR2 (30) NOT NULL,
| |
| − | SIZE_SCH NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolSchema for &&UserIndicator..tsqw_VolSchema;
| |
| − | grant all on &&UserIndicator..tsqw_VolSchema to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolSchema to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolSchema ADD (
| |
| − | CONSTRAINT isqw_VolSchema_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE, OWNER)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolSegments|
| |
| − | <nowiki>Volumetry of segments</nowiki>|
| |
| − | <pre>
| |
| − | /* Volumetry of segments */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolSegments (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | OWNER VARCHAR2 (30) NOT NULL,
| |
| − | SEGMENT_NAME VARCHAR2 (30) NOT NULL,
| |
| − | SEGMENT_TYPE VARCHAR2 (30) NOT NULL,
| |
| − | SIZE_SEG NUMBER DEFAULT 0
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolSegments for &&UserIndicator..tsqw_VolSegments;
| |
| − | grant all on &&UserIndicator..tsqw_VolSegments to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolSegments to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolSegments ADD (
| |
| − | CONSTRAINT isqw_VolSegments_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE, OWNER, SEGMENT_NAME, SEGMENT_TYPE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_GenInfos|
| |
| − | <nowiki>Infos générales sur la database et l'instance</nowiki>|
| |
| − | <pre>
| |
| − | /* Infos générales sur la database et l'instance */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_GenInfos (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | VERSION VARCHAR2 (17) NOT NULL,
| |
| − | STARTUP_TIME DATE NOT NULL,
| |
| − | HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | DBID NUMBER NOT NULL,
| |
| − | RESETLOGS_TIME DATE NOT NULL,
| |
| − | LOG_MODE VARCHAR2 (12) NOT NULL,
| |
| − | NLS_CHARACTERSET VARCHAR2 (40) NOT NULL,
| |
| − | NB_USERS INTEGER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_GenInfos for &&UserIndicator..tsqw_GenInfos;
| |
| − | grant all on &&UserIndicator..tsqw_GenInfos to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_GenInfos to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_GenInfos ADD (
| |
| − | CONSTRAINT isqw_GenInfos_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_StartedInstances|
| |
| − | <nowiki>Tables des instances démarrées</nowiki>|
| |
| − | <pre>
| |
| − | /* Tables des instances démarrées */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_StartedInstances (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | USERNAME VARCHAR2 (60) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_StartedInstances for &&UserIndicator..tsqw_StartedInstances;
| |
| − | grant all on &&UserIndicator..tsqw_StartedInstances to &&UserIndicator._RMAJ;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_StartedInstances ADD (
| |
| − | CONSTRAINT isqw_StartedInstances_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolFS|
| |
| − | <nowiki>Volumetry of FS</nowiki>|
| |
| − | <pre>
| |
| − | /* Volumetry of FS */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolFS (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | HOST_NAME VARCHAR2 (64) NOT NULL,
| |
| − | FILESYSTEM VARCHAR2 (200) NOT NULL,
| |
| − | SIZE_FS NUMBER NOT NULL,
| |
| − | USED NUMBER NOT NULL,
| |
| − | FREE NUMBER NOT NULL,
| |
| − | RATE NUMBER NOT NULL,
| |
| − | MOUNT VARCHAR2 (200)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolFS for &&UserIndicator..tsqw_VolFS;
| |
| − | grant all on &&UserIndicator..tsqw_VolFS to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolFS to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolFS ADD (
| |
| − | CONSTRAINT isqw_VolFS_pk PRIMARY KEY (ORACLE_SID, GATHER_DATE,MOUNT)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolFSThreshold|
| |
| − | <nowiki>Definition of dynamic thresholds specic for FS</nowiki>|
| |
| − | <pre>
| |
| − | /* Definition of dynamic thresholds specic for FS */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolFSThreshold (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | MOUNT VARCHAR2 (200),
| |
| − | THRESHOLD_C NUMBER NOT NULL,
| |
| − | THRESHOLD_W NUMBER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolFSThreshold for &&UserIndicator..tsqw_VolFSThreshold;
| |
| − | grant all on &&UserIndicator..tsqw_VolFSThreshold to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolFSThreshold to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolFSThreshold ADD (
| |
| − | CONSTRAINT isqw_VolFSThreshold_pk PRIMARY KEY (ORACLE_SID, MOUNT)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_VolFSThresholdDef|
| |
| − | <nowiki>Definition of default dynamic thresholds for FS</nowiki>|
| |
| − | <pre>
| |
| − | /* Definition of default dynamic thresholds for FS */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_VolFSThresholdDef (
| |
| − | SIZE_FS NUMBER NOT NULL,
| |
| − | THRESHOLD_C NUMBER NOT NULL,
| |
| − | THRESHOLD_W NUMBER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_VolFSThresholdDef for &&UserIndicator..tsqw_VolFSThresholdDef;
| |
| − | grant all on &&UserIndicator..tsqw_VolFSThresholdDef to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_VolFSThresholdDef to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_VolFSThresholdDef ADD (
| |
| − | CONSTRAINT isqw_VolFSThresholdDef_pk PRIMARY KEY (SIZE_FS)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | insert into tsqw_VolFSThresholdDef (SIZE_FS, THRESHOLD_C, THRESHOLD_W) values (2048, 90, 80);
| |
| − | insert into tsqw_VolFSThresholdDef (SIZE_FS, THRESHOLD_C, THRESHOLD_W) values (10240, 92, 85);
| |
| − | insert into tsqw_VolFSThresholdDef (SIZE_FS, THRESHOLD_C, THRESHOLD_W) values (20480, 95, 90);
| |
| − | insert into tsqw_VolFSThresholdDef (SIZE_FS, THRESHOLD_C, THRESHOLD_W) values (10240000000, 98, 95);
| |
| − | commit;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrElapsedBef111|
| |
| − | <nowiki>Stats AWR Top Elapsed <= 11.1</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Elapsed <= 11.1 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrElapsedBef111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | ElapsedperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrElapsedBef111 ADD (
| |
| − | CONSTRAINT isqw_AwrElapsedBef111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrElapsedBef111 for &&UserIndicator..tsqw_AwrElapsedBef111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrElapsedBef111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrElapsedBef111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrElapsedAft112|
| |
| − | <nowiki>Stats AWR Top Elapsed >= 11.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Elapsed >= 11.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrElapsedAft112 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | ElapsedperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | PctCpu INTEGER NOT NULL,
| |
| − | PctIO INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrElapsedAft112 ADD (
| |
| − | CONSTRAINT isqw_AwrElapsedAft112_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrElapsedAft112 for &&UserIndicator..tsqw_AwrElapsedAft112;
| |
| − | grant all on &&UserIndicator..tsqw_AwrElapsedAft112 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrElapsedAft112 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | create or replace view &&UserIndicator..tsqw_AwrElapsed (
| |
| − | ORACLE_SID,
| |
| − | GATHER_DATE,
| |
| − | REPORT,
| |
| − | Elapsed,
| |
| − | Cpu,
| |
| − | Executions,
| |
| − | ElapsedperExec,
| |
| − | PctTotal,
| |
| − | SQL_SID
| |
| − | )
| |
| − | as select ORACLE_SID, GATHER_DATE, REPORT, Elapsed, Cpu, Executions, ElapsedperExec, PctTotal, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrElapsedBef111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, Elapsed, PctCpu, Executions, ElapsedperExec, PctTotal, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrElapsedAft112
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AwrElapsed for &&UserIndicator..tsqw_AwrElapsed;
| |
| − | grant select on &&UserIndicator..tsqw_AwrElapsed to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrCpuBef111|
| |
| − | <nowiki>Stats AWR Top CPU <= 10.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top CPU <= 10.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrCpuBef111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | CpuperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrCpuBef111 ADD (
| |
| − | CONSTRAINT isqw_AwrCpuBef111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrCpuBef111 for &&UserIndicator..tsqw_AwrCpuBef111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrCpuBef111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrCpu111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrCpu111|
| |
| − | <nowiki>Stats AWR Top CPU = 11.1</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top CPU = 11.1 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrCpu111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | CpuperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | PctDbTime INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrCpu111 ADD (
| |
| − | CONSTRAINT isqw_AwrCpu111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrCpu111 for &&UserIndicator..tsqw_AwrCpu111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrCpu111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrCpu111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrCpuAft112|
| |
| − | <nowiki>Stats AWR Top CPU >= 11.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top CPU >= 11.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrCpuAft112 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | CpuperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | PctCpu INTEGER NOT NULL,
| |
| − | PctIO INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrCpuAft112 ADD (
| |
| − | CONSTRAINT isqw_AwrCpuAft112_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrCpuAft112 for &&UserIndicator..tsqw_AwrCpuAft112;
| |
| − | grant all on &&UserIndicator..tsqw_AwrCpuAft112 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrCpuAft112 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | create or replace view &&UserIndicator..tsqw_AwrCpu (
| |
| − | ORACLE_SID,
| |
| − | GATHER_DATE,
| |
| − | REPORT,
| |
| − | Elapsed,
| |
| − | Cpu,
| |
| − | Executions,
| |
| − | ElapsedperExec,
| |
| − | PctTotal,
| |
| − | SQL_SID
| |
| − | )
| |
| − | as select ORACLE_SID, GATHER_DATE, REPORT, Cpu, Elapsed, Executions, CpuperExec, PctTotal, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrCpuBef111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, Cpu, Elapsed, Executions, CpuperExec, PctTotal, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrCpu111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, Cpu, Elapsed, Executions, CpuperExec, PctTotal, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrCpuAft112
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AwrCpu for &&UserIndicator..tsqw_AwrCpu;
| |
| − | grant select on &&UserIndicator..tsqw_AwrCpu to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrBufferGetsBef111|
| |
| − | <nowiki>Stats AWR Top Buffer gets <= 11.1</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Buffer gets <= 11.1 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrBufferGetsBef111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | BufferGets INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | GetsperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrBufferGetsBef111 ADD (
| |
| − | CONSTRAINT isqw_AwrBufferGetsBef111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrBufferGetsBef111 for &&UserIndicator..tsqw_AwrBufferGetsBef111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrBufferGetsBef111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrBufferGetsBef111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrBufferGetsAft112|
| |
| − | <nowiki>Stats AWR Top Buffer gets >= 11.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Buffer gets >= 11.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrBufferGetsAft112 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | BufferGets INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | GetsperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | PctCpu INTEGER NOT NULL,
| |
| − | PctIO INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrBufferGetsAft112 ADD (
| |
| − | CONSTRAINT isqw_AwrBufferGetsAft112_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrBufferGetsAft112 for &&UserIndicator..tsqw_AwrBufferGetsAft112;
| |
| − | grant all on &&UserIndicator..tsqw_AwrBufferGetsAft112 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrBufferGetsAft112 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | create or replace view &&UserIndicator..tsqw_AwrBufferGets (
| |
| − | ORACLE_SID,
| |
| − | GATHER_DATE,
| |
| − | REPORT,
| |
| − | BufferGets,
| |
| − | Executions,
| |
| − | GetsperExec,
| |
| − | PctTotal,
| |
| − | Cpu,
| |
| − | Elapsed,
| |
| − | SQL_SID
| |
| − | )
| |
| − | as select ORACLE_SID, GATHER_DATE, REPORT, BufferGets, Executions, GetsperExec, PctTotal, Cpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrBufferGetsBef111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, BufferGets, Executions, GetsperExec, PctTotal, PctCpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrBufferGetsAft112
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AwrBufferGets for &&UserIndicator..tsqw_AwrBufferGets;
| |
| − | grant select on &&UserIndicator..tsqw_AwrBufferGets to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrPhysicalReadsBef111|
| |
| − | <nowiki>Stats AWR Top Physical Reads <= 11.1</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Physical Reads <= 11.1 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrPhysicalReadsBef111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | PhysicalReads INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | ReadsperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrPhysicalReadsBef111 ADD (
| |
| − | CONSTRAINT isqw_AwrPhysicalReadsBef111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrPhysicalReadsBef111 for &&UserIndicator..tsqw_AwrPhysicalReadsBef111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrPhysicalReadsBef111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrPhysicalReadsBef111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrPhysicalReadsAft112|
| |
| − | <nowiki>Stats AWR Top Physical Reads >= 11.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Physical Reads >= 11.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrPhysicalReadsAft112 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | PhysicalReads INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | ReadsperExec INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | PctCpu INTEGER NOT NULL,
| |
| − | PctIO INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrPhysicalReadsAft112 ADD (
| |
| − | CONSTRAINT isqw_AwrPhysicalReadsAft112_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrPhysicalReadsAft112 for &&UserIndicator..tsqw_AwrPhysicalReadsAft112;
| |
| − | grant all on &&UserIndicator..tsqw_AwrPhysicalReadsAft112 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrPhysicalReadsAft112 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | create or replace view &&UserIndicator..tsqw_AwrPhysicalReads (
| |
| − | ORACLE_SID,
| |
| − | GATHER_DATE,
| |
| − | REPORT,
| |
| − | PhysicalReads,
| |
| − | Executions,
| |
| − | ReadsperExec,
| |
| − | PctTotal,
| |
| − | Cpu,
| |
| − | Elapsed,
| |
| − | SQL_SID
| |
| − | )
| |
| − | as select ORACLE_SID, GATHER_DATE, REPORT, PhysicalReads, Executions, ReadsperExec, PctTotal, Cpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrPhysicalReadsBef111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, PhysicalReads, Executions, ReadsperExec, PctTotal, PctCpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrPhysicalReadsAft112
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AwrPhysicalReads for &&UserIndicator..tsqw_AwrPhysicalReads;
| |
| − | grant select on &&UserIndicator..tsqw_AwrPhysicalReads to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrUnOptimizedReads|
| |
| − | <nowiki>Stats AWR Top UnOptimized Physical Reads</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top UnOptimized Physical Reads */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrUnOptimizedReads (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | UnOptimizedReads INTEGER NOT NULL,
| |
| − | PhysicalReads INTEGER NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | ReadsperExec INTEGER NOT NULL,
| |
| − | PctOpt INTEGER NOT NULL,
| |
| − | PctTotal INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrUnOptimizedReads ADD (
| |
| − | CONSTRAINT isqw_AwrUnOptimizedReads_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrUnOptimizedReads for &&UserIndicator..tsqw_AwrUnOptimizedReads;
| |
| − | grant all on &&UserIndicator..tsqw_AwrUnOptimizedReads to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrUnOptimizedReads to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrExecutionsBef111|
| |
| − | <nowiki>Stats AWR Top Executions <= 11.1</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Executions <= 11.1 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrExecutionsBef111 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | RowsProcessed INTEGER NOT NULL,
| |
| − | RowsperExec INTEGER NOT NULL,
| |
| − | Cpu INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrExecutionsBef111 ADD (
| |
| − | CONSTRAINT isqw_AwrExecutionsBef111_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrExecutionsBef111 for &&UserIndicator..tsqw_AwrExecutionsBef111;
| |
| − | grant all on &&UserIndicator..tsqw_AwrExecutionsBef111 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrExecutionsBef111 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrExecutionsAft112|
| |
| − | <nowiki>Stats AWR Top Executions >= 11.2</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Top Executions >= 11.2 */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrExecutionsAft112 (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | Executions INTEGER NOT NULL,
| |
| − | RowsProcessed INTEGER NOT NULL,
| |
| − | RowsperExec INTEGER NOT NULL,
| |
| − | Elapsed INTEGER NOT NULL,
| |
| − | PctCpu INTEGER NOT NULL,
| |
| − | PctIO INTEGER NOT NULL,
| |
| − | SQL_SID VARCHAR2(13) NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrExecutionsAft112 ADD (
| |
| − | CONSTRAINT isqw_AwrExecutionsAft112_PK PRIMARY KEY (ORACLE_SID,REPORT,SQL_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrExecutionsAft112 for &&UserIndicator..tsqw_AwrExecutionsAft112;
| |
| − | grant all on &&UserIndicator..tsqw_AwrExecutionsAft112 to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrExecutionsAft112 to &&UserIndicator._RLEC;
| |
| − |
| |
| − | create or replace view &&UserIndicator..tsqw_AwrExecutions (
| |
| − | ORACLE_SID,
| |
| − | GATHER_DATE,
| |
| − | REPORT,
| |
| − | Executions,
| |
| − | RowsProcessed,
| |
| − | RowsperExec,
| |
| − | Cpu,
| |
| − | Elapsed,
| |
| − | SQL_SID
| |
| − | )
| |
| − | as select ORACLE_SID, GATHER_DATE, REPORT, Executions, RowsProcessed, RowsperExec, Cpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrExecutionsBef111
| |
| − | union all
| |
| − | select ORACLE_SID, GATHER_DATE, REPORT, Executions, RowsProcessed, RowsperExec, PctCpu, Elapsed, SQL_SID
| |
| − | from &&UserIndicator..tsqw_AwrExecutionsAft112
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AwrExecutions for &&UserIndicator..tsqw_AwrExecutions;
| |
| − | grant select on &&UserIndicator..tsqw_AwrExecutions to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AwrHits|
| |
| − | <nowiki>Stats AWR Hits ratios</nowiki>|
| |
| − | <pre>
| |
| − | /* Stats AWR Hits ratios */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AwrHits (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | REPORT VARCHAR2(50) NOT NULL,
| |
| − | BufferNowait INTEGER NOT NULL,
| |
| − | BufferHit INTEGER NOT NULL,
| |
| − | LibraryHit INTEGER NOT NULL,
| |
| − | RedoNoWait INTEGER NOT NULL,
| |
| − | InmemorySort INTEGER NOT NULL,
| |
| − | SoftParse INTEGER NOT NULL,
| |
| − | LatchHit INTEGER NOT NULL
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AwrHits ADD (
| |
| − | CONSTRAINT isqw_AwrHits_PK PRIMARY KEY (ORACLE_SID,REPORT)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | create public synonym tsqw_AwrHits for &&UserIndicator..tsqw_AwrHits;
| |
| − | grant all on &&UserIndicator..tsqw_AwrHits to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AwrHits to &&UserIndicator._RLEC;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_GenHisto|
| |
| − | <nowiki>Monthly general Informations</nowiki>|
| |
| − | <pre>
| |
| − | /* Monthly general Informations */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_GenHisto (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | MONTH DATE NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | VERSION VARCHAR2 (17),
| |
| − | MAJ_VERSION VARCHAR2 (5),
| |
| − | SIZE_DB NUMBER DEFAULT 0,
| |
| − | FREE NUMBER DEFAULT 0,
| |
| − | CLIENT VARCHAR2 (60),
| |
| − | ENV CHAR(3)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_GenHisto for &&UserIndicator..tsqw_GenHisto;
| |
| − | grant all on &&UserIndicator..tsqw_GenHisto to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_GenHisto to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_GenHisto ADD (
| |
| − | CONSTRAINT isqw_GenHisto_pk PRIMARY KEY (ORACLE_SID,MONTH)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | CREATE UNIQUE INDEX &&UserIndicator..isqw_GenHisto_u ON &&UserIndicator..tsqw_GenHisto(MONTH, ORACLE_SID)
| |
| − | TABLESPACE &&UserIndicator.X;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_AlertFile|
| |
| − | <nowiki>Alert log location</nowiki>|
| |
| − | <pre>
| |
| − | /* Alert log location*/
| |
| − | CREATE TABLE &&UserIndicator..tsqw_AlertFile (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | ALERT_FILE VARCHAR2 (800)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_AlertFile for &&UserIndicator..tsqw_AlertFile;
| |
| − | grant all on &&UserIndicator..tsqw_AlertFile to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_AlertFile to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_AlertFile ADD (
| |
| − | CONSTRAINT isqw_AlertFile_pk PRIMARY KEY (ORACLE_SID)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_CheckInstance|
| |
| − | <nowiki>Checks instance</nowiki>|
| |
| − | <pre>
| |
| − | /* Checks instance */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_CheckInstance (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | GATHER_DATE DATE NOT NULL,
| |
| − | TypeCheck VARCHAR2 (80),
| |
| − | TypeWar VARCHAR2 (80),
| |
| − | Comments VARCHAR2 (800)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_CheckInstance for &&UserIndicator..tsqw_CheckInstance;
| |
| − | grant all on &&UserIndicator..tsqw_CheckInstance to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_CheckInstance to &&UserIndicator._RLEC;
| |
| − |
| |
| − | CREATE INDEX &&UserIndicator..isqw_CheckInstance ON &&UserIndicator..tsqw_CheckInstance(GATHER_DATE, ORACLE_SID, TypeCheck, TypeWar)
| |
| − | TABLESPACE &&UserIndicator.X;
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
| − |
| |
| − | {{SQWareRepositoryTables|tsqw_CheckInstanceExclude|
| |
| − | <nowiki>Checks instance justification</nowiki>|
| |
| − | <pre>
| |
| − | /* Checks instance justification */
| |
| − | CREATE TABLE &&UserIndicator..tsqw_CheckInstanceExclude (
| |
| − | ORACLE_SID VARCHAR2(9) NOT NULL,
| |
| − | TypeCheck VARCHAR2 (80),
| |
| − | TypeWar VARCHAR2 (80),
| |
| − | Comments VARCHAR2 (80),
| |
| − | Justification VARCHAR2 (255)
| |
| − | )
| |
| − | TABLESPACE &&UserIndicator.D
| |
| − | ;
| |
| − |
| |
| − | create public synonym tsqw_CheckInstanceExclude for &&UserIndicator..tsqw_CheckInstanceExclude;
| |
| − | grant all on &&UserIndicator..tsqw_CheckInstanceExclude to &&UserIndicator._RMAJ;
| |
| − | grant select on &&UserIndicator..tsqw_CheckInstanceExclude to &&UserIndicator._RLEC;
| |
| − |
| |
| − | ALTER TABLE &&UserIndicator..tsqw_CheckInstanceExclude ADD (
| |
| − | CONSTRAINT isqw_CheckInstanceExclude_pk PRIMARY KEY (ORACLE_SID,TypeCheck,TypeWar,Comments)
| |
| − | DEFERRABLE
| |
| − | USING INDEX
| |
| − | TABLESPACE &&UserIndicator.X) NOPARALLEL;
| |
| − |
| |
| − |
| |
| − | </pre>
| |
| − | }}
| |
