From CTrassens@uni2.es Mon, 19 Mar 2001 01:18:15 -0800 From: "Trassens, Christian" Date: Mon, 19 Mar 2001 01:18:15 -0800 Subject: RE: Script to know the size of each table in a schema ? Message-ID: MIME-Version: 1.0 Content-Type: text/plain Beware!!. That select will only return you the bytes allocated up to the HWM. To know the real used space issue this for all the tables: select count( distinct substr(rowid,15,4)||substr(rowid,1,8)) from ; ...................7.X or select count( distinct substr(rowid,1,15)) from
............ 8.X. You can make an script from this. Regards. > -----Mensaje original----- > De: Bambang Setiawan [SMTP:Bambang.Setiawan@Sidola.com] > Enviado el: lunes 19 de marzo de 2001 2:05 > Para: Multiple recipients of list ORACLE-L > Asunto: Re: Script to know the size of each table in a schema ? > > Thanks a lot Ruth , > It works fine... : ) > > sincerely yours, > > =bambang= > > > > <> Bambang Setiawan <> > > >>> "Ruth Gramolini" 03/16/01 08:00 >>> > To get the number of bytes in each table in a schema you can use the > following: > > select segment_name, sum(bytes) from dba_segments where > segment_type='TABLE' > and owner='SCHEMA_NAME' > group by segment_name > > This gives bytes but you can get blocks, extents, or bytes from > dba_segments. > > Ruth > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > Sent: Friday, March 16, 2001 3:56 AM > > > Ya , you're right : ) > > but I think I have to appreciate all the answers for my question ... > > one replied that DBA_SEGMENTS can be used to calculate > the size of each table in a schema ( in MBytes ). > It's big clue , I still found difficulty in its implementation... > > Do you have the script Tapas ? > please let me know : ) > > HTH, > > =bambang= > > > > > <> Bambang Setiawan <> > > >>> tapas dutta 03/16/01 02:25 >>> > HEY, > > THERE IS SOMETHING WRONG IN THE UNDERSTANDING. > BY THE MENTIONED SCRIPT ONE CAN ONLY KNOW THE NO. OF ROWS > PRESENT IN EACH TABLE FOR THAT PARTICULAR SCHEMA NOT THE SIZE. > > IS N'T IT. > > > -----Original Message----- > Sent: Friday, March 16, 2001 8:45 AM > To: Multiple recipients of list ORACLE-L > > Hi , > > Thanks a lot for your help , Ruth : ) > > sincerely yours, > > =bambang= > > > <> Bambang Setiawan <> > > >>> "Ruth Gramolini" 03/15/01 11:40 >>> > You can spool the output of this SQL and run it. > > select 'select '''||table_name||''', count(*) from '||table_name||';' from > dba_tables where owner='SCHEMA_OWNER'; > > To get the table name in the output you have to put 3 ' s before and after > the first ||table_name||. > > HTH, > Ruth > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" > Sent: Wednesday, March 14, 2001 9:25 PM > > > Dear Listers, > > I just made script to know the size of datafile in a tablepace , > but I still have no idea to make a script to know the size of each table > in > a schema ? > > is there anyone who has the script ? > furthermore , I need some information about relationship diagram which > describe > the relation among tables/view to calculate storage of objects ( i.e > tablespace , datafile , segment , extent ) . > > thanks in advance : ) > > =bambang= > > > > <> Bambang Setiawan <> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bambang Setiawan > INET: Bambang.Setiawan@Sidola.com > > 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@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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > INET: rgramolini@tax.state.vt.us > > 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@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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bambang Setiawan > INET: Bambang.Setiawan@Sidola.com > > 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@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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: tapas dutta > INET: tapasoracle@umtl.co.in > > 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@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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bambang Setiawan > INET: Bambang.Setiawan@Sidola.com > > 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@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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ruth Gramolini > INET: rgramolini@tax.state.vt.us > > 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@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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bambang Setiawan > INET: Bambang.Setiawan@Sidola.com > > 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@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Trassens, Christian INET: CTrassens@uni2.es 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@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).