Reuse Oracle data dictionary to parse all tables used in SQL statements

Principle:

Each SQL statement has its execution plan, and the Execution Plan is placed in plan_table. plan_table contains the object_name column, which indicates the object name in the SQL statement. The execution plan is very interested in the table, so if object_type = 'table', object_name is the table_name

Procedure:

1. Create a tmp_table table and store the job name job_id, SQL statement select_ SQL, and table name object_name.

Create table tmp_table (job_idvarchar2 (50), select_ SQL clob, object_name varchar2 (50 ));

Analog data:

A job is A short SQL statement.

B job is a long SQL statement.

INSERTINTO TMP_TABLE VLAUES ('A', Q' [content in a] ');

INSERTINTO TMP_TABLE VLAUES ('B', Q' [content in B] ');

Commit;

2. Create a p stored procedure according to the execution plan

-- Run the following command with A job of less than 4000 and B of more than 4000

Create or replace procedure sqljiexiqi

V_str varchar2 (4000 );

V_ SQL varchar2 (4000 );

V_sql1 varchar2 (4000 );

V_sql2 varchar2 (4000 );

V_sql3 varchar2 (4000 );

V_sql4 varchar2 (4000 );

V_sql5 varchar2 (4000 );

V_sql6 varchar2 (4000 );

V_sql7 varchar2 (4000 );

V_sql8 varchar2 (4000 );

V_sql9 varchar2 (4000 );

Begin

Dbms_output.enable (8000 );

Execute immediate 'truncate table tmp_table ';

Forc in (select job_id, select_ SQL

From bds. etl_job_info

Where length (trim (select_ SQL) <4000

And job_id = 'A') loop

V_ SQL: = 'explain plan SET STATEMENT_ID = ''' | c. job_id | ''' for '|

C. select_ SQL;

Execute immediate v_ SQL;

For re in (select distinct object_name

From plan_table

Where statement_id = c. job_id

And object_type = 'table') loop

Insert into tmp_table

(Job_id, select_ SQL, object_name)

Values

(C. job_id, c. select_ SQL, re. object_name );

End loop;

Commit;

Endloop;

Forc in (select job_id, select_ SQL

From bds. etl_job_info

Where length (trim (select_ SQL) >=4000

And job_id = 'sj _ AS_CM_MORT_DTL_D ') loop

V_str: = 'explain plan SETSTATEMENT_ID = ''' | c. job_id | ''' ';

V_ SQL: = substr (c. select_ SQL, 1,4000 );

V_sql1: = substr (c. select_ SQL, 1*4000 + 1, 4000 );

V_sql2: = substr (c. select_ SQL, 2*4000 + 1, 4000 );

V_sql3: = substr (c. select_ SQL, 3*4000 + 1, 4000 );

V_sql4: = substr (c. select_ SQL, 4*4000 + 1, 4000 );

V_sql5: = substr (c. select_ SQL, 5*4000 + 1, 4000 );

V_sql6: = substr (c. select_ SQL, 6*4000 + 1, 4000 );

V_sql7: = substr (c. select_ SQL, 7*4000 + 1, 4000 );

V_sql8: = substr (c. select_ SQL, 8*4000 + 1, 4000 );

V_sql9: = substr (c. select_ SQL, 9*4000 + 1, 4000 );

Execute immediate v_str | v_ SQL | v_sql1 | v_sql2 | v_sql3 |

V_sql4 | v_sql5 | v_sql6 | v_sql7 | v_sql8 |

V_sql9;

For re in (select distinct object_name

From plan_table

Where statement_id = c. job_id

And object_type = 'table') loop

Insert into tmp_table

(Job_id, select_ SQL, object_name)

Values

(C. job_id, c. select_ SQL, re. object_name );

End loop;

Commit;

Endloop;

End;

3. Execute the Stored Procedure

Exec sqljiexiqi;

4. query the tmp_table table to obtain the tables in the SQL statement.

Select * from tmp_table;