Динамический запрос, чтобы найти всю активность таблицы DML во всех табличных оракулах
Привет, мне нужно найти активность DML указанного списка таблиц в схеме.
один запрос
select max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) FROM 'TABLE_NAME'
так как выполнение отдельных запросов занимает много времени и сложно, поэтому я пытаюсь подготовить динамический sql для извлечения max (ora_rowscn), SCN_TO_TIMESTAMP (max (ora_rowscn)) из всех таблиц, чтобы я мог использовать фильтр и выбрать набор столов
Шаблон запроса
select 'with tmp(table_name, row_number) as (' from dual
union all
select 'select '''||table_name||''',count(*) from '||table_name||' union ' from USER_TABLES
union all
select 'select '''',0 from dual) select table_name,row_number from tmp order by row_number desc ;' from dual;
как я использовал max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) для всех таблиц
любые предложения по исправлению синтаксиса запроса?
Ответы 2
Вот мое предложение. Я бы не рекомендовал использовать SCN_TO_TIMESTAMP для всех таблиц, так как это часто выдает ORA-01405.
select 'with tmp(table_name, max_rscn, ct) as (' from dual
union all
select 'select '''||table_name||''',max(ora_rowscn), count(*) from '||table_name||' union ' from USER_TABLES
union all
select 'select '''',0,0 from dual) select table_name, max_rscn from tmp;' from dual;
Вы можете использовать такой код PLSQL, содержащий EXECUTE IMMEDIATE, чтобы получить желаемые значения через динамический SQL.
SET SERVEROUTPUT ON
DECLARE
v_rowscn NUMBER;
v_tmstp TIMESTAMP;
BEGIN
FOR c IN
(SELECT t.table_name FROM user_tables t)
LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) FROM '||
c.table_name INTO v_rowscn, v_tmstp;
DBMS_OUTPUT.PUT_LINE( c.table_name||' - max_scn : '|| v_rowscn||
' - max_scn_timestamp : '|| v_tmstp );
EXCEPTION WHEN others THEN DBMS_OUTPUT.PUT_LINE( sqlerrm );
END;
END LOOP;
END;
/
если для каждой отдельной таблицы не возникает никаких исключений.
Большое спасибо, но если мне нужно отфильтровать схему и список таблиц?
Я не знаю вашего правила фильтрации, но просто замените запрос, добавив условие WHERE, такое как ... WHERE table_name LIKE 'R%', чтобы вывести только таблицы, начинающиеся с буквы R @rakesh
для тестирования я отфильтровал по схеме, а также по таблице анонимный блок завершен, но как просмотреть результаты?
Вы поставили SET SERVEROUTPUT ON перед тем, как вызвать DBMS_OUTPUT.PUT_LINE @rakesh ?
ой, я забыл сказать, что теперь это работает, один вопрос, как получить значение метки времени?
в чем проблема с переменной v_tmstp в данном случае @rakesh?
Большое спасибо за логику и решение, оно сработало после тестирования
хорошо, продолжай... не за что @rakesh
@Kinfinity Мне нужно получить данные из обоих этих двух столбцов max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) для выбранных таблиц