Python查询Oracle中指定表并收集其统计信息

1、环境准备

首先要确保当前环境已经安装了Python及cx_Oracle模块，Linux下安装cx_Oracle模块链接
https://blog.youkuaiyun.com/u011285708/article/details/104096011

创建不同用户下的3张表

SYS@TNS_PDB01>conn scott/scott@TNS_PDB01 ;
Connected.

SCOTT@TNS_PDB01>create table t1 as select * from dept;

Table created.

SCOTT@TNS_PDB01>conn sys/oracle@TNS_PDB01 as sysdba
Connected.
SYS@TNS_PDB01>create table t2 as select * from scott.emp;

Table created.

SCOTT@TNS_PDB01>create table hr.t3 as select * from dept;

Table created.

执行收集统计信息脚本前

SYS@TNS_PDB01>select owner,table_name,last_analyzed from dba_tab_statistics where table_name in ('T1','T2','T3') ;

OWNER	   TABLE_NAME	   LAST_ANALYZED
---------- --------------- -------------------
SYS	   T2
SCOTT	   T1		   2020-01-28 10:08:59
HR	   T3		   2020-01-28 13:16:01

2、编写并执行Python脚本

[root@kkk my_python]# cat exec.py 
#!/usr/bin/env python

import cx_Oracle

conn = cx_Oracle.connect('sys/oracle@TNS_PDB01',mode = cx_Oracle.SYSDBA)
#print(conn.version)
cursor = conn.cursor()
sql1 = "select owner,table_name from dba_tab_statistics where table_name in ('T1','T2','T3')"

cursor.execute(sql1)
rows = cursor.fetchall()

for row in rows:
	#print(row)
	ownname = row[0]
	tabname = row[1]
	#print(tabname + "'s owner is " + ownname + ".")
	cursor.execute("""
		begin
			dbms_stats.gather_table_stats(
				ownname => '""" + ownname + """',
				tabname => '"""+ tabname  + """',
				estimate_percent => 100,
				method_opt => 'FOR ALL COLUMNS SIZE 1',
				cascade => true,
				no_invalidate => false);
		end;
	""")
cursor.close()
conn.close()

执行脚本

python3 exec.py

3、查看执行收集统计信息脚本后统计信息最后收集时间

SYS@TNS_PDB01>select owner,table_name,last_analyzed from dba_tab_statistics where table_name in ('T1','T2','T3') ;

OWNER	   TABLE_NAME	   LAST_ANALYZED
---------- --------------- -------------------
SYS	   T2		   2020-01-28 13:28:01
SCOTT	   T1		   2020-01-28 13:28:02
HR	   T3		   2020-01-28 13:28:02

最近在捣鼓Python，感觉Python能做的事有很多，得慢慢研究…，文档中有啥不对的请指正，谢谢！