oracle_tablespace_details.py 文件源码

def metricCollector(self):
        c=None
        conn=None
        try:
            import cx_Oracle
        except Exception as e:
            self.data['status'] = 0
            self.data['msg'] = 'cx_Oracle module is not installed'
            return self.data

        try:
            try:
                dsnStr = cx_Oracle.makedsn(self.host, self.port, self.sid)
                conn = cx_Oracle.connect(user=self.username, password=self.password, dsn=dsnStr, mode=cx_Oracle.SYSDBA)
                c = conn.cursor()
            except Exception as e:
                self.data['status']=0
                self.data['msg']='Exception while connecting to '+self.host

            c.execute("select * from (SELECT t.tablespace_name,t.logging,t.contents,t.status,NVL(df.allocated_bytes,0)-NVL((NVL(f.free_bytes,0)+df.max_free_bytes),0) usedBytes,NVL((NVL(f.free_bytes,0)+df.max_free_bytes),0) freeBytes,NVL(f.free_blocks,0) freeBlocks FROM sys.dba_tablespaces t, (select ff.tablespace_name,sum(ff.free_bytes) free_bytes,sum(ff.free_blocks) free_blocks from (SELECT fs.tablespace_name, SUM(fs.bytes) free_bytes, SUM(fs.blocks) free_blocks FROM sys.dba_free_space fs,sys.dba_data_files dfs where fs.file_id=dfs.file_id and fs.tablespace_name='"+TABLESPACE_NAME+"' GROUP BY fs.tablespace_name,dfs.autoextensible) ff group by tablespace_name) f, (select dff.tablespace_name,sum(dff.allocated_bytes) allocated_bytes,sum(dff.max_free_bytes) max_free_bytes from (select tablespace_name,autoextensible,sum(decode(sign(maxbytes-bytes),1,maxbytes,bytes)) allocated_bytes,sum(decode(sign(maxbytes-bytes),1,abs(maxbytes-bytes),0)) max_free_bytes from dba_data_files where tablespace_name='"+TABLESPACE_NAME+"' group by tablespace_name,autoextensible) dff group by tablespace_name) df WHERE t.tablespace_name = f.tablespace_name(+) and t.tablespace_name=df.tablespace_name(+) and t.tablespace_name='"+TABLESPACE_NAME+"'),(SELECT  SUM(rw.phyrds) phyrds, SUM(rw.phywrts) phywrts FROM sys.dba_data_files drw, V$filestat rw WHERE drw.file_id = rw.file# and drw.tablespace_name = '"+TABLESPACE_NAME+"' GROUP BY drw.tablespace_name)")

            for row in c:
                name, log, content, status, usedbytes, freebytes, free_blocks, reads, writes = row
                if name == TABLESPACE_NAME :
                    self.data['logging'] = log
                    self.data['content']=content
                    self.data['tablespace_status']=status
                    self.data['used_space']=convertToMB(usedbytes)
                    self.data['free_space']=convertToMB(freebytes)
                    self.data['reads']=reads
                    self.data['writes']=writes
                    self.data['free_blocks']=free_blocks
                    self.data['tablespace_usage_percent']=round((float(usedbytes)/float(usedbytes+freebytes))*100,2)
                    self.data['tablespace_name']=name
                else:
                    self.data['status'] = 0
                    self.data['msg'] = 'Please check the Tablespace Name in the configuration section'


        except Exception as e:
            self.data['status'] = 0
            self.data['msg'] = str(e)
        finally:
            if c!= None : c.close()
            if conn != None : conn.close()
            return self.data