Definetly u can select all the rows from all the tables from one or more user .
let me know if column names and data types are same in all the tables . then it will be simpler .
select col1 , col2 , col3 from tab1 ;
union
select col1 , col2 , col3 from tab2 ;
union
......
......
if table structures are different ( column names , datatypes , number of columns in a table ) then u have to follow different procedure .
1) use aliases for each column name .
2) put all datatypes column in one row.
3) see that each select statement has same number of columns when using UNION Clause .
ex :
select eff_dt date , empno NUM01 from tab1 ;
union
select dep_dt date , deptno NUM01 from tab2 ;
union
select ced_dt date , null from tab3 ;
....
....
set serveroutput on;
spool myscript.sql
declare
stmt VARCHAR2(400);
cursor t_cursor is select table_name from user_tables;
begin
for t_rec in t_cursor loop
stmt:='SELECT * FROM '||t_rec.table_name ;
dbms_output.put_line(stmt);
dbms_output.put_line('union');
end loop;
end;
spool off;
verify the script and remove the unnecessary lines at end
I think I responded to this on another board, but depending on what you want to find out, you could export the user's schema including data then either grep (or if you have the right tools, edit/search) for the pattern you want.
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'
Reply With Quote
Bookmarks