Oracle DB: разное

Установка сервера на Windows

Oracle Database 11g Express Edition – с сервером идет сразу WEB интерфейс встроенный для управления БД. Качнуть можно бесплатно с сайта Oracle.

Файлы/папки

C:\oraclexe\app\oracle\product\11.2.0\server – основная папка сервера

C:\oraclexe\app\oracle\product\11.2.0\server\bin – тут утилиты oracle, например:

• sqlplus.exe – Sql plus CLI, по умолчанию через ярлык запускается с опцией /nolog (sqlplus.exe /nolog), чтобы не запрашивал login/pass.
• sqlldr
• tnsping

C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN – путь к файлам конфигурации (в папке sample примеры)

• listener.ora
• sqlnet.ora
• tnsnames.ora
• afiedt.buf – файл в котором хранится буфер команд sqlplus

WEB
После установки ярлык “Get Started” ведущий на URL http://127.0.0.1:8080/apex/f?p=4950:1:4495930668737803 может не запускаться из-за незапущенного web-сервиса. Лечится перезагрузкой.

Заходим на веб и видим, что почему то не подходит пароль на пользователя System (ошибка Invalid Login Credentials при авторизации), хотя он точно тот же, что мы задавали при установке 🙂

Лечим через командную строку (команды будут разобраны ниже):

sqlplus /nolog
connect / as sysdba;
alter user system identified by <password>;

Службы

Проверяем что корректно стартанули службы OracleServiceXE (сама база), OracleXETNSListener (служба по связи клиентов с сервером). Осторожно, СУБД “отжирает” минимум 300мб оперативной памяти для работы. Поэтому если постоянная работа базы не нужна – имеет смысл отключить автозапуск служб.

 

 

SQLPLUS

sqlplus /nolog – запускаем sqlplus, по умолчанию через ярлык запускается с опцией /nolog, чтобы не запрашивал login/pass.

/ или RUN – повтор предыдущего запроса

LIST – просмотр истории запросов

EDIT – редактирование истории запросов (требует запуска sqlplus из под админа)

CLEAR BUFFER – очистка буфера afiedt.buf

CLEAR SCREEN – экрана CLI

SET TIME ON/OFF – включить в prompt время

SPOOL C:\sqlplus_log.txt – сохраняем логи sqlplus в файл

@C:\TEMP\TEST.SQL – для запуска скрипта в SQLPlus нужно создать скрипт с расширением .sql с последовательностью строк, далее запустить скрипт через SQLplus поставив перед путем знак @.

PL/SQL (plsql)

 

ORACLE команды

SHOW USER – смотрим пользователя

GRANT CREATE/DELETE/DROP/ALTER ANY TABLE/VIEW/PROCEDURE/SEQUENCE/TRIGGER TO TESTOV – даем права на соответствующие действия с указанными объектами пользователю TESTOV

connect / as sysdba; – подключаемся к базе

alter user system identified by <password>; – создаем пользователя system с паролем <password>

Commit

В Oracle важно не забывать после операций update/insert commit’ить изменения, при этом важно понимать, что при commit данные пишутся из оперативной памяти в постоянную, поэтому в случае большого количества commit’ов, возможно, будет рациональней их использовать не после каждой операции работы с БД, а по каждой 10/100/1000ой операции или по времени.

Because the sample code is using an UPDATE statement and committing the results to the database as each record is read in the loop, it might take as long as a few minutes to process all the data. The commit insists on completely writing all the data to disk every time it is called. The program can be speeded up greatly by moving the commit operation outside of the loop. In any database program, there is a balance between the number of operations you execute between commits and the importance of not losing the results of operations that have not yet been committed.

Переменные

Правильное определение переменных LD_LIBRARY_PATH и NLS_LANG крайне важно, иначе могут быть разные ошибки.

Пример определения

$ echo $LD_LIBRARY_PATH
/usr/lib/oracle/11.2/client64/lib

$ echo $NLS_LANG
AMERICAN_AMERICA.CL8MSWIN1251

Пример ошибок

Perl, лечится определением переменной $LD_LIBRARY_PATH (в том числе не забываем ее прописать и для cron):

install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libocci.so.11.1: невозможно открыть разделяемый объектный фа
йл: Нет такого файла или каталога at /usr/lib64/perl5/DynaLoader.pm line 200.
 at (eval 7) line 3
Compilation failed in require at (eval 7) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /home/tools/bin/check_vlan.pl line 29

ФУНКЦИИ

Преобразования

0.178766998E9  – при аналогичных запросах в PHP и ruby в выводе php нет конвертации числа в форму 0.<число>E9 (количество разрядов), а в ruby есть. Если это ненужно – решается простой конвертацией в char при запросе. Вместо select t.id пишем select to_char(t.id).

DATE

select date – для Select по дате достаточно стандартно указать формат даты функции to_date. Если в дате есть еще время (и оно отлично от 00:00), то нужно преобразовывать в строковую переменную дату для работы только по дате.

delete from tb_test where fd=to_date('$date','yyyy.mm.dd'); 
delete from tb_test where to_char(DATEL,'yyyy.mm.dd') = '$date'";

Примеры использования функций to_date и to_timestamp:

to_date('2018-03-23','YYYY-MM-DD')
to_date('2017-12-07 11:08:50','YYYY-MM-DD HH24:MI:SS')
to_date('13.06.2018 11:30','dd.mm.yyyy HH24:MI')
to_timestamp('2018-03-22 14:56:19','YYYY-MM-DD HH24:MI:SS')

сравнение date в базе (в базе хранится чистая дата типа 2017-12-07)  и sysdate делается через обрезку через trunc у sysdate всего кроме даты (по умолчанию):

where STRT_DATE >= trunc(sysdate)

if/else в базе с датой: если текущая дата больше даты в базе – подставляем дату в базе +1 день, если меньше или равна – подставляем текущую дату. Зачастую очень полезная штука – иначе нужно делать больше запросов или костылять в коде. При использовании case главное смотрим, чтобы SQL не стал намного дольше обрабатываться – в таком случае мы сильно нагружаем БД.

(case when sysdate>tb.tb_date then (tb.tb_date+1) else sysdate end)

REGEXP

Oracle имеет функции, которые работают с регулярными выражениями. Очень полезно. Например, заменяем данные в выгрузке на основе REGEXP. Конкретно в этом случае удаляем IP адреса из поля.

REGEXP_REPLACE(dd.device_name,' (\d+)\.(\d+)\.(\d+)\.(\d+)', '') device_name

LIMIT

В ORACLE нет LIMIT, но есть ROWNUM, который правда не работает c ORDER BY.

SELECT count(*) FROM tb WHERE ROWNUM <= 5

Для генерации следующего значения в качестве метода можно использовать max, например:

SELECT max(n) FROM tb;

BIND

Невозможно сделать like с % если переменная задана через bind.

concat

Concat в Oracle аналогичен Mysql, но только для двух переменных, иначе нужно гарадить concat на concat. Поэтому чаще всего проще использовать ||.

concat(street, house) # ok
concat(street, ' ', house) # не сработает, ножно делать 
concat( concat(street, ' '), house) # используем для трех переменных

SELECT street || ' ' || house FROM ... # намного красивее, особенно если конкатенация будет включать дополнительные переменные

Database link

Для создания database link нужно всего лишь указать логин-пароль и базу для подключения, как для sqlplus.

Database link позволяет обращаться к функциям – нужно указывать в конце функций до переменных (без собственника функции в начале).

api.ftest@link('test', dv.device_id, 'param')

Позволяет изучать таблицы через desc, используя sqlplus (не через pl-sql, как и с таблицами без линков).

desc table@link;

Изучение базы и таблиц

Показать все таблицы с полями

Во всех запросах можно использовать database link dba_tab_columns@link

Выгрузить все таблицы со статистикой по каждой, например по полю NUM_ROWS можно понять какие используются, а какие нет

SELECT * FROM ALL_ALL_TABLES;

Если есть права на dba_tab_columns

select table_name,column_name from dba_tab_columns;

Если прав нет, то можно попробовать

SELECT table_name,column_name FROM COLS;
SELECT table_name,column_name FROM all_tab_columns;

Ошибки

ora-00918 column ambiguously defined – происходит когда например делается выборка из одной и той же таблицы дважды с разными идентификаторами, а в запросе поля идентификатор не указывается.

select td from test t1, test t2 # неправильно
select t1.td from test t1, test t2 # правильно

Проверка связи

telnet <ip> 1521 – проверяем доступность порта.

tnsping <db_name> – проверяем связь со службой БД.

$ tnsping test
TNS Ping Utility for Linux: Production on 30-APR-2017 16:06:12
Used parameter files:
TNS-03505: Failed to resolve name
$ tnsping <db>
TNS Ping Utility for Linux: Production on 30-APR-2017 16:06:32
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP)(HOST=<host>)(PORT=1521)) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=<db>)))
OK (0 msec)

Объем

Как узнать свободное пространство для БД.

select s.tablespace_name "Tablespace",
 to_char(sum(s.bytes), 'fm999g999g999g999g990') "Space (bytes)"
from dba_free_space s
where s.tablespace_name='<bd_name>'
group by s.tablespace_name
order by s.tablespace_name

Информация в целом справочная т.к. в oracle датафайлы авторасширяемые и по мере заполнения они увеличатся.  Если и их будет недостаточно – есть возможность добавить .dbf файлы вручную в табличное пространство. Основное ограничение в объеме файловой системы/диска.

Ускорение

Ускорение select может быть достигнуто за счет использования индексов. Нужно учитывать, что index’ы могут негативно влиять на write. В общем описано в статье SQL.

Сессии

Посмотреть сессии

select
 substr(a.spid,1,9) pid,
 substr(b.sid,1,5) sid,
 status,
 substr(b.serial#,1,5) ser#,
 substr(b.machine,1,6) box,
 substr(b.username,1,10) username,
-- b.server,
 substr(b.osuser,1,8) os_user,
 substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by status, spid;