| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566 |
- [database]
- Db = "xugusql"
- DbHost = "10.28.20.101"
- DbPort = "5190"
- DbName = "SYSTEM"
- DbUser = "SYSDBA"
- DbPassWord = "SYSDBA"
- [setting]
- limit = "0" #会在sql语句后面拼接 limit语句 ，为 0 则不拼接 limit 语句。只在 （-）参数下起效。
- colLimit = "13" #限制命令行打印表格列数
- [sql]
- gstores = "SELECT * FROM sys_gstores WHERE GSTO_NO = %s"
- stores = "SELECT * FROM sys_all_stores WHERE GSTO_NO = %s"
- tablespaces = "SELECT * FROM sys_tablespaces;"
- tablenames = "select table_name from sys_tables;"
- tableid = "select table_id from sys_tables"
- tables = "select * from sys_tables"
- t1 = "select %s from %s;"
- # 查询权限
- qx = """SELECT 'GRANT SELECT ON ' || D.SCHEMA_NAME || '.' || B.OBJ_NAME || ' TO ' || C.USER_NAME ||';'
- FROM ALL_ACLS A,ALL_OBJECTS B,ALL_USERS C,ALL_SCHEMAS D WHERE C.USER_ID=A.GRANTEE_ID
- AND A.OBJECT_ID = B.OBJ_ID AND B.SCHEMA_ID=D.SCHEMA_ID AND D.SCHEMA_NAME='USR_SOD'
- AND B.OBJ_NAME ='SURF_WEA_GLB_MUL_HOR_TAB' AND BIT_AND(AUTHORITY,5) = 1"""
- #查看线程状态
- thd =" SELECT STATE,COUNT(*) FROM SYS_ALL_THD_STATUS WHERE STATE>0 GROUP BY STATE ; "
- #查看事务信息
- transid ="""select * from sys_all_thd_session
- where curr_tid in (select r_transid from ( select *, bit_and(tranid,16777215) lockid
- from sys_all_trans where lockid in (SELECT WAIT_OBJ
- FROM SYS_all_THD_STATUS WHERE STATE=8 GROUP BY WAIT_OBJ)));"""
- #查看表大小 按TB (db_name='%s' and t.table_name='%s')
- "tablesize" = """select d.db_name,sn.schema_name,t.table_name,count(*)*8/1024/1024||'T'
- as cnt from sys_schemas@sys sn,sys_tables@sys t,sys_gstores@sys s,sys_databases@sys d
- where s.obj_id=t.table_id and sn.schema_id=t.schema_id and sn.db_id=t.db_id and s.db_id=d.db_id and sn.db_id=t.db_id
- and db_name='%s' and t.table_name='%s' group by t.table_name,sn.schema_name,d.db_name;"""
- clusters = "show clusters"
- "daka" = """SELECT
- NULL AS '加班强度'
- ,ROUND(NVL(当月加班次数/当月应出勤次数,0),4) AS '加班频率'
- ,NVL(ROUND(当月加班时长总和/当月加班次数,2),0) AS '加班时长(平均)/分钟'
- ,NVL(当月加班时长总和,0) AS '当月加班时长总和(分钟)'
- ,当月加班次数
- ,当月实际出勤次数+当月出差次数 AS '当月实际出勤次数（公司+外勤）'
- ,当月应出勤次数
- ,当月实际出勤次数
- ,当月出差次数
- ,round((当月实际出勤次数+当月出差次数)/当月应出勤次数,4) AS '出勤频率(公司+外勤)'
- ,round(当月出差次数/当月应出勤次数,4) AS '出差频率'
- ,年月,部门,姓名
- FROM xugu.KQ_VIEW
- WHERE 考勤日期 >= '2023-12-01'
- GROUP BY ROUND(NVL(当月加班次数/当月应出勤次数,0),4),NVL(ROUND(当月加班时长总和/当月加班次数,2),0)
- ,NVL(当月加班时长总和,0),当月加班次数,当月实际出勤次数+当月出差次数,当月应出勤次数,当月实际出勤次数,当月出差次数
- ,round((当月实际出勤次数+当月出差次数)/当月应出勤次数,4),年月,部门,姓名;
- """
|
