python使用cx_Oracle連線oracle資料庫獲取常用資訊

raysuen發表於2018-04-22
PythonOracle資料庫
直接使用：
    CheckOracle.py -H [remote_home] -u [oracle_user] -p [oracle_password] -s [oracle_SID] -i [information] [table_name]
   具體使用細節和例子，使用-h獲取幫助         
其他python呼叫：
    1 可以在例項化物件時指定引數
    2 呼叫的函式為OraInfo()
    3 返回結果集




點選(此處)摺疊或開啟

  1. #!/usr/bin/env python3
  2. #_*_coding:utf-8_*_
  3. #Auth by raysuen
  4. #v1

  6. import cx_Oracle
  7. import sys,re,datetime


  10. class OracleConn(object): #封裝類
  11.     #存放連線資訊的
  12.     Info = {
  13.         "host":"127.0.0.1", #遠端oracle的host
  14.         "port":1521, #遠端oracle的port
  15.         "OraSID":None, #遠端oracle的SID
  16.         "OraUser":None, #遠端oracle的username
  17.         "OraPasswd":None, #遠端oracle的username對應的密碼
  18.         # "OraSchemas":None,
  19.         "OraInfo":None, #想要獲取遠端資料庫的那種資訊
  20.         "OraTable":None #有關係的oracle的表名
  21.             }

  23.     def __init__(self,host=None,port=None,sid=None,orauser=None,orapwd=None,orainfo=None,oratable=None):
  24.         if host != None:
  25.             self.Info["host"] = host
  26.         if sid != None:
  27.             self.Info["OraSID"] = sid
  28.         if port != None:
  29.             self.Info["port"] = port
  30.         if orauser != None:
  31.             self.Info["OraUser"] = orauser
  32.         if orapwd != None:
  33.             self.Info["OraPasswd"] = orapwd
  34.         if orainfo != None:
  35.             self.Info["OraInfo"] = orainfo
  36.         if oratable != None:
  37.             self.Info["OraTable"] = oratable


  40.     def Check_Info(self): #判斷Info字典裡面的key是否存在必要的值
  41.         if self.Info["OraUser"] == None:
  42.             print("You must specify a oracle username for connecting oracle.")
  43.             print("If you don't know how to specify the parameters.You can -h to get help")
  44.             exit(3)
  45.         if self.Info["OraPasswd"] == None:
  46.             print("You must specify a oracle password for connecting oracle.")
  47.             print("If you don't know how to specify the parameters.You can -h to get help")
  48.             exit(3)
  49.         if self.Info["OraSID"] == None:
  50.             print("You must specify a oracle SID for connecting oracle.")
  51.             print("If you don't know how to specify the parameters.You can -h to get help")
  52.             exit(3)
  53.         if self.Info["OraInfo"] == None:
  54.             print("You must specify a Information about oracle")
  55.             print("If you don't know how to specify the parameters.You can -h to get help")
  56.             exit(3)

  58.     def ConnectOracle(self): #封裝連線資料庫的連線，並返回連線物件
  59.         try:
  60.             tnsname = cx_Oracle.makedsn(self.Info["host"], self.Info["port"], self.Info["OraSID"])
  61.             ora = cx_Oracle.connect(self.Info["OraUser"], self.Info["OraPasswd"], tnsname)
  62.         except Exception as e:
  63.             print(e)
  64.             exit(4)
  65.         return ora

  67.     def CloseOracle(self,oraCon): #封裝管理資料庫連線的函式
  68.         oraCon.close()

  70.     def ExecSql(self,SqlStr): #封裝資料執行sql的函式
  71.         try:
  72.             ora = self.ConnectOracle()
  73.             cursor = ora.cursor()
  74.             cursor.execute(SqlStr)
  75.             res = cursor.fetchall()
  76.             cursor.close
  77.             self.CloseOracle(ora)
  78.         except Exception as e:
  79.             print(e)
  80.             exit(5)
  81.         return res


  84.     def GetTableSpace(self): #獲取tablespace資訊的函式
  85.         sqlStr="""
  86.               select a.tablespace_name,
  87.                      round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
  88.                      round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
  89.                      round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
  90.                      round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_Free,
  91.                      100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100,2)||'%' Pct_used,
  92.                      round(maxbytes/1048576,2) Max,
  93.                      round(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) / round((case maxbytes when 0 then a.bytes_alloc else maxbytes end)/1048576,2) * 100,2) || '%' "USED_MAX%"
  94.               from ( select f.tablespace_name,
  95.                              sum(f.bytes) bytes_alloc,
  96.                              sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
  97.                       from dba_data_files f
  98.                       group by tablespace_name) a,
  99.                     ( select f.tablespace_name,
  100.                              sum(f.bytes) bytes_free
  101.                       from dba_free_space f
  102.                       group by tablespace_name) b
  103.               where a.tablespace_name = b.tablespace_name (+)
  104.               union all
  105.               select h.tablespace_name,
  106.                      round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_alloc,
  107.                      round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
  108.                      round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
  109.                      round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' Pct_Free,
  110.                      100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2)||'%' pct_used,
  111.                      round(sum(f.maxbytes) / 1048576, 2) max,
  112.                      round(round(sum(nvl(p.bytes_used, 0))/ 1048576, 2)/round(sum(case f.maxbytes when 0 then (h.bytes_free + h.bytes_used) else f.maxbytes end) / 1048576, 2) * 100,2)||'%' "USED_MAX%"
  113.               from sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
  114.               where p.file_id(+) = h.file_id
  115.               and p.tablespace_name(+) = h.tablespace_name
  116.               and f.file_id = h.file_id
  117.               and f.tablespace_name = h.tablespace_name
  118.               group by h.tablespace_name
  119.               ORDER BY 1
  120.             """

  122.         res = self.ExecSql(sqlStr)
  123.         return res

  125.     def PrintTablespace(self,res):
  126.         headStr = """|%s|%s|%s|%s|%s|%s|%s|%s|"""%("tablespace_name".center(30),"megs_alloc".center(15),"megs_free".center(15),"megs_used".center(15),"Pct_Free".center(15),"Pct_used".center(15),"Max_MB".center(15),"USED_MAX_PCT".center(15))
  127.         print("%s"%"".center(144,"-"))
  128.         print(headStr)
  129.         print("%s" % "".center(144, "-"))
  130.         for t in res:
  131.             print("|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(30),str(t[1]).center(15),str(t[2]).center(15),str(t[3]).center(15),t[4].center(15),t[5].center(15),str(t[6]).center(15),t[7].center(15)))
  132.         print("%s" % "".center(144, "-"))

  134.     def GetAsmDiskGroup(self):
  135.         sqlStr="""select name,total_mb,free_mb from v$asm_diskgroup"""
  136.         res = self.ExecSql(sqlStr)
  137.         return res

  139.     def PrintAsmDiskGroup(self,res):
  140.         print("%s" % "".center(50, "-"))
  141.         print("|%s|%s|%s|"%("GROUP_NAME".center(20),"TOTAL_MB".center(15),"FREE_MB".center(15)))
  142.         print("%s" % "".center(50, "-"))
  143.         for t in res:
  144.             print("|%s|%s|%s|"%(t[0].center(20),str(t[1]).center(15),str(t[2]).center(15)))
  145.         print("%s" % "".center(50, "-"))

  147.     def GetRedo(self):
  148.         sqlStr="""
  149.             select a.group#,a.BYTES/1024/1024 mb,b.MEMBER,a.thread#,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time from gv$log a,gv$logfile b where a.GROUP#=b.GROUP# group by a.group#,a.thread#,a.BYTES/1024/1024,b.MEMBER,a.sequence#,a.members,a.archived,a.status,a.first_time,a.next_time order by group#
  150.             """
  151.         res = self.ExecSql(sqlStr)
  152.         return res

  154.     def PrintRedo(self,res):
  155.         print("%s" % "".center(148, "-"))
  156.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("GROUP".center(5),"SIZE_MB".center(7),"MEMBER".center(50),"THREAD".center(6),"SEQUENCE".center(8),"MEMBERS".center(7),"ARCHIVED".center(8),"STATUS".center(8),"FIRST_TIME".center(19),"NEXT_TIME".center(19)))
  157.         print("%s" % "".center(148, "-"))
  158.         for t in res:
  159.             if t[9] == None:
  160.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(5),str(t[1]).center(7),t[2].center(50),str(t[3]).center(6),str(t[4]).center(8),str(t[5]).center(7),t[6].center(8),t[7].center(8),datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S"),"None".center(19))
  161.             else:
  162.                 tStr = "|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|" % (
  163.                 str(t[0]).center(5), str(t[1]).center(7), t[2].center(50), str(t[3]).center(6), str(t[4]).center(8),
  164.                 str(t[5]).center(7), t[6].center(8), t[7].center(8),
  165.                 datetime.datetime.strftime(t[8], "%Y-%m-%d %H:%M:%S"),
  166.                 datetime.datetime.strftime(t[9], "%Y-%m-%d %H:%M:%S"))
  167.             print(tStr)
  168.         print("%s" % "".center(148, "-"))

  170.     def GetRedoShift(self):
  171.         sqlStr = """
  172.                   SELECT
  173.                   to_char(first_time,'YYYY-MM-DD') day,
  174.                   to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
  175.                   to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
  176.                   to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
  177.                   to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
  178.                   to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
  179.                   to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
  180.                   to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
  181.                   to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
  182.                   to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
  183.                   to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
  184.                   to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
  185.                   to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
  186.                   to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
  187.                   to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
  188.                   to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
  189.                   to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
  190.                   to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
  191.                   to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
  192.                   to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
  193.                   to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
  194.                   to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
  195.                   to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
  196.                   to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
  197.                   to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
  198.                   from
  199.                   v$log_history
  200.                   GROUP by
  201.                   to_char(first_time,'YYYY-MM-DD') order by day desc
  202.                  """
  203.         res = self.ExecSql(sqlStr)
  204.         return res

  206.     def PrintRedoShift(self,res):
  207.         print("%s" % "".center(132, "-"))
  208.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("DAY".center(10),"00".center(4),"01".center(4),"02".center(4),"03".center(4),"04".center(4),"05".center(4),"06".center(4),"07".center(4),"08".center(4),"09".center(4),"10".center(4),"11".center(4),"12".center(4),"13".center(4),"14".center(4),"15".center(4),"16".center(4),"17".center(4),"18".center(4),"19".center(4),"20".center(4),"21".center(4),"22".center(4),"23".center(4)))
  209.         print("%s" % "".center(132, "-"))
  210.         for t in res:
  211.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(t[0].center(10),t[1].center(4),t[2].center(4),t[3].center(4),t[4].center(4),t[5].center(4),t[6].center(4),t[7].center(4),t[8].center(4),t[9].center(4),t[10].center(4),t[11].center(4),t[12].center(4),t[12].center(4),t[13].center(4),t[14].center(4),t[15].center(4),t[16].center(4),t[17].center(4),t[18].center(4),t[19].center(4),t[20].center(4),t[21].center(4),t[22].center(4),t[23].center(4)))
  212.         print("%s" % "".center(132, "-"))


  215.     def GetExecNow(self):
  216.         sqlStr = """
  217.                 select distinct b.SID,b.SERIAL#,p.SPID,b.LAST_CALL_ET,a.sql_id, a.sql_text,b.status,b.event,b.MODULE, b.OSUSER,b.MACHINE from v$sql a,v$session b,v$process p where a.SQL_ID=b.SQL_ID and b.PADDR=p.ADDR and b.STATUS='ACTIVE' order by B.LAST_CALL_ET desc
  218.                 """
  219.         res = self.ExecSql(sqlStr)
  220.         return res

  222.     def PrintExecNow(self,res):
  223.         print("%s" % "".center(188, "-"))
  224.         print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%("SID".center(6),"SERIAL".center(6),"SPID".center(5),"LAST_ET".center(7),"SQL_ID".center(15),"SQL_TEXT".center(60),"STATUS".center(7),"EVENT".center(30),"MODULE".center(15),"OSUSER".center(10),"MACHINE".center(15)))
  225.         print("%s" % "".center(188, "-"))
  226.         for t in res:
  227.             print("|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|"%(str(t[0]).center(6),str(t[1]).center(6),t[2].center(5),str(t[3]).center(7),t[4].center(15),t[5].strip()[0:60].center(60),t[6].center(7),t[7][0:30].center(30),t[8][0:15].center(15),t[9].center(10),t[10][0:15].center(15)))
  228.         print("%s" % "".center(188, "-"))


  231.     def GetIndexInfo(self):
  232.         if self.Info["OraTable"] == None:
  233.             print("You must specify a oracle table to get indexs information of table.")
  234.             print("If you don't know how to specify the parameters.You can -h to get help")
  235.             exit(6)
  236.         sqlStr = """
  237.                 select
  238.                     table_name,
  239.                     TABLE_TYPE,
  240.                     INDEX_NAME,
  241.                     INDEX_TYPE,
  242.                     TABLE_OWNER,
  243.                     max(columns) columns
  244.                 from
  245.                 (SELECT
  246.                     ui.table_name,
  247.                     ui.TABLE_TYPE,
  248.                     ui.INDEX_NAME,
  249.                     ui.INDEX_TYPE,
  250.                     uic.TABLE_OWNER,
  251.                     to_char(wm_concat (uic.COLUMN_NAME)
  252.                         over(partition by ui.table_name,ui.TABLE_TYPE,ui.INDEX_NAME,ui.INDEX_TYPE,uic.TABLE_OWNER order by uic.COLUMN_POSITION)) columns
  253.                 FROM
  254.                     dba_indexes ui,
  255.                     dba_IND_COLUMNS uic
  256.                 WHERE
  257.                     ui.INDEX_NAME (+) = uic.INDEX_NAME
  258.                 AND ui.TABLE_NAME = UPPER ('%s'))
  259.                 GROUP BY
  260.                     table_name,
  261.                     TABLE_TYPE,
  262.                     INDEX_NAME,
  263.                     INDEX_TYPE,
  264.                     TABLE_OWNER
  265.                 """%self.Info["OraTable"]
  266.         res = self.ExecSql(sqlStr)
  267.         return res


  270.     def PrintIndexInfo(self,res):
  271.         print("%s" % "".center(142, "-"))
  272.         print("|%s|%s|%s|%s|%s|%s|"%("TABLE_NAME".center(20),"TABLE_TYPE".center(20),"INDEX_NAME".center(25),"INDEX_TYPE".center(15),"TABLE_OWNER".center(15),"COLUMNS".center(40)))
  273.         print("%s" % "".center(142, "-"))
  274.         for t in res:
  275.             print("|%s|%s|%s|%s|%s|%s|" % (t[0].center(20), t[1].center(20), t[2].center(25), t[3].center(15), t[4].center(15), t[5].center(40)))

  277.         print("%s" % "".center(142, "-"))


  280.     def GetPartInfo(self):
  281.         if self.Info["OraTable"] == None:
  282.             print("You must specify a oracle table to get indexs information of table.")
  283.             print("If you don't know how to specify the parameters.You can -h to get help")
  284.             exit(6)
  285.         sqlStr = """
  286.                 SELECT
  287.                     a.TABLE_OWNER,
  288.                     a.table_name,
  289.                     c. M,
  290.                     a.PARTITION_NAME,
  291.                     a.HIGH_VALUE,
  292.                     a.NUM_ROWS,
  293.                     a.TABLESPACE_NAME,
  294.                     b.COLUMN_NAME,
  295.                     A .LAST_ANALYZED
  296.                 FROM
  297.                     dba_TAB_PARTITIONS A,
  298.                     DBA_PART_KEY_COLUMNS b,
  299.                     (
  300.                         SELECT
  301.                             SUM (bytes / 1024 / 1024) M,
  302.                             segment_name,
  303.                             partition_name
  304.                         FROM
  305.                             dba_segments
  306.                         WHERE segment_type LIKE '%%TABLE%%'
  307.                         AND partition_name IS NOT NULL
  308.                         and segment_name = upper('%s')
  309.                         GROUP BY
  310.                             segment_name,
  311.                             partition_name
  312.                         ORDER BY
  313.                             segment_name,
  314.                             partition_name DESC
  315.                     ) c
  316.                 WHERE
  317.                     A .TABLE_NAME = b. NAME (+)
  318.                 AND A .table_name = c.SEGMENT_NAME
  319.                 AND A .partition_name = c.PARTITION_NAME
  320.                 AND A .table_name = upper('%s')
  321.                 ORDER BY
  322.                     A .TABLE_NAME,
  323.                     partition_name DESC
  324.                 """%(self.Info["OraTable"],self.Info["OraTable"])
  325.         res = self.ExecSql(sqlStr)
  326.         return res

  328.     def PrintPartInfo(self,res):
  329.         print("%s" % "".center(184, "-"))
  330.         print("|%s|" % "TABLE_OWNER".center(15), end='')
  331.         print("%s|" % "TABLE_NAME".center(25), end='')
  332.         print("%s|" % "MB".center(5), end='')
  333.         print("%s|" % "PARTITION_NAME".center(20), end='')
  334.         print("%s|" % "HIGH_VALUE".center(40), end='')
  335.         print("%s|" % "NUM_ROWS".center(10), end='')
  336.         print("%s|" % "TABLESPACE".center(20), end='')
  337.         print("%s|" % "CULUMN_NAME".center(20), end='')
  338.         print("%s|" % "LAST_ANALYZED".center(19))
  339.         print("%s" % "".center(184, "-"))

  341.         for t in res:
  342.             print("|%s|"%t[0].center(15),end='')
  343.             print("%s|"%t[1].center(25), end='')
  344.             print("%s|"%str(t[2]).center(5), end='')
  345.             print("%s|"%t[3].center(20), end='')
  346.             print("%s|"%t[4][0:40].center(40), end='')
  347.             print("%s|"%str(t[5]).center(10), end='')
  348.             print("%s|"%t[6].center(20), end='')
  349.             if t[7] == None:
  350.                 print("%s|"%"None".center(20))
  351.             else:
  352.                 print("%s|"%t[7].center(20), end='')
  353.             print("%s|"%(datetime.datetime.strftime(t[8],"%Y-%m-%d %H:%M:%S")))
  354.         print("%s" % "".center(184, "-"))

  356.     def OraInfo(self): #這個函式不格式化列印返回結果，只返回結果集
  357.         self.Check_Info()
  358.         if self.Info["OraInfo"].upper() == "TABLESPACE":
  359.             self.GetTableSpace()
  360.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
  361.             self.GetAsmDiskGroup()
  362.         elif self.Info["OraInfo"].upper() == "REDO":
  363.             self.GetRedo()
  364.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
  365.             self.GetRedoShift()
  366.         elif self.Info["OraInfo"].upper() == "EXECNOW":
  367.             self.GetExecNow()
  368.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
  369.             self.GetIndexInfo()
  370.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
  371.             self.GetPartInfo()
  372.         else:
  373.             print("Please enter valid value for -i")
  374.             exit(6)


  377.     def PrintOraInfo(self):
  378.         self.Check_Info()
  379.         if self.Info["OraInfo"].upper() == "TABLESPACE":
  380.             self.PrintTablespace(self.GetTableSpace())
  381.         elif self.Info["OraInfo"].upper() == "ASMDISKGROUP":
  382.             self.PrintAsmDiskGroup(self.GetAsmDiskGroup())
  383.         elif self.Info["OraInfo"].upper() == "REDO":
  384.             self.PrintRedo(self.GetRedo())
  385.         elif self.Info["OraInfo"].upper() == "REDOSHIFT":
  386.             self.PrintRedoShift(self.GetRedoShift())
  387.         elif self.Info["OraInfo"].upper() == "EXECNOW":
  388.             self.PrintExecNow(self.GetExecNow())
  389.         elif self.Info["OraInfo"].upper() == "INDEXINFO":
  390.             self.PrintIndexInfo(self.GetIndexInfo())
  391.         elif self.Info["OraInfo"].upper() == "PARTITIONINFO":
  392.             self.PrintPartInfo(self.GetPartInfo())
  393.         else:
  394.             print("Please enter valid value for -i")
  395.             exit(6)

  397. def fun_help():
  398.     helpStr = """
  399.     Name
  400.         CheckOracle.py display information which you want to know
  401.     
  402.     Synopsis
  403.         CheckOracle.py -H [romote_host] -u [oracle user] -p [oracle password] -s [oracle SID] -i [information] [table_name]
  404.     
  405.     Description
  406.         -H Specify a remote host,defaul 127.0.0.1.
  407.         -u Specify a oracle user
  408.         -p Specify a oracle password
  409.         -s Specify a oracle SID
  410.         -i Specify a action information what you want to know
  411.             Value:
  412.                 tablespace
  413.                 asmdiskgroup
  414.                 redo
  415.                 redoshift
  416.                 execnow
  417.                 indexinfo
  418.                     Table_name must be specified after indexinfo.
  419.                 partitioninfo
  420.                     Table_name must be specified after partitioninfo.
  421.     
  422.     Example
  423.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i execnow
  424.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i indexinfo dept
  425.         python3 CheckOracle.py -H 127.0.0.1 -u system -p BIIpass01 -s masdb3 -i partitioninfo dept
  426.     """
  427.     print(helpStr)

  429. if __name__=="__main__":
  430.     # print(sys.argv)
  431.     # print(len(sys.argv))

  433.     if len(sys.argv) > 1: #判斷是否傳遞了引數
  434.         for i in range(1,len(sys.argv)): #迴圈引數下標
  435.             # print(sys.argv[i])
  436.             if sys.argv[i] == "-H": #判斷引數
  437.                 if re.match("^-",sys.argv[i+1]) == None: #判斷當先的下一個引數是否為-開頭，-開頭不是正取的value值
  438.                     i += 1 #下標加1
  439.                     OracleConn.Info["host"] = sys.argv[i] #獲取引數值
  440.                 else:
  441.                     print("-H.You use this parameter,but no value are specified") #當前引數的下一個引數，不是正取的value值，報錯退出
  442.                     exit(2)
  443.             elif sys.argv[i] == "-u":
  444.                 if re.match("^-", sys.argv[i+1]) == None:
  445.                     i += 1
  446.                     OracleConn.Info["OraUser"] = sys.argv[i]
  447.                 else:
  448.                     print("-u.You use this parameter,but no value are specified")
  449.                     exit(2)
  450.             elif sys.argv[i] == "-p":
  451.                 if re.match("^-", sys.argv[i+1]) == None:
  452.                     i += 1
  453.                     OracleConn.Info["OraPasswd"] = sys.argv[i]
  454.                 else:
  455.                     print("-p.You use this parameter,but no value are specified")
  456.                     exit(2)
  457.             elif sys.argv[i] == "-P":
  458.                 if re.match("^-", sys.argv[i+1]) == None:
  459.                     i += 1
  460.                     OracleConn.Info["port"] = sys.argv[i]
  461.                 else:
  462.                     print("-P.You use this parameter,but no value are specified")
  463.                     exit(2)
  464.             elif sys.argv[i] == "-S":
  465.                 if re.match("^-", sys.argv[i+1]) == None:
  466.                     i += 1
  467.                     OracleConn.Info["OraSchemas"] = sys.argv[i].upper()
  468.                 else:
  469.                     print("-S.You use this parameter,but no value are specified")
  470.                     exit(2)
  471.             elif sys.argv[i] == "-s":
  472.                 if re.match("^-", sys.argv[i+1]) == None:
  473.                     i += 1
  474.                     OracleConn.Info["OraSID"] = sys.argv[i]
  475.                 else:
  476.                     print("-S.You use this parameter,but no value are specified")
  477.                     exit(2)
  478.             elif sys.argv[i] == "-i":
  479.                 if re.match("^-", sys.argv[i+1]) == None:
  480.                     i += 1
  481.                     OracleConn.Info["OraInfo"] = sys.argv[i]
  482.                     if i+1 < len(sys.argv) and re.match("^-", sys.argv[i+1]) == None:
  483.                         i += 1
  484.                         OracleConn.Info["OraTable"] = sys.argv[i]
  485.                 else:
  486.                     print("-s.You use this parameter,but no value are specified")
  487.                     exit(2)
  488.             elif sys.argv[i] == "-h":
  489.                 fun_help()
  490.                 exit(0)
  491.     else:
  492.         print("Please enter right parameters")
  493.         exit(1)
  494.     oc = OracleConn() #例項化類物件
  495.     oc.PrintOraInfo() #呼叫類的介面函式,列印結果















來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2153174/，如需轉載，請註明出處，否則將追究法律責任。

相關文章