A python script that converts Oracle Database data to csv

Purpose

Dictionary information of Oracle Database, Using the python library (cx_Oracle) This is a general-purpose data acquisition script.

why?

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?

Created script

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()

Execution method

$ 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.

Because it is troublesome

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

Finally

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.