Динамический запрос для поиска всей активности таблицы DML во всей таблице oracle

#sql #oracle #plsql #dynamic-sql #dml

#sql #Oracle #plsql #динамический-sql #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)) для всех таблиц

есть предложения по исправлению синтаксиса запроса?

Ответ №1:

Вы можете использовать такой код 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;
/

до тех пор, пока не возникнет какое-либо исключение для каждой отдельной таблицы.

1. Большое спасибо, но если мне нужно фильтровать по схеме и списку таблиц?

2. Я не знаю вашего правила фильтрации, но просто замените запрос, добавив условие WHERE, например, ... WHERE table_name LIKE 'R%' для того, чтобы только таблицы начинались с буквы R @rakesh

3. для тестирования я отфильтровал по схеме, а также по таблице анонимный блок завершен, но как просмотреть результаты?

4. Вы выполняли проблему SET SERVEROUTPUT ON перед вызовом DBMS_OUTPUT.PUT_LINE @rakesh?

5. упс, я забыл указать, что это работает сейчас, один вопрос, как получить значение метки времени?

Ответ №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;