Захват продуктивной SQL нагрузки

-- Создаем STS:

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
END;
/


--Захватываем SQL из CURSOR_CACHE в SQLSET:

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/


-- Просмотр STS:

SELECT name, created, statement_count
FROM dba_sqlset;

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;

SELECT sql_id, elapsed_time
,cpu_time, buffer_gets
,disk_reads, sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('PROD_WORKLOAD'));


-- Выборочно удалаем ненужные SQL statements из STS:

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,basic_filter => 'disk_reads < 100000');
END;
/
select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;



-- Создаем таблицу для загрузки SQL statements из STS:

BEGIN
dbms_sqltune.create_stgtab_sqlset(
table_name => 'STS_TABLE'
,schema_name => 'SCOTT');
END;
/
select * from STS_TABLE;


-- Загружаем в неё SQL statements из STS:

BEGIN
dbms_sqltune.pack_stgtab_sqlset(
sqlset_name => 'PROD_WORKLOAD'
,sqlset_owner => 'SCOTT'
,staging_table_name => 'STS_TABLE'
,staging_schema_owner => 'SCOTT');
END;
/
select * from STS_TABLE;

SELECT name, owner, created, statement_count
FROM dba_sqlset;


-- Переносим  таблицу  STS_TABLE в другую СУБД:

drop database link source_db;
create database link source_db
connect to scott
identified by tiger
using 'testdb';

create table STS_TABLE as select * from STS_TABLE@source_db;

-- Создать все STS из таблицы (с опцией replace):

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%'
,replace => TRUE
,staging_table_name => 'STS_TABLE'
,staging_schema_owner=> 'SCOTT');
END;
/

-- Проверяем, что STS создан:

SELECT name, owner, created, statement_count
FROM dba_sqlset;

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

Загрузка SQL statements в SQL Tuning Set из CURSOR_CACHE

Параметры функции:

DBMS_SQLTUNE.SELECT_CURSOR_CACHE (
  basic_filter        IN   VARCHAR2 := NULL,
  object_filter       IN   VARCHAR2 := NULL,
  ranking_measure1    IN   VARCHAR2 := NULL,
  ranking_measure2    IN   VARCHAR2 := NULL,
  ranking_measure3    IN   VARCHAR2 := NULL,
  result_percentage   IN   NUMBER   := 1,
  result_limit        IN   NUMBER   := NULL,
  attribute_list      IN   VARCHAR2 := NULL)
 RETURN sys.sqlset PIPELINED;


sqlset_name   The SQL tuning set name

basic_filter  The SQL predicate to filter the SQL from the cursor cache defined on attributes of the
ELAPSED_TIME
CPU_TIME
BUFFER_GETS
DISK_READS
DIRECT_WRITES
ROWS_PROCESSED


object_filter Specifies the objects that should exist in the object list of selected SQL from the cursor cache

ranking_measure(n) An order-by clause on the selected SQL

result_percentage  A filter which picks the top N% according to the ranking measure given. Note that this applies only if one ranking measure is given.

result_limit  The top L(imit) SQL from the (filtered) source ranked by the ranking measure

attribute_list  List of SQL statement attributes to return in the result. The possible values are:

    BASIC (default) -all attributes (such as execution statistics and binds) are returned except the plans The execution context is always part of the result.

    TYPICAL - BASIC + SQL plan (without row source statistics) and without object reference list

    ALL - return all attributes

    Comma separated list of attribute names this allows to return only a subset of SQL attributes:
    EXECUTION_STATISTICS, BIND_LIST, OBJECT_LIST, SQL_PLAN,SQL_PLAN_STATISTICS: similar to SQL_PLAN + row source statistics


Return Values

This function returns a one SQLSET_ROW per SQL_ID or PLAN_HASH_VALUE pair found in each data source.



Примеры запросов:

SELECT *  FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE);
   


SELECT *  FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                                                                                           'parsing_schema_name <> ''SYS'''  ));
    

SELECT sql_id, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;


SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''bc688uy91wzka'''));


SELECT sql_id, plan_hash_value
FROM table(dbms_sqltune.select_cursor_cache('sql_id = ''bc688uy91wzka'''))
ORDER BY sql_id, plan_hash_value;


SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('module = ''MMON_SLAVE'''));


-- all statements that ran for at least five seconds   
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 5000000'));

   
-- select all statements that pass a simple buffer_gets threshold and
-- are coming from an SCOTT user
SELECT *
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'buffer_gets > 100 and parsing_schema_name = ''SCOTT'''));

       
-- select all statements exceeding 5 seconds in elapsed time, but also
-- select the plans (by default we only select execution stats and binds
-- for performance reasons - in this case the SQL_PLAN attribute of sqlset_row
-- is NULL)        
SELECT *
FROM table(dbms_sqltune.select_cursor_cache(
'elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL,
'EXECUTION_STATISTICS, SQL_BINDS, SQL_PLAN'));


-- Select the top 100 statements in the cursor cache ordering by elapsed_time.     
SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
NULL, NULL, 'ELAPSED_TIME', NULL, NULL, 1, 100));
                                               

SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;


SELECT sql_id, substr(sql_text,1,20), disk_reads
,cpu_time, elapsed_time
,buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'parsing_schema_name <> "SYS"'
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));


SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> "SYS"
AND elapsed_time > 1000000'))
ORDER BY sql_id;




-- Запрос к  CURSOR_CACHE
SELECT *
FROM TABLE( DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1  => NULL,
          ranking_measure2  => NULL,
          ranking_measure3  => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' ));


-- Удаляем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/


-- Создаем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/



-- Загружаем SQL statements в SQL Tuning Set из CURSOR_CACHE
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
          basic_filter      => 'parsing_schema_name <> ''SYS'' AND disk_reads > 100',
          object_filter     => NULL,
          ranking_measure1  => NULL,
          ranking_measure2  => NULL,
          ranking_measure3  => NULL,
          result_percentage => 1,
          result_limit      => NULL,
          attribute_list    => 'ALL' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
  CLOSE ref_cursor;
END;
/


-- Просмотр STS:

SELECT * FROM  DBA_SQLSET_STATEMENTS;


-- Просмотр плана из STS:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => 'btd7m8k25qr9h',          
       plan_hash_value => 1081635106
       ));


-- Планы из CURSOR_CACHE можно посмотреть так:

-- находим sql_id:

select * from table (dbms_sqltune.select_cursor_cache('sql_text like ''select /*MY_CRITICAL_SQL*/%'''));
select * from table (dbms_sqltune.select_cursor_cache('sql_id = ''4n3pdustvb0yk'''));

 -- смотрим план:

select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'BASIC'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL -projection'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0, 'ALL +peeked_binds'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +alias -predicate'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ALLSTATS LAST +outline'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED'));
select * from table (dbms_xplan.display_cursor('4n3pdustvb0yk',0,'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));


dbms_xplan.display_cursor(
sql_id          IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN INTEGER DEFAULT 0,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;


-- или через SQL monitor:

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => '&sql_id', type=>'TEXT' , report_level => 'ALL') from dual;
/

Загрузка SQL statements в SQL Tuning Set из AWR

-- Запрос к  AWR
SELECT *
  FROM TABLE(
     DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
      begin_snap   => 415,  
      end_snap     => 420,   
      basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p
WHERE parsing_schema_name = 'SCOTT';



-- Удаляем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/


-- Создаем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/


-- Загружаем SQL statements в SQL Tuning Set из AWR
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
         begin_snap   => 415,  
         end_snap     => 420,   
         basic_filter => 'SQL_ID = ''0r1gq4aapgnxd''' )) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
  CLOSE ref_cursor;
END;
/


-- Просмотр STS:

SELECT * FROM  DBA_SQLSET_STATEMENTS;


SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => '0r1gq4aapgnxd',           
       plan_hash_value => 426858176
       ));




Список параметров функции select_workload_repository:

dbms_sqltune.select_workload_repository(
            BEGIN_SNAP        => 415,
            END_SNAP          => 420,
            BASIC_FILTER      => 'SQL_ID = ''64qdks642mqt2'' AND PLAN_HASH_VALUE = 3406458838',
            OBJECT_FILTER     => NULL,
            RANKING_MEASURE1  => 'disk_reads',
            RANKING_MEASURE2  => NULL,
            RANKING_MEASURE3  => NULL,
            RESULT_PERCENTAGE => 1,
            RESULT_LIMIT      => 250,
            ATTRIBUTE_LIST    => 'ALL',
            RECURSIVE_SQL     => 'Y'
)


Вместо   BEGIN_SNAP  и END_SNAP,  можно указать BASELINE_NAME.


BEGIN_SNAP          Non-inclusive beginning snapshot ID

END_SNAP            Inclusive ending snapshot ID

BASELINE_NAME       Name of AWR baseline

BASIC_FILTER        SQL predicate to filter SQL statements from workload; if not set, then only SELECT, INSERT, UPDATE, DELETE, MERGE, and CREATE TABLE statements are captured.

OBJECT_FILTER       Not currently used

RANKING_MEASURE(n)  Order by clause on selected SQL statement(s), such as elapsed_time, cpu_time, buffer_gets, disk_reads, and so on;
N can be 1, 2, or 3. The elapsed_time and cpu_time are measured in seconds.

RESULT_PERCENTAGE   Filter for choosing top N% for ranking measure

RESULT_LIMIT        Limit of the number of SQL statements returned in the result set

ATTRIBUTE_LIST      List of SQL statement attributes (TYPICAL, BASIC, ALL, and so on)

RECURSIVE_SQL       Include/exclude recursive SQL (HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL)



cpu_time  : Number of seconds
elapsed_time : Number of seconds
disk_reads : Number of reads from disk
buffer_gets : Number of reads from memory
rows_processed : Average number of rows
optimizer_cost : Calculated optimizer cost
executions : Total execution count of SQL statement



Примеры использования параметров:

BASIC_FILTER   => 'parsing_schema_name <> "SYS"'  
( 'parsing_schema_name not in  (''DBSNMP'',''SYS'',''ORACLE_OCM'')',)

RANKING_MEASURE1  => 'cpu_time'
( 'elapsed_time', buffer_gets, disk_reads, )





Еще несколько примеров :

SELECT snap_id, instance_number, end_interval_time

FROM dba_hist_snapshot

ORDER BY snap_id;

SELECT sql_id

,substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time

FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

null, null, 'disk_reads',null, null, null, 10))

ORDER BY disk_reads DESC;

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(415,420,

'parsing_schema_name <> ''SYS''',

NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

==========================================================

SELECT sql_id, substr(sql_text,1,20)

,disk_reads, cpu_time, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'buffer_gets'

,result_limit => 10

));

==========================================================

--

SELECT MAX(snap_id) bsnap

FROM dba_hist_snapshot

WHERE begin_interval_time < sysdate-7;

--

SELECT MAX(snap_id) esnap

FROM dba_hist_snapshot;

--

COL sql_text FORMAT A40

COL sql_id FORMAT A15

COL parsing_schema_name FORMAT A15

COL cpu_seconds FORMAT 999,999,999,999,999

SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON

--

SELECT sql_id, sql_text

,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name

FROM table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(

begin_snap => 415

,end_snap => 420

,basic_filter => 'parsing_schema_name <> ''SYS'''

,ranking_measure1 => 'cpu_time'

,result_limit => 10

));

-- Посмотреть менялся ли план у запроса:
 

select   snap_id, plan_hash_value
    from dba_hist_sqlstat
   where snap_id in (415,420) and sql_id='0r1gq4aapgnxd'
order by snap_id desc;

-- Посмотреть из AWR какие планы изменялись:

select   a.sql_id, a.plan_hash_value snap_id_1_plan, b.plan_hash_value snap_id_2_plan
    from dba_hist_sqlstat a, dba_hist_sqlstat b
   where (a.snap_id = 415 and b.snap_id = 420)
     and (a.sql_id = b.sql_id)
     and (a.plan_hash_value != b.plan_hash_value)
order by a.sql_id; 

-- по всему репозиторию:

select distinct sql_id, plan_hash_value, f snapshot,
                (select begin_interval_time
                   from dba_hist_snapshot
                  where snap_id = f) snapdate
           from (select sql_id, plan_hash_value,
                        first_value (snap_id) over (partition by sql_id, plan_hash_value order by snap_id) f
                   from (select   sql_id, plan_hash_value, snap_id,
                                  count (distinct plan_hash_value) over (partition by sql_id) a
                             from dba_hist_sqlstat
                            where plan_hash_value > 0
                         order by sql_id)
                  where a > 1)
       order by sql_id, f;


-- Планы из AWR можно посмотреть так:

select * from table(dbms_xplan.display_awr('5k5207588w9ry'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953 ));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'BASIC'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL -projection'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALL +peeked_binds'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED'));
select * from table(dbms_xplan.display_awr('5k5207588w9ry', 1388734953, null, 'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS'));


dbms_xplan.display_awr(
sql_id          IN VARCHAR2,
plan_hash_value IN INTEGER DEFAULT NULL,
db_id           IN INTEGER DEFAULT NULL,
format          IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN dbms_xplan_type_table PIPELINED;
 

Установка пакетов в Linux

Oracle Linux 7

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol7.repo

Oracle Linux 6

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo

Oracle Linux 5

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo



rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' binutils \
compat-libcap1 \
compat-libstdc++-33 \
gcc \
gcc-c++ \
glibc \
glibc-devel \
ksh \
libgcc \
libstdc++ \
libstdc++-devel \
libaio \
libaio-devel \
make \
sysstat | grep not

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc-devel -y
yum install ksh -y
yum install libgcc -y
yum install libstdc++ -y
yum install libstdc++-devel -y
yum install libaio -y
yum install libaio-devel -y
yum install make -y
yum install sysstat -y





rpm -q --qf '%{NAME}-%{VERSION}-%{RELEASE}(%{ARCH})\n' compat-libstdc++-33.i686 \
glibc.i686 \
glibc-devel.i686 \
libgcc.i686 \
libstdc++.i686 \
libstdc++-devel.i686 \
libaio.i686 \
libaio-devel.i686 | grep not

yum install compat-libstdc++-33.i686 -y
yum install glibc.i686 -y
yum install glibc-devel.i686 -y
yum install libgcc.i686 -y
yum install libstdc++.i686 -y
yum install libstdc++-devel.i686 -y
yum install libaio.i686 -y
yum install libaio-devel.i686 -y



Опционально можно установить:

yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y


Для работы с источниками данных ODBC:

yum install unixODBC -y
yum install unixODBC-devel -y


OpenSSH требует, чтобы был установлен пакет zlib-devel,
который содержит заголовочные файлы и библиотеки нужные программам,
использующим библиотеки zlib компрессии и декомпрессии.

yum install zlib-devel -y

Загрузка SQL statements в SQL Tuning Set из Trace File

-- Включаем трассировку
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'


-- Выполняем запросы
SELECT 1 FROM DUAL;
SELECT COUNT(*) FROM all_objects WHERE object_type = 'SCHEDULE';


-- Выключаем трассировку
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';


-- Создаем  mapping таблицу
DROP TABLE map_tab;
CREATE TABLE map_tab AS
SELECT object_id id, owner, substr(object_name, 1, 30) name
   FROM dba_objects
   WHERE object_type NOT IN ('CONSUMER GROUP', 'EVALUATION CONTEXT',
                             'FUNCTION', 'INDEXTYPE', 'JAVA CLASS',
                             'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY',
                             'LOB', 'OPERATOR', 'PACKAGE',
                             'PACKAGE BODY', 'PROCEDURE', 'QUEUE',
                             'RESOURCE PLAN', 'TRIGGER', 'TYPE',
                             'TYPE BODY')
UNION ALL
SELECT user_id id, username owner, NULL name
   FROM dba_users;
  

-- Находим файл трассировки текущей сессии
SELECT name, value FROM v$diag_info WHERE   name ='Default Trace File';

--- /u01/app/oracle/diag/rdbms/testdb_p/testdb/trace/testdb_ora_3648.trc


-- Создаем объект directory
CREATE DIRECTORY SQL_TRACE_DIR as '/u01/app/oracle/diag/rdbms/testdb_p/testdb/trace';



-- Удаляем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.DROP_SQLSET(
    sqlset_name => 'my_sts');
END;
/


-- Создаем SQL Tuning Set
BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'my_sts',
    description => 'SQL Tuning Set for loading from trace file');
END;
/


-- Загружаем SQL statements в SQL Tuning Set из ранее созданного Trace File
DECLARE
  ref_cursor sys_refcursor;
BEGIN
   OPEN ref_cursor FOR
   SELECT value(p)
     FROM TABLE(
        DBMS_SQLTUNE.SELECT_SQL_TRACE(
           directory=>'SQL_TRACE_DIR',
           file_name=>'testdb_ora_3648.trc',
           mapping_table_name=>'map_tab')) p;
   DBMS_SQLTUNE.LOAD_SQLSET(
     sqlset_name => 'my_sts',
     populate_cursor => ref_cursor,
     sqlset_owner => 'SCOTT'
     );
  CLOSE ref_cursor;
END;
/



-- Просмотр STS:

SELECT * FROM  DBA_SQLSET_STATEMENTS;

-- Используя функцию select_sqlset
SELECT
  first_load_time,
  executions as execs,
  parsing_schema_name,
  elapsed_time  / 1000000 as elapsed_time_secs,
  cpu_time / 1000000 as cpu_time_secs,
  buffer_gets,
  disk_reads,
  direct_writes,
  rows_processed,
  fetches,
  optimizer_cost,
  sql_plan,
  plan_hash_value,
  sql_id,
  sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'my_sts'));

            
-- Обработка в курсорном цикле
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value (p)
    FROM table(dbms_sqltune.select_sqlset(sqlset_name => 'my_sts')) p;

  -- Process each statement (or pass cursor to load_sqlset)

  CLOSE cur;
END;
/

--Можно использовать функцию display_sqlset:
SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       sqlset_name => 'my_sts',
       sql_id => '4xjfbbpfj40xc',            
       plan_hash_value => 4294967295
       ));



С помощью функции  SELECT_SQL_TRACE, можно непосредственно обращаться к файлу трассировки:


SELECT *
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2                 -- (1- only first execution,   2 - all executions)
             )) t
WHERE parsing_schema_name = 'SCOTT'
ORDER BY elapsed_time DESC;


SELECT sql_id,
          sum(elapsed_time) AS elapsed_time,
          sum(executions) AS executions,
          round(sum(elapsed_time)/sum(executions)) AS elapsed_time_per_execution
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
             )) t
WHERE parsing_schema_name = 'SCOTT'
GROUP BY sql_id
ORDER BY elapsed_time DESC;



SELECT plan_hash_value, executions, fetches, elapsed_time, cpu_time, disk_reads, buffer_gets, rows_processed
FROM table(dbms_sqltune.select_sql_trace(
                 directory => 'SQL_TRACE_DIR',
                 file_name => 'testdb_ora_3648.trc',
                 select_mode => 2
              )) t
WHERE sql_id = '4xjfbbpfj40xc'
ORDER BY elapsed_time DESC;


SELECT elapsed_time,
          value(b).gettypename() AS type,
          value(b).accessnumber() AS value
FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
             )) t,
        table(bind_list) b
WHERE sql_id = '4xjfbbpfj40xc'
ORDER BY elapsed_time DESC;



Можно загрузить SQL statements в SQL Tuning Set так:

DECLARE
     cur sys_refcursor;
BEGIN
     dbms_sqltune.create_sqlset('my_sts');
     OPEN cur FOR
       SELECT value(p)
       FROM table(dbms_sqltune.select_sql_trace(
                directory => 'SQL_TRACE_DIR',
                file_name => 'testdb_ora_3648.trc',
                select_mode => 2
                 )) p;
     dbms_sqltune.load_sqlset('my_sts', cur);
     CLOSE cur;
END;
 /

Подключение к Oracle из java

Переменные окружения:

CLASSPATH=C:\app\client\oracle\product\12.1.0\client_1\jdbc\lib\ojdbc6.jar;C:\app\client\oracle\product\12.1.0\client_1\jlib\orai18n.jar;.
Path=C:\app\client\oracle\product\12.1.0\client_1\bin;C:\Program Files\Java\jdk1.8.0_25\bin;


TestDBOracle.java


import java.sql.*;

public class TestDBOracle {

    public static void main(String[] args)
    throws ClassNotFoundException, SQLException {

        //jdbc драйвер можно зарегистрировать так:
        Class.forName("oracle.jdbc.driver.OracleDriver");
        // или так:
        // DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

       
        // Для подключения можно использовать тонкий драйвер
        //jdbc:oracle:thin:@//host:port/service   
        String url = "jdbc:oracle:thin:@//alpha:1521/testdb_p.localdomain"; 
        // или драйвер OCI
        // String url = "jdbc:oracle:oci:@//alpha:1521/testdb_p.localdomain";
        // Причем для обоих драйверов вместо //host:port/service , можно указать так:
        // String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=alpha.localdomain)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=testdb_p.localdomain)))";
        // Для кластера так:
        // String url = "jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=yes)(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1-VIP)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2-VIP)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORARAC)))";   


        Connection conn = DriverManager.getConnection(url,"scott","tiger");

        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement();
        ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
        while (rset.next()) {
            System.out.println (rset.getString(1));
        }

        stmt.close();   

        System.out.println ("Success!");

    }
}


C:\project\java>java TestDBOracle

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Success!

C:\project\java>



Еще несколько примеров использования jdbc:

import java.sql.*;

public class instest
{
    static public void main(String args[]) throws Exception
    {
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection
            conn = DriverManager.getConnection
            ("jdbc:oracle:thin:@heesta:1521:ORA12CR1","scott","tiger");
        conn.setAutoCommit( false );
        Statement stmt = conn.createStatement();
        for( int i = 0; i < 25000; i++ )
        {
            stmt.execute
            ("insert into "+ args[0] +
                " (x) values(" + i + ")" );
        }
        conn.commit();
        conn.close();
    }
}



import java.sql.*;

public class instest
{
    static public void main(String args[]) throws Exception
    {
        System.out.println( "start" );
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        Connection
            conn = DriverManager.getConnection
                ("jdbc:oracle:thin:@heesta:1521:ORA12CR1", "scott","tiger");
        conn.setAutoCommit( false );
        PreparedStatement pstmt =
            conn.prepareStatement
            ("insert into "+ args[0] + " (x) values(?)" );
        for( int i = 0; i < 25000; i++ )
        {
            pstmt.setInt( 1, i );
            pstmt.executeUpdate();
        }
        conn.commit();
        conn.close();
        System.out.println( "done" );
    }
}



import java.sql.*;

public class perftest
{
    public static void main (String arr[]) throws Exception
    {
        DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
        Connection con = DriverManager.getConnection
            ("jdbc:oracle:thin:@csxdev:1521:ORA12CR1", "scott", "tiger");
        Integer iters = new Integer(arr[0]);
        Integer commitCnt = new Integer(arr[1]);
        con.setAutoCommit(false);
        doInserts( con, 1, 1 );
        Statement stmt = con.createStatement ();
        stmt.execute( "begin dbms_monitor.session_trace_enable(waits=>true); end;" );
        doInserts( con, iters.intValue(), commitCnt.intValue() );
       con.close();
    }
    static void doInserts(Connection con, int count, int commitCount )
    throws Exception
    {
        PreparedStatement ps =
            con.prepareStatement
            ("insert into test " +
             "(id, code, descr, insert_user, insert_date)"
             + " values (?,?,?, user, sysdate)");

            int rowcnt = 0;
            int committed = 0;
            for (int i = 0; i < count; i++ )
            {
                ps.setInt(1,i);
                ps.setString(2,"PS - code" + i);
                ps.setString(3,"PS - desc" + i);
                ps.executeUpdate();
                rowcnt++;
                if ( rowcnt == commitCount )
                {
                    con.commit();
                    rowcnt = 0;
                    committed++;
                }
            }
            con.commit();
            System.out.println
            ("pstatement rows/commitcnt = " + count + " / " + committed );
     }
}

Установка пакета cx_Oracle

1. Устанавливаем Oracle Client

2. Выставляем переменные окружения

[angor@omega admin]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/client_1
[angor@omega admin]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[angor@omega admin]$ export PATH=$ORACLE_HOME/bin:$PATH

3. Устанавливаем  cx_oracle

[angor@omega admin]$ sudo easy_install cx_oracle

4. Проверяем

[angor@omega ~]$ python
Python 3.4.1 (default, May 19 2014, 17:23:49)
[GCC 4.9.0 20140507 (prerelease)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>>


import cx_Oracle
ip = '192.168.0.1'
port = 1521
SID = 'YOURSIDHERE'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
db = cx_Oracle.connect('username', 'password', dsn_tns)


import cx_Oracle
connstr = 'scott/tiger@server:1521/orcl'
conn = cx_Oracle.connect(connstr)


import cx_Oracle
CONN_INFO = {
    'host': '192.168.0.1',
    'port': 1521,
    'user': 'user_name',
    'psw': 'your_password',
    'service': 'my_service',
}
CONN_STR = '{user}/{psw}@{host}:{port}/{service}'.format(**CONN_INFO)
connection = cx_Oracle.connect(CONN_STR)


import cx_Oracle
ip = '192.168.0.1'
port = 1521
service_name = 'my_service'
dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)
db = cx_Oracle.connect('user', 'password', dsn)


import cx_Oracle
dsn = cx_Oracle.makedsn(host='127.0.0.1', port=1521, sid='your_sid')
conn = cx_Oracle.connect(user='angor', password='password', dsn=dsn)
conn.close()


import cx_Oracle
ip = '192.168.0.1'
port = 1524
SID = 'dev3'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
conn = cx_Oracle.connect('angor', 'pass', dsn_tns)
print conn.version
conn.close()


Пример:

test_ora.py

import sys
import getpass
import platform
import cx_Oracle

# Версии Python и модулей

print ("Python version: " + platform.python_version())
print ("cx_Oracle version: " + cx_Oracle.version)
print ("Oracle client: " + str(cx_Oracle.clientversion()).replace(', ','.'))
print ('-' * 90)

# Приконнектимся к Oracle

username = 'scott'
pwd = 'tiger'
database = 'testdb'
connection = cx_Oracle.connect(username, pwd, database)

# Или так:
#connection = cx_Oracle.connect('scott', 'tiger', 'testdb')
#connection = cx_Oracle.connect('scott/tiger@testdb')


# Некоторые атрибуты объекта connection:
print ("Oracle DB version: " + connection.version)
print ("Oracle client encoding: " + connection.encoding)
print ('-' * 90)


# Создадим курсор и выполним запрос к БД:

cursor = connection.cursor()
query = "select * from v$version"

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

for row in rows:
    print(row)

connection.close()

C:\project\py>test_ora.py

Python version: 3.5.1
cx_Oracle version: 5.2
Oracle client: (12.1.0.2.0)
------------------------------------------------------------------------------------------
Oracle DB version: 12.1.0.2.0
Oracle client encoding: WINDOWS-1252
------------------------------------------------------------------------------------------
('Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production', 0)
('PL/SQL Release 12.1.0.2.0 - Production', 0)
('CORE\t12.1.0.2.0\tProduction', 0)
('TNS for 64-bit Windows: Version 12.1.0.2.0 - Production', 0)
('NLSRTL Version 12.1.0.2.0 - Production', 0)

C:\project\py>


Ещё способы создания объектов соединений с БД Oracle:



import cx_Oracle

#lsnrctl servises
service = 'testdb.localdomain'


connection = cx_Oracle.connect('scott', 'tiger', 'localhost:1521/' + service)


connection = cx_Oracle.connect('scott/tiger@localhost:1521/' + service)


dsn_tns = cx_Oracle.makedsn('localhost', 1521, service).replace('SID','SERVICE_NAME')

print(dsn_tns)

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=testdb.localdomain)))

connection = cx_Oracle.connect('scott', 'tiger', dsn_tns)



Пример создания таблицы:

DROP TABLE EMP;

CREATE TABLE EMP(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2)
);

alter session set nls_date_format='DD-MON-YYYY';
alter session set nls_language=AMERICAN;


INSERT INTO EMP VALUES(7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800,  NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN',  'SALESMAN',  7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300,  30);
INSERT INTO EMP VALUES(7521, 'WARD',   'SALESMAN',  7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500,  30);
INSERT INTO EMP VALUES(7566, 'JONES',  'MANAGER',   7839,TO_DATE('2-APR-1981',  'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN',  7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE',  'MANAGER',   7839,TO_DATE('1-MAY-1981',  'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK',  'MANAGER',   7839,TO_DATE('9-JUN-1981',  'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT',  'ANALYST',   7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING',   'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN',  7698,TO_DATE('8-SEP-1981',  'DD-MON-YYYY'), 1500, 0,    30);
INSERT INTO EMP VALUES(7876, 'ADAMS',  'CLERK',     7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES',  'CLERK',     7698,TO_DATE('3-DEC-1981',  'DD-MON-YYYY'), 950,  NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD',   'ANALYST',   7566,TO_DATE('3-DEC-1981',  'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK',     7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);



С использованием cx_Oracle:

import cx_Oracle

connection = cx_Oracle.connect('scott/tiger@testdb')

cursor = connection.cursor()

try:
    cursor.execute("DROP TABLE EMP")
except:
    print('Таблица не существует')


create_table = """
CREATE TABLE EMP(
    EMPNO NUMBER(4) NOT NULL,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7, 2),
    COMM NUMBER(7, 2),
    DEPTNO NUMBER(2))
"""

cursor.execute(create_table)

cursor.execute("alter session set nls_date_format='DD-MON-YYYY'")
cursor.execute("alter session set nls_language=AMERICAN")
  
cursor.execute("INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20)")
cursor.execute("INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30)")
cursor.execute("INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30)")
cursor.execute("INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20)")
cursor.execute("INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30)")
cursor.execute("INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30)")
cursor.execute("INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10)")
cursor.execute("INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20)")
cursor.execute("INSERT INTO EMP VALUES(7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10)")
cursor.execute("INSERT INTO EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30)")
cursor.execute("INSERT INTO EMP VALUES(7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20)")
cursor.execute("INSERT INTO EMP VALUES(7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30)")
cursor.execute("INSERT INTO EMP VALUES(7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20)")
cursor.execute("INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10)")

connection.commit()

query = "select * from emp"

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

for row in rows:
    print(row)

print ('-' * 90)

query1 = "select empno, ename, to_char(hiredate,'dd.mm.yyyy hh24:mi:ss') from emp"

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

for row in rows:
    print(row)
  

drop_table = "delete from emp"
cursor.execute(drop_table)
connection.commit()

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

for row in rows:
    print(row)

connection.close()

(7369, 'SMITH', 'CLERK', 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)
(7499, 'ALLEN', 'SALESMAN', 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30)
(7521, 'WARD', 'SALESMAN', 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30)
(7566, 'JONES', 'MANAGER', 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20)
(7654, 'MARTIN', 'SALESMAN', 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30)
(7698, 'BLAKE', 'MANAGER', 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30)
(7782, 'CLARK', 'MANAGER', 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10)
(7788, 'SCOTT', 'ANALYST', 7566, datetime.datetime(1982, 12, 9, 0, 0), 3000.0, None, 20)
(7839, 'KING', 'PRESIDENT', None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10)
(7844, 'TURNER', 'SALESMAN', 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30)
(7876, 'ADAMS', 'CLERK', 7788, datetime.datetime(1983, 1, 12, 0, 0), 1100.0, None, 20)
(7900, 'JAMES', 'CLERK', 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30)
(7902, 'FORD', 'ANALYST', 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)
(7934, 'MILLER', 'CLERK', 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 10)
------------------------------------------------------------------------------------------
(7369, 'SMITH', '17.12.1980 00:00:00')
(7499, 'ALLEN', '20.02.1981 00:00:00')
(7521, 'WARD', '22.02.1981 00:00:00')
(7566, 'JONES', '02.04.1981 00:00:00')
(7654, 'MARTIN', '28.09.1981 00:00:00')
(7698, 'BLAKE', '01.05.1981 00:00:00')
(7782, 'CLARK', '09.06.1981 00:00:00')
(7788, 'SCOTT', '09.12.1982 00:00:00')
(7839, 'KING', '17.11.1981 00:00:00')
(7844, 'TURNER', '08.09.1981 00:00:00')
(7876, 'ADAMS', '12.01.1983 00:00:00')
(7900, 'JAMES', '03.12.1981 00:00:00')
(7902, 'FORD', '03.12.1981 00:00:00')
(7934, 'MILLER', '23.01.1982 00:00:00')

Подключение к базам в цикле:

database.csv

sid;pwdsys;uconn;host
TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega
TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega
TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega
TESTDB;oracle;DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega
TESTDB;oracle;(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=omega)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TESTDB_OMEGA)));omega


import cx_Oracle
import pandas as pd
import numpy as np

data = pd.read_csv('database.csv', sep=';')
data_rows, data_columns = data.shape


for i in range(data_rows):
    sid = data.loc[i,'sid']
    pwd = data.loc[i,'pwdsys']
    conn = data.loc[i,'uconn']
    s = 'sys/'+pwd+'@'+conn
    #print(s)
    try:
        ora_conn = cx_Oracle.connect(s, mode = cx_Oracle.SYSDBA)
        cursor = ora_conn.cursor()
        query = "select * from dual"
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            print(row)
    except cx_Oracle.DatabaseError as e:
        error, = e.args
        if error.code == 1017:
            print('Please check your credentials. {}'.format(e))
        else:
            print('Database connection error: {}'.format(e))
            
    finally:
        try:
            cursor.close()           
            ora_conn.close()
        except:
            print('cx_Oracle.connect or db.cursor')


('X',)
('X',)
('X',)
Database connection error: ORA-12154: TNS:could not resolve the connect identifier specified
cx_Oracle.connect or db.cursor
('X',)

 

username = 'angor'
password = ''
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'

import cx_Oracle
import config
 
sql = 'select customer_id, name ' \
      'from customers ' \
      'order by name'
try:
    with cx_Oracle.connect(
                config.username,
                config.password,
                config.dsn,
                encoding=config.encoding) as connection:
        #fetchone 
        with connection.cursor() as cursor:
            cursor.execute(sql)
            while True:
                row = cursor.fetchone()
                if row is None:
                    break
                print(row)
except cx_Oracle.Error as error:
    print(error)



        #fetchall
        with connection.cursor() as cursor:
            # execute the SQL statement
            cursor.execute(sql)
            # fetch all rows
            rows = cursor.fetchall()
            if rows:
                for row in rows:
                    print(row)
except cx_Oracle.Error as error:
    print(error)




batch_size = 20

        #fetchmany
        with connection.cursor() as cursor:
            # execute the SQL statement
            cursor.execute(sql)
            while True:
                # fetch rows
                rows = cursor.fetchmany(batch_size)
                if not rows:
                    break
                # display rows
                for row in rows:
                    print(row)
except cx_Oracle.Error as error:
    print(error)
 

Существует четыре типа больших объектов:

BLOB - Большой двоичный объект, используемый для хранения двоичных данных. cx_Oracle использует тип cx_Oracle.BLOB.
CLOB - Большой символьный объект, используемый для символьных строк в формате набора символов базы данных. cx_Oracle использует тип cx_Oracle.CLOB.
NCLOB - большой объект национального символа, используемый для символьных строк в формате набора национальных символов. cx_Oracle использует тип cx_Oracle.NCLOB.
BFILE - внешний двоичный файл, используемый для ссылки на файл, хранящийся в операционной системе хоста за пределами базы данных. cx_Oracle использует тип cx_Oracle.BFILE.

Большие объекты могут передаваться в Oracle Database и из нее.
Большие объекты длиной до 1 ГБ также могут обрабатываться непосредственно как строки или байты в cx_Oracle.
Это облегчает работу с большими объектами и дает значительные преимущества в производительности по сравнению с потоковой передачей.
Однако для этого требуется, чтобы все данные больших объектов присутствовали в памяти Python, что может быть невозможно.
Смотрите GitHub для примеров LOB.
Простая вставка больших объектов

Рассмотрим таблицу со столбцами CLOB и BLOB:

CREATE TABLE lob_tbl (
    id NUMBER,
    c CLOB,
    b BLOB
);

С помощью cx_Oracle данные больших объектов могут быть вставлены в таблицу путем привязки строк или байтов по мере необходимости:

with open('example.txt', 'r') as f:
    textdata = f.read()

with open('image.png', 'rb') as f:
    imgdata = f.read()

cursor.execute("""
        insert into lob_tbl (id, c, b)
        values (:lobid, :clobdata, :blobdata)""",
        lobid=10, clobdata=textdata, blobdata=imgdata)

Обратите внимание, что при таком подходе размер больших данных ограничен 1 ГБ.
Извлечение больших объектов в виде строк и байтов
CLOB и BLOB размером менее 1 ГБ могут запрашиваться из базы данных напрямую в виде строк и байтов.
Это может быть намного быстрее, чем потоковое.
Необходимо использовать Connection.outputtypehandler или Cursor.outputtypehandler, как показано в этом примере:

def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
    if defaultType == cx_Oracle.CLOB:
        return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize)
    if defaultType == cx_Oracle.BLOB:
        return cursor.var(cx_Oracle.LONG_BINARY, arraysize=cursor.arraysize)

id_v = 1
textData = "Текстовые данные"
bytesData = b"Бинарные данные"
cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)",
        [id_v, textData, bytesData])

connection.outputtypehandler = OutputTypeHandler
cursor.execute("select c, b from lob_tbl where id = :1", [id_v])
clobData, blobData = cursor.fetchone()
print("CLOB length:", len(clobData))
print("CLOB data:", clobData)
print("BLOB length:", len(blobData))
print("BLOB data:", blobData)


Без обработчика типа вывода значения CLOB и BLOB извлекаются как объекты LOB.
Размер объекта LOB можно получить, вызвав LOB.size (), а данные можно прочитать, вызвав LOB.read ():

id_v = 1
textData = "Текстовые данные"
bytesData = b"Бинарные данные"
cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)",
        [id_v, textData, bytesData])

cursor.execute("select b, c from lob_tbl where id = :1", [id_v])
b, c = cursor.fetchone()
print("CLOB length:", c.size())
print("CLOB data:", c.read())
print("BLOB length:", b.size())
print("BLOB data:", b.read())

Этот подход дает те же результаты, что и в предыдущем примере, но он будет работать медленнее
потому что это требует большего количества обращений к базе данных Oracle и имеет более высокие издержки.
 

Читать большие BLOB объекты можно с помощью метода LOB.read().
Метод LOB.read() может вызываться повторно до тех пор, 
пока не будут прочитаны все данные


cursor.execute("select b from lob_t where id = :1", [10])
blob, = cursor.fetchone()
offset = 1
numBytesInChunk = 65536
with open("image.png", "wb") as f:
    while True:
        data = blob.read(offset, numBytesInChunk)
        if data:
            f.write(data)
        if len(data) < numBytesInChunk:
            break
        offset += len(data)
 

Записывать большие BLOB объекты можно с помощью метода BLOB.write():

id_v = 9
lob_v = cursor.var(cx_Oracle.BLOB)
cursor.execute("""
        insert into lob_tbl (id, b)
        values (:1, empty_blob())
        returning b into :2""", [id_v, lob_v])
blob, = lob_v.getvalue()
offset = 1
numBytesInChunk = 65536
with open("image.png", "rb") as f:
    while True:
        data = f.read(numBytesInChunk)
        if data:
            blob.write(data, offset)
        if len(data) < numBytesInChunk:
            break
        offset += len(data)
connection.commit()
 

Соответствие типов данных :

Oracle	cx_Oracle	Python
VARCHAR2 NVARCHAR2 LONG	cx_Oracle.STRING	str
CHAR	cx_Oracle.FIXED_CHAR
NUMBER	cx_Oracle.NUMBER	int
FLOAT		float
DATE	cx_Oracle.DATETIME	datetime.datetime
TIMESTAMP	cx_Oracle.TIMESTAMP
CLOB	cx_Oracle.CLOB	cx_Oracle.LOB
BLOB	cx_Oracle.BLOB

Some quick syntax reminders for common tasks using DB-API2 modules.

task	postgresql	sqlite	MySQL	Oracle	ODBC	oursql
create database	createdb mydb	created automatically when opened with sqlite3		created with Oracle XE install
command-line tool	psql -d mydb	sqlite3 mydb.sqlite	mysql testdb	sqlplus scott/tiger
GUI tool	pgadmin3		mysql-admin	sqldeveloper
install module	easy_install psycopg2	included in Python 2.5 standard library	easy_install mysql-python or apt-get install python-mysqldb	easy_install cx_oracle (but see note)
import	from psycopg2 import *	from sqlite3 import *	from MySQLdb import *	from cx_Oracle import *
connect	conn = connect("dbname='testdb' user='me' host='localhost' password='mypassword'”)	conn = connect('mydb.sqlite') or conn=connect(':memory:')	conn = connect (host="localhost", db="testdb", user="me", passwd="mypassword")	conn=connect('scott/tiger@xe')	conn = odbc.odbc('DBALIAS') or odbc.odbc('DBALIAS/USERNAME/PASSWORD')
get cursor	curs = conn.cursor()	curs = conn.cursor()	curs = conn.cursor()	curs = conn.cursor()
execute SELECT	curs.execute('SELECT * FROM tbl')	curs.execute('SELECT * FROM tbl')	curs.execute('SELECT * FROM tbl')	curs.execute('SELECT * FROM tbl')
fetch	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany()	curs.fetchone(); curs.fetchall(); curs.fetchmany(); for r in curs
use bind variables	curs.execute('SELECT * FROM tbl WHERE col = %(varnm)s', {'varnm':22})	curs.execute('SELECT * FROM tbl WHERE col = ?', [22])	curs.execute('SELECT * FROM tbl WHERE col = %s', [22])	curs.execute('SELECT * FROM tbl WHERE col = :varnm', {'varnm':22})		curs.execute('SELECT * FROM tbl WHERE col = ?', (22,))
commit	conn.commit() (required)	conn.commit() (required)	conn.commit() (required)	conn.commit() (required)

Подключение к MySQL

import mysql.connector
from mysql.connector import Error

def connect():
    """ Connect to MySQL database """
    try:
        conn = mysql.connector.connect(host='192.168.0.1',
                                       database='my_db',
                                       user='angor',
                                       password='my_pwd')
        if conn.is_connected():
            print('Connected to MySQL database')

    except Error as e:
        print(e)

    finally:
        conn.close()

if __name__ == '__main__':
    connect()