Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Получить список всех синонимов, указывающих на несуществующие объекты  [new]
NikoTrend
Member

Откуда:
Сообщений: 10
Ребята, выручайте) Как получить список ВСЕХ синонимов, которые указывают на несуществующие объекты?
30 мар 14, 18:51    [15807900]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
Рустамка
Member

Откуда:
Сообщений: 55
NikoTrend,

select * from dba_objects where type='SYNONYM';

сработало?
30 мар 14, 19:06    [15807970]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
NikoTrend
Member

Откуда:
Сообщений: 10
Рустамка
NikoTrend,

select * from dba_objects where type='SYNONYM';

сработало?

Так если я правильно понимаю это просто все объекты с типом синоним? Вопрос-то как раз в том, как получить список, указывающий на несуществующие объекты?
Я не уверен, правильно ли это?:
select * from dba_objects where type='SYNONYM' and db_link IS NULL;
30 мар 14, 19:24    [15808019]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
NikoTrend,
select * from dba_synonyms s 
where NOT EXISTS (select null 
                    from dba_objects o ...) 
30 мар 14, 19:34    [15808040]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
ORA__SQL,

Не все так просто:

Синоним может быть на обьект в удаленной базе.
Cиноним может указывать на синоним, т.е. пройтись надо по иерархии.

SY.

Сообщение было отредактировано: 30 мар 14, 22:35
30 мар 14, 22:35    [15808486]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
Вадиман
Member

Откуда: Владивосток
Сообщений: 1072
простой вариант:

цикл по синонимам, внутри цикла dbms_utility.name_resolve:

DECLARE
  v_schema VARCHAR2(30);
  v_part1 VARCHAr2(30);
  v_part2 VARCHAR2(30);
  v_dblink VARCHAR2(30);
  v_part1_type VARCHAR2(30);
  v_obj_number NUMBER;
BEGIN
  DBMS_UTILITY.name_resolve('yy1', 2, v_schema, v_part1, v_part2, v_dblink, v_part1_type, v_obj_number);
  DBMS_OUTPUT.put_line('schema     =' || v_schema);
  DBMS_OUTPUT.put_line('part1      =' || v_part1);
  DBMS_OUTPUT.put_line('part2      =' || v_part2);
  DBMS_OUTPUT.put_line('dblink     =' || v_dblink);
  DBMS_OUTPUT.put_line('part1_type =' || v_part1_type);
  DBMS_OUTPUT.put_line('obj_number =' || v_obj_number);
END;


Ловим exception "ORA-06564: object yy1 does not exist", чтобы определить, что синоним мертвый
31 мар 14, 06:16    [15809110]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
Viewer
Member

Откуда: Самара
Сообщений: 5369
ORA__SQL
NikoTrend,
select * from dba_synonyms s 
where NOT EXISTS (select null 
                    from dba_objects o ...) 

+ just query generated by TOAD: дохлые --публичные, --частные синонимы
Select s.synonym_name, s.table_owner, s.table_name
from  sys.DBA_synonyms s
where not exists (Select 'x'
                  from sys.DBA_objects o
                  where o.owner = s.table_owner
                  and   o.object_name = s.table_name)
and db_link is null
-- and s.owner = 'PUBLIC'
-- and s.owner <> 'PUBLIC'
order by 1
31 мар 14, 09:53    [15809417]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
NikoTrend
Member

Откуда:
Сообщений: 10
Всем спасибо!
1 апр 14, 10:50    [15814821]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
NikoTrend
Member

Откуда:
Сообщений: 10
Кстати, если кому интересно вот красивый вариант:
SELECT owner,object_name
FROM dba_invalid_objects
WHERE object_type = ‘SYNONYM’;
3 апр 14, 21:37    [15829838]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
Viewer
Member

Откуда: Самара
Сообщений: 5369
NikoTrend
Кстати, если кому интересно вот НЕкрасивый вариант:
.......
FROM dba_invalid_objects
WHERE object_type = ‘SYNONYM’;

Когда синоним инвалидируется
Invalid Synonyms After an Upgrade
4 апр 14, 09:47    [15831246]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
Viewer, спасибо за подсказку со скриптом из TOAD (сообщение от 31.03.2014).
Хотел уточнить про следующую непонятную _обратную_ ситуацию.

Имеем набор скриптов создания демо схемы OE для Oracle 18c (взят с GitHub).
Основной скрипт oe_main.sql вызывает скрипт coe_v3.sql -> coe_xml.sql, который вызывает скрипты xdbConfiguration.sql и xdbUtilities.sql, которые создают 5 пакетов с именами coe_%, создают для этих пакетов одноименные PUBLIC SYNONYMs coe_% и используют вызовы процедур из этих пакетов. В конце скрипт coe_xml.sql _удаляет_ все эти 5 пакетов.

После возврата из этой цепочки вызовов скриптов в основной скрипт oe_main.sql в нем _повторно_ выполняются операторы создания PUBLIC SYNONYMs coe_% - те же самые, что и ранее:
CREATE OR REPLACE PUBLIC SYNONYM COE_CONFIGURATION FOR COE_CONFIGURATION;
CREATE OR REPLACE PUBLIC SYNONYM COE_NAMESPACES FOR COE_NAMESPACES;
...
Но при этом пакетов с именами coe_% уже _нет_, т.к. они были удалены после использования.
Тем не менее эти синонимы создаются и имеют статус VALID:
select owner, object_name, object_type, status from dba_objects where object_name like 'COE_%';

owner   object_name          object_type  status
PUBLIC COE_CONFIGURATION SYNONYM VALID
PUBLIC COE_DOM_HELPER SYNONYM VALID
PUBLIC COE_NAMESPACES SYNONYM VALID
PUBLIC COE_TOOLS SYNONYM VALID
PUBLIC COE_UTILITIES SYNONYM VALID

А указанный выше скрипт из TOAD выводит их как указывающие "в никуда":
select s.owner as synonym_owner, s.synonym_name, s.table_owner, s.table_name
from DBA_synonyms s
where not exists (select 'x'
from DBA_objects o
where o.owner = s.table_owner
and o.object_name = s.table_name)
and db_link is null
order by 1, 2;

synonym_owner synonym_name table_owner table_name
PUBLIC COE_CONFIGURATION SYS COE_CONFIGURATION
PUBLIC COE_DOM_HELPER SYS COE_DOM_HELPER
PUBLIC COE_NAMESPACES SYS COE_NAMESPACES
PUBLIC COE_TOOLS SYS COE_TOOLS
PUBLIC COE_UTILITIES SYS COE_UTILITIES

Вопрос: Как могли быть созданы синонимы со ссылкой на несуществующие пакеты? Почему синонимы имеют статус VALID, если они никуда не ссылаются?
9 сен 19, 09:22    [21966557]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
SBremen
Вопрос: Как могли быть созданы синонимы со ссылкой на несуществующие пакеты? Почему синонимы имеют статус VALID, если они никуда не ссылаются?


Oracle не проверяет существовaние обьекта при создании синонима. Синоним инвалидируется как только обьект изменяется (т.е. выдан DDL на обьект). Так-что создание обьекта инвалидирует синоним если он создан раньше самого обьекта:

SQL> create or replace
  2    synonym synonym_test for no_such_object
  3  /

Synonym created.

SQL> select status from user_objects where object_name = 'SYNONYM_TEST'
  2  /

STATUS
-------
VALID

SQL> create table no_such_object(n number)
  2  /

Table created.

SQL> select status from user_objects where object_name = 'SYNONYM_TEST'
  2  /

STATUS
-------
INVALID

SQL> select * from synonym_test
  2  /

no rows selected

SQL> select status from user_objects where object_name = 'SYNONYM_TEST'
  2  /

STATUS
-------
VALID


SY.

Сообщение было отредактировано: 9 сен 19, 14:22
9 сен 19, 14:21    [21966861]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
SY, спасибо за разъяснение.
Тогда получается, что повторно выполняющиеся в конце скрипта oe_main.sql операторы создания PUBLIC SYNONYMs coe_% на убитые пакеты - просто лишние, не вызывающие ошибки, но и не имеющие смысла.
10 сен 19, 20:16    [21968101]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
SY, еще один вопрос.

Есть ли какая-то настройка в глубинах Oracle, чтобы в сообщении об успешном завершении SQL оператора (хотя бы DDL) выводилось имя объекта?
То есть не "View created", а "View <Имя> created".
Не "Synonym dropped, а "Synonym <Имя> dropped".
Не "Package Body created with compilation errors", а "Package Body <Имя> created with compilation errors".

Понятно, что можно включить настройки SQL Plus:
SET ECHO ON
SET VERIFY ON
и смотреть SQL операторы целиком вместе с сообщениями о результате их исполнения, идущими после операторов.
Но вариант, описанный выше, существенно более компактный для просмотра и достаточно наглядный.

В документации по SQL и SQL Plus ничего похожего не нашел.
10 сен 19, 21:48    [21968154]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
SBremen
Есть ли какая-то настройка в глубинах Oracle, чтобы в сообщении об успешном завершении SQL оператора (хотя бы DDL) выводилось имя объекта?


Нет, но при большом желании можно создать AFTER DDL триггер который через DBMS_OUTPUT выводит

ora_dict_obj_owner || '.' || ora_dict_obj_name


Правда придется включать SET SERVEROUTPUT ON.

SY.

Сообщение было отредактировано: 10 сен 19, 22:25
10 сен 19, 22:24    [21968158]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
SY, "большое желание" связано с тем, что SQL Developer выводит сообщения о результатах DDL с указанием имени объекта:
create table test_t (m number);
Table TEST_T created.

drop table test_t;
Table TEST_T dropped.

А SQL Plus имена объектов не пишет (странно, почему это не сделано просто по умолчанию):
SQL> create table test_t (m number);
Table created.

SQL> drop table test_t;
Table dropped.

Поэтому и появилась мысль, что есть какая-то настройка Oracle (типа SET SERVEROUTPUT ON), которая включает/выключает вывод имени объекта в сообщении.

По Вашей рекомендации я создал триггер уровня БД (от имени SYSTEM):
CREATE OR REPLACE TRIGGER db_ddl_info_object
AFTER DDL ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('DB object name is: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name);
END;
Trigger DB_DDL_INFO_OBJECT compiled (это сообщение в SQL Developer, с именем объекта)

Этот триггер выводит необходимую информацию. Спасибо за подсказку.

Некоторая странность состоит в том, что почему-то вывод триггера по-разному работает в схемах SYSTEM и HR.
В схеме SYSTEM все происходит, как ожидалось:
SQL> show user
USER is "SYSTEM"

SQL> set serveroutput on
SQL> create table test (v number);
DB object name is: SYSTEM.TEST

Table created.

SQL> drop table test;
DB object name is: SYSTEM.TEST

Table dropped.

А в схеме HR при удалении таблицы выдается ДВА сообщения от триггера:
SQL> show user
USER is "HR"

SQL> set serveroutput on
SQL> create table test_t (m number);
DB object name is: HR.TEST_T

Table created.

SQL> drop table test_t;
DB object name is: HR.TEST_T
DB object name is: HR.TEST_T

Table dropped.

Так и должно быть? Или что я делаю не так?
БД Oracle 18c XE, все операции в контейнере БД XEPDB1 (создан по умолчанию при установке Oracle 18c).
11 сен 19, 13:00    [21968463]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
SBremen
Так и должно быть? Или что я делаю не так?


Зависит от того включен ли recyclebin или нет. У тебя включен. У пользователя SYSTEM tablespace по умолчанию SYSTEM. На SYSTEM recyclebin не распрoстраняется. Создай под SYSTEM таблицу в, скажем, tablespace USERS и получишь дважды. Добавь вывод DDL в триггер и все увидишь:

CREATE OR REPLACE
  TRIGGER db_ddl_info_object
  AFTER DDL
  ON DATABASE
  DECLARE
      V_SQL_STMT_LINE_CNT NUMBER;
      V_SQL_STMT_LINE     ORA_NAME_LIST_T;
  BEGIN
      DBMS_OUTPUT.PUT_LINE('DB object name is: ' || ora_dict_obj_owner || '.' || ora_dict_obj_name);
      V_SQL_STMT_LINE_CNT := ORA_SQL_TXT(V_SQL_STMT_LINE);
      FOR V_I IN 1..V_SQL_STMT_LINE_CNT LOOP
        DBMS_OUTPUT.PUT_LINE(V_SQL_STMT_LINE(V_I));
      END LOOP;
END;
/


SQL> create table system.test_t (m number);
DB object name is: SYSTEM.TEST_T
create table system.test_t (m number)

Table created.

SQL> drop table system.test_t;
DB object name is: SYSTEM.TEST_T
drop table system.test_t

Table dropped.

SQL> create table system.test_t (m number) tablespace users;
DB object name is: SYSTEM.TEST_T
create table system.test_t (m number) tablespace users

Table created.

SQL> drop table system.test_t;
DB object name is: SYSTEM.TEST_T
ALTER TABLE "SYSTEM"."TEST_T" RENAME TO "BIN$93ggIh9oQ9GbFPX38O
ZtUw==$0"
DB object name is: SYSTEM.TEST_T
drop table system.test_t

Table dropped.

SQL> 


Можешь добавить проверку DDL на NOT LIKE '%RENAME TO "BIN$%' перед выводом ora_dict_obj_owner || '.' || ora_dict_obj_name.

SY.
11 сен 19, 13:56    [21968536]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
SY, так и сделал. Итоговый триггер получился такой:
CREATE OR REPLACE TRIGGER db_ddl_info_object
AFTER DDL ON DATABASE
DECLARE
V_SQL_STMT_LINE_CNT NUMBER;
V_SQL_STMT_LINE ORA_NAME_LIST_T;
v_stmt VARCHAR2(2000) := '';
BEGIN
V_SQL_STMT_LINE_CNT := ORA_SQL_TXT(V_SQL_STMT_LINE);
FOR i IN 1..V_SQL_STMT_LINE_CNT LOOP
v_stmt := v_stmt || V_SQL_STMT_LINE(i);
END LOOP;
-- Additional output of DDL text
-- DBMS_OUTPUT.PUT('DDL executed is: ');
-- DBMS_OUTPUT.PUT_LINE(v_stmt);
-- Suppress duplicated trigger output when DDL is DROP (TABLE or INDEX) and RECYCLEBIN is enabled
if v_stmt NOT LIKE '%RENAME TO "BIN$%' then
DBMS_OUTPUT.PUT('DB object name is: ');
if ora_dict_obj_owner IS NOT NULL then
DBMS_OUTPUT.PUT(ora_dict_obj_owner || '.');
end if;
DBMS_OUTPUT.PUT_LINE(ora_dict_obj_name);
end if;
END;

Свою функцию выполняет, при создании/удалении объектов в SQL Plus имена объектов показывает...
И тут всплыл подводный камень.

В составе комплекта демо схем Oracle есть скрипты mkunplug.sql и mkplug.sql, которые соответственно выгружают ТП EXAMPLE с демо схемами как переносимое ТП и загружают его в БД. Я чуть раньше уже проверил, что они работают правильно - табличное пространство восстанавливается из бэкапа. А сейчас в ходе очередного эксперимента с объектами ТП снова запустил скрипт импорта mkplug.sql - при активном триггере AFTER DDL ON DATABASE с кодом выше.
И скрипт не смог воссоздать ТП, т.к. рухнул вызов impdp с ошибкой _в этом триггере_ (в скрипте импорт идет от имени SYS).

Получается, что использование такого триггера совсем небезопасно и имеет побочные эффекты.
Или я чего-то не учел?

К сообщению приложен файл. Размер - 95Kb
11 сен 19, 20:19    [21968885]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
SBremen
Получается, что использование такого триггера совсем небезопасно и имеет побочные эффекты.
Или я чего-то не учел?


Hе учел. Ты почему-то решил что SQL > 2000 байт в природе не бывает. Меняй 2000 нa 32767 алучше на CLOB.

SY.
11 сен 19, 21:30    [21968919]     Ответить | Цитировать Сообщить модератору
 Re: Получить список всех синонимов, указывающих на несуществующие объекты  [new]
SBremen
Member

Откуда:
Сообщений: 14
Эх, Семён Семёныч, точно...
Исправил на CLOB, пересоздал триггер - теперь он не мешает работе impdp.
Отлично!

Но зато наблюдаю другой эффект (поначалу непонятный).
Согласно документации "Oracle 18c Database PL-SQL Language Reference":
"Trigger AFTER DDL fires when most SQL DDL statements are issued. Not fired for ALTER DATABASE,
CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL subprogram interface".

Однако при исполнении скрипта mkplug.sql импорта ТП EXAMPLE с демо схемами при включенном триггере - вывода сообщений триггера _нет_ при выполнении практически всех SQL: CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE INDEX, ALTER TABLE, CREATE SYNONYM, DROP DIRECTORY, CREATE TYPE...

Не мог сначала понять, почему триггер так избирательно работает - в диалоге SQL Plus на создание / удаление тестовых таблиц триггер выводит сообщение, а при работе скрипта - нет (хотя я добавил set serveroutput on в начало скрипта mkplug.sql, а во всех вызываемых скриптах параметр set serveroutput отсутствует).

Потом дошло. В этом скрипте происходит постоянное _изменение connect_ между всеми 6 демо схемами и схемой SYS. И при каждом коннекте - параметр serveroutput _сбрасывается_ в OFF.
Наверное, сбрасываются и другие параметры SQL Plus (?) - я не проверял.

В общем, вопрос с этим триггером закрыт.
Спасибо за помощь, SY.
12 сен 19, 12:58    [21969369]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить