How to query which new tables are added in Oracle database

Summary: There are three methods to query newly added tables in Oracle database: use data dictionary view: query DBA_OBJECTS view and specify time range filtering. Use flashback query: use FLASHBACK_TABLE pseudo table and specify timestamp query. Use log file queries: Parse Oracle redo log files, filtering rows that create tables and add columns.

How to query the newly added table in Oracle database

Method 1: Use data dictionary view

• Using DBA_OBJECTS View:

<code class="sql">SELECT TABLE_NAME, CREATED
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND CREATED >= TO_DATE('2023-03-01', 'YYYY-MM-DD') -- 指定一个日期范围
ORDER BY CREATED DESC;</code>

Method 2: Using Flashback Query

• Use FLASHBACK_TABLE Pseudo table:

<code class="sql">SELECT * FROM FLASHBACK_TABLE('SCHEMA_NAME'.'TABLE_NAME', TIMESTAMP '2023-03-01')
ORDER BY CREATE_TIME DESC;</code>

Method 3: Use log file

• Query Oracle redo log File (redo logs):

<code class="sql">SET SERVEROUTPUT ON;
DECLARE
  sql_text VARCHAR2(4000);
BEGIN
  FOR logfn IN (SELECT MEMBER FROM V$LOG) LOOP
    DBMS_OUTPUT.PUT_LINE('Reading log file: ' || logfn);
    FOR line IN (SELECT LINE FROM V$LOGFILE(logfn) WHERE INST_ID = (SELECT INST_ID FROM V$INSTANCE) ORDER BY SEQ) LOOP
      IF line LIKE '%CREATE TABLE%' OR line LIKE '%ALTER TABLE ADD COLUMN%' THEN
        DBMS_OUTPUT.PUT_LINE('New table created: ' || line);
      END IF;
    END LOOP;
  END LOOP;
END;
/</code>

The above is the detailed content of How to query which new tables are added in Oracle database. For more information, please follow other related articles on the PHP Chinese website!