0X01 Oracle常用SQL语句汇总
(1)获取数据表名和行数:
select OWNER, TABLE_NAME, NUM_ROWS from all_tables order by NUM_ROWS desc
(2)获取数据表的字段名和值为空的行数:
select COLUMN_NAME,COLUMN_ID,NUM_NULLS from all_tab_columns where TABLE_NAME = 'XXX' and OWNER = 'XXX' order by COLUMN_ID
(3)获取数据表和字段名的注释信息:
select a.OWNER, a.TABLE_NAME,c.COMMENTS,a.COLUMN_NAME,b.COMMENTS,a.COLUMN_ID,a.NUM_NULLS from all_tab_columns a
left join dba_col_comments b on a.COLUMN_NAME = b.COLUMN_NAME
left join user_tab_comments c on c.TABLE_NAME = a.TABLE_NAME
where a.OWNER = b.OWNER and b.OWNER='XXX' and a.TABLE_NAME = 'XXX' and b.TABLE_NAME =a.TABLE_NAME
order by a.COLUMN_ID
(4)获取查询结果的前10行:
select * from Table_Name where rownum <=10
(5)查看表操作历史记录:
select * from v$sqlarea
select * from ALL_TAB_MODIFICATIONS
0X02 Oracle导出数据到Excel表
#!/usr/bin/python2.7
# -*- coding: UTF-8 -*-
import cx_Oracle
import xlsxwriter
import sys
reload(sys)
sys.setdefaultencoding('utf8')
def conn_db():
conn_str = u'username/pass@192.168.55.33:1521/orcl'
con = cx_Oracle.connect(conn_str)
return con
def close_db(con):
con.close()
def Query_TabName(con):
cursor = con.cursor()
col_vaule = ''
sql = 'select * from TabName where COLUMN_NAME = %s' %(col_vaule)
cursor.execute(sql)
return cursor
def Save_DB2Excel():
con = conn_db()
Cursor = Query_TabName(con)
filename = u'./excel/信息文件.xlsx'
workbook = xlsxwriter.Workbook(filename)
worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells. 设置粗体,默认是False
bold = workbook.add_format({'bold': True})
bg_color = workbook.add_format({'bg_color':'FF0033'})
# Write some data headers. 带自定义粗体blod格式写表头
worksheet.write('A1', 'COLUMN_NAME', bold)
rows = 1
while True:
result = Cursor.fetchone() #依次读取一行数据
if result == None:
break
#依次写入一行各字段数据
for cols in range(len(result)):
# print type(result[cols])
if result[cols] is not None:
if type(result[cols]) is str:
worksheet.write(rows, cols, result[cols].decode('gbk')) #字符串类型 需要解码
else:
worksheet.write(rows, cols, result[cols],bg_color)
else:
worksheet.write(rows, cols, '——' )
print rows
rows = rows + 1
Cursor.close()
close_db(con)
print '正在保存数据....'
workbook.close()
print("EXIT!")
if __name__ == "__main__":
Save_DB2Excel()
备注:需要下载Oracle官方32位客户端,并在系统环境变量Path里添加该路径(客户端的位数与所安装Python的位数一致)