<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.its.op.dao.mapper.its.dbms.DbmsSttsMapper">

    <select id="findAll" resultType="com.its.op.dto.its.dbms.TbDbmsSttsDto">
        <![CDATA[
        SELECT DBMS_ID AS dbSvrId, DBMS_NM AS dbSvrNm, TBLS_NM AS tblsNm, TBLS_RMND_CPCT AS tblsRmndCpct, USE_SESN AS useSesn, UPDT_DT AS updtDt
          FROM TB_DBMS_STTS
         ORDER BY DBMS_ID
        ]]>
    </select>

    <select id="findSessionUsage" resultType="com.its.op.dto.its.dbms.SessionSttsDto">
        <![CDATA[
        SELECT INST_ID AS INST_ID, ROUND(COUNT(1) / 200 * 100, 1) AS USE_SESN FROM GV$SESSION GROUP BY INST_ID
        ]]>
    </select>

    <select id="findTblsUsage" resultType="com.its.op.dto.its.dbms.TblSpaceSttsDto">
        <![CDATA[
        SELECT A.TABLESPACE_NAME AS TBLS_NM, A.TOTAL AS TOTAL, B.FREE AS FREE,
               100 - ROUND((A.TOTAL - NVL(B.FREE,0))*100/TOTAL,2) AS TBLS_REMAIN_STTS
        FROM (SELECT X.TABLESPACE_NAME, ROUND((SUM(X.BYTES)/1024/1024),0) AS TOTAL
              FROM DBA_DATA_FILES X
              GROUP BY X.TABLESPACE_NAME) A,
             (SELECT Y.TABLESPACE_NAME, ROUND((SUM(Y.BYTES)/1024/1024),0) AS FREE
              FROM DBA_FREE_SPACE Y
              GROUP BY Y.TABLESPACE_NAME) B
        WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
          AND A.TABLESPACE_NAME NOT IN ('UNDO', 'UNDO0', 'UNDO1', 'USR', 'TEMP', 'SYSTEM')
        ]]>
    </select>

    <update id="updateDbmsStts" parameterType="com.its.op.dto.its.dbms.TbDbmsSttsDto">
        <![CDATA[
        UPDATE TB_DBMS_STTS
           SET TBLS_NM        = #{stts.tblsNm},
               TBLS_RMND_CPCT = #{stts.tblsRmndCpct},
               USE_SESN       = #{stts.useSesn},
               UPDT_DT        = #{stts.updtDt}
        WHERE DBMS_ID         = #{stts.dbSvrId}
        ]]>
</update>

</mapper>
