--テーブルの作成
CREATE TABLE MACHINE_VERSION
  (MACHINE VARCHAR2(64)
  ,OSUSER VARCHAR2(30)
  ,CLIENT_VERSION VARCHAR2(40)
  ,INSDATE DATE
  ,UPDDATE DATE
  ,CONSTRAINT PK_MACHINE_VERSION PRIMARY KEY (MACHINE,OSUSER,CLIENT_VERSION)
  )
;

--列コメントの設定
COMMENT ON COLUMN MACHINE_VERSION.MACHINE IS '端末名';
COMMENT ON COLUMN MACHINE_VERSION.OSUSER IS 'ユーザ名';
COMMENT ON COLUMN MACHINE_VERSION.CLIENT_VERSION IS 'バージョン';
COMMENT ON COLUMN MACHINE_VERSION.INSDATE IS '新規登録日時';
COMMENT ON COLUMN MACHINE_VERSION.UPDDATE IS '最終更新日時';

--PUBLICシノニムの作成
CREATE PUBLIC SYNONYM SYN_MACHINE_VERSION FOR MACHINE_VERSION;

--トリガの作成
CREATE OR REPLACE TRIGGER MACHINE_VERSION_TRG1
  BEFORE INSERT OR UPDATE ON MACHINE_VERSION FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.INSDATE := SYSDATE;
        :NEW.UPDDATE := SYSDATE;
    ELSIF UPDATING THEN
        :NEW.UPDDATE := SYSDATE;
    END IF;
END;
/

--オブジェクト権限の付与
GRANT SELECT,INSERT,UPDATE,DELETE ON MACHINE_VERSION TO PUBLIC;

--接続端末別OracleClientバージョン情報のマージ
MERGE INTO SYN_MACHINE_VERSION
  USING
    (
    SELECT UPPER(T1.MACHINE) AS 端末名
          ,UPPER(T1.OSUSER) AS ユーザ名
          ,UPPER(T2.CLIENT_VERSION) AS バージョン
      FROM GV$SESSION T1
        LEFT OUTER JOIN
          (
          SELECT INST_ID
                ,SID
                ,SERIAL#
                ,CLIENT_VERSION
            FROM GV$SESSION_CONNECT_INFO
            WHERE AUTHENTICATION_TYPE ='DATABASE'
              AND OSUSER <>'SYSTEM'
            GROUP BY INST_ID
                    ,SID
                    ,SERIAL#
                    ,OSUSER
                    ,CLIENT_VERSION
          ) T2
          ON  T1.INST_ID = T2.INST_ID
          AND T1.SID = T2.SID
          AND T1.SERIAL# = T2.SERIAL#
      WHERE T1.USERNAME IS NOT NULL
        AND T1.TYPE ='USER'
      GROUP BY T1.MACHINE
              ,T1.OSUSER
              ,T2.CLIENT_VERSION
    )
    ON (MACHINE = 端末名 AND OSUSER = ユーザ名 AND CLIENT_VERSION = バージョン)
  WHEN NOT MATCHED THEN
    INSERT(MACHINE,OSUSER,CLIENT_VERSION) VALUES(端末名,ユーザ名,バージョン)
;