RE: sql query help

To take this to the next level, you can use sql to create sql but include the results of your first query so that you get what you want:

select
 'select '''||table_name||''', ', sum(bytes), ', count(*) from '||dt.owner||'.'||table_name||';'
from dba_tables dt, dba_segments ds
where
 dt.table_name = ds.segment_name
 and ds.segment_type = 'TABLE'
group by 'select '''||table_name||''', ', ', count(*) from '||dt.owner||'.'||table_name||';'

It's messy and maybe a little hard to follow, but it works!

Steve Monaghan

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Diane
> Whitehead
> Sent: Wednesday, June 28, 2000 12:09 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: sql query help
>
>
> The only suggestion I can give you is to use sql to generate sql which you
> then run
> ie
> select 'select count(*) from '||owner||'.'||tablename;'|| from dba_tables;
>
> This will give a number of select statements. If you then set echo on and
> run this list you should get the info you need in one file
> > -----Original Message-----
> > From: Suhen Pather [SMTP:pathers5_at_telkom.co.za]
> > Sent: Wednesday, June 28, 2000 4:25 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: sql query help
> >
> > Hi there Oracle Boffins
> >
> > I am trying to create a script that can give me the following
> information.
> >
> > All tables in the database (for specified users) together with
> the number
> > of rows in each table,size in bytes.
> > We are using the Rule based optimizer so I cannot use the row count from
> > the dictionary views.
> >
> > I can achieve the row count from 1 query and the other information from
> > another query.
> >
> > I can achieve all of the information but I cannot put this into
> one query.
> >
> > I will have to cut and paste the information if I use 2
> different queries
> > to get the desired information.
> > This is however not very efficient and could be a bit misleading.
> >
> > Any ideas
> >
> > TIA
> >
> > $uhen
> > 0racle DBA
> >
> >
> > --
> > Author: Suhen Pather
> > INET: pathers5_at_telkom.co.za
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Author: Diane Whitehead
> INET: Diane.Whitehead_at_palmerharvey.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jun 28 2000 - 12:15:07 CDT