Oracle Weblogic: ALTER SESSION-команды в Init SQL

Задача - в качестве Init SQL необходимо выполнить 2 ALTER SESSION-команды

Решение - ALTER SESSION-команды в Init SQL заворачиваем в анонимный PL/SQL-блок, а внутри него каждую команду - в execute immediate:

SQL BEGIN execute immediate('alter session set ...');execute immediate('alter session set ...'); END;

Oracle: посчитать число индексов по char/varchar-колонкам

SELECT COUNT(DISTINCT i.INDEX_NAME)
FROM dba_ind_columns i
    JOIN dba_tab_columns c
        ON (c.TABLE_NAME = i.TABLE_NAME AND c.OWNER = i.TABLE_OWNER AND c.COLUMN_NAME = i.COLUMN_NAME)
WHERE
    (UPPER(c.DATA_TYPE) LIKE 'VARCHAR%'
        OR UPPER(c.DATA_TYPE) LIKE 'CHAR%')

Узнать SID сессии по database link

Проблема

Есть БД "А" и БД "Б", в первой создан DATABASE LINK ко второй. Нужно запросом к БД "А" узнать идентификатор сессии (SID) между БД "А" и БД "Б".

Решение

Из всех опробованных вариантов заработал только этот:

select
   to_number(substr(dbms_session.unique_session_id@DBLINK_NAME,1,4),'XXXX') mysid
from dual;

, где DBLINK_NAME - имя DATABASE LINK'а.

Oracle DB: определение размера MATERIALIZED VIEW

Следующий запрос возвращает размеры (в Мб) всех материализованных представлений в БД:

SELECT segment_name,sum((BYTES)/(1024*1024)) "Allocated(MB)"
FROM dba_extents
WHERE segment_name in (SELECT mview_name FROM dba_mviews)
GROUP BY segment_name ;

Спасибо http://javeedkaleem.blogspot.ru/2010/04/find-space-used-by-materialized-views.html.

ORA-02020: используется слишком много каналов связи БД

Проблема

Есть ADF-приложение, в нем некоторые Entity смотрят на вьюхи, которые, в свою очередь, смотрят на удаленные таблицы через DATABASE LINK. При работе с этими Entity падает ошибка:

ORA-02020: используется слишком много каналов связи БД

Решение

Срабатывает ограничение OPEN_LINKS (ограничение на число link-ов в рамках одной сессии, см. https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams139.htm#REFRN10138) или OPEN_LINKS_PER_INSTANCE (тоже самое, но не на уровне сессии, а глобально, актуально при использовании SHARED-линков и распределенных транзакций, см. https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams140.htm#REFRN10139). Оба по-умолчанию равны 4. Посмотреть текущие значения можно SQL-запросом:

show parameter OPEN_LINKS

Необходимо поменять значения командами:

ALTER SYSTEM SET OPEN_LINKS=255 SCOPE=SPFILE;
ALTER SYSTEM SET OPEN_LINKS_PER_INSTANCE=255 SCOPE=SPFILE; 

(для применения потребуется рестартануть БД)

ORA-24777: использование непереносимых ссылок на базы данных недопустимо

Проблема

Есть ADF-приложение, работающее на weblogic. Приложение использует Data source, предоставляемый weblogic'ом, для соединения с БД. Некоторые Entity приложения смотрят на вьюхи, которые, в свою очередь, смотрят на удаленные таблицы через DATABASE LINK.

При попытке приложения обратиться к данным в удаленной БД падает ошибка:

ORA-24777: использование непереносимых ссылок на базы данных недопустимо

Решение

Data source на weblogic использует JDBC-драйвер "oracle.jdbc.xa.client.OracleXADataSource, либо обычный (non-XA) драйвер oracle.jdbc.OracleDriver, но в настройках датасорса стоит галочка "Supports Global Transactions" (которая, кстати, установлена там по-умолчанию). При этом DATABASE LINK создан не как SHARED.
Соответственно, варианта 2:

1. Использовать oracle.jdbc.OracleDriver со снятой галочкой "Supports Global Transactions", если распределенные транзакции по факту приложению не нужны
2. Пересоздать DATABASE LINK с использованием SHARED-опции (подробнее см. https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm#i2061693).  Например:

CREATE SHARED PUBLIC DATABASE LINK MY_DBLINK
CONNECT TO "USR1" IDENTIFIED BY "PSWRD1"
AUTHENTICATED BY "USR1" IDENTIFIED BY "PSWRD1"
USING 'MYRMTORCL';

Oracle Database11g Express Edition: "ORA-12505, TNS:listener does not currently know of SID given in connect descriptor"

Установил я Oracle Database11g Express Edition Release 2 на Win 10 x64. Проблемы начались еще при скачивании дистрибутива. Сайт Oracle ругался на Unauthorized Request "In order to download products from Oracle Technology Network you must agree to the OTN license terms", хотя я, естественно, условия лицензии принял. Пришлось качать с rutracker. Наконец поставив, пытаюсь подключиться через JDeveloper по jdbc:oracle:thin:@localhost:1521:XE. Получаю:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Тоже самое через cmd с помощью:

sqlplus sys/******@XE as sysdba

Вроде бы службы запущены, "Start Database" в меню Пуск я нажимал. Перезапуск служб не помог (хотя вроде бы стартовал в правильном порядке - сначала OracleXETNSListener, потом - OracleServiceXE).
Помогло следующее. Приконнектился с помощью:

sqlplus sys/111111 as sysdba

Выполнил:

alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope = both;
alter system register;
exit;

После чего:

lsnrctl stat 

Вуаля, можно коннектится.

Oracle: проверка наличия ограничения на таблицу с помощью SQL-запроса

Задача

Проверить с помощью SQL, есть ли на таблице K ограничение (например, внешний ключ) с именем N.

Решение

SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'K' AND CONSTRAINT_NAME = 'N'

Pentaho: подключение к БД Oracle по Service Name

При создании "Database connection" в Pentaho при выборе СУБД Oracle нет возможности выбрать, как подключаться к БД - по SID или по Service Name. Есть только единственное поле - "Database". При этом, если ввести в него просто значение Service Name, на некоторых серверах подключение проходит успешно, тогда как на других приводит к ошибке:

2014/01/25 20:06:27 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
2014/01/25 20:06:27 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Listener refused the connection with the following error:ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

Из текста ошибки видно, что СУБД считает, что подключение ведется по SID, а не по Service Name. Почему ошибка возникает лишь на некоторых серверах - не знаю, видимо это зависит от каких-то настроек на стороне сервера.

Почему СУБД считает, что подключение ведется именно по SID, понятно: форматы JDBC-URL'а при подключении по SID и по Service Name различаются. Для SID URL выглядит так (используется thin-драйвер):

jdbc:oracle:thin:@server:1521:mysid

, а для Service Name - так:

jdbc:oracle:thin:@server:1521/myservicename

Pentaho использует первый формат, что и ведет к ошибке. Однако, не все так плохо: если указать в начале значения поля "Database" прямой слеш (перед значением Service Name) подключение проходит успешно. Спасибо http://forums.pentaho.com/archive/index.php/t-87866.html?s=57295456a6292b611f3e46e3fa0ea7d6.

Oracle + Weblogic connection pool: задание схемы по-умолчанию

Недавно писал про организацию пула соединения с БД в приложении, развертываемом на Weblogic (http://barbitoff.blogspot.ru/2013/07/weblogic-1035-connection-pooling.html). Xml-конфигурация пула позволяет задать все параметры соединения, кроме имени используемой схемы. Хардкодить имя схемы в SQL-запросах не хочется, как и изобретать велосипед с подтягиванием имени схемы из какого-то своего отдельного конфига. Выход - использовать в SQL схему по-умолчанию (т.е. не задавать имя схемы вообще) и воспользоваться таким параметром пула, как "init-sql". В этом параметре нужно указать SQL-запрос, который при инициализации соединения с БД поменяет схему по-умолчанию на необходимую нам. Этот SQL зависит от используемой СУБД, для Oracle он выглядит так:

  <jdbc-connection-pool-params>
    <max-capacity>20</max-capacity>
    <connection-reserve-timeout-seconds>25</connection-reserve-timeout-seconds>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
    <init-sql>SQL ALTER SESSION SET CURRENT_SCHEMA = USR</init-sql>  </jdbc-connection-pool-params>

Oracle: "SELECT ... INTO ..." и ошибка "No data found"

В случае, если есть вероятность, что запрос, используемый в SELECT ... INTO ..., не вернет строк, можно либо указать обработчик исключения, либо воспользоваться следующей конструкцией, которая в случае, если запрос не вернет строк, запишет NULL в переменную my_var:

SELECT (SELECT some_field FROM some_table WHERE <some_condition>) INTO my_var FROM dual

Oracle: смена схемы по-умолчанию в рамках сессии

ALTER SESSION SET CURRENT_SCHEMA = ABC

Oracle: аналог AUTO_INCREMENT в MySQL

В Oracle автогенерация целочисленных идентификаторов, реализуемая в MySQL посредством модификатора AUTO_INCREMENT, выполняется по схеме sequence + trigger, т.е.:

1. Создаем таблицу с целочисленным первичным ключом, например:

   CREATE TABLE my_table(
        my_id NUMBER(16),
        CONSTRAINT my_id_pk PRIMARY KEY (my_id)
   )

2. Создаем последовательность:
   

   CREATE SEQUENCE my_id_seq;

3. Создаем триггер:
   

   DELIMITER /
   CREATE OR REPLACE TRIGGER my_id_trg
        BEFORE INSERT ON my_table FOR EACH ROW
   BEGIN
        IF :NEW.my_id IS NULL THEN
             SELECT my_id_seq.NEXTVAL INTO :NEW.my_id FROM DUAL;
        END IF;
   END;
   /

Oracle: просмотр списка активных запросов

Следующий запрос выдает информацию по активным запросам, включая sid и serial (необходимые для "убивания" запроса), а также сам текст запроса:

select a.sid, a.serial#, a.osuser, sql_text
from v$session a, v$sqlarea b
where b.hash_value = a.sql_hash_value
  and a.schemaname != 'SYS'
  and a.status = 'ACTIVE'

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

ALTER SYSTEM KILL SESSION 'sid,serial'

Oracle: загрузка из csv

В поставку клиента Oracle входит утилита SQL Loader (sqlldr), предназначенная как раз таки для загрузки данных из внешних файлов. Подробно её использование описано тут: http://www.orafaq.com/wiki/SQL*Loader_FAQ. В т.ч. она умеет загружать данные в таблицы из scv. Для этого нужно создать управляющий файл примерно следующего содержания:

load data
 characterset utf8
 infile '/path/to/my.csv'
 into table table_name_to_import
 fields terminated by ","
 ( col1, col2, col3 )

Данный файл указывает путь к csv для загрузки, говорит о том, что содержимое файла закодировано с помощью UTF8 (без BOM), поля разделены запятыми, в каждой строке содержится 3 поля, которые должны быть загружены в таблицу "table_name_to_import" в колонки "col1", "col2" и "col3".

После этого можно вызывать sqlldr:

T:\app\username\product\11.2.0\client_1\BIN>sqlldr <username>@<alias>/<password> control=/путь/к/управляющему.файлу

В случае, если какие-то записи не были загружены (например, было превышено ограничение на длину поля), sqlldr рядом со входящим файлом создаст файл с расширением ".bad", в который будут помещены ошибочные записи из входящего csv.

В частности, при конфигурации, приведенной выше, в этот файл попадут все строки с пустыми полями, т.е. когда подряд идут два разделителя. Чтобы такие записи все же загружались (с установкой соотв. полей в NULL), нужно указать "trailing nullcols" в управляющем файле (после указания разделителя):

load data
 characterset utf8
 infile '/path/to/my.csv'
 into table table_name_to_import
 fields terminated by "," TRAILING NULLCOLS
 ( col1, col2, col3 )

Кстати, чтобы таблица, в которую осуществляется импорт, предварительно очищалась, можно воспользоваться командой "TRUNCATE", размещаемой перед "into":

load data
 characterset utf8
 infile '/path/to/my.csv'
 TRUNCATE into table table_name_to_import
 fields terminated by "," trailing nullcols
 ( col1, col2, col3 )

Oracle: аналог LIMIT / TOP

Некоторым аналогом функционала LIMIT / TOP по ограничению числа возвращаемых строк является псевдостолбец "rownum", представляющий собой номер строки в результирующей выборке. Таким образом, запрос:

SELECT * FROM abc WHERE rownum<=10

аналогичен запросу:

SELECT * FROM abc LIMIT 10

в MySQL / PostgreSQL, или:

SELECT TOP 10 * FROM abc

в MS SQL.

Правда, у такого подхода есть важная особенность: ограничение выборки производится раньше, чем сортировка с помощью ORDER BY, в отличие от конструкции LIMIT в MySQL / PostgreSQL. Т.е.:

SELECT * FROM abc WHERE rownum<=10 ORDER BY a

вовсе не аналогичен вызову

SELECT * FROM abc ORDER BY a LIMIT 10

в других СУБД, т.к. оракл проведет сначала выборку первых 10 значений, а потом уже отсортирует их по столбцу "a". Чтобы ограничение выборки выполнялось уже после сортировки, необходимо использовать вложенный запрос:

SELECT * FROM (SELECT * FROM abc ORDER BY a) WHERE rownum<=10

Альтернативой такому, мягко говоря, несимпатичному запросу может быть использование функции ROW_NUMBER.

Конфигурация пула JDBC-соединений c3p0 как JDNI DataSource в Tomcat

Ниже приведен пример минимальной конфигурации JNDI DataSource пула соединений c3p0, размещаемой внутри тега <Context> в context.xml веб-приложения Tomcat (на примере соединения с БД Oracle):

<Resource name="jdbc/myDb" auth="Container"
  type="com.mchange.v2.c3p0.ComboPooledDataSource"
  factory="org.apache.naming.factory.BeanFactory"
  user="xxx" password="yyy" driverClass="oracle.jdbc.driver.OracleDriver"
  jdbcUrl="jdbc:oracle:thin:@//myoraserv:1521/zzz"/>    

Пул соединений с БД от Orcale

Oracle вместе со своим JDBC-драйвером поставляет также и пул соединений, поэтому я решил попробовать использовать его вместо стандартного томкатовского commons-dbcp. Его подключение несколько отличается от подключения dbcp и выглядит примерно следующим образом (показан конфиг context.xml веб-приложения):

<Context antiJARLocking="true" path="/myapp">
  <Resource name="jdbc/myOraDb" auth="Container" type="oracle.jdbc.pool.OracleConnectionPoolDataSource"
               driverClassName="oracle.jdbc.driver.OracleDriver"
               factory="oracle.jdbc.pool.OracleDataSourceFactory"
               maxActive="100" maxIdle="30" maxWait="10000"
               user="xxx" password="yyy"
               url="jdbc:oracle:thin:@//myoraserver:1521/xxx"/>              
</Context>

Вот только нагрузочное тестирование показывает, что он почему-то примерно на 40% медленнее DBCP. Возможно из-за неоптимальной конфигурации, пока не было времени с этим разобраться.

Oracle: просмотр содержимого пакета и его тела

Просмотр содержимого пакета (доступных функций, процедур, и их входных / выходных параметров и возвращаемых значений):

desc package_name;

Просмотр тела пакета:

select * from all_source where name = 'package_name' and type = 'PACKAGE BODY' order by line 

Определение версии Oracle из SQL-консоли

SELECT * FROM V$VERSION;