Dictionary information of Oracle Database, Using the python library (cx_Oracle) This is a general-purpose data acquisition script.
I think that it is common to get it with spool of sqlplus After all it is late, isn't it? Isn't it troublesome to write a script according to the acquired data every time? It's good to get it, but the format is rattling and you often get it again, right?
staticinfo.py
#!/usr/bin/python
import cx_Oracle
import os
import sys
import csv
USER='******' #Please change according to the environment(system)
PASS='********' #Please change according to the environment(manager)
HOST='***.***.***.***' #Please change according to the environment(192.168.0.1)
PORT='****' #Please change according to the environment(1521)
SERVICE_NAME='****' #Please change according to the environment(ORCL)
FETCH_ROWS=100 #Please change according to the environment
argv = sys.argv
argc = len(argv)
if argc != 2:
print('Usage: %s filename or tablename' % argv[0])
quit()
print("execution..." + argv[1])
p1 = argv[1]
name, ext = os.path.splitext(p1)
if ext == ".sql":
#SQL statement acquisition
f = open(p1)
sql = f.read()
f.close()
out_file_name = name + '.csv'
else:
#SQL statement creation
sql = 'select * from %s' % p1
out_file_name = p1.replace('$','_') + '.csv'
with cx_Oracle.connect(USER,PASS,HOST+':'+PORT+'/'+SERVICE_NAME) as conn:
#SQL statement execution
cur_detail = conn.cursor()
cur_detail.execute(sql)
#Get column name
csv_header = [str[0] for str in cur_detail.description]
#Get the data part
f = open(out_file_name, 'w')
writer = csv.writer(f, lineterminator='\n', quoting=csv.QUOTE_ALL)
writer.writerow(csv_header)
while 1:
csv_detail = cur_detail.fetchmany(FETCH_ROWS)
if len(csv_detail) == 0:
break
writer.writerows(csv_detail)
cur_detail.close()
$ python staticinfo.py dba_users
-It is necessary to specify environment variables (ORACLE_HOME / LD_LIBRARY_PATH), etc. -The target file (dba_users.csv) is created in the current directory. -If you prepare a script in advance, it is possible to convert the result of the script to csv.
Create a shell like the one below and output it all at once.
staticinfo.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/client_1
export LANG=ja_JP.UTF-8
export NLS_LANG=Japanese_Japan.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
python staticinfo.py dba_users
python staticinfo.py dba_profiles
python staticinfo.py dba_data_files
python staticinfo.py dba_temp_files
python staticinfo.py dba_tablespaces
python staticinfo.py dba_segments
python staticinfo.py dba_roles
python staticinfo.py dba_role_privs
python staticinfo.py dba_sys_privs
python staticinfo.py dba_tab_privs
python staticinfo.py gv\$database
python staticinfo.py gv\$instance
python staticinfo.py gv\$log
python staticinfo.py gv\$logfile
python staticinfo.py gv\$controlfile
python staticinfo.py gv\$parameter2
python staticinfo.py 01_tablespace_capacity.sql
python staticinfo.py 02_datafile_capacity.sql
python staticinfo.py 03_tempfile_capacity.sql
I wanted to make it generic, so I was particular about executing the SQL file and automatically getting the column name. Of course, it is also possible to acquire user data. Depending on the customization, I think that the script has potential.
Recommended Posts