RE: Dynamic SQL to query all columns on all tables
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: <bmullin@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2005 15:50:00 -0500
Well that crashed my database, however I was able to easily union 1000
columns on a small db together and then perform a search. This DB is
only 2GB, 9204 with about a 150MB SGA. Of course this thrashed the db
but not a bad solution I think if I was just needed to play around and
find something. You could easily break this up into a small set of
views and then ?? union them together ?? ok, enough bad advice for now.
Have fun.
set head off feed off trims on lines 200 term off
spool foovu.sql
select 'create or replace view foovu as ('||chr(10) x from dual
union all
select
'select '''||table_name||'.'||column_name||''' col, '
||column_name||' str from ' || table_name ||' union all'||chr(10) x
from all_tab_columns
where owner=3D'DORIS'
and data_type in ('CHAR','VARCHAR2')
and table_name not like 'AQ$_%'
and table_name not in (select view_name from all_views where
owner=3D'DORIS')
and rownum < 1000
union all
select 'select null col, null x from dual' x from dual
union all
select ');'||chr(10) x from dual;
spool off
set head on feed on term on
set sqlbl on
@foovu.sql
=3D=3D=3D=3D THEN RUN =3D=3D=3D=3D
dba1>@foo
View created.
dba1>select count(*) from foovu where str like 'AA%';
COUNT(*)
----------
3710
1 row selected.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Post, Ethan
Sent: Tuesday, April 26, 2005 3:24 PM
To: bmullin@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Dynamic SQL to query all columns on all tables
...
"foo.sql" 17008 lines, 4268377 characters
dba1>@foo.sql
I am betting this doesn't work.=3D20
--
//www.freelists.org/webpage/oracle-l
Other related posts:
