home *** CD-ROM | disk | FTP | other *** search
Text File | 1999-11-01 | 23.6 KB | 1,438 lines |
- SAY --- Installing system tables of SYSDBA ...
- /* @(#)SYSDBA.ins 10.01.00 1998-01-30
- /
- / * Hier muss END INIT SERVERDB und darf kein
- / * INTERNAL stehen, sonst wird SYSSTATISTICS
- / * zur SYSTEM Tabelle.
- /
- END INIT SERVERDB
- /
- IF $RC (EXISTS TABLE SYSSTATISTICS) <> 0
- THEN
- BEGIN
- /
- CREATE TABLE SYSSTATISTICS
- (OWNER CHAR(18),
- TABLENAME CHAR(18),
- COLUMNNAME CHAR(18),
- INDEXNAME CHAR(18),
- PAGES FIXED(10),
- DISTINCT_VALUES FIXED(10),
- AVG_LIST_LEN FIXED(10,1)
- )
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- STOP 7
- /
- END
- /
- END
- /
- INIT SERVERDB
- /
- IF $RC (EXISTS TABLE SYSACCOUNT) = 0
- THEN
- BEGIN
- /
- DROP TABLE SYSACCOUNT
- /
- COMMIT WORK
- /
- END
- /
- CREATE TABLE SYSACCOUNT
- (SERVERDB_NO FIXED (4),
- SESSION FIXED(18),
- NOLOG_SESSION CHAR(3),
- USERNAME VARCHAR(18),
- GROUPNAME VARCHAR(18),
- TERMID VARCHAR(18),
- DBANAME VARCHAR(18),
- CONNECTDATE DATE,
- CONNECTTIME TIME,
- RELEASEDATE DATE,
- RELEASETIME TIME,
- COMMANDCOUNT FIXED(10),
- CPUTIME FIXED(10),
- DATA_CACHE_ACCESS FIXED(18),
- SESSIONEND CHAR(8),
- PROGTYPE VARCHAR(8),
- PROGOWNER VARCHAR(18),
- PROGNAME VARCHAR(18),
- CATALOG_CACHE_SIZE INTEGER,
- CAT_CACHE_HITRATE FIXED(4),
- DBPROC_CACHE_SIZE INTEGER,
- TEMP_CACHE_SIZE INTEGER,
- TEMP_CACHE_HITRATE FIXED(4)
- )
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE SYS_UPDSTATISTICS) = 0
- THEN
- BEGIN
- /*
- DROP TABLE SYS_UPDSTATISTICS
- /*
- COMMIT WORK
- /*
- END
- /**
- /*IF $RC (EXISTS TABLE SYS_UPDSTATISTICS) <> 0
- /*THEN
- /* BEGIN
- /*/
- /* CREATE TABLE SYS_UPDSTATISTICS
- /* (OWNER CHAR(18),
- /* TABLENAME CHAR(18),
- /* COLUMNNAME CHAR(18),
- /* TABLEID CHAR(8) BYTE,
- /* "DATE" DATE,
- /* "TIME" TIME,
- /* PRIMARY KEY (OWNER, TABLENAME, COLUMNNAME)
- /* )
- /*/*
- /* IF $RC <> 0
- /* THEN
- /*/*
- /* STOP 7
- /*/*
- /* GRANT ALL ON SYS_UPDSTATISTICS TO DOMAIN
- /*/*
- /* IF $RC <> 0
- /* THEN
- /*/*
- /* STOP 7
- /*/*
- /* END
- /*/**
- /
- IF $RC (EXISTS TABLE "SYS%CATALOG1") <> 0
- THEN
- BEGIN
- /
- CREATE TABLE "SYS%CATALOG1"
- (K CHAR(48) BYTE,
- INFO CHAR(3900) BYTE,
- PRIMARY KEY (K)
- )
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- END
- /
- IF $RC (EXISTS TABLE "SYS%CATALOG2") <> 0
- THEN
- BEGIN
- /
- CREATE TABLE "SYS%CATALOG2"
- (K CHAR(48) BYTE,
- INFO CHAR(3900) BYTE,
- PRIMARY KEY (K)
- )
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- END
- /
- IF $RC (EXISTS TABLE SYS_PACKAGE) = 0
- THEN
- BEGIN
- /
- DROP TABLE SYS_PACKAGE
- /
- COMMIT WORK
- /
- END
- /
- CREATE TABLE SYS_PACKAGE
- (CMD_ID CHAR(8) BYTE,
- SEQ CHAR(4) BYTE,
- CNT CHAR(1) BYTE,
- PROG_LANGUAGE CHAR(8),
- PROG_NAME CHAR(18),
- MODULE_NAME CHAR(18),
- ISO_LEVEL FIXED(2),
- CNS_TOKEN CHAR(8),
- CMD CHAR(3900),
- PRIMARY KEY (CMD_ID, SEQ, CNT)
- )
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- CREATE INDEX SYS_PACKAGE.CNS_TOKEN
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- CREATE INDEX SYS_PACKAGE.PROG_NAME
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE DUAL) <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- CREATE TABLE DUAL (DUMMY CHAR(1))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- INIT SERVERDB
- /
- INSERT INTO DUAL VALUES ('a')
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON DUAL TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- END
- /
- IF $RC (EXISTS TABLE OPTIMIZERSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW OPTIMIZERSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW OPTIMIZERSTATISTICS
- (OWNER,TABLENAME,INDEXNAME,COLUMNNAME,
- DISTINCTVALUES,PAGECOUNT,AVGLISTLENGTH)
- AS SELECT
- OWNER,TABLENAME,INDEXNAME,COLUMNNAME,
- DISTINCTVALUES,PAGECOUNT,AVGLISTLENGTH
- FROM SYSDD.OPTIMIZER_STATS
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON OPTIMIZERSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE CONFIGURATION) = 0
- THEN
- BEGIN
- /
- DROP VIEW CONFIGURATION
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW CONFIGURATION
- (DESCRIPTION,CHAR_VALUE,NUMERIC_VALUE)
- AS SELECT
- DESCRIPTION,"VALUE",NUMERIC_VALUE
- FROM SYSDD.CONFIGURATION
- WHERE USER IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON CONFIGURATION TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE DATADEVSPACES) = 0
- THEN
- BEGIN
- /
- DROP VIEW DATADEVSPACES
- /
- COMMIT WORK
- /
- END
- /
- /CREATE VIEW DATADEVSPACES
- / (DEVSPACENAME,DEVSPACESIZE,MAXDATAPAGENO,
- / USEDPERMPAGES,PCTUSEDPERM,USEDTMPPAGES,
- / PCTUSEDTMP,UNUSEDPAGES,PCTUNUSED)
- /AS SELECT
- / DEVSPACENAME,PAGES,MAX_DATA_PAGE_NO,
- / USED_PERM_PAGES,PCT_USED_PERM,USED_TEMP_PAGES,
- / PCT_USED_TEMP,UNUSED_PAGES,PCT_UNUSED_PAGES
- / FROM SYSDD.DEVSPACESTATISTICS
- / WHERE USER IN(SELECT USERNAME
- / FROM SYSDD.USERS
- / WHERE USERNAME = USERGROUP AND USERMODE IN('DBA','SYSDBA'))
- /
- CREATE VIEW DATADEVSPACES
- (DEVSPACENAME,DEVSPACESIZE,MAXDATAPAGENO,
- USEDPERMPAGES,PCTUSEDPERM,USEDTMPPAGES,
- PCTUSEDTMP,UNUSEDPAGES,PCTUNUSED)
- AS SELECT
- DEVSPACENAME,1,1,
- 1,1,1,
- 1,1,1
- FROM SYSDD.DEVSPACESTATISTICS
- WHERE USER IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON DATADEVSPACES TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE INDEXSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW INDEXSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW INDEXSTATISTICS
- (OWNER,TABLENAME,INDEXNAME,COLUMNNAME,
- DESCRIPTION,CHAR_VALUE,NUMERIC_VALUE)
- AS SELECT
- OWNER,TABLENAME,INDEXNAME,COLUMNNAME,
- DESCRIPTION,"VALUE",NUMERIC_VALUE
- FROM SYSDD.INDEXSTATISTICS
- WHERE OWNER = USERGROUP OR USER = SYSDBA
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON INDEXSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE LOCKSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW LOCKSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW LOCKSTATISTICS
- (SESSION,TRANSACTION,SERVERDBNO,PROCESS,USERNAME,TERMID,
- REMOTEUSER,PENDINGLOCK,LOCKMODE,LOCKREQUESTMODE,
- OWNER,TABLENAME,ROWIDLENGTH,"ROWID",ROWIDHEX)
- AS SELECT
- SESSION,TRANSACTION,SERVERDB,PROCESS,USERNAME,TERMID,
- REMOTE_USER,PENDING_LOCK,LOCK_MODE,LOCK_REQUEST_MODE,
- OWNER,TABLENAME,ROWID_LENGTH,"ROWID",ROWID_HEX
- FROM SYSDD.LOCKS
- WHERE USERNAME = USERGROUP OR OWNER = USERGROUP
- OR USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERMODE IN('DBA', 'SYSDBA')
- AND USERNAME = USERGROUP)
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON LOCKSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE LOCKLISTSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW LOCKLISTSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW LOCKLISTSTATISTICS
- (DESCRIPTION, "VALUE")
- AS SELECT
- DESCRIPTION, "VALUE"
- FROM SYSDD.LOCKSTATISTICS
- WHERE
- USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERMODE IN('DBA', 'SYSDBA')
- AND USERNAME = USERGROUP)
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON LOCKLISTSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE SERVERDBSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW SERVERDBSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW SERVERDBSTATISTICS
- (SERVERDBSIZE,MAXDATAPAGENO,USEDPERMPAGES,PCTUSEDPERM,USEDTMPPAGES,
- PCTUSEDTMP,UNUSEDPAGES,PCTUNUSED,UPDATEDPERMPAGES,
- LOGSIZE,USEDLOGPAGES,PCTUSEDLOGPAGES,UNUSEDLOGPAGES,
- PCTUNUSEDLOGPAGES,RESERVEDLOGPAGES,LOGSEGMENTSIZE,COMPLETESEGMENTS,
- SAVEPOINTS,CHECKPOINTS,PAGESPERSAVEPOINT,PAGESPERCHECKPOINT)
- AS SELECT
- SIZE,MAX_DATA_PAGE_NO,USED_PERM_PAGES,PCT_USED_PERM,USED_TEMP_PAGES,
- PCT_USED_TEMP,UNUSED_PAGES,PCT_UNUSED_PAGES,UPDATED_PERM_PAGES,
- LOG_PAGES,USED_LOG_PAGES,PCT_USED_LOG,UNUSED_LOG_PAGES,
- PCT_UNUSED_LOG,RESERVED_LOG_PAGES,LOG_SEGMENT_SIZE,COMPLETED_LOG_SEGM,
- SAVEPOINTS,CHECKPOINTS,PAGES_PER_SAVEPNT,PAGES_PER_CHECKPNT
- FROM SYSDD.SERVERDBSTATISTICS
- WHERE USER IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP AND USERMODE IN('DBA', 'SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON SERVERDBSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE TABLESTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW TABLESTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW TABLESTATISTICS
- (OWNER,TABLENAME,DESCRIPTION,CHAR_VALUE,NUMERIC_VALUE)
- AS SELECT
- OWNER,TABLENAME,DESCRIPTION,"VALUE",NUMERIC_VALUE
- FROM SYSDD.TABLESTATISTICS
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON TABLESTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE TRANSACTIONS) = 0
- THEN
- BEGIN
- /
- DROP VIEW TRANSACTIONS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW TRANSACTIONS
- (SESSION,TRANSACTION,SERVERDBNO,PROCESS,USERNAME,
- CONNECTDATE, CONNECTTIME, TERMID,
- REMOTEUSER,PENDINGLOCK,LOCKMODE,LOCKREQUESTMODE)
- AS SELECT
- SESSION,TRANSACTION,SERVERDB,PROCESS,USERNAME,
- "DATE", "TIME", TERMID,
- REMOTE_USER,PENDING_LOCK,LOCK_MODE,LOCK_REQUEST_MODE
- FROM SYSDD.TRANSACTIONS
- WHERE USERNAME = USER
- OR USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERMODE IN('DBA', 'SYSDBA')
- AND USERNAME = USERGROUP)
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON TRANSACTIONS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE USERSTATISTICS) = 0
- THEN
- BEGIN
- /
- DROP VIEW USERSTATISTICS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW USERSTATISTICS
- (USERNAME,USERMODE,PERMLIMIT,PERMCOUNT,TEMPLIMIT,TEMPCOUNT)
- AS SELECT
- USERNAME,USERMODE,PERMLIMIT,PERMCOUNT,TEMPLIMIT,TEMPCOUNT
- FROM SYSDD.USERSTATISTICS
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON USERSTATISTICS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_CACHES) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_CACHES
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_CACHES
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'CACHES'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_CACHES TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_DBPROC) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_DBPROC
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_DBPROC
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'DBPROC'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_DBPROC TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_LOAD) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_LOAD
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_LOAD
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'LOAD'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_LOAD TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_LOCK) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_LOCK
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_LOCK
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'LOCK'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_LOCK TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_LOG) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_LOG
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_LOG
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'LOG'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_LOG TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_LONG) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_LONG
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_LONG
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'LONG'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_LONG TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_PAGES) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_PAGES
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_PAGES
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'PAGES'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_PAGES TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_ROW) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_ROW
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_ROW
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'ROW'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_ROW TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_SERVERDB) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_SERVERDB
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_SERVERDB
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'SERVERDB'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_SERVERDB TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_TRANS) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_TRANS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_TRANS
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'TRANS'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_TRANS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR_VTRACE) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR_VTRACE
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR_VTRACE
- (DESCRIPTION,"VALUE")
- AS SELECT
- DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE "TYPE" = 'VTRACE'
- AND USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR_VTRACE TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE MONITOR) = 0
- THEN
- BEGIN
- /
- DROP VIEW MONITOR
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW MONITOR
- ("TYPE",DESCRIPTION,"VALUE")
- AS SELECT
- "TYPE",DESCRIPTION,"VALUE"
- FROM SYSDD.MONITOR
- WHERE USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON MONITOR TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE DBTIMES) = 0
- THEN
- BEGIN
- /
- DROP VIEW DBTIMES
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW DBTIMES
- (RESTART_DATE, RESTART_TIME)
- AS SELECT
- "DATE", "TIME"
- FROM SYSDD.SERVERDBSTATISTICS
- WHERE USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON DBTIMES TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE DBPARAMETERS) = 0
- THEN
- BEGIN
- /
- DROP VIEW DBPARAMETERS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW DBPARAMETERS
- (DESCRIPTION, "VALUE")
- AS SELECT
- "DESCRIPTION", "VALUE"
- FROM SYSDD.XPARAMETERS
- WHERE USERGROUP IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON DBPARAMETERS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- IF $RC (EXISTS TABLE ROOTS) = 0
- THEN
- BEGIN
- /
- DROP VIEW ROOTS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW ROOTS
- (TABLEID,OWNER,TABLENAME,INDEXNAME,TYPE,ROOT)
- AS SELECT
- TABLEID,OWNER,TABLENAME,INDEXNAME,TYPE,ROOT
- FROM SYSDD.ROOTS
- WHERE USERGROUP IN(OWNER,LOCALSYSDBA)
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON ROOTS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- COMMIT WORK
- /
- IF $RC (EXISTS TABLE PAGES) = 0
- THEN
- BEGIN
- /
- DROP VIEW PAGES
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW PAGES
- (OWNER,TABLENAME,DESCRIPTION,CHAR_VALUE,NUMERIC_VALUE)
- AS SELECT
- OWNER,TABLENAME,DESCRIPTION,"VALUE",NUMERIC_VALUE
- FROM SYSDD.PAGES
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON PAGES TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- COMMIT WORK
- /
- IF $RC (EXISTS TABLE TREELOCKS) = 0
- THEN
- BEGIN
- /
- DROP VIEW TREELOCKS
- /
- COMMIT WORK
- /
- END
- /
- CREATE VIEW TREELOCKS
- ("ROOT","LEAF","PROCESS","STATE","MODE")
- AS SELECT
- DECODE ("ROOT", 2147483647, 'NIL PAGE NO', CHR("ROOT", 10)),
- DECODE ("LEAF", 2147483647, 'NIL PAGE NO', chr("LEAF", 10)),
- PROCESS, STATE, MODE
- FROM SYSDD.TREELOCKS
- WHERE USER IN(SELECT USERNAME
- FROM SYSDD.USERS
- WHERE USERNAME = USERGROUP
- AND USERMODE IN('DBA','SYSDBA'))
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- GRANT SELECT ON TREELOCKS TO PUBLIC WITH GRANT OPTION
- /
- IF $RC <> 0
- THEN
- BEGIN
- /
- END INIT SERVERDB
- /
- STOP 7
- /
- END
- /
- COMMIT WORK
- /
- /END INIT SERVERDB
- /
-