Отличная статья от Toon Koppelaars с перечнем front-end технологий, которые появились за последние несколько лет. И многие из них, как правильно заметил автор
technologies du-jour: hot today, forgotten tomorrow
Надеюсь, что он прав и нам, разработчикам БД, можно расслабиться и продолжать развиваться в своей области.
В ходе исследований по loopback links чуть было не попал впросак с отловом сессий, создаваемых для dblink.
Судя по всему Oracle создает сессию для dblink один раз. В доказательство этого сделаем after logon trigger, который будет собирать информацию о подключениях
SQL> DROP TABLE log_session PURGE;
Table dropped.
SQL> CREATE TABLE log_session(username VARCHAR2(30), conn_time timestamp, info VARCHAR2(4000));
Table created.
SQL> CREATE OR REPLACE TRIGGER ta_connect AFTER logon ON DATABASE
2 BEGIN
3 INSERT INTO log_session VALUES (USER, SYSTIMESTAMP, NULL);
4 COMMIT;
5 END;
6 /
Trigger created.
SQL> SHOW ERRORS
No errors.
SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
1
Мы подключились, строка вставилась
SQL> SELECT * FROM dual@loopback;
D
-
X
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
2
Первое обращение по dblink, создалась сессия
SQL> SELECT * FROM dual@loopback;
D
-
X
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
2
Второе обращение по dblink – сессия не создалась, используется предыдущая
SQL> SELECT 'Hello' FROM a@loopback WHERE ROWNUM=1;
'HELL
-----
Hello
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
2
Поменяем таблицу – результат тот же. Новой сессии нет
SQL> CONNECT SYSTEM/manager
Connected.
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
3
Переподключились, теперь счетчик стал 3
SQL> SELECT * FROM dual@loopback;
D
-
X
SQL> SELECT COUNT(*) FROM log_session;
COUNT(*)
----------
4
В свежей сессии для dblink выполняется еще одно подключение
При чтении статьи встретилась знакомая по триггерам ошибка mutating table error в неожиданном месте, интересный способ ее обхода и совершенно неожиданные результаты.
CREATE TABLE a(n NUMBER);
Table created
INSERT INTO a VALUES(100);
1 row inserted
INSERT INTO a VALUES(200);
1 row inserted
INSERT INTO a VALUES(300);
1 row inserted
COMMIT;
Commit complete
CREATE OR REPLACE FUNCTION a_avg RETURN NUMBER AS
l_avg NUMBER;
BEGIN
SELECT AVG(n) INTO l_avg FROM a;
dbms_output.put_line('avg=' || l_avg);
RETURN l_avg;
END a_avg;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG
UPDATE a SET n = a_avg();
UPDATE a SET n = a_avg()
ORA-04091: table SYSTEM.A is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.A_AVG", line 4
Мы создали простую таблицу и функцию, которая считает среднее значение по этой таблице. С помощью функции мы попробуем усреднить все значения.
В результате мы получаем ORA-04091 без каких-либо триггеров
Далее в статье приводится способ обхода через loopback database link. Модифицируем немного функцию и вставляем @loopback
CREATE OR REPLACE FUNCTION a_avg_loopback RETURN NUMBER AS
l_avg NUMBER;
BEGIN
SELECT AVG(n) INTO l_avg FROM a@loopback;
dbms_output.put_line('avg=' || l_avg);
RETURN l_avg;
END a_avg_loopback;
/
Function created
SHOW ERRORS
No errors for FUNCTION SYSTEM.A_AVG_LOOPBACK
UPDATE a SET n = a_avg_loopback();
avg=200
avg=233.333333333333333333333333333333333333
avg=244.444444444444444444444444444444444444
3 rows updated
ROLLBACK;
Rollback complete
Ошибка пропала, но… Через loopback мы смогли увидеть данные, которые еще не закоммичены. Этакий dirty read, но скорее всего мы просто присоединяемся к той же транзакции. Причем, как видно, результат для каждой строчки считается с учетом обновленных строк.
UPD: дальнейшее исследование показало, что loopback link вообще не создал отдельной сессии.
UPD2: сессия создается, но ровно 1 раз за сессию. Исследование тут
Наткнулся на очень интересную статью адепта триггеров о констрейнтах.
Классификация вышла такая:
Такие классификации полезны для построения однозначных архитектур, т.к. формализовав правила реализации каждого из видов мы не получим всего многообразия методов :)
Немножко о parallel merge в упрощенном виде. Для того, что бы часть insert/update шла параллельно необходимо:
1. alter session enable parallel dml;
2. указывать в хинте таблицу в которую мержим
merge /*+ parallel(t1) */ into t1
USING (select c1, c2 from t2) t2
on (t1.c1 = t2.c1)
when matched then
update set t1.c2 = t1.c2
when not matched then
INSERT(c1, c2) values(t2.c1, t2.c2)Взято отсюда
К сожалению проблема с прода, когда параллельность не возникала на стенде не воспроизвелась.
Если Merge не использует индекс, то можно попробовать явно указать колонки явно. Т.е. вместо
merge into t1
using t2написать
merge into (select c1, c2 from t1) t1
using (select c3 from t2) t2Это же относится и к избыточной работе с temp/памяти для HASH JOIN.
Отличные исследования по этому поводу:
https://alexanderanokhin.wordpress.com/2012/07/18/dont-forget-about-column-projection/
https://jonathanlewis.wordpress.com/2016/06/06/merge-precision/
Стырено отсюда
If you don’t control the SQL execution then you can still create an Optimizer trace file if you know the SQL_ID of the SQL statement. The SQL_ID can then be added to the DBMS_SQLDIAG.DUMP_TRACE
command (added to DBMS_SQLDIAG in Oracle Database 11g R2). To create an Optimizer trace for any SQL statement that has been run and is in the shared pool.
Note that this procedure will automatically trigger a hard parse of the statement.
The following will show an example for SQL_ID 1n482vfrxw014
begin
DBMS_SQLDIAG.DUMP_TRACE(
p_sql_id=>'1n482vfrxw014',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'MY_SPECIFIC_STMT_TRC');
end;
/After running the procedure above you can find the trace file in the USER_DUMP_DEST directory.
To make it easier to find the trace file you should set the P_FILE_ID parameter to a string that starts with an alphabetic character and does not contain any leading or trailing white space
Powershell script which get a list of all saved wi-fi networks with passwords:
function Get-WifiNetworks {
$networks = netsh wlan show profiles | where {$_ -match '^.*All User Profile.*$'}
foreach ($network in $networks) {
$SSID = $network.split(':')[1].Trim()
$networkInfo = netsh wlan show profiles key=clear name="$SSID"
$current = @{}
$current['SSID']=$SSID
foreach ($infoString in $networkInfo) {
if ($infoString -match '^\s+(.*)\s+:\s+(.*)\s*$') {
$current[$matches[1].trim()] = $matches[2].trim()
}
}
new-object psobject -property $current
}
}
Get-WifiNetworks | select SSID, "Key Content", Authentication | Format-Table -Wrap -AutosizeВ командной строке под Администратором
netsh wlan show profiles
Profiles on interface Wi-Fi:
Group policy profiles (read only)
---------------------------------
<None>
User profiles
-------------
All User Profile : asrc_acess point
All User Profile : SWEETINN-AP1Находим нужную сеть и подставляем его в параметр команды
netsh wlan show profiles key=clear name="asrc_acess point"
Profile asrc_acess point on interface Wi-Fi:
=======================================================================
Applied: All User Profile
Profile information
-------------------
Version : 1
Type : Wireless LAN
Name : asrc_acess point
Control options :
Connection mode : Connect automatically
Network broadcast : Connect only if this network is broadcasting
AutoSwitch : Do not switch to other networks
MAC Randomization : Disabled
Connectivity settings
---------------------
Number of SSIDs : 1
SSID name : "asrc_acess point"
Network type : Infrastructure
Radio type : [ Any Radio Type ]
Vendor extension : Not present
Security settings
-----------------
Authentication : WPA2-Personal
Cipher : CCMP
Security key : Present
Key Content : 1243683176383
Cost settings
-------------
Cost : Unrestricted
Congested : No
Approaching Data Limit : No
Over Data Limit : No
Roaming : No
Cost Source : DefaultПароль в строке Key Content
Отличная статья про left deep trees, right deep trees и bushy joins http://www.oaktable.net/content/right-deep-left-deep-and-bushy-joins
Что можно подчерпнуть
1. Для получения right deep trees скорее всего потребуется хинтовать SWAP_JOIN_INPUTS
2. right deep trees могут возвращать результат сразу. Но это потребует несколько больше памяти для создания working sets.
3. Nested loops может быть только left deep
Ну и в картинках очень здорово показано, как работают left и right deep trees
Скрипт, который для Windows Server 2003 (в котором нет Get-ScheduledTask):
1. Останавливает запущенные задания
2. Переносит логи рекурсивно не сохраняя структуры директорий
3. Запускает остановленные задания
Bat файл для запуска
@ECHO OFF
PowerShell.exe -NoProfile -ExecutionPolicy Bypass -Command "& '%~dpn0.ps1'"
PAUSEСкрипт Powershell
$Source = "C:\Work\bat\source"
$Target = "C:\Work\bat\target"
Write-Host "Stoping tasks"
$scheduledTasks = schtasks /query /fo csv | ConvertFrom-Csv
$runningTasks =@()
foreach($task in $scheduledTasks)
{
if ($task.Status -eq 'Running' -and $task.taskName -eq 'testTask') {
$runningTasks += $task.taskName
Write-Host $task.taskName " will be stopped"
schtasks /END /TN $task.taskName
}
}
Write-Host "Moving files from $Source to $Target"
Get-Childitem $Source -recurse -include *.txt | Move-Item -destination $Target -force
Write-Host "Starting Tasks"
if ($runningTasks.count -gt 0) {
foreach($runningTask in $runningTasks)
{
Write-Host "$runningTask starting"
schtasks /RUN /TN $runningTask
}
}Нашел на форуме быстый и элегантный способ сохранить clob в файл на сервере
DBMS_XSLPROCESSOR.clob2file(
cl => l_clob
, flocation => 'XML_LOG'
, fname => myfile_name
);Встала задачка собрать много строчек таблицы в один BLOB.
Краткий алгоритм как это сделать: делаем temporary lob - изменяем его, вставляя строки - вставляем - очищаем - возвращаемся к шагу 1.
В нижележащих тестах разница, на сколько отличается по времени работа с temporary lob, созданного с параметром cache = true и cache = false
Результаты: cache = true в 20 раз быстрее
Код и результата теста
SET SERVEROUTPUT ON
PROMPT CACHE=TRUE
DECLARE
bl BLOB;
l_raw RAW(32767);
l_temp_cnt NUMBER;
l_start_time NUMBER := dbms_utility.get_time;
FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
l_cnt NUMBER;
BEGIN
SELECT SUM(u.blocks)
INTO l_cnt
FROM v$tempseg_usage u, v$session s
WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
RETURN NVL(l_cnt, 0);
END;
BEGIN
l_temp_cnt := get_temp_blocks_cnt;
DBMS_LOB.createtemporary(bl, TRUE);
FOR i IN 1 .. 100000 LOOP
l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
IF MOD(i, 10) = 0 THEN
dbms_lob.freetemporary(lob_loc => bl);
DBMS_LOB.createtemporary(bl, TRUE);
END IF;
END LOOP;
dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt));
dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100);
END;
/
PROMPT CACHE=FALSE
DECLARE
bl BLOB;
l_raw RAW(32767);
l_temp_cnt NUMBER;
l_start_time NUMBER := dbms_utility.get_time;
FUNCTION get_temp_blocks_cnt RETURN NUMBER IS
l_cnt NUMBER;
BEGIN
SELECT SUM(u.blocks)
INTO l_cnt
FROM v$tempseg_usage u, v$session s
WHERE u.session_addr = s.saddr AND AUDSID = USERENV('SESSIONID');
RETURN NVL(l_cnt, 0);
END;
BEGIN
l_temp_cnt := get_temp_blocks_cnt;
DBMS_LOB.createtemporary(bl, FALSE);
FOR i IN 1 .. 100000 LOOP
l_raw := utl_raw.cast_to_raw(RPAD(i, 1000, ' '));
dbms_lob.writeappend(bl, utl_raw.length(l_raw), l_raw);
IF MOD(i, 10) = 0 THEN
dbms_lob.freetemporary(lob_loc => bl);
DBMS_LOB.createtemporary(bl, FALSE);
END IF;
END LOOP;
dbms_output.put_line('Temp used=' || (get_temp_blocks_cnt - l_temp_cnt));
dbms_output.put_line('Time used ' || (dbms_utility.get_time - l_start_time) / 100);
END;
/
CACHE=TRUE
Temp used=0
Time used 2.08
PL/SQL procedure successfully completed
CACHE=FALSE
Temp used=0
Time used 42.29
PL/SQL procedure successfully completedПримечательно, что единственный способ, который сработал для очистки BLOB это
dbms_lob.freetemporary(lob_loc => bl);
DBMS_LOB.createtemporary(bl);Ни присвоение NULL, ни empty_blob, который работает только для insert и select, как оказалось из найденной документации от версии 8.1
An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.
А для свежих версий
You cannot use the locator returned from this function as a parameter
to the DBMS_LOB package or the OCI.
Для дальнейших исследований можно заняться замерами потребляемой памяти.
Случайно удалил пользователя OEBS в OID. Как результат: пользователь в OEBS живет (а удалить оотуда ничего нельзя), не изменяется (пишет ошибку на выполение пакета fnd_ldap_wrapper) и не синхронизируется назад.
Покопавшись, так и не нашел культурного способа синхронизировать одного пользователя.
Решил действовать в лоб, а именно:
1. Завести руками пользователя в OID
2. Узнать его guid
3. Прописать guid в fnd_user
Первый шаг делается через Oracle Directory Manager, второй и третий скриптом
DECLARE
l_user_name VARCHAR2(4000) := 'test_user';
l_guid RAW(16);
BEGIN
l_guid := fnd_ldap_user.get_user_guid_and_count(p_user_name => l_user_name,
n => :user_count);
dbms_output.put_line('l_guid=' || rawtohex(l_guid));
UPDATE fnd_user u SET u.user_guid = l_guid WHERE user_name = l_user_name;
END;После этого пользователь без ошибок меняется через интерфейс и может зайти под своим паролем.
Два самых больших вопроса, которые меня мучают и о которых, возможно, напишу позднее:
* есть ли культурный способ запушить пользователя (ровно одного, без удаления всей ветки) в OID
* Если верить функции fnd_ldap_user.get_user_guid GUID пользователя хранится в атрибуте orclguid. Но в Oracle Directory Manager такого атрибута я так и не нашел. Наверное эта утилита не позволяет смотреть raw значения. Ну хоть бы написало, что атрибут есть и не пустой :(
В одном из планов вылез ужасно медленный SORT GROUP BY
После беглого анализа нашел статью
http://guyharrison.squarespace.com/blog/2009/8/5/optimizing-group-and-order-by.html
В котором в красивых картинках все было рассказано, а после приведен хинт /*+USE_HASH_AGGREGATION*/
К сожалению надежды на быструю починку запроса разрушились комментарием VJ Kumar и следующим его подтверждением Jason
when subquery is used to insert records into another table, Oracle
seems to always use sort group by, even hint USE_HASH_AGGREGATION is
in place.
К счастью на 11.2.0.4 добавление хинта сделало запрос работающим как полагается.
В продолжении темы мониторинга индексов провел небольшое исследование про index monitoring + v$object_usage
Когда анализировал использование индексов по dba_hist_sql_plan запросы, которые собирают статистику приходилось отфильтровывать руками.
**Для index monitoring написал небольшой тест, который показал, что
index monitoring показывает только select по индексу и не показывает сбор статистики и insert в таблицу**
DROP TABLE t PURGE;
Table dropped
CREATE TABLE t(ix NUMBER);
Table created
CREATE INDEX ix_t ON t(ix);
Index created
ALTER INDEX ix_t MONITORING USAGE;
Index altered
SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T T YES NO 08/31/2015 19:18:23
INSERT INTO t
SELECT LEVEL FROM dual CONNECT BY LEVEL <= 1000;
1000 rows inserted
COMMIT;
Commit complete
SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T T YES NO 08/31/2015 19:18:23
BEGIN
dbms_stats.gather_table_stats(USER, 'T', CASCADE => TRUE);
END;
/
PL/SQL procedure successfully completed
SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T T YES NO 08/31/2015 19:18:23
SELECT COUNT(*) FROM t WHERE ix = 10;
COUNT(*)
----------
1
SELECT * FROM v$object_usage;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------------------------------ ------------------------------ ---------- ---- ------------------- -------------------
IX_T T YES YES 08/31/2015 19:18:23 По теме удаления неиспользуемых индексов в последнюю неделю начали писать все.
Статья Льюиса https://jonathanlewis.wordpress.com/2015/08/17/index-usage/ (обещал продолжение)
Вторая статья Льюиса немного не по теме https://jonathanlewis.wordpress.com/2015/08/29/index-usage-2/ – про то, что с 11.2.0.2 не надо делать индексы по trunc(datetime column),Oracle сам научился добавлять предикаты
Статья на форуме https://jonathanlewis.wordpress.com/2015/08/17/index-usage/
Старая статья Тима Холла про monitoring usage https://oracle-base.com/articles/10g/index-monitoring
В статье https://blogs.oracle.com/datawarehousing/entry/parallel_execution_precedence_of_hints
интересные результаты получились для строчек, в которых используются хинты PARALLEL, PARALLEL(degree) и PARALLEL(auto) (строки 9, 10 и 11). Получается, что если установлен такой хинт, то делать
ALTER SESSION ENABLE PARALLEL DMLне надо?
Надо провести тесты.
Типичная задача об удалении неиспользуемых индексов решается индивидуально для каждой системы.
Можно выбрать такой алгоритм:
выбрать индексы, которые реально необходимо оптимизировать, например
* большие по размеру
* на поддержку которых уходит много времени: много операций записи
индексы не используются
* проверяем по dba_hist_sql_plan без учета сбора статистики
* проверяем по dba_hist_seg_stat
Главная идея такая: собираем все подознительное и из этого откидываем, что есть в планах или читается.
Мой запрос такой, но его можно подкорректировать в зависимости от нужд (в части bad_idx + поиграться с количеством чтений и записей + еще что-нибудь дописать)
WITH large_idx AS (
SELECT owner, segment_name, segment_type, ROUND(sum(bytes)/1024/1024/1024, 2) size_gb
FROM dba_segments t
WHERE segment_type LIKE 'INDEX%'
GROUP BY owner, segment_name, segment_type
HAVING ROUND(sum(bytes)/1024/1024/1024, 2) > 5
),
seg_stat AS (
SELECT o.object_name, o.owner, SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) READS,
SUM(s.physical_write_requests_delta) + SUM(s.physical_writes_direct_delta) + sum(s.physical_writes_delta) writes
FROM dba_hist_seg_stat s, dba_objects o
WHERE s.obj# = o.object_id
AND o.object_type LIKE 'INDEX%'
AND o.owner LIKE 'OWNER%'
GROUP BY o.object_name, o.owner
HAVING SUM(s.logical_reads_delta) + SUM(s.physical_read_requests_delta) + SUM(s.physical_reads_direct_delta) + sum(s.physical_reads_delta) = 0
),
sql_plan AS (
SELECT /*+ MATERIALIZE*/
p.*
FROM (SELECT DISTINCT o.owner,
o.object_name,
sql_id
FROM dba_objects o,
dba_hist_sql_plan p
WHERE o.object_type LIKE 'INDEX%'
AND owner LIKE 'OWNER%'
AND p.object_owner = o.owner
AND p.object_name = o.object_name) p
WHERE EXISTS (SELECT NULL
FROM dba_hist_sqltext t
WHERE p.sql_id = t.sql_id
AND t.sql_text NOT LIKE '%\*%dbms_stats%*\%')
),
bad_idx AS (
SELECT owner, segment_name object_name, 'LARGE' reason FROM large_idx
UNION
SELECT owner, object_name, 'NOT USED' reason FROM seg_stat WHERE READS = 0 AND writes > 10000
)
SELECT /*+ PARALLEL(8)*/i.owner, i.object_name, round((SELECT sum(bytes) FROM dba_segments s WHERE s.owner = i.owner
AND s.segment_name = i.object_name)/1024/1024, 2) mb, LISTAGG(reason, '; ') WITHIN GROUP (ORDER BY 1),
'ALTER INDEX ' || i.owner || '.' || i.object_name || ' MONITORING USAGE;' monitiring_on,
'ALTER INDEX ' || i.owner || '.' || i.object_name || ' NOMONITORING USAGE;' monitiring_off
FROM bad_idx i
WHERE (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM sql_plan)
AND (i.owner, i.object_name) NOT IN (SELECT owner, object_name FROM seg_stat WHERE READS > 0)
GROUP BY i.owner, i.object_name
;Пока сильные подозрения вызываем правильность заполнения dba_hist_seg_stat
Таблица после MOVE не уменьшилась, хотя по оценкам должна быть в 15 раз меньше.
Выяснилось, что причина в неадекватном initial extents
Уменьшить его можно в самом move
ALTER TABLE test MOVE STORAGE (INITIAL 2097152) PARALLEL 8;Один INSERT очень сильно тормозил из-за существующих на таблице индексов. Быстрее было их удалить, вставить и построить их заново.
Тут столкнулся с проблемой: на индексах были построены primary key и unique key, а на PK еще и ссылались FK с других таблиц.
Минимальный работающий алгоритм получился такой:
1. Удалить внешние ключи (именно DROP, DISABLE не работает)
2. Удалить или сделать DISABLED PK и UK
3. Удалить индексы. Те, которые поддерживают PK и UK именно DROP, а не UNUSABLE
4. INSERT
5. Создаем назад индексы и FK, делаем PK ENABLE (для скорости можно NOVALIDATE)
Выводы
1. Любой FK мешает удалить PK или UK на который он ссылается. Даже DISABLE.
2. Индекс, поддерживающий PK или UK можно сделать UNUSABLE, но вставить записи в таблицу потом нельзя.
3. Удалить индекс, который поддерживает PK или UK невозможно.
4. Но можно сделать PK UNUSABLE, удалить индекс и вставлять
Скрипт и результаты работы
DROP TABLE chi PURGE;
DROP TABLE par PURGE;
CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));
CREATE TABLE chi(par_id NUMBER);
ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
-- тест с первичным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_pk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_pk;
-- подготовка к следующему эксперементу
ALTER TABLE chi DROP CONSTRAINT chi_fk;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
-- тест с уникальным ключом
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
-- нельзя удалить даже с DISABLED FK
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
-- работает, но записи добавить потом нельзя, т.к. это PK
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
-- Удалить индекс нельзя
DROP INDEX par_uk;
-- но можно сделать constraint DISABLE
ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
-- с UNUSABLE INDEX все равно нельзя вставить
ALTER INDEX par_uk UNUSABLE;
INSERT INTO par SELECT ROWNUM FROM dual;
--а вот без индекса можно
DROP INDEX par_uk;
INSERT INTO par SELECT ROWNUM FROM dual;
Скрипт с результатами
SQL> CREATE TABLE par(ID NUMBER, CONSTRAINT par_pk PRIMARY KEY (ID));
Table created
SQL> CREATE TABLE chi(par_id NUMBER);
Table created
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с первичным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_pk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_pk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_PK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_pk;
DROP INDEX par_pk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- подготовка к следующему эксперементу
SQL> ALTER TABLE chi DROP CONSTRAINT chi_fk;
Table altered
SQL> ALTER TABLE par DROP CONSTRAINT par_pk DROP INDEX;
Table altered
SQL> ALTER TABLE par ADD CONSTRAINT par_uk UNIQUE (ID);
Table altered
SQL> ALTER TABLE chi ADD CONSTRAINT chi_fk FOREIGN KEY (par_id) REFERENCES par(ID) DISABLE NOVALIDATE;
Table altered
SQL> -- тест с уникальным ключом
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk DROP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- нельзя удалить даже с DISABLED FK
SQL> ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX;
ALTER TABLE par DROP CONSTRAINT par_uk KEEP INDEX
ORA-02273: this unique/primary key is referenced by some foreign keys
SQL> -- работает, но записи добавить потом нельзя, т.к. это PK
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> -- Удалить индекс нельзя
SQL> DROP INDEX par_uk;
DROP INDEX par_uk
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> -- но можно сделать constraint DISABLE
SQL> ALTER TABLE par MODIFY CONSTRAINT par_uk DISABLE KEEP INDEX;
Table altered
SQL> -- с UNUSABLE INDEX все равно нельзя вставить
SQL> ALTER INDEX par_uk UNUSABLE;
Index altered
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
INSERT INTO par SELECT ROWNUM FROM dual
ORA-01502: index 'SPS.PAR_UK' or partition of such index is in unusable state
SQL> --а вот без индекса можно
SQL> DROP INDEX par_uk;
Index dropped
SQL> INSERT INTO par SELECT ROWNUM FROM dual;
1 row inserted