--#1: Create some test data
create table employee (id number primary key, name varchar2(100), department_id number);
create table department(id number primary key, name varchar2(100), test number);
insert into department select level, 'department test', level from dual connect by level <= 100;
insert into employee select level, 'employee test', level from dual connect by level <= 100;
--Actually run the query for this example so there will be data in the data dictionary.
select employee.* from employee inner join department on department_id = department.id;
--#2: The first difficult part is to find the sql_id of the query. Can you get this directly from the
-- result set? If not not you'll have to find it.
-- I'm not exactly sure how you'll want to do this, here are some options:
--Look at the last loaded query in v$sql (I don't think this always works, especially if the query has run multiple times)
select * from v$sql where v$sql.parsing_schema_name = user order by first_load_time desc;
--Compare the query text (sql_text removes newlines, sql_fulltext is a clob)
select * from v$sql where sql_text like 'select employee.* from employee inner join department on department_id = department.id%';
--Find the last sql_id for this session. This doesn't work for me, maybe it's just an express edition bug?
select prev_sql_id, v$session.* from v$session where sid = sys_context('USERENV', 'SID');
--Look at the plan. Note that there may be an index instead of a table.
--(On my system the sql_id is 0k2t2y1d312j8, but it will probably be different on yours)
select * from v$sql_plan where sql_id = '0k2t2y1d312j8';
--3: Create a type and a function to return all of the columns from a specific query.
--It'd be more consistent to use the SQL_ID here, but then there are permission issues if we
--have to get the text from v$sql.
create or replace type varchar2_tab is table of varchar2(30);
/
create or replace function get_columns(sql_text in varchar2) return varchar2_tab
authid current_user pipelined is
my_cursor number;
column_count number;
my_columns DBMS_SQL.DESC_TAB;
begin
select count(*) into column_count from v$sql;
my_cursor := dbms_sql.open_cursor;
dbms_sql.parse(my_cursor, sql_text, dbms_sql.native);
dbms_sql.describe_columns(my_cursor, column_count, my_columns);
for i in 1 .. my_columns.count loop
pipe row(my_columns(i).col_name);
end loop;
dbms_sql.close_cursor(my_cursor);
end;
/
--Test queries. Note that it's possible for a column to be listed twice.
select * from table(get_columns('select employee.* from employee inner join department on department_id = department.id'));
--4: Find the columns and their tables and schemas that are used in a query.
--Currently this only works for tables and indexes in the explain plan.
--There's probably a large number of items that won't work - materialized views, clusters(?), pipelined functiions, etc.
--You'll need to add each object type as necessary.
--(Remember to replace the SQL_ID and the query text with the real values)
select distinct owner, table_name, column_name
from
(
--Find all the columns for the relevant tables
select all_tab_cols.owner, all_tab_cols.table_name, all_tab_cols.column_name
from
(
--Find the relevant tables from the plans (may need to find the table behind an index)
select
nvl(all_indexes.table_owner, plan_objects.object_owner) owner,
nvl(all_indexes.table_name, plan_objects.object_name) table_name
from
(
select object_owner, object_name, object_type
from v$sql_plan
where sql_id = '0k2t2y1d312j8'
and
(
object_type = 'TABLE'
or object_type like 'INDEX%'
)
) plan_objects
left outer join all_indexes
on plan_objects.object_name = all_indexes.index_name
and plan_objects.object_type like 'INDEX%'
) relevant_tables
inner join all_tab_cols
on relevant_tables.owner = all_tab_cols.owner
and relevant_tables.table_name = all_tab_cols.table_name
) relevant_tab_cols
--It would be more
inner join table(get_columns('select employee.* from employee inner join department on department_id = department.id')) all_possible_columns
on relevant_tab_cols.column_name = all_possible_columns.column_value;
