package com.sitech.ismp.coll;

import com.sitech.ismp.check.downtime.CheckConnectionState;
import com.sitech.ismp.check.downtime.ParamterBean;
import com.sitech.ismp.coll.basic.TblATO_KPIDETAIL;
import com.sitech.util.DES3;
import com.sitech.util.Formater;
import com.sitech.util.JSONUtil;
import com.sitech.util.upload.RomoteController;
import org.apache.log4j.Logger;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

public class CollOracleBySql {

    private String KBP_ID = "10-11-30";

    private String hostip = null;
    private String user = null;
    private String passwd = null;
    private String kbp_id = "10-11-30";
    private String SID = null;
    private String PORT = "1521";
    private String device_id = null;
    private String NEATENSID = null;
    RomoteController tt = null;

    private Logger logger = Logger.getLogger("COLL");
    private static Logger log = Logger.getLogger(CollOracleBySql.class);

//    public void init(HashMap params) {
//        String hostip = (String) params.get("IP_ADDR");
//        String user = (String) params.get("USERNAME");
//        String passwd = (String) params.get("PASSWORD");
//        String protocol = (String) params.get("PROTOCOL");
//        if (protocol != null && protocol.equals("telnet")) {
//            tt = new TelnetThread(hostip, 23, user, passwd);
//        } else {
//            tt = new SSHThread(hostip, 22, user, passwd);
//        }
//        try {
//            tt.initial();
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }

    public java.util.Vector getDataFile(String IP_ADDR, String USERNAME,
                                        String PASSWORD, String KBP_ID, String SID) throws Exception {
        // this.KBP_ID = KBP_ID;
        this.KBP_ID = "10-11-30"; // 标识Oracle数据库
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        // collectiondbbase.addKPI(KBP_ID+"-10"+":"+SID+"-SYS","FM-00-03-001-01","up");
        getDataFile(collectiondbbase);

        Vector result = collectiondbbase.getKPISet();
        collectiondbbase.release();
        // collectiondbbase.displayKPI();
        return result;
    }

    public java.util.Vector getTableSpaces(String IP_ADDR, String USERNAME,
                                           String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
            getTableSpaces(collectiondbbase);
        Vector result = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return result;
    }

    public java.util.Vector getTableSpacesSpecial(String IP_ADDR, String USERNAME,
                                                  String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
        getTableSpacesSpecial(collectiondbbase);
        Vector result = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return result;
    }

    public java.util.Vector getUndoTableSpaces(String IP_ADDR, String USERNAME,
                                               String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
        getUndoTableSpaces(collectiondbbase);
        Vector result = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return result;
    }

    public Vector getSystem(String IP_ADDR, String USERNAME,
                                      String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getSystem(collectiondbbase);
        collectiondbbase.addKPI(KBP_ID + "-10" + ":" + NEATENSID + "-SYS","CM-00-03-001-50", IP_ADDR);
        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    public java.util.Vector getSpecialTable(String IP_ADDR, String USERNAME,
                                            String PASSWORD, String KBP_ID, String SID, String tableOwner,
                                            String tableName) throws Exception {
        logger.info("tableOwner = " + tableOwner + " IP_ADDR = " + IP_ADDR + " tableName " + tableName);
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getSpecialTable(collectiondbbase, tableOwner, tableName);

        Vector aa = collectiondbbase.getKPISet();

        collectiondbbase.release();
        // collectiondbbase.displayKPI();
        return aa;
    }

    public java.util.Vector getCMStandKPI(String IP_ADDR, String USERNAME,
                                          String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        System.out.println(SID + "****************");

        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getCMStandKPI(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    private boolean getParams(HashMap<String, String> params) {
        try {
            hostip = params.get("IP_ADDR");
            user = params.get("USERNAME");
            passwd = DES3.decrypt(params.get("PASSWORD"));
            SID = params.get("SID");

            if (params.get("PORT") == null) {
                PORT = "1521";
            } else {
                PORT = (String) params.get("PORT");
            }
            device_id = (String) params.get("DEVICE_ID");
            this.NEATENSID = Formater.neatenunitid(this.device_id);
        } catch (Exception e) {
            throw new RuntimeException("Exception while init params:" + JSONUtil.toJSON(params), e);
        }

        return true;
    }

    public java.util.Vector getDataFile(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getDataFile(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getTableSpaces(java.util.HashMap params) throws Exception {
        getParams(params);
        return getTableSpaces(hostip, user, passwd, kbp_id, SID);
    }

    public Vector getTableSpacesSpecial(HashMap params) throws Exception {
        getParams(params);
        return getTableSpacesSpecial(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getUndoTableSpaces(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getUndoTableSpaces(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getSystem(java.util.HashMap params)throws Exception {
        getParams(params);
        return getSystem(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getSpecialTable(java.util.HashMap params)
            throws Exception {
        String TABLEOWNER = (String) params.get("TABLEOWNER");
        String TABLENAME = (String) params.get("TABLENAME");
        getParams(params);
        return getSpecialTable(hostip, user, passwd, kbp_id, SID, TABLEOWNER,
                TABLENAME);
    }

    public java.util.Vector getCMStandKPI(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getCMStandKPI(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getExtendKPI(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getExtendKPI(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getExtendKPI(String IP_ADDR, String USERNAME,
                                         String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getExtendKPI(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    //全库数据量 CM-00-03-006-02 单位G  1个月一次
    public java.util.Vector getTotalData(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getTotalData(hostip, user, passwd, kbp_id, SID);
    }

    //全库数据量 CM-00-03-006-02 单位G  1个月一次
    public java.util.Vector getTotalData(String IP_ADDR, String USERNAME,
                                         String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getTotalData(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    //数据库Headroom时间 PM-00-03-009-10   ASM归档磁盘组  PM-00-03-001-05
    public java.util.Vector getHeadroom(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getHeadroom(hostip, user, passwd, kbp_id, SID);
    }

    //数据库Headroom时间 PM-00-03-009-10   ASM归档磁盘组  PM-00-03-001-05
    public java.util.Vector getHeadroom(String IP_ADDR, String USERNAME,
                                        String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getHeadroom(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    //数据库Headroom时间 PM-00-03-009-10   ASM归档磁盘组  PM-00-03-001-05
    public java.util.Vector getAsmDiskGroup(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getAsmDiskGroup(hostip, user, passwd, kbp_id, SID);
    }

    //数据库Headroom时间 PM-00-03-009-10   ASM归档磁盘组  PM-00-03-001-05
    public java.util.Vector getAsmDiskGroup(String IP_ADDR, String USERNAME,
                                        String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getAsmDiskGroup(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }


    /**
     * maozr 新扩，数据库状态采集 2008-05-15
     *
     * @param params
     * @return
     * @throws Exception
     */
    public java.util.Vector getStatus(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getStatus(hostip, user, passwd, kbp_id, SID);
    }

    /**
     * 获取数据库所在主机的ping状态
     * @param params
     * @return
     */
    public Vector getPingStatus(HashMap params)throws Exception {
        String isPing = params.get("isPing")+""; // frank 2017-05-19 添加。Y进行ping操作，N不执行
        Vector<TblATO_KPIDETAIL> vector=new Vector<TblATO_KPIDETAIL>();
        if("Y".equals(isPing)){
            getParams(params);
            CheckConnectionState server=new CheckConnectionState();
            String KBP_ID = "10-11-30-10:" + this.NEATENSID + "-SYS";
            SQLTarget collectiondbbase = new SQLTarget();
            ParamterBean bean=new ParamterBean();
            bean.setType("oracle");
            bean.setDEVICE_IP(hostip);
            String stat=server.getPingState(bean);
            collectiondbbase.addKPI(KBP_ID, "FM-00-11-001-999", stat);
            log.info(" ************************* getPingStatus ****UNIT_ID ="+KBP_ID+"****stat="+stat+"**");
            vector=collectiondbbase.getKPISet();
        }
        return vector;
    }


    /**
     * 数据库jdbc连接状态
     * @param IP_ADDR
     * @param USERNAME
     * @param PASSWORD
     * @param KBP_ID
     * @param SID
     * @return
     * @throws Exception
     */
    private java.util.Vector getStatus(String IP_ADDR, String USERNAME,
                                       String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        System.out.println("--采集数据库状态--");
        KBP_ID = KBP_ID + "-10:" + this.NEATENSID + "-SYS";
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
        // 获取数据库连接状态 UP:连接正常/DOWN:连接异常
        if (collectiondbbase.getState()){
            collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-01", "UP");
        } else {
            collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-01", "DOWN");
        }
        Vector kpi_value = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return kpi_value;
    }

    private void getExtendKPI(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";
        try {
//        String sqlLockDetail = "select oracle_username,os_user_name from v$locked_object";
            String sqlLockDetail = "select count(*) from v$session a, v$session_wait b where a.sid=b.sid and b.event not like 'SQL%' and b.event not like '%time%' and b.event not like '%message%'";
            // 锁数量及详细情况

            Vector lockDetail = collectiondbbase.getSQLKPIResult(sqlLockDetail);

            String bLockDetail = "select a.object_name,o.object_id,o.session_id,o.ORACLE_USERNAME,"
                    + "o.OS_USER_NAME, o.PROCESS, o.locked_mode from "
                    + "v$locked_object o,all_objects a "
                    + "where a.object_id=o.object_id and o.session_id in "
                    + "(select sid from v$lock where block=1)";

            Vector blockedLockDetail = collectiondbbase.getSQLKPIResult(bLockDetail);
            // --------------2008-01-24 22:21
            // for (int i = 0; i < lockDetail.size(); i++) {
            // Vector lockDetailVector = (Vector) lockDetail.elementAt(i);
            //
            // String oracle_username = (String) lockDetailVector.get(0);
            // String os_user_name = (String) lockDetailVector.get(1);
            // collectiondbbase.addKPI(KBP_ID + "-" + i, "PM-00-03-008-011",
            // oracle_username);
            // collectiondbbase.addKPI(KBP_ID + "-" + i, "PM-00-03-008-012",
            // os_user_name);
            // }
            // PM-00-03-008-01 锁数量
            collectiondbbase.addKPI(KBP_ID, "PM-00-03-008-01", String.valueOf(blockedLockDetail.size()));
            // PM-00-03-008-02 等待数量
            collectiondbbase.addKPI(KBP_ID, "PM-00-03-008-02", String.valueOf(lockDetail.size()));


            // //连接数及详细情况
            String sqlSession = "select count(*),username,osuser,machine  from v$session	group by username,osuser,machine";
            Vector sessionDetail = collectiondbbase.getSQLKPIResult(sqlSession);
            //add start 活动会话数量
            String sqlActiveSession = "select STATUS,count(*) from v$session where status='ACTIVE' group by STATUS";
            Vector activeSessionDetail = collectiondbbase.getSQLKPIResult(sqlActiveSession);
            int activeSessionNum = 0;
            Vector activeSessionDetailVector = (Vector) activeSessionDetail.elementAt(0);
            String num = (String) activeSessionDetailVector.get(1);
            activeSessionNum = Integer.parseInt(num);
            collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-04", String.valueOf(activeSessionNum));
            //add end
            int sessionNum = 0;
            for (int i = 0; i < sessionDetail.size(); i++) {
                Vector sessionDetailVector = (Vector) sessionDetail.elementAt(i);

                String number = (String) sessionDetailVector.get(0);
                sessionNum = sessionNum + Integer.parseInt(number);

                // --------------2008-01-24 22:21
                // collectiondbbase
                // .addKPI(KBP_ID + "-" + i, "PM-00-03-009-031", number);
                // collectiondbbase.addKPI(KBP_ID + "-" + i, "PM-00-03-009-032",
                // username);
                // collectiondbbase
                // .addKPI(KBP_ID + "-" + i, "PM-00-03-009-033", osuser);
                // collectiondbbase.addKPI(KBP_ID + "-" + i, "PM-00-03-009-034",
                // machine);
            }

            collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-03", String
                    .valueOf(sessionNum));

            //================== 会话百分比=活动会话数/最大会话数 2017.05.23 swy ===========================
            String sqlMaxSession = "select value from v$parameter where name='sessions'";
            Vector maxSessionDetail = collectiondbbase.getSQLKPIResult(sqlMaxSession);
            int maxSessionNum = 0;
            Vector numberVector = (Vector)maxSessionDetail.get(0);
            String number=(String)numberVector.get(0);
            maxSessionNum = Integer.parseInt(number);
            float activeSessionPercentage= 0;
            DecimalFormat df = new DecimalFormat("0.00");
            try{
                activeSessionPercentage=((float)activeSessionNum*100/maxSessionNum);
            }catch (Exception ex){
                activeSessionPercentage=0;
                logger.error("计算会话百分比出错;ex="+ex.getMessage());

            }finally {
                collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-888", String.valueOf(df.format(activeSessionPercentage)));
            }



            // 4. buffer Hit %
            collectiondbbase
                    .addKPI(
                            KBP_ID,
                            "PM-00-03-009-08",
                            collectiondbbase
                                    .getSQLKPIInfo("select  round(100*(1-(a.value-b.value-nvl(c.value,0))/d.value),2)  "
                                            + "from v$sysstat a,v$sysstat b ,v$sysstat c, v$sysstat d "
                                            + "where a.name='physical reads' and b.name='physical reads direct' "
                                            + "and c.name='physical reads direct (lob)'  and d.name='session logical reads'"));
            // 9. Redo Nowait %
            collectiondbbase
                    .addKPI(
                            KBP_ID,
                            "PM-00-03-009-05",
                            collectiondbbase
                                    .getSQLKPIInfo(" SELECT round(t1.value * 100 / t2.value, 6) FROM V$SYSSTAT t1, V$SYSSTAT t2 WHERE"
                                            + " t1.name = 'redo log space requests' AND t2.name = 'redo entries' "));
            collectiondbbase
                    .addKPI(
                            KBP_ID,
                            "PM-00-03-009-06",
                            collectiondbbase
                                    .getSQLKPIInfo(" SELECT  round(100*(t1.value/(t2.value+t3.value)),2)  in_memory FROM V$SYSSTAT t1, "
                                            + "V$SYSSTAT t2, V$SYSSTAT t3 WHERE t1.name like 'sorts%memory%'  AND "
                                            + "t2.name like 'sorts%memory%'  AND t3.name like 'sorts%disk%' "));
            // 11. 共享内存已利用百分比
            collectiondbbase
                    .addKPI(
                            KBP_ID,
                            "PM-00-03-009-07",
                            Formater
                                    .formatDecimalKpivalue(collectiondbbase
                                            .getSQLKPIInfo(" SELECT 100*( 1 - SUM(DISTINCT(f.bytes)) / SUM(t.bytes) )   Memory_Usage  FROM v$sgastat f , v$sgastat t"
                                                    + "	 where f.name = 'free memory' AND f.pool IN ('shared pool', 'all pools') AND t.pool IN ('shared pool', 'all pools') ")));
            //“最大连接数占用率”、“数据库等待数”、“UNDO表空间情况”放到已有的getExtendKPI方法中实现
            Vector conn = collectiondbbase.getSQLKPIResult("select TO_NUMBER(limit_value) limit_value,round(current_utilization / limit_value * 100,2) limit_rate" +
                    " from v$resource_limit where resource_name = 'processes'");
            Vector connVector = (Vector) conn.elementAt(0);
            String maxConn = (String) connVector.get(1);


            // 最大连接数占用率  PM-00-03-009-09
            collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-09", Formater.formatDecimalKpivalue(maxConn));
            // 数据库等待数  PM-00-03-009-10
            collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-10", Formater.formatDecimalKpivalue(collectiondbbase
                    //原有采集逻辑
                    // .getSQLKPIInfo("select count(1) from v$session_wait  where event not like '%rdbms%' and event not like '%SQL%'")));
                    //江西现场采集逻辑
                    .getSQLKPIInfo("select max(num) from (select event, count(*) num from v$session where status in ('ACTIVE', 'KILLED') and wait_class# <> 6 group by event)")));

            String UNIT_ID = this.kbp_id + "-22:" + NEATENSID + "-";
            // UNDO表空间情况  PM-00-03-009-11
            String sql = "select tablespace_name, status, round(sum(bytes) / 1024 / 1024 / 1024, 3) used_size from dba_undo_extents " +
                    "            where status = 'ACTIVE' or status = 'UNEXPIRED' or status = 'EXPIRED' " +
                    "            group by tablespace_name, status ";
            Vector undo = collectiondbbase.getSQLKPIResult(sql);
            System.out.println("undo's sql is " + sql + ", and result's size is " + undo.size());
            if (undo != null && undo.size() > 0) {
                for (int i = 0; i < undo.size(); i++) {
                    Vector undoVector = (Vector) undo.elementAt(i);
                    String undoTablespaceName = (String) undoVector.get(0);
                    String undoTablespaceStatus = (String) undoVector.get(1);
                    String undoTablespaceSize = (String) undoVector.get(2);
                    System.out.println("undoTablespaceName is " + undoTablespaceName + ", and undoTablespaceStatus is " + undoTablespaceStatus + ", and undoTablespaceSize is " + undoTablespaceSize);
                    String unit_id = UNIT_ID + undoTablespaceName + "-" + undoTablespaceStatus;
                    // 表空间状态 FM-00-03-001-02
                    collectiondbbase.addKPI(unit_id, "FM-00-03-001-02", undoTablespaceStatus);
                    // UNDO表空间名称 CM-00-03-005-07
                    collectiondbbase.addKPI(unit_id, "CM-00-03-005-07", Formater.formatDecimalKpivalue(undoTablespaceName));
                    // UNDO表空间情况 PM-00-03-009-11
                    collectiondbbase.addKPI(unit_id, "PM-00-03-009-11", Formater.formatDecimalKpivalue(undoTablespaceSize));
                }
            } else {
                logger.info("UNDO tablespace is null ");
            }

            String sqlExpired = "select sum(bytes / 1024 / 1024) from dba_undo_extents where status = 'EXPIRED'";
            String sqlUndo = "select sum(round(t.TABLESPACE_SIZE * 8 / 1024, 2)) from dba_tablespace_usage_metrics t where t.TABLESPACE_NAME like 'UNDO%'";
            Vector undoExpired = collectiondbbase.getSQLKPIResult(sqlExpired);
            Vector undos = collectiondbbase.getSQLKPIResult(sqlUndo);
            System.out.println("undoExpired's sql is " + sqlExpired + ", and result's size is " + undoExpired.size());
            System.out.println("undo's sql is " + sqlUndo + ", and result's size is " + undos.size());
            if (undoExpired != null && undoExpired.size() > 0 && undos != null && undos.size() > 0) {
                Vector undoExpiredVector = (Vector) undoExpired.elementAt(0);
                String undoExpireSize = (String) undoExpiredVector.get(0);
                Vector undoVector = (Vector) undos.elementAt(0);
                String undoSize = (String) undoVector.get(0);
                if (!"".equals(undoExpireSize) && !"".equals(undoSize)) {
                    Float undoused = Float.valueOf(undoExpireSize) / Float.valueOf(undoSize);
                    System.out.println("undoExpireSize is " + undoExpireSize + ", and undoSize is " + undoSize);
                    String unit_id = UNIT_ID + "UNDO";
                    // 表空间状态 FM-00-03-001-02
                    collectiondbbase.addKPI(unit_id, "FM-00-03-001-02", "EXPIRED");
                    // UNDO表空间名称 CM-00-03-005-07
                    collectiondbbase.addKPI(unit_id, "CM-00-03-005-07", "ALL-EXPIRED-UNDOTBS");
                    // UNDO表空间情况 PM-00-03-009-11
                    collectiondbbase.addKPI(unit_id, "PM-00-03-009-11", Formater.formatDecimalByScale(String.valueOf(undoused * 100), 2));
                } else {
                    logger.info("UNDO tablespace is null ");
                }

            } else {
                logger.info("UNDO tablespace is null ");
            }

            // INST_ID	INSTANCE_NUMBER	INSTANCE_NAME	HOST_NAME	STATUS
            // 1	1	1	e3cloud	kf-db2	OPEN
            String sqlRacStatus = "SELECT INST_ID, INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, STATUS FROM GV$INSTANCE";
            Vector racStatus = collectiondbbase.getSQLKPIResult(sqlRacStatus);
            System.out.println("racStatus's sql is " + sqlRacStatus + ", and result's size is " + racStatus.size());
            if (racStatus != null && racStatus.size() > 0) {
                for (int i = 0; i < racStatus.size(); i++) {
                    Vector _racStatus = (Vector) racStatus.elementAt(i);
                    String racInstId = (String) _racStatus.get(0);
                    String racInstNo = (String) _racStatus.get(1);
                    String racInstName = (String) _racStatus.get(2);
                    String racHostName = (String) _racStatus.get(3);
                    String rac_Status = (String) _racStatus.get(4);
                    String unit_id = this.kbp_id + "-32:" + NEATENSID + "-" + racInstName;
                    // 实例名 CM-00-03-001-20
                    collectiondbbase.addKPI(unit_id, "CM-00-03-001-20", racInstName);
                    // 主机名称 CM-00-01-001-01
                    collectiondbbase.addKPI(unit_id, "CM-00-01-001-01", racHostName);
                    // RAC节点状态 FM-00-03-001-12
                    collectiondbbase.addKPI(unit_id, "FM-00-03-001-12", rac_Status);
                }
            } else {
                logger.info("GV$INSTANCE is null ");
            }

            String sqlUserProcessNum = "select * from v$process where BACKGROUND = 1";
            Vector userProcessNum = collectiondbbase.getSQLKPIResult(sqlUserProcessNum);
            System.out.println("sqlUserProcessNum's sql is " + sqlUserProcessNum + ", and result's size is " + userProcessNum.size());
            if (userProcessNum != null && userProcessNum.size() > 0) {
                String unit_id = this.kbp_id + "-10:" + NEATENSID + "-SYS";
                // 用户进程数 PM-00-01-005-16
                collectiondbbase.addKPI(unit_id, "PM-00-01-005-16", userProcessNum.size() + "");
            } else {
                logger.info("v$process is null ");
            }
        }catch (Exception ex){
            logger.error("coll oracle extend kpi error.",ex);
        }
    }

    //全库数据量
    private void getTotalData(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";

        // 全库数据量 CM-00-03-006-02 单位G  1个月一次
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-00-03-009-12",
                        Formater
                                .formatDecimalKpivalue(collectiondbbase
                                        .getSQLKPIInfo("select round(sum(BYTES/1024/1024/1024),3) total  from dba_segments")));

    }

    private void getHeadroom(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";

        //数据库Headroom时间 PM-00-03-009-13
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-00-03-009-13",
                        Formater
                                .formatDecimalKpivalue(collectiondbbase
                                        .getSQLKPIInfo("select round(((((((to_number(to_char(sysdate, 'YYYY')) - 1988) * 12 * 31 * 24 * 60 * 60) +" +
                                                "             ((to_number(to_char(sysdate, 'MM')) - 1) * 31 * 24 * 60 * 60) +" +
                                                "             (((to_number(to_char(sysdate, 'DD')) - 1)) * 24 * 60 * 60) +" +
                                                "             (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +" +
                                                "             (to_number(to_char(sysdate, 'MI')) * 60) +" +
                                                "             (to_number(to_char(sysdate, 'SS')))) * (16 * 1024)) -" +
                                                "             dbms_flashback.get_system_change_number) /" +
                                                "             (16 * 1024 * 60 * 60))) Headroom" +
                                                "  from dual")));
    }

    private void getAsmDiskGroup(SQLTarget collectiondbbase) {
        // ASM归档磁盘组  PM-00-03-001-05
        String sql = "select name, trunc((TOTAL_MB - FREE_MB) / TOTAL_MB * 100, 2) rate from v$asm_diskgroup";
        Vector asm = collectiondbbase.getSQLKPIResult(sql);
        logger.info("getAsmDiskGroup's sql is " + sql + ", and result's size is " + asm.size());
        for (int i = 0; i < asm.size(); i++) {
            Vector asmVector = (Vector) asm.elementAt(i);
            String asmDiskName = (String) asmVector.get(0);
            String asmDiskUsedRate = (String) asmVector.get(1);
            logger.info("asmDiskName is " + asmDiskName + ", and asmDiskUsedRate is " + asmDiskUsedRate);
            // 磁盘名称 CM-00-01-001-20
            collectiondbbase.addKPI(this.KBP_ID + "-30" + ":" + NEATENSID + "-" + asmDiskName, "CM-00-01-001-20", asmDiskName);
            // 磁盘使用率 PM-00-01-004-14
            collectiondbbase.addKPI(this.KBP_ID + "-30" + ":" + NEATENSID + "-" + asmDiskName, "PM-00-01-004-14", asmDiskUsedRate);
        }
    }

    public void getKPI(HashMap params) {
        getParams(params);
        getKPI(hostip, SID, user, passwd, kbp_id);
        // saveKPI();
    }

    public void getKPI(String hostip, String sid, String user, String passwd,
                       String kbp_id) {

        this.KBP_ID = kbp_id;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(hostip, PORT, sid, user, passwd);

        getSystem(collectiondbbase);
        getTableSpaces(collectiondbbase);
        getUndoTableSpaces(collectiondbbase);
        getDataFile(collectiondbbase);

        collectiondbbase.release();

    }

    /*
     * CM-00-03-001-01 数据库名 : select NAME from v$database
     * CM-00-03-001-02 版本信息 : select BANNER from v$version where BANNER like 'Oracle%'
     * CM-00-03-001-03 数据库的位数0 : select BANNER from v$version where BANNER like 'Oracle%';
     * CM-00-03-001-04 安装的选项 : select OPEN_MODE from v$database;
     * CM-00-03-001-05 归档方式 : select LOG_MODE from v$database;
     * CM-00-03-001-06 归档日志目录0 : select name||value from v$parameter where name like 'log%'
     * CM-00-03-001-11 数据库监听地址 : ObjectIP
     * CM-00-03-001-17 内存配置数量0 : select sum(value) from v$sga
     * CM-00-03-002-01 共享内存的大小- : select VALUE into from v$parameter where NAME = 'shared_pool_size';
     * PM-00-03-008-01 锁数量 : select count(*) from v$locked_object; | select sid,lmod,request from v$lock;
     * PM-00-03-009-01 CPU占用率 :
     * PM-00-03-001-02 共享内存的命中率: SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 FROM v$rowcache;
     * 					方法二：1 SELECT 1 - (phy.value / (cur.value + con.value)) "CACHE HIT RATIO" FROM v$sysstat cur, v$sysstat con, v$sysstat phy WHERE cur.name = 'db block gets' AND con.name = 'consistent gets' AND phy.name = 'physical reads';
     * PM-10-12-10-13 share pool 命中率：p select namespace,gethitratio from v$librarycache where namespace='SQL AREA'; select gethidratio from v$library PM-10-12-10-14 db buffer cache 命中率：r
     * PM-10-12-10-15 lath free数量- ：t
     * PM-10-12-10-16 lath free等待时间：f
     * PM-10-12-10-17 buffer busy wait ：f
     * PM-10-12-10-18 free buffer busy ：f
     * PM-10-12-10-19 log file sync 等待时间：v
     * PM-10-12-10-20 log file sync 等待数量：v
     * PM-00-03-009-03 数据库连接用户数:ui
     * FM-00-03-001-01 数据库状态 :
     * FM-10-12-10-22 有无死锁产生0 :
     * PM-10-12-10-23 锁等待数量:select count(*) from v$lock where request<>0
     * PM-10-12-10-24 等待事件数量:select count(*) from v$session_wait where event not like 'SQL%' and event not like '%time%' and event not like '%message%'
     * PM-10-12-10-25 数据库总容量:select sum(a.bytes/1024/1024)-sum(b.bytes/1024/1024) from dba_data_files a,b
     * PM-10-12-10-26 数据库空闲容量:select sum(bytes/1024/1024) from dba_free_spaces
     * CM-10-12-10-27 等待事件分类:select event,count(*) from v$session_wait where event not like 'SQL%' and event not like '%time%' and event not like '%message%'
     * PM-10-12-10-28 数据库Extent扩展大小:select owner,segment_name,segment_type,max_extents-extents from dba_segments where max_extents-extents<10
     * PM-10-12-10-29 数据库Extent扩展大小记录数:select count(*) from dba_segments where max_extents-extents<10
     * PM-10-12-10-30 数据库Extent扩展数量:select owner,segment_name,segment_type,extents from dba_segments where extents>4000
     * PM-10-12-10-31 数据库Extent扩展数量记录数:select count(*) from dba_segments where extents>4000
     * CM-00-03-005-01 1 CM-00-03-005-01 回滚段名- 数据库日志空间或回滚段名 1天 字符串型 select value from v$parameter where NAME = 'undo_tablespace'
     * CM-00-03-005-02 2 CM-00-03-005-02 回滚段大小0 数据库的回滚段的大小（MB）e 1小时 数值型 SELECT sum(bytes) FROM dba_data_files where tablespace_name=#UNDOtablespace_name
     *
     */
    private void getSystem(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-10" + ":" + NEATENSID + "-SYS";


        collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-01", collectiondbbase
                .getSQLKPIInfo("select NAME from v$database"));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "CM-00-03-001-11",
                        collectiondbbase
                                .getSQLKPIInfo("select value from v$parameter t where t.NAME = 'cluster_database'"));

        String verionInfo = collectiondbbase
                .getSQLKPIInfo("select BANNER from v$version where BANNER like 'Oracle%'");
        int m = verionInfo.indexOf("Release");
        int n = verionInfo.indexOf("-");
        verionInfo = verionInfo.substring(m + "Release".length() + 1, n);
        collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-02", verionInfo);

        // begin 获取位数信息
        // ---当前支持 xxx 64bit,xxx 64 bit,xxx 64 bit,xxx 32bit,32 bit,xxx 32
        // bit---
        // 9i的话，格式类似于 Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit
        // Production
        String bitInfo = collectiondbbase
                .getSQLKPIInfo("select BANNER from v$version where BANNER like 'Oracle%'");
        String bit_info = bitInfo;
        int bit_pos = bitInfo.indexOf("bit");
        if (bit_pos > 0) {
            // bit_info = versionInfo.substring(bit_pos-2 ,bit_pos+3 );

            // 定位bit之前的第一个空格
            int first_pos = bitInfo.lastIndexOf(' ', bit_pos);
            if (first_pos < 0) {
                first_pos = 0;
            }
            bit_info = bitInfo.substring(first_pos, bit_pos + 3);

            if (bit_info.trim().equals("bit")) {
                // 说明bit之前有个空格
                int second_pos = bitInfo.lastIndexOf(' ', bit_pos - 4);
                if (second_pos < 0) {
                    second_pos = 0;
                }
                bit_info = bitInfo.substring(second_pos, bit_pos + 3);
            }
            // bit_info = versionInfo.substring(bit_pos,bit_pos+2);
        }
        bit_info = bit_info.trim();
        collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-03", bit_info);
        // end 获取位数信息

        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "CM-00-03-001-04",
                        collectiondbbase
                                .getSQLKPIInfo("select OPEN_MODE from v$database"));
        collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-05", collectiondbbase
                .getSQLKPIInfo("select LOG_MODE from v$database"));

        /*String logDir = collectiondbbase
                .getSQLKPISetInfo("select value from v$parameter where name = 'log_archive_dest'");
        logDir = logDir == null ? "" : logDir;
        logDir = logDir.replaceAll("\r", " ").replaceAll("\n", " ").replaceAll("\t", " ");
        collectiondbbase.addKPI(KBP_ID, "CM-00-03-001-06", logDir);*/

        collectiondbbase
                .addKPI(KBP_ID,
                        "CM-00-03-001-06",
                        collectiondbbase
                                .getSQLKPIInfo("select value from v$parameter where name = 'db_recovery_file_dest'"));

        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "CM-00-03-001-17",
                        collectiondbbase
                                .getSQLKPIInfo("select sum(value) from v$sga                            "));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "CM-00-03-002-01",
                        Long.parseLong(collectiondbbase
                                .getSQLKPIInfo("select VALUE from v$parameter where NAME = 'shared_pool_reserved_size'")) / 1024 + "");

        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "CM-00-03-002-02",
                        collectiondbbase
//                                .getSQLKPIInfo("select VALUE from v$parameter where NAME = 'instance_name'"));
                                .getSQLKPIInfo("select to_char(sysdate, 'yyyy-MM-dd HH24:mm:ss') from dual"));

        // collectiondbbase.addKPI(KBP_ID,"PM-00-03-009-01",collectiondbbase.getSQLKPIInfo(""));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-00-03-001-02",
                        Formater
                                .getByScale(
                                        collectiondbbase
                                                .getSQLKPIInfo("SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100 FROM v$rowcache"),
                                        2));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-10-12-10-13",
                        Formater
                                .getByScale(
                                        collectiondbbase
                                                .getSQLKPIInfo("select gethitratio*100 from v$librarycache where namespace='SQL AREA'"),
                                        2));
        // collectiondbbase.addKPI(KBP_ID,"FM-10-12-10-22",collectiondbbase.getSQLKPIInfo(""));

        collectiondbbase.addKPI(KBP_ID, "PM-10-12-10-23", collectiondbbase
                .getSQLKPIInfo("select/*+ rule */ count(*) from v$lock where request<>0"));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-10-12-10-24",
                        collectiondbbase
                                .getSQLKPIInfo("select count(*) from v$session_wait where event not like '%time%' and event not like '%message%'"));
        collectiondbbase
                .addKPI(
                        KBP_ID,
                        "PM-10-12-10-25",
                        collectiondbbase
                                .getSQLKPIInfo("select sum(a.bytes/1024/1024) from dba_data_files a"));
//		collectiondbbase
//				.addKPI(
//						KBP_ID,
//						"PM-10-12-10-26",
//						collectiondbbase
//								.getSQLKPIInfo("select sum(bytes/1024/1024) from dba_free_space"));
        /*
		 * 去掉了 collectiondbbase .addKPI( KBP_ID, "CM-10-12-10-27",
		 * collectiondbbase .getSQLKPISetInfo("select event||' 分类数 '||count(*)
		 * from v$session_wait where event not like '%time%' and event not like
		 * '%message%' group by event"));
		 */

        System.out.println("------------mmc----------");

        // collectiondbbase.addKPI(KBP_ID,"PM-10-12-10-28",collectiondbbase.getSQLKPIInfo("select
        // owner|| ||segment_name|| ||segment_type|| ||max_extents-extents from
        // dba_segments where max_extents-extents<10"));
        // collectiondbbase.addKPI(KBP_ID,"PM-10-12-10-29",collectiondbbase.getSQLKPIInfo("select
        // count(*) from dba_segments where max_extents-extents<10"));
        // collectiondbbase.addKPI(KBP_ID,"PM-10-12-10-30",collectiondbbase.getSQLKPIInfo("select
        // owner,segment_name,segment_type,extents from dba_segments where
        // extents>4000"));
        // collectiondbbase.addKPI(KBP_ID,"PM-10-12-10-31",collectiondbbase.getSQLKPIInfo("select
        // count(*) from dba_segments where extents>4000"));
        // 注释掉回滚段名及大小
        // String undotablespacename = collectiondbbase
        // .getSQLKPIInfo("select value from v$parameter where NAME =
        // 'undo_tablespace'");
        // collectiondbbase.addKPI(KBP_ID, "CM-00-03-005-01",
        // undotablespacename);
        // collectiondbbase
        // .addKPI(
        // KBP_ID,
        // "CM-00-03-005-02",
        // collectiondbbase
        // .getSQLKPIInfo("SELECT sum(bytes)/1024/1024 FROM dba_data_files where
        // tablespace_name='"
        // + undotablespacename + "'"));
        String sql_all = "SELECT file_name,status,tablespace_name,bytes/1024/1024,(bytes/1024/1024-user_bytes/1024/1024),user_bytes/1024/1024,(bytes-user_bytes)/bytes*100 FROM dba_data_files a ORDER BY file_name";
        Vector CM = collectiondbbase.getSQLKPIResult(sql_all);
//		String sql_rw = "Select Df.Name File_Name, Fs.Phyblkrd, Fs.Phyblkwrt From V$Filestat Fs, V$Datafile Df Where Df.File# = Fs.File# Order By File_Name";
//		Vector RW = collectiondbbase.getSQLKPIResult(sql_rw);
        // 数据文件CM指标
        for (int i = 0; i < CM.size(); i++) {
            Vector row = (Vector) CM.get(i);
            String filename = getFileName((String) row.get(0));
            KBP_ID = this.KBP_ID + "-12" + ":" + NEATENSID + "-DATAFILE";
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "CM-00-03-006-01",
                    (String) row.get(0));
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "CM-10-12-12-11",
                    (String) row.get(2));
        }

        // 回滚段指标 “-17”
        String sql_rb = "select name, nvl(rssize * 100 / hwmsize, 0), nvl(waits * 100 / gets, 0) " +
                "from v$rollstat a, v$rollname b where a.usn = b.usn order by name";
        Vector RB = collectiondbbase.getSQLKPIResult(sql_rb);
        for (int i = 0; i < RB.size(); i++) {
            Vector rb = (Vector) RB.get(i);
            String rbname = getRollbackName((String) rb.get(0));
            KBP_ID = this.KBP_ID + "-17" + ":" + NEATENSID + "-ROLLBACK";
            // 回滚段名称
            collectiondbbase.addKPI(KBP_ID + "-" + rbname, "CM-00-03-005-01", rbname);
            // 利用情况
            collectiondbbase.addKPI(KBP_ID + "-" + rbname, "PM-00-03-007-01",
                    Formater.formatDecimalKpivalue((String) rb.get(1)));
            // 竞争情况
            collectiondbbase.addKPI(KBP_ID + "-" + rbname, "PM-00-03-007-02",
                    Formater.formatDecimalByScale((String) rb.get(2), 8));
        }
    }

    String getRollbackName(String rbname) {
        if (rbname.indexOf("_") != -1) {
            String[] ss = Formater.split2Array(rbname, "_");
            return ss[1];
        } else {
            return rbname;
        }
    }

	/*
	 * CM-00-03-003-01 表空间名 select name from v$tablespace order by NAME
	 * CM-00-03-003-02 表空间大小 SELECT NAME,NVL(SUM(bytes/1024/1024),0) FROM
	 * dba_data_files a,v$tablespace b WHERE b.NAME=a.TABLESPACE_NAME(+) GROUP
	 * BY NAME order by NAME PM-10-12-11-12 表空间剩余空间A SELECT
	 * NAME,NVL(SUM(bytes)/1024/1024,0) FROM v$tablespace a ,dba_free_space b
	 * WHERE a.NAME=b.tablespace_name(+) GROUP BY NAME order by NAME
	 * PM-10-12-11-13 表空间已用空间a CM-00-03-003-02 - PM-10-12-11-12 PM-00-03-004-01
	 * 表空间使用率- 1 - PM-10-12-11-12/CM-00-03-003-02
	 */

	/*
	 * CM-00-03-003-01 所有表空间 select name from v$tablespace
	 * 
	 * SELECT a.tablespace_name,sum(a.bytes),sum(bb.bytes) FROM dba_data_files
	 * a,dba_free_space b where a.file_id=b.file_id group by a.tablespace_name
	 * 
	 */

    private void getTableSpaces(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-11" + ":" + NEATENSID + "-TABLESPACES";
        try {
            //山西移动 #1352 lijy  提升性能
            String sql = "select t.TABLESPACE_NAME," +
                " round(t.USED_SPACE * v.VALUE / 1024 / 1024, 2)," +
                " round(t.TABLESPACE_SIZE * v.VALUE / 1024 / 1024, 2)," +
                " round(t.USED_PERCENT, 2)" +
                " from dba_tablespace_usage_metrics t, v$parameter v" +
                " where v.name = 'db_block_size'"+
                " and TABLESPACE_NAME not in " +
                " (SELECT distinct TABLESPACE_NAME from dba_undo_extents )";
        Vector tablespace = collectiondbbase.getSQLKPIResult(sql);
        for (int i = 0; i < tablespace.size(); i++) {
            Vector vsize = (Vector) tablespace.elementAt(i);
            String name = (String) vsize.get(0);
            String usedsize = (String) vsize.get(1);
            String totalsize = (String) vsize.get(2);
            String rate = (String) vsize.get(3);
            String freesize = Formater.double2String(Float.parseFloat(totalsize) - Float.parseFloat(usedsize));

                String tablespace_name = Formater.neatenunitid(name);
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "CM-00-03-003-01", tablespace_name);
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "CM-00-03-003-02", totalsize); // 表空间大小
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "PM-00-03-004-01", rate); // 表空间利用率
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "PM-00-03-904-01", freesize);// 14. 表空间剩余大小

            }

            log.info("This is Oracle 11g");
        }catch (Exception ex){
            log.info("This is Oracle 9i");
            //String KBP_ID = KBP_ID + "-11:" + deviceId + "-TABLESPACES";

            String sql = "SELECT upper(d.tablespace_name), "+
                    " round(d.total_bytes, 3), "+
                    " round(d.total_bytes - f.total_free_bytes, 3), "+
                    " round(f.total_free_bytes, 3), "+
                    " round((d.total_bytes - f.total_free_bytes) / d.total_bytes * 100, 2) "+
                    " FROM (SELECT tablespace_name, SUM(bytes) / (1024 * 1024) total_free_bytes "+
                    " FROM dba_free_space "+
                    " GROUP BY tablespace_name) f, "+
                    " (SELECT dd.tablespace_name, SUM(dd.bytes) / (1024 * 1024) total_bytes "+
                    "  FROM dba_data_files dd "+
                    " GROUP BY dd.tablespace_name) d "+
                    " WHERE d.tablespace_name = f.tablespace_name "+
                    " ORDER BY upper(d.tablespace_name) ";
            Vector tablespaceDetail = collectiondbbase.getSQLKPIResult(sql);
            System.out.println("table size:"+tablespaceDetail.size());
            if(tablespaceDetail.size()>0){
                for(int i=0;i<tablespaceDetail.size();i++){
                    Vector everyRecordDetail = (Vector) tablespaceDetail.get(i);
//				System.out.println("Vector value:"+everyRecordDetail.toString());
                    String tableSpaceName = "";
                    String tableSpaceSize = "";
                    String tableSpaceUsed = "";
                    String tableSpaceFree = "";
                    String tableSpaceUsedRate = "";
//				System.out.println("record size:"+everyRecordDetail.size());
                    if(everyRecordDetail.size()>4){

                        tableSpaceName = (String) everyRecordDetail.get(0);
                        tableSpaceSize = (String) everyRecordDetail.get(1);
                        tableSpaceUsed = (String) everyRecordDetail.get(2);
                        tableSpaceFree = (String) everyRecordDetail.get(3);
                        tableSpaceUsedRate = (String) everyRecordDetail.get(4);
                        if(tableSpaceUsed.indexOf(".")==0){
                            tableSpaceUsed = "0"+tableSpaceUsed;
                        }
                        if(tableSpaceSize.indexOf(".")==0){
                            tableSpaceSize = "0"+tableSpaceSize;
                        }
                        if(tableSpaceFree.indexOf(".")==0){
                            tableSpaceFree = "0"+tableSpaceFree;
                        }
                        if(tableSpaceUsedRate.indexOf(".")==0){
                            tableSpaceUsedRate = "0"+tableSpaceUsedRate;
                        }
                    }
                    String tablespace_name = Formater.neatenunitid(tableSpaceName);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-01", tableSpaceName);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-02",tableSpaceSize);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-003-03",tableSpaceUsed);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-904-01",tableSpaceFree);
                    collectiondbbase.addKPI( KBP_ID + "-" + tablespace_name, "PM-00-03-004-01",Formater.getByScale(String.valueOf(tableSpaceUsedRate), 2));
                    System.out.println("tablename:"+tablespace_name+",tablesize:"+tableSpaceSize+",tableused:"+tableSpaceUsed+",tablefree:"+tableSpaceFree+",tablerate:"+tableSpaceUsedRate);
                }
            }
        }
    }

    private void getTableSpacesSpecial(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-11" + ":" + NEATENSID + "-TABLESPACES";
        // 查询oracle中配置的回滚段空间名称，请注意，一个oracle只能设置一个online回滚段表空间
        String sql = "select distinct tablespace_name from dba_undo_extents";
        // oracle回滚段真实使用率
        String sql1 = "select (select sum(BYTES)" +
                "          from dba_undo_extents" +
                "         where TABLESPACE_NAME = '#UNDO#'" +
                "           and STATUS in ('UNEXPIRED', 'ACTIVE')) * 100 /" +
                "       (select sum(BYTES)" +
                "          from dba_data_files" +
                "         where TABLESPACE_NAME = '#UNDO#')" +
                "  from dual";
        Vector undoTablespaceName = collectiondbbase.getSQLKPIResult(sql);
        List<String> list = new ArrayList<String>();
        for (int n = 0; n < undoTablespaceName.size(); n++) {
            String undotablespacename = (String) ((Vector) undoTablespaceName.elementAt(n)).get(0);
            logger.info("undo table space name=" + undotablespacename);
            list.add(undotablespacename);
        }

        String sql2 = "SELECT NAME,NVL(SUM(bytes/1024/1024),0) FROM dba_data_files a,v$tablespace b "
                + " WHERE b.NAME=a.TABLESPACE_NAME(+) "
                + " GROUP BY NAME "
                + " order by NAME";
        String sql3 = "SELECT NAME,NVL(SUM(bytes)/1024/1024,0) FROM dba_free_space a,v$tablespace b "
                + " WHERE b.NAME=a.TABLESPACE_NAME(+) "
                + " GROUP BY NAME "
                + " order by NAME";

        Vector tablespacenametotalsize = collectiondbbase.getSQLKPIResult(sql2);
        Vector tablespacenamefreesize = collectiondbbase.getSQLKPIResult(sql3);
        for (int i = 0; i < tablespacenametotalsize.size(); i++) {
            Vector vtotalsize = (Vector) tablespacenametotalsize.elementAt(i);
            Vector vfreesize = (Vector) tablespacenamefreesize.elementAt(i);
            String name1 = (String) vtotalsize.get(0);
            String totalsize = (String) vtotalsize.get(1);
            System.out.println("table space-name=" + name1);
            String name2 = (String) vfreesize.get(0);
            String freesize = (String) vfreesize.get(1);

            if (name1.equals(name2)) {
                if (!list.contains(name1)) {
                    // 非回滚段表空间使用率
                    float rate = 0;
                    if (!totalsize.equals("0")) {
                        rate = 100F - Float.parseFloat(freesize) / Float.parseFloat(totalsize) * 100F;
                    }
                    String tablespace_name = Formater.neatenunitid(name1);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-01", tablespace_name);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-02", totalsize); // 表空间大小
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-004-01", Formater.getByScale(String.valueOf(rate), 2)); // 表空间利用率
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-904-01", freesize);// 14. 表空间剩余大小
                } else {
                    // 回滚段表空间使用率
                    String temp_sql = sql1.replaceAll("#UNDO#", name1);
                    Vector undoTableSpace = collectiondbbase.getSQLKPIResult(temp_sql);
                    Vector vundo = (Vector) undoTableSpace.elementAt(0);
                    String undo = (String) vundo.get(0);
                    String tablespace_name = Formater.neatenunitid(name1);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-01", tablespace_name);
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-02", totalsize); // 表空间大小
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-004-01", Formater.getByScale(String.valueOf(undo), 2)); // 表空间利用率
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-904-01", freesize);// 14. 表空间剩余大小
                }
            } else {
                System.err.println(name1 + "|" + name2);
            }
        }
    }


    /**
     * undo表空间（回滚段）的监控方法
     */
    private void getUndoTableSpaces(SQLTarget collectiondbbase) {
//		logger.info("into getUndoTableSpaces 1");
        String KBP_ID = this.KBP_ID + "-13" + ":" + NEATENSID + "-TABLESPACES";

//        String sql2 = "SELECT NAME, NVL(SUM(bytes/1024/1024),0) FROM dba_data_files a, v$tablespace b "
//                + " WHERE b.NAME = a.TABLESPACE_NAME(+) "
//                + " AND NAME LIKE 'UNDOTBS%'"
//                + " GROUP BY NAME "
//                + " order by NAME";
        String sql2 = "SELECT NAME, NVL(SUM(bytes/1024/1024),0) FROM dba_data_files a, v$tablespace b "
                + " ,(SELECT distinct TABLESPACE_NAME from dba_undo_extents WHERE status in ('ACTIVE','UNEXPIRED')) c"
                + " WHERE b.NAME = a.TABLESPACE_NAME(+) "
                + " AND b.NAME = c.TABLESPACE_NAME"
                + " GROUP BY NAME "
                + " order by NAME";

//		按照多维创新的同事的建议，使用sql7，sql3暂时不用，不过有的机子使用sql7可能会有问题。。
        String sql3 = "SELECT NAME, NVL(SUM(bytes)/1024/1024, 0) FROM dba_free_space a, v$tablespace b "
                + " WHERE b.NAME = a.TABLESPACE_NAME(+) "
                + " AND NAME LIKE 'UNDOTBS%'"
                + " GROUP BY NAME "
                + " order by NAME";
        String sql4 = "select tablespace_name, sum(free) * 100 / sum(bytes) " +
                "from (select b.tablespace_name tablespace_name, b.bytes Bytes, sum(nvl(a.bytes, 0)) free " +
                "from dba_free_space a, dba_data_files b " +
                "where a.file_id = b.file_id " +
                "group by b.tablespace_name, b.file_id, b.bytes order by b.file_id) " +
                "where tablespace_name LIKE 'UNDOTBS%' " +
                "group by tablespace_name " +
                "order by tablespace_name";
        String sql5 = "select df.tablespace_name name, NVL(SUM(f.phyrds), 0), NVL(SUM(f.phywrts), 0) " +
                "from v$filestat f, dba_data_files df " +
                "where f.file# = df.file_id " +
                "AND df.tablespace_name LIKE 'UNDOTBS%' " +
                "group by df.tablespace_name " +
                "order by df.tablespace_name";
        String sql6 = "select TABLESPACE_NAME, STATUS from dba_tablespaces " +
                " WHERE TABLESPACE_NAME LIKE 'UNDOTBS%' " +
                " order by TABLESPACE_NAME";
//        String sql7 = "Select TABLESPACE_NAME,sum(bytes)/1024/1024/ " +
//                "( SELECT SUM(BYTES)/1024/1024 " +
//                "    FROM DBA_DATA_FILES " +
//                "   WHERE TABLESPACE_NAME LIKE '%UNDO%')*100 USESIZE " +
//                " from dba_undo_extents " +
//                " where status in ('ACTIVE','UNEXPIRED') " +
//                " GROUP BY tablespace_name" +
//                " order by tablespace_name ";
        String sql7 = "Select TABLESPACE_NAME,sum(bytes)/1024/1024 " +
                "    USESIZE " +
                " from dba_undo_extents " +
                " where status in ('ACTIVE','UNEXPIRED') " +
                " GROUP BY tablespace_name" +
                " order by tablespace_name ";

        /**
         * 另一种获取回滚段的语句。
         * select segment_name, tablespace_name, r.status,
         (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
         max_extents, v.curext CurExtent
         From dba_rollback_segs r, v$rollstat v
         Where r.segment_id = v.usn(+)
         order by segment_name ;
         * */


        Vector tablespacenametotalsize = collectiondbbase.getSQLKPIResult(sql2);
        Vector tablespacenamefreesize = collectiondbbase.getSQLKPIResult(sql7);
        Vector tablespacenamechipsize = collectiondbbase.getSQLKPIResult(sql4);
        Vector tablespacenamerwnum = collectiondbbase.getSQLKPIResult(sql5);
        Vector tablespacenamestate = collectiondbbase.getSQLKPIResult(sql6);
//		Vector tablespacenamepercent = collectiondbbase.getSQLKPIResult(sql7);

        Map cmap = new HashMap();
        for (int c = 0; c < tablespacenamechipsize.size(); c++) {
            Vector vchipsize = (Vector) tablespacenamechipsize.elementAt(c);
            String namec = (String) vchipsize.get(0);
            String sizec = (String) vchipsize.get(1);
            cmap.put(namec, sizec);
        }
        Map rmap = new HashMap();
        Map wmap = new HashMap();
        for (int rw = 0; rw < tablespacenamerwnum.size(); rw++) {
            Vector vrwnum = (Vector) tablespacenamerwnum.elementAt(rw);
            String namerw = (String) vrwnum.get(0);
            String sizer = (String) vrwnum.get(1);
            String sizew = (String) vrwnum.get(2);
            rmap.put(namerw, sizer);
            wmap.put(namerw, sizew);
        }
        Map smap = new HashMap();
        for (int s = 0; s < tablespacenamestate.size(); s++) {
            Vector vstate = (Vector) tablespacenamestate.elementAt(s);
            String names = (String) vstate.get(0);
            String sizes = (String) vstate.get(1);
            smap.put(names, sizes);
        }

        for (int i = 0; i < tablespacenametotalsize.size(); i++) {
            Vector vtotalsize = (Vector) tablespacenametotalsize.elementAt(i);
            Vector vfreesize = (Vector) tablespacenamefreesize.elementAt(i);
            String name1 = (String) vtotalsize.get(0);
            String totalsize = (String) vtotalsize.get(1);
            System.out.println("table space-name = " + name1);
            String name2 = (String) vfreesize.get(0);
            String freesize = (String) vfreesize.get(1);

            if (name1.equals(name2)) {
                float rate = 0;
                if (!totalsize.equals("0")) {
//					上一行为使用率，下一行其实为空闲率。
					rate = 100F - Float.parseFloat(freesize) / Float.parseFloat(totalsize) * 100F;
//                    rate = Float.parseFloat(freesize) / Float.parseFloat(totalsize) * 100F;
                }

                logger.info("VIP is :" + rate);

                String tablespace_name = Formater.neatenunitid(name1);
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "CM-00-03-003-01", tablespace_name); // 表空间名
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "CM-00-03-003-02", totalsize); // 表空间大小
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "PM-00-03-004-01", Formater.getByScale(String.valueOf(rate), 4)); // 表空间利用率
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name,
                        "PM-00-03-904-01", freesize);// 表空间剩余大小
                // 碎片程度
                if (cmap.get(tablespace_name) != null) {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-03",
                            Formater.formatDecimalKpivalue((String) cmap.get(tablespace_name)));
                } else {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "CM-00-03-003-03", "unchecked");
                }
                // 读操作次数
                if (rmap.get(tablespace_name) != null) {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-003-01",
                            Formater.formatDecimalKpivalue((String) rmap.get(tablespace_name)));
                } else {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-003-01", "unchecked");
                }
                // 写操作次数
                if (wmap.get(tablespace_name) != null) {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-003-02",
                            Formater.formatDecimalKpivalue((String) wmap.get(tablespace_name)));
                } else {
                    collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "PM-00-03-003-02", "unchecked");
                }
                // 表空间状态
                collectiondbbase.addKPI(KBP_ID + "-" + tablespace_name, "FM-00-03-001-02",
                        ((String) smap.get(tablespace_name)).length() == 0 ?
                                "unchecked" : ((String) smap.get(tablespace_name)));
            } else {
                System.err.println(name1 + "|" + name2);
            }
        }
    }

    /*
     * CM-00-03-006-01 数据设备名m SELECT file_name FROM dba_data_files a ORDER BY file_name
     * FM-10-12-12-10 状态d
     * CM-10-12-12-11 表空间名d SELECT file_name,tablespace_name FROM dba_data_files a ORDER BY file_name
     * PM-10-12-12-10 空间大小 SELECT file_name,bytes FROM dba_data_files a ORDER BY file_name
     * PM-10-12-12-11 空余空间 SELECT file_name,NVL(SUM(b.bytes),0) FROM dba_data_files a,dba_free_space b WHERE a.File_id=b.FILE_ID(+) GROUP BY file_name ORDER BY file_name
     * PM-10-12-12-12 已用空间Y
     * PM-10-10-13-10 -
     * PM-10-10-13-11
     * PM-00-03-007-01 空?涫褂寐?
     * PM-10-10-12-12 /
     * PM-10-10-12-10
     *
     */
    private void getDataFile(SQLTarget collectiondbbase) {
        String KBP_ID = this.KBP_ID + "-12" + ":" + NEATENSID + "-DATAFILE";
        // String KBP_ID = this.KBP_ID + ":DATAFILE";
        String sql_all = "SELECT file_name,status,tablespace_name,bytes/1024/1024,(bytes/1024/1024-user_bytes/1024/1024),user_bytes/1024/1024,(bytes-user_bytes)/bytes*100 FROM dba_data_files a ORDER BY file_name";
//		String sql_rw = "select substr(a.name, 1, 30) Name, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file# order by Name";
        String sql_rw = "select a.name Name, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file# order by Name";
		/*
		 * String sql_cm10 = "SELECT file_name FROM dba_data_files a ORDER BY
		 * file_name"; String sql_cm11 = "SELECT tablespace_name FROM
		 * dba_data_files a ORDER BY file_name"; String sql_pm10 = "SELECT
		 * bytes/1024/1024 FROM dba_data_files a ORDER BY file_name";
		 */
        // String sql_pm11 = "SELECT NVL(SUM(b.bytes),0)/1024/1024 FROM
        // dba_data_files a,dba_free_space b WHERE a.File_id=b.FILE_ID(+) GROUP
        // BY file_name ORDER BY file_name";
        Vector CM = collectiondbbase.getSQLKPIResult(sql_all);
        Vector RW = collectiondbbase.getSQLKPIResult(sql_rw);
        for (int i = 0; i < CM.size(); i++) {
            Vector row = (Vector) CM.get(i);
            Vector rw = (Vector) RW.get(i);
            String filename = getFileName((String) row.get(0));
            String rwname = getFileName((String) rw.get(0));
            // CM指标已配置在getSystem采集中，周期为一天
            // collectiondbbase.addKPI(KBP_ID + "-" + filename, "CM-00-03-006-01",
            //		(String) row.get(0));
            // 数据文件状态
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-005-04",
                    (String) row.get(1));
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "FM-10-12-12-10",
                    (String) row.get(1));
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-10-12-12-10",
                    (String) row.get(3));
            // collectiondbbase.addKPI(KBP_ID+"-"+filename,"PM-10-12-12-11",
            //		(String)row.get(4));
            collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-10-12-12-12",
                    (String) row.get(5));
//			collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-007-01",
//					Formater.getByScale((String) row.get(6), 2));
            if (filename.equals(rwname)) {
                // 读操作次数
                collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-005-01",
                        (String) rw.get(1));
                // 写操作次数
                collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-005-02",
                        (String) rw.get(2));
            } else {
                // 读操作次数
                collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-005-01", "0");
                // 写操作次数
                collectiondbbase.addKPI(KBP_ID + "-" + filename, "PM-00-03-005-02", "0");
            }
        }
		/*
		 * Vector CM10 =
		 * collectiondbbase.addKPI(KBP_ID,"CM-00-03-06-01",collectiondbbase.getSQLKPISet(sql_cm10));
		 * Vector CM11 =
		 * collectiondbbase.addKPI(KBP_ID,"CM-10-12-12-11",collectiondbbase.getSQLKPISet(sql_cm11));
		 * Vector PM10 =
		 * collectiondbbase.addKPI(KBP_ID,"PM-10-12-12-10",collectiondbbase.getSQLKPISet(sql_pm10));
		 */
        // Vector PM11 =
        // collectiondbbase.addKPI(KBP_ID,"PM-10-12-12-11",collectiondbbase.getSQLKPISet(sql_pm11));
		/*
		 * Vector PM12 = new Vector();//PM10-PM11 Vector PM13 = new Vector();//1 -
		 * PM11/PM10 for(int i=0;i<PM10.size();i++){ String cPM10 =
		 * (String)PM10.elementAt(i); String cPM11 = (String)PM11.elementAt(i);
		 * if(cPM10==null || cPM10.equals("")){ cPM10 = "0"; } if(cPM11==null ||
		 * cPM11.equals("")){ cPM11 = "0"; } float fPM12 =
		 * Float.parseFloat(cPM10) - Float.parseFloat(cPM11); float fPM13 = 100F -
		 * Float.parseFloat(cPM11) / Float.parseFloat(cPM10) * 100F;
		 * PM12.add(String.valueOf(fPM12)); PM13.add(String.valueOf(fPM13)); }
		 * collectiondbbase.addKPI(KBP_ID,"PM-10-12-12-12",PM12);
		 * collectiondbbase.addKPI(KBP_ID,"PM-00-03-07-01",PM13);
		 */
    }

    String getFileName(String filepath) {
        String[] ss = Formater.split2Array(filepath, "/");
        return ss[ss.length - 1];
    }

    /*
     * CM-00-03-004-01 1 CM-00-03-04-01 表名0 数据库特定表名称g 1小时?字符串型 segment_name
     * CM-00-03-004-02 2 CM-00-03-04-02 初始值0 该表建表的空间初始值n 1小时?数值型 initial_extent
     * CM-00-03-004-03 3 CM-00-03-04-03 Next扩展值4 该表下次空间扩展值t 1小时?数值型 next_extent
     * CM-00-03-004-04 4 CM-00-03-04-04 扩展最大极限值4 该表空间扩展的扩展最大次数a 1小时?数值型
     * max_extents CM-00-03-004-05 5 CM-00-03-04-05 是否有分区4 指该表是分区表还是变通表?1小时?字符型
     * PARTITIONED select table_name, initial_extent,next_extent,max_extents
     * ,PARTITIONED from dba_tables owner=#tableOwner and table_name=#tableName
     * PM-00-03-002-01 扩展次数 EXTENTS EXTENTS select segment_name,EXTENTS from
     * DBA_SEGMENTS where owner=#tableOwner and segment_name=#tableName and
     * segment_type='TABLE'
     *
     */
    private void getSpecialTable(SQLTarget collectiondbbase, String tableOwner,
                                 String tableName) {
        String KBP_ID = this.KBP_ID + "-14" + ":" + NEATENSID + "-TABLES";
        // String KBP_ID = this.KBP_ID + ":TABLES"+":"+tableName;
//		String sql1 = "select table_name, initial_extent, next_extent, max_extents, PARTITIONED"
//				+ " from dba_tables"
//				+ " where owner = '"
//				+ tableOwner
//				+ "' and table_name = '" + tableName + "'";
        String sql1 = "select table_name, initial_extent, next_extent, max_extents, PARTITIONED"
                + " from dba_tables "
                + "where table_name in (select segment_name from DBA_SEGMENTS "
                + "where owner not in ('SYS', 'SYSTEM') and segment_type = 'TABLE' "
                + "and extents > " + tableName + ") order by 1";
        logger.info(sql1);
//		String sql2 = "select EXTENTS from DBA_SEGMENTS" 
//				+ " where owner = '"
//				+ tableOwner 
//				+ "' and segment_name = '" 
//				+ tableName + "' and segment_type = 'TABLE'";
        String sql2 = "select segment_name, EXTENTS from DBA_SEGMENTS " +
                "where owner not in ('SYS', 'SYSTEM') and segment_type = 'TABLE' " +
                "and extents > " + tableName + " order by 1";
        logger.info(sql2);
        Vector CM10 = collectiondbbase.getKPISet(sql1);
        Vector PM10 = collectiondbbase.getKPISet(sql2);
        Map pmmap = new HashMap();
        for (int j = 0; j < PM10.size(); j++) {
            Vector row = (Vector) PM10.get(j);
            String segment_name = (String) row.get(0);
            String extents = (String) row.get(1);
            if (segment_name != null && extents != null) {
                pmmap.put(segment_name, extents);
            }
        }
        for (int i = 0; i < CM10.size(); i++) {
            Vector row = (Vector) CM10.get(i);
            String tablename = (String) row.get(0);
            String initialextent = (String) row.get(1);
            String nextextent = (String) row.get(2);
            String maxextents = (String) row.get(3);
            String partitioned = (String) row.get(4);
            String TABLES_KBP_ID = KBP_ID + "_" + tablename;
            // 数据库特定表名
            collectiondbbase
                    .addKPI(TABLES_KBP_ID, "CM-00-03-004-01", tablename);
            // 建表空间初始值
            collectiondbbase
                    .addKPI(TABLES_KBP_ID, "CM-00-03-004-02", initialextent);
            // 下次空间扩展值
            collectiondbbase
                    .addKPI(TABLES_KBP_ID, "CM-00-03-004-03", nextextent);
            // 扩展最大极限值
            collectiondbbase
                    .addKPI(TABLES_KBP_ID, "CM-00-03-004-04", maxextents);
            // 是否有分区
            collectiondbbase
                    .addKPI(TABLES_KBP_ID, "CM-00-03-004-05", partitioned);

            if (pmmap.get(tablename) != null) {
                // 扩展次数 该表已经完成的扩展次数
                collectiondbbase.addKPI(TABLES_KBP_ID,
                        "PM-00-03-002-01", (String) pmmap.get(tablename));
            } else {
                collectiondbbase.addKPI(TABLES_KBP_ID,
                        "PM-00-03-002-01", "0");
            }
        }
//		for (int j = 0; j < PM10.size(); j++) {
//			Vector row = (Vector) PM10.get(j);
//			String extents = (String) row.get(0);
//			// 扩展次数 该表已经完成的扩展次数
//			collectiondbbase
//					.addKPI(KBP_ID, "PM-00-03-002-01", extents);
//		}
    }

    /*
     * noneffective
     * 1 PM-00-03-006-01 碎片程度- 该表空间的碎片程度百分比K 1天?数值型?2 Free Space Fragmentation noneffective
     * 2 PM-00-03-007-02 竞争情况- 数据库日志空间或回滚段数据快等待操作占总操作的百分比情况可以其显示竞争情况R 1小时 数值型 2
     * (Total Header Waits)/(Total Header Gets)
     *
     * noneffective
     * 1 PM-00-03-009-01 CPU占用率0 其对应操作一天内平均占用CPU时间d 1小时 数值型 2 Server CPU Percent
     * 2 FM-00-03-001-02 表空间状态0 表空间所处状态2 15分钟 字符型 major Tablespace Status noneffective
     * 3 FM-00-03-001-03 有无死锁产生- 有无死锁产生- 10分钟 字符型 major Lock
     * Mode Held
     *
     */
    private void getCMStandKPI(SQLTarget collectiondbbase) {
        // String KBP_ID = this.KBP_ID + "-15" + ":" + NEATENSID + "-OTH";
        // String KBP_ID = this.KBP_ID + ":OTH";
        // collectiondbbase.addKPI(KBP_ID, "PM-00-03-006-01", "noneffective");
        // collectiondbbase.addKPI(KBP_ID, "PM-00-03-007-02", "noneffective");
        // collectiondbbase.addKPI(KBP_ID, "PM-00-03-009-01", "noneffective");
        // collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-02", "noneffective");
        // collectiondbbase.addKPI(KBP_ID, "FM-00-03-001-03", "noneffective");
        String KBP_ID = this.KBP_ID + "-19" + ":" + NEATENSID + "-LOCK";
        String sql = "select sid, decode(lmode, 0, 'None', 1, 'Null', 2, 'Row share'," +
                " 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_type, ctime" +
                " from v$lock where TYPE IN ('TX', 'TM') and rownum between 1 and 10 order by ctime desc";
        logger.info(sql);
        Vector CMStand_v = collectiondbbase.getKPISet(sql);
//		Map pmmap = new HashMap();
//		for (int j = 0; j < PM10.size(); j++) {
//			Vector row = (Vector) PM10.get(j);
//			String segment_name = (String) row.get(0);
//			String extents = (String) row.get(1);
//			if (segment_name != null && extents != null) {
//				pmmap.put(segment_name, extents);
//			}
//		}
        int index = 1;
        for (int i = 0; i < CMStand_v.size(); i++) {
            Vector row = (Vector) CMStand_v.get(i);
            String sid = (String) row.get(0);
            String type = (String) row.get(1);
            String ctime = (String) row.get(2);
            String TABLES_KBP_ID = KBP_ID + "_" + index;
            // SID CM-00-03-008-01
            collectiondbbase.addKPI(TABLES_KBP_ID,
                    "CM-00-03-008-01", sid);
            // 锁类型 CM-00-03-008-02
            collectiondbbase.addKPI(TABLES_KBP_ID,
                    "CM-00-03-008-02", type);
            // 锁时长 PM-00-03-008-16
            collectiondbbase.addKPI(TABLES_KBP_ID,
                    "PM-00-03-008-16", ctime);
            index++;
        }
    }

    public java.util.Vector getSyslog(java.util.HashMap params)
            throws Exception {
        CollBase collResult = new CollBase();
//		logger.info("begin getSyslog ");
//		// 保存采集结果，并返回值
//		try {
//			// 登陆数据库主机，获取日志信息
//			init(params);
//			String log_path = (String) params.get("log_path");
//			if (log_path != null && !log_path.endsWith("/")) {
//				log_path += "/";
//			}
//			String SID = tt
//					.getResultAsStr("ps -ef|grep oracle|grep smon|awk '{print $9}'|cut -f3 -d'_'");
//			String ora_log_file = log_path + "alert_" + SID + ".log";
//			String ANALY_FILE = data_path + "ora_analy_data.dat";
//			String ANALY_PROCESS_FILE = data_path
//					+ "ora_analy_data_process.dat";
//			String COUNT_LINE_FILE = data_path + "ora_count_line.dat";
//			String COUNT_LINE = tt.getResultAsStr("wc -l " + ora_log_file
//					+ "| awk '{print $1}'");
//			String Flag1 = "^[M,m]on|^[T,t]ues|^[W,w]ed|^[T,t]hurs|^[F,f]ri|^[S,s]at|^[S,s]un|^ORA-";
//			String Flag = " Jan| Feb| Mar| Apr| May| Jun| Jul| Aug| Sept| Oct| Nov| De|[0-9]:";
//			String new_data = tt.getResultAsStr("grep -n -E \"" + Flag1 + "\" "
//					+ ora_log_file + "|grep -E \"" + Flag
//					+ "\"| sed -n \"1,200p\"" + "| wc -l");
//			File analy_file = new File(ANALY_FILE);
//			File analy_process_file = new File(ANALY_PROCESS_FILE);
//			PrintWriter write_analy = new PrintWriter(new OutputStreamWriter(
//					new FileOutputStream(analy_file), "GB2312"));
//			PrintWriter write_analy_process = new PrintWriter(
//					new OutputStreamWriter(new FileOutputStream(
//							analy_process_file), "GB2312"));
//			if (new_data != null && new Integer(new_data).intValue() > 0) {
//				String analy_data = tt.getResultAsStr("grep -n -E \"" + Flag1
//						+ "\" " + ora_log_file + " | grep -E \"" + Flag
//						+ "\"|sed -n \"1,1000p\"");
//				tt.close();
//				// 日志信息获取完毕。退出数据库主机，登陆网管主机
//				initBomc();
//				write_analy.write(analy_data);
//				write_analy.flush();
//				write_analy.close();
//				String analy_process_data = tt
//						.getResultAsStr("grep -n \"ORA-\" " + ANALY_FILE);
//				write_analy_process.write(analy_process_data);
//				write_analy_process.flush();
//				write_analy_process.close();
//				String[] analy_process_array = analy_process_data.split("\n");
//				logger.info("analy_process_array's length is: "
//						+ analy_process_array.length);
//				for (int i = 0; i < analy_process_array.length; i++) {
//					logger.info("analy_process_data's " + i + " line is: "
//							+ analy_process_array[i]);
//					String line = analy_process_array[i];
//					String old_line = line.split(":")[0];
//					String ora_line = tt.getResultAsStr("expr " + old_line
//							+ " - 1");
//					String alarm_msg = line.split(":")[2] + line.split(":")[3];
//					String alarm_time = tt.getResultAsStr("sed -n \""
//							+ ora_line + "p\" " + ANALY_FILE
//							+ "|awk -F \":\" '{print $2\":\"$3\":\"$4}'");
//					if (alarm_time != null && alarm_time.equals("")) {
//						System.out.println("alarm_time is null");
//						continue;
//					}
//					String alarm_time_isTRUE = tt
//							.getResultAsStr("sed -n \""
//									+ ora_line
//									+ "p\" "
//									+ ANALY_FILE
//									+ " | awk -F \":\" '{print $2}' | awk -F \"-\" '{print $1}'");
//					String TME_TIME = "";
//					if (alarm_time_isTRUE != null
//							&& !alarm_time_isTRUE.equals("ORA")) {
//						TME_TIME = alarm_time_isTRUE;
//					}
//					String alarm_type = tt.getResultAsStr("echo \"" + alarm_msg
//							+ "\"|awk '{print $1}'|cut -f2 -d':'");
//					String tmp_kbp_time = tt.getResultAsStr("echo \""
//							+ TME_TIME + "\"|tr ' ' '-'");
//					String tmp_alarm_len = tt.getResultAsStr("echo \""
//							+ alarm_msg + "\"|wc -c");
//					logger.info("tmp_alarm_len is " + tmp_alarm_len);
//					if (tmp_alarm_len != null
//							&& new Integer(tmp_alarm_len).intValue() > 250) {
//						alarm_msg = tt.getResultAsStr("echo \"" + alarm_msg
//								+ "\"|awk '{print substr($0,1,249)}'");
//					}
//					collResult.addKPI(
//							"10-11-30-18:" + SID + "-" + tmp_kbp_time,
//							"CM-00-01-900-17", TME_TIME);
//					collResult.addKPI(
//							"10-11-30-18:" + SID + "-" + tmp_kbp_time,
//							"CM-00-01-900-18", alarm_type);
//					collResult.addKPI(
//							"10-11-30-18:" + SID + "-" + tmp_kbp_time,
//							"CM-00-01-900-19", alarm_msg);
//				}
//			}
//			File count_line_file = new File(COUNT_LINE_FILE);
//			PrintWriter write_count_line = new PrintWriter(
//					new OutputStreamWriter(new FileOutputStream(
//							count_line_file, true), "GB2312"));
//			write_count_line.write(COUNT_LINE);
//			write_count_line.flush();
//			write_count_line.close();
//		} catch (Exception e) {
//			e.printStackTrace();
//		} finally {
//			tt.close();
//		}
        logger.info("end getSyslog ");
        return collResult.getKPISet();
    }

    public java.util.Vector getPMExtendKPI(java.util.HashMap params)
            throws Exception {
        getParams(params);
        return getPMExtendKPI(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getPMExtendKPI(String IP_ADDR, String USERNAME,
                                           String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        //this.NEATENSID = Formater.neatenunitid(SID);
        this.NEATENSID = Formater.neatenunitid(this.device_id);

        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);

        getPMExtendKPI(collectiondbbase);

        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    private void getPMExtendKPI(SQLTarget collectiondbbase) {
        //山西移动 lijy 功能 #1352 用户让取消采集
        String bLockDetail = "select a.object_name,o.object_id,o.session_id,o.ORACLE_USERNAME,"
                + "o.OS_USER_NAME, o.PROCESS, o.locked_mode from "
                + "v$locked_object o,all_objects a "
                + "where a.object_id=o.object_id and o.session_id in "
                + "(select sid from v$lock where block=1)";

        Vector blockedLockDetail = collectiondbbase.getSQLKPIResult(bLockDetail);
        // PM-00-03-008-01 锁数量
        collectiondbbase.addKPI(KBP_ID+"-10:"+NEATENSID+"-SYS", "PM-00-03-008-01", String.valueOf(blockedLockDetail.size()));

        //山西移动 #1352 lijy  用户让取消采集。
        //PM-10-12-10-26 表空间剩余大小
        collectiondbbase
                .addKPI(
                        KBP_ID+"-10:"+NEATENSID+"-SYS",
                        "PM-10-12-10-26",
                        collectiondbbase
                                .getSQLKPIInfo("select sum(bytes/1024/1024) from dba_free_space"));
    }

    // 数据库等待数
    public Vector<TblATO_KPIDETAIL> getSessionWait(HashMap<String,String> params) throws Exception {
        getParams(params);
        return getSessionWait(hostip, user, passwd, kbp_id, SID);
    }

    public java.util.Vector getSessionWait(String IP_ADDR, String USERNAME,
                                            String PASSWORD, String KBP_ID, String SID) throws Exception {
        this.KBP_ID = KBP_ID;
        this.NEATENSID = Formater.neatenunitid(this.device_id);
        SQLTarget collectiondbbase = new SQLTarget();
        collectiondbbase.init(IP_ADDR, PORT, SID, USERNAME, PASSWORD);
        getSessionWait(collectiondbbase);
        Vector aa = collectiondbbase.getKPISet();
        collectiondbbase.release();
        return aa;
    }

    private void getSessionWait(SQLTarget collectiondbbase) {
        String sql = "select event, count(*) from v$session group by event";
        Vector sw = collectiondbbase.getSQLKPIResult(sql);
        for (int i = 0; i < sw.size(); i++) {
            Vector swVector = (Vector) sw.elementAt(i);
            String eventName = (String) swVector.get(0);
            String eventCount = (String) swVector.get(1);
            // 等待事件类型 CM-00-03-009-01
            collectiondbbase.addKPI(this.KBP_ID + "-31" + ":" + NEATENSID + "-" + parseSessionWaitType(eventName), "CM-00-03-009-01", eventName);
            // 等待事件数量 PM-00-03-009-10
            collectiondbbase.addKPI(this.KBP_ID + "-31" + ":" + NEATENSID + "-" + parseSessionWaitType(eventName), "PM-00-03-009-10", eventCount);
        }
    }

    private String parseSessionWaitType(String eventName) {
        String res = "";
        String[] _res = eventName.split(" ");
        for (int i = 0; i < _res.length; i++) {
            res += _res[i].substring(0, 1).toUpperCase();
        }
        return res;
    }
}