RE: Table Size
SQL @actual eid_roles BYTES_USED -- 30711808 SQL select sum(bytes) from dba_segments where segment_name = 'EID_ROLES' SQL / SUM(BYTES) -- 34996224 The script reports the blocks (as determined by the rowid substring) that have live rows in them and multiplies it by the db_block size. It does not report blocks that have no rows and, presumably, are available for new data. In your example, it would depend on how many rows per block. It could or could not return the same result. It returns the blocks with live data expressed as bytes. Mercadante, Thomas FTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] NDATFM cc: @labor.state.ny. Subject: RE: Table Size us Sent by: ml-errors 10/16/2003 10:19 AM Please respond to ORACLE-L Thomas, All that this does is report the number of rows. It does not deal with any columns within the table. If I had two tables with the exact number of rows, but one table had 10 2K columns fully populated, and the other table had 10 1-char columns fully populated, this query would return the same result. I'm not really sure what value this query has. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, October 15, 2003 4:49 PM To: Multiple recipients of list ORACLE-L This will get you that actual bytes used by the data in the table; however, it will hit every row in the table and take a long time to run. It takes one parameter, the table being sized. rem rem this code was inspired by Kevin Loney rem set verify off; set pages 24; select COUNT(DISTINCT(SUBSTR(a.ROWID,1,16)))* b.value Bytes_Used from 1 a, v$parameter b where b.name = 'db_block_size' group by b.value / Pillai, Rajesh Rajesh.Pillai To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @nordstrom.com cc: Sent by: Subject: RE: Table Size ml-errors 10/15/2003 04:19 PM Please respond to ORACLE-L Allan, I cannot run stats on the table Regards, Rajesh -Original Message- From: Nelson, Allan [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Table Size If you are running stats on this table or can run stats use dba_segements Allan -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: Table Size Hi All, Could somebody help me in finding the actual size of an oracle table in GB
Re: Table Size
select bytes from dba_segments where segment_name = 'your_table'; divide the result by 1024*1024*1024 and you'll get the size in GB - Original Message - From: Pillai, Rajesh To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 15, 2003 9:24 PM Subject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
RE: Table Size
Thomas, All that this does is report the number of rows. It does not deal with any columns within the table. If I had two tables with the exact number of rows, but one table had 10 2K columns fully populated, and the other table had 10 1-char columns fully populated, this query would return the same result. I'm not really sure what value this query has. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, October 15, 2003 4:49 PM To: Multiple recipients of list ORACLE-L This will get you that actual bytes used by the data in the table; however, it will hit every row in the table and take a long time to run. It takes one parameter, the table being sized. rem rem this code was inspired by Kevin Loney rem set verify off; set pages 24; select COUNT(DISTINCT(SUBSTR(a.ROWID,1,16)))* b.value Bytes_Used from 1 a, v$parameter b where b.name = 'db_block_size' group by b.value / Pillai, Rajesh Rajesh.Pillai To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @nordstrom.com cc: Sent by: Subject: RE: Table Size ml-errors 10/15/2003 04:19 PM Please respond to ORACLE-L Allan, I cannot run stats on the table Regards, Rajesh -Original Message- From: Nelson, Allan [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Table Size If you are running stats on this table or can run stats use dba_segements Allan -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: Table Size Hi All, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Table Size
Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
RE: Table Size
Raj, Does the table already exist have data or are you looking for a gestimate? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
RE: Table Size
Try:
SELECT segment_type,
segment_name,BLOCKS*8192/1024 "Kb"
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('owner') AND SEGMENT_NAME =
UPPER('table_name');
Tony
-Original
Message-From: Pillai, Rajesh
[mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15,
2003 3:24 PMTo: Multiple recipients of list
ORACLE-LSubject: Table Size
Hi
All,
Couldsomebody help me in
finding the actual size of an oracle table in GB.
TIA,
Rajesh
Re: Table Size
On 10/15/2003 03:24:29 PM, Pillai, Rajesh wrote:
Hi All,
Could somebody help me in finding the actual size of an oracle
table in GB.
TIA,
Rajesh
Select sum(bytes)/1073741824 Baud Rate
from dba_extents
where segment_type='TABLE PARTITION'
and segment_name=upper('table');
--
Mladen Gogala
Oracle DBA
Note:
This message is for the named person's use only. It may contain confidential,
proprietary or legally privileged information. No confidentiality or privilege is
waived or lost by any mistransmission. If you receive this message in error, please
immediately delete it and all copies of it from your system, destroy any hard copies
of it and notify the sender. You must not, directly or indirectly, use, disclose,
distribute, print, or copy any part of this message if you are not the intended
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
RE: Table Size
All the tables already exist and have data. Thanks a lot for your response, Rajesh -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 12:45 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Table Size Raj, Does the table already exist have data or are you looking for a gestimate? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
RE: Table Size
Title: Message If you are running stats on this table or can run stats use dba_segements Allan -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 2:24 PMTo: Multiple recipients of list ORACLE-LSubject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]
Re: Table Size
Well, if the table is partitioned you'll get a nice fat zero On 10/15/2003 03:49:36 PM, Stephen Lee wrote: How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -Original Message- Hi All, Could somebody help me in finding the actual size of an oracle table in GB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
Steve, That gives you the size of the segments/extents that are the table, but not the actual amount of space being used. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, October 15, 2003 3:50 PM To: Multiple recipients of list ORACLE-L How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -Original Message- Hi All, Could somebody help me in finding the actual size of an oracle table in GB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -Original Message- Hi All, Could somebody help me in finding the actual size of an oracle table in GB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
Title: Message Allan, I cannot run stats on the table Regards, Rajesh -Original Message-From: Nelson, Allan [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 12:54 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Table Size If you are running stats on this table or can run stats use dba_segements Allan -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 2:24 PMTo: Multiple recipients of list ORACLE-LSubject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh __This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]
RE: Table Size
How true. My previous query would be incorrect also. ANALYZE the table COMPUTE STATISTICS (or ESTIMATE STATISTICS if it's a very large table) and then select num_rows * avg_row_len from dba_tables where owner = 'table_owner' and table_name = 'table_name' ; -Original Message- Goulet, Dick That gives you the size of the segments/extents that are the table, but not the actual amount of space being used. -Original Message- How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
The query proposed by Anthony Hsu assumes that the tablespace has an 8K block size.
Instead use the query below. Of course the result will be in bytes, not gigabytes, but
any good DBA can instantly convert bytes to gigabytes or terabytes at a glance. :)
P.S. I include LOB indexes in the table total. Some people might argue that those
should be in the index total, not the table total, but I figure that without the LOB
column those indexes wouldn't be there so they should properly be counted in the table
total.
select sum (x.bytes)
from
(select a.bytes
from dba_segments a
where a.owner = 'table_owner'
and a.segment_name = 'table_name'
and a.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
union all
select d.bytes
from
dba_tab_columns b, dba_lobs c, dba_segments d
where
b.owner = 'table_owner'
and b.table_name = 'table_name'
and b.column_name = c.column_name
and d.owner = c.owner
and d.segment_name = c.segment_name
and d.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
union all
select g.bytes
from
dba_tab_columns e, dba_lobs f, dba_segments g
where
e.owner = 'table_owner'
and e.table_name = 'table_name'
and e.column_name = f.column_name
and g.owner = f.owner
and g.segment_name = f.index_name
and g.segment_type in ('LOBINDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
) x ;
-Original Message-
Hsu, Anthony C., ,CPMS
Try:
SELECT segment_type, segment_name,BLOCKS*8192/1024 Kb
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('owner') AND SEGMENT_NAME = UPPER('table_name');
-Original Message-
Could somebody help me in finding the actual size of an oracle table in GB.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jacques Kilchoer
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
RE: Table Size
This will get you that actual bytes used by the data in the table; however, it will hit every row in the table and take a long time to run. It takes one parameter, the table being sized. rem rem this code was inspired by Kevin Loney rem set verify off; set pages 24; select COUNT(DISTINCT(SUBSTR(a.ROWID,1,16)))* b.value Bytes_Used from 1 a, v$parameter b where b.name = 'db_block_size' group by b.value / Pillai, Rajesh Rajesh.Pillai To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @nordstrom.com cc: Sent by: Subject: RE: Table Size ml-errors 10/15/2003 04:19 PM Please respond to ORACLE-L Allan, I cannot run stats on the table Regards, Rajesh -Original Message- From: Nelson, Allan [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Subject: RE: Table Size If you are running stats on this table or can run stats use dba_segements Allan -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Subject: Table Size Hi All, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
Then the easy answer is: select sum(nvl(vsize(column_1),1)+nvl(vsize(column_2),1)+.. )/1073741824 from table_name; Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 3:55 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Table Size All the tables already exist and have data. Thanks a lot for your response, Rajesh -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 12:45 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Table Size Raj, Does the table already exist have data or are you looking for a gestimate? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Pillai, Rajesh [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Table Size Hi All, Couldsomebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
RE: Table Size
I think we can use dbms_space_admin package to get the accurate size of the table...not the allocated (querying the data dictionary will report the allocated space...) Murali. |-+ | | Goulet, Dick | | | [EMAIL PROTECTED]| | | | | | Sent by: | | | [EMAIL PROTECTED]| | | .com | | || | || | | 10/15/2003 05:24 | | | PM | | | Please respond to| | | ORACLE-L | | || |-+ --| | | | To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] | | cc: | | Subject: RE: Table Size | --| Then the easy answer is: select sum(nvl(vsize(column_1),1)+nvl(vsize(column_2),1)+.. )/1073741824 from table_name; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 3:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: Table Size All the tables already exist and have data. Thanks a lot for your response, Rajesh -Original Message- From: Goulet, Dick [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 12:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: Table Size Raj, Does the table already exist have data or are you looking for a gestimate? Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- From: Pillai, Rajesh [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Subject: Table Size Hi All, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Table Size
no, it should get the correct size allocated to all the partitions select sum(bytes)/(1024*1024*1024) from dba_segments where segment_name = table_name; SUM(BYTES)/(1024*1024*1024) --- 18 select partition_name, sum(bytes)/(1024*1024*1024) from dba_segments where segment_name = table_name group by partition_name; PARTITION_NAME SUM(BYTES)/(1024*1024*1024) -- --- TAB_2002_1Q 2.375 TAB_2002_2Q 2.25 TAB_2002_3Q 2 TAB_2002_4Q 2.25 TAB_2003_1Q 2.625 TAB_2003_2Q 2.125 TAB_2003_3Q 2 TAB_2003_4Q .125 TAB_HIST2.125 TAB_MAX1 .125 Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 3:04 PM Well, if the table is partitioned you'll get a nice fat zero On 10/15/2003 03:49:36 PM, Stephen Lee wrote: How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -Original Message- Hi All, Could somebody help me in finding the actual size of an oracle table in GB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Table Size?
Dear List, I have a database with 500 tables I want to put these tables based on size to Three different TBS,Small size tables less than 5 M on TBS1 with extend size 160K ,Tables less than 160 M and bigger than 5 M on TBS2 with extend size 5 M and tables bigger than 160 M on TBS3 with extend size 160 M, My question is do I have to calculate the size of tables for the life cycle of database(for example 5 years) or Some thing else? Example: tableA size = 10M in first year so this table must be multiple 5 and assume as Medium size or NOT? Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size?
Hamid Be sure to read How to Stop Defragmenting and Start Living very carefully so you really understand the concepts. These concepts free you from being overly concerned about details. Myself, I try to plan for 1 year of growth. The future gets too unpredictable past that. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, April 03, 2003 10:44 AM To: Multiple recipients of list ORACLE-L Dear List, I have a database with 500 tables I want to put these tables based on size to Three different TBS,Small size tables less than 5 M on TBS1 with extend size 160K ,Tables less than 160 M and bigger than 5 M on TBS2 with extend size 5 M and tables bigger than 160 M on TBS3 with extend size 160 M, My question is do I have to calculate the size of tables for the life cycle of database(for example 5 years) or Some thing else? Example: tableA size = 10M in first year so this table must be multiple 5 and assume as Medium size or NOT? Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: how to calculate table size
Hi Ravindra,
Use the following, supposing your db_block_size is 2048 (change as appropiate).
SELECT segment_type, segment_name,BLOCKS*2048/1024 Kb
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('owner') AND SEGMENT_NAME = UPPER('table_name');
You should substract emptied blocks from this table, using:
ANALYZE TABLE owner.table_name ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 Kb
FROM DBA_TABLES
WHERE OWNER=UPPER('owner') AND TABLE_NAME = UPPER('table_name');
This will give you how many kb are occupied by empty blocks, so substract this
amount from the prior result.
Hope this helps.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja,
Ravindra
Enviado el: martes, 01 de abril de 2003 1:24
Para: Multiple recipients of list ORACLE-L
Asunto: how to calculate table size
Hi,
Anyone having any formula to calculate table size?Basically to estimate the growth of
table over a peroid of time.
I have the row_size,db_block_size.How do i get the table size.
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
RE: how to calculate table size
I've been using this query for both normal and empty tables and works so far.
For those tables with no data in them (or that have suffered no deletion) you
can ommit the second query since the table should not have any emptied block. Emptied
blocks are those who have been occupied by data from the table but that have been
deleted; for these, Oracle marks them as deleted but are still asigned to a table. I
do not know if you want to consider this free space as part of the table or not.
There is a way to deallocate unused space to a table that has been previously
used. You must use 'alter table ... deallocate' for that. You have explanations on
this in the manual, check:
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/statements_32.htm#2080417
I am sending this mail to the list as well, I am a learner and do not consider
myself an expert, maybe someone else can join and comment something.
-Mensaje original-
De: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]
Enviado el: martes, 01 de abril de 2003 20:41
CC: '[EMAIL PROTECTED]'
Asunto: RE: how to calculate table size
Hi Fermin,
Thanks for your reply.
I am estimating the growth of database tables for a new database and many tables don't
have any data.
Can I still use the same queries to estimate the size of the tables or do you have
anything different?
Thanks
-Original Message-
Sent: Tuesday, April 01, 2003 4:24 AM
To: Multiple recipients of list ORACLE-L
Hi Ravindra,
Use the following, supposing your db_block_size is 2048 (change as appropiate).
SELECT segment_type, segment_name,BLOCKS*2048/1024 Kb
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('owner') AND SEGMENT_NAME = UPPER('table_name');
You should substract emptied blocks from this table, using:
ANALYZE TABLE owner.table_name ESTIMATE STATISTICS;
SELECT TABLE_NAME, EMPTY_BLOCKS*2048/1024 Kb
FROM DBA_TABLES
WHERE OWNER=UPPER('owner') AND TABLE_NAME = UPPER('table_name');
This will give you how many kb are occupied by empty blocks, so substract this
amount from the prior result.
Hope this helps.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Basavaraja,
Ravindra
Enviado el: martes, 01 de abril de 2003 1:24
Para: Multiple recipients of list ORACLE-L
Asunto: how to calculate table size
Hi,
Anyone having any formula to calculate table size?Basically to estimate the growth of
table over a peroid of time.
I have the row_size,db_block_size.How do i get the table size.
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Basavaraja, Ravindra
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: Fermin Bernaus Berraondo
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
how to calculate table size
Hi, Anyone having any formula to calculate table size?Basically to estimate the growth of table over a peroid of time. I have the row_size,db_block_size.How do i get the table size. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: TABLE SIZE
On Thursday 20 March 2003 18:19, Hamid Alavi wrote: Dear List, Hi, I have a question regarding Table size, I want to put the small size of tables in a small TBS,But don't know the size of table do I have to calculate the size for which period, Assume we want to keep the data for 5 years so do I have to calculate the size of table for 5 years then decide if this table is in the range of small table or NOT. two years ahead, we did the same. In our application we have est. 350 Tables with a range of rows between 10 and 10.000.000. Because of this and the fact that some of our customers use Oracle 7.3.4 we decided to split the different tables across 3 different tablespaces with fixed extent sizes. It is very good described in the white paper from Oracle about fragmentation. Here is the Link: http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 We decided to think about 1 year in the future for every table. One reason was the ability of Oracle 8.1.7 to move tables to another tablespace. The customers with Ora7 are forced to change the release to ORA8i. So we don´t need to expect the rowcount for 5 years, because of the fact, that new Oracle Releases often have new abilities dealing with such problems. One example is of this fact is Ora8i and the local managed tablespaces. It´s worth to think about a solution with one ore more tablespaces with this feature enabled. We have very good experiences with this. Any Idea appreciate. Thanks, Hamid Alavi hth Jörg Jost -- - Software Engineering Vattmannstraße 7, 33100 Paderborn Phone: (+49) 52 51 / 30 1 6333 Fax: (+49) 52 51 / 30 16 399 eMail: [EMAIL PROTECTED] http://www.unitrade.com -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joerg Jost INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
TABLE SIZE
Dear List, I have a question regarding Table size, I want to put the small size of tables in a small TBS,But don't know the size of table do I have to calculate the size for which period, Assume we want to keep the data for 5 years so do I have to calculate the size of table for 5 years then decide if this table is in the range of small table or NOT. Any Idea appreciate. Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: TABLE SIZE
Load up a sample set of rows, check the number of blocks used (analyze, then look at user_tables), then multiply by the expected increase factor hth connor --- Hamid Alavi [EMAIL PROTECTED] wrote: Dear List, I have a question regarding Table size, I want to put the small size of tables in a small TBS,But don't know the size of table do I have to calculate the size for which period, Assume we want to keep the data for 5 years so do I have to calculate the size of table for 5 years then decide if this table is in the range of small table or NOT. Any Idea appreciate. Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 === Confidentiality Statement === The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. = End Confidentiality Statement = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Table Size
The best way to calculate the size of a table is to load it with 1000 production data rows. Then calculate the size of the predicted volume. You should be able to handle the first year of data at day 1. Do not loose time to calculate the table size at the byte level with formulas. For the temp tablespace, it depends on your application needs. You should be able to rebuild all objects successfully. As for the rollback segment tablespace, then again it depends on your application and number of users. Do not forget space for exports, backups, workspace, at least 2 versions of Oracle software, ... I just went to a DB2 fast track for experienced DBA. According to IBM, the total size for the DB environment is 4 times the size of the data in the DB. (DB2 uses a lot of temporary files outside the DB). --- [EMAIL PROTECTED] a écrit : Dear List, I am using Oracle 9i database. I need to identify the space requirements for our database for the production database. Can anybody suggest how to calculate the size of a table. and also additionally how to calculate the size of the whole database.? Regards Prem = Stéphane Paquette DBA Oracle et DB2, consultant entrepôt de données Oracle and DB2 DBA, datawarehouse consultant [EMAIL PROTECTED] __ Lèche-vitrine ou lèche-écran ? magasinage.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Table Size
Prem - Simple way to get in the ballpark with table size. Figure out how large your average row is and multiply by the number of rows. This is only approximate and you'll want to size larger for indexes and other effects like the fact that your data blocks won't be 100% utilized. Better yet, create a test table, insert some data, ANALYZE the table, and then get the average row length from USER_TABLES view. How large the entire database will be depends on many factors. If you are just creating a simple test database and have a gig or so of space, then you shouldn't have a problem. Oracle Installer is good today about sizing everything for you. If you care to, look up the new feature Oracle Managed Files, designed just for people like you. On the other hand, if you have a special reason for being really, really concerned about precisely how much space it will take, then welcome to the world of the Oracle DBA. We can help you, but you need to provide more information. Hope this is what you were asking. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 28, 2002 10:44 PM To: Multiple recipients of list ORACLE-L Dear List, I am using Oracle 9i database. I need to identify the space requirements for our database for the production database. Can anybody suggest how to calculate the size of a table. and also additionally how to calculate the size of the whole database.? Regards Prem -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Table Size
Dear List, I am using Oracle 9i database. I need to identify the space requirements for our database for the production database. Can anybody suggest how to calculate the size of a table. and also additionally how to calculate the size of the whole database.? Regards Prem
Actual table size (Has been posted/discussed before)
Hi All, It has previously been discussed in this list regarding the actual size of a table. Can someone re-post the SQL to get this? I've got a table that has been ANALYZED. It has only 1 extent (INITIAL 50M). There's been a lot of deletes and insert on it. I wanted to know how much of the 50M has data in it? On 817. Rgds, Ross -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ross Collado INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Actual table size (Has been posted/discussed before)
You can try this:
REM == From Oracle-l posting
set echo off term on feed on pause off verify off
prompt Username to check space for:
col cobjuser noprint new_value uobjuser
set term off feed off
select upper('1') cobjuser from dual;
set term on feed on
prompt Object to check space on ( for uobjuser user ):
col cobjname noprint new_value uobjname
set term off feed off
select upper('2') cobjname from dual;
set term on feed on
prompt Type of object: - (T)able (I)ndex
col cobjtype noprint new_value uobjtype
set term off feed off
select ('3') cobjtype from dual;
set term on feed on
set serverout on size 100
prompt
prompt
prompt
set feed off
declare
op1 number;
op2 number;
op3 number;
op4 number;
op5 number;
op6 number;
op7 number;
objname varchar2(30);
objtype varchar2(10);
objuser varchar2(30);
free_blocks number :=0;
begin
select upper('uobjuser') into objuser from dual;
select
decode(upper('uobjtype'),
'T','TABLE',
'I','INDEX',
NULL
) into objtype from dual;
select upper('uobjname') into objname from dual;
dbms_space.unused_space(objuser,
objname, objtype,
op1,op2,op3,op4,op5,op6,op7);
dbms_output.put_line('schema= ' || objuser);
dbms_output.put_line('object name = ' || objname);
dbms_output.put_line('object type = ' || objtype);
dbms_output.put_line('-');
dbms_output.put_line(' total_blocks = '||op1);
dbms_output.put_line(' total_bytes = '||op2);
dbms_output.put_line(' unused_blocks = '||op3);
dbms_output.put_line(' unused_bytes = '||op4);
dbms_output.put_line(' last_used_extent_file_id = '||op5);
dbms_output.put_line(' last_used_extent_block_id = '||op6);
dbms_output.put_line(' last_used_block = '||op7);
end ;
/
prompt
prompt
prompt
set feed on
undef 1 2
-Original Message-
Sent: Wednesday, October 16, 2002 10:39 PM
To: Multiple recipients of list ORACLE-L
Hi All,
It has previously been discussed in this list regarding the actual size of a
table. Can someone re-post the SQL to get this?
I've got a table that has been ANALYZED. It has only 1 extent (INITIAL
50M). There's been a lot of deletes and insert on it. I wanted to know how
much of the 50M has data in it?
On 817.
Rgds,
Ross
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ross Collado
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
RE: Actual table size (Has been posted/discussed before)
Thanks Kirti. I'll give that a go.
RC
-Original Message-
From: Deshpande, Kirti [mailto:[EMAIL PROTECTED]]
Sent: Thursday, 17 October 2002 14:29
To: Multiple recipients of list ORACLE-L
Subject: RE: Actual table size (Has been posted/discussed before)
You can try this:
REM == From Oracle-l posting
set echo off term on feed on pause off verify off
prompt Username to check space for:
col cobjuser noprint new_value uobjuser
set term off feed off
select upper('1') cobjuser from dual;
set term on feed on
prompt Object to check space on ( for uobjuser user ):
col cobjname noprint new_value uobjname
set term off feed off
select upper('2') cobjname from dual;
set term on feed on
prompt Type of object: - (T)able (I)ndex
col cobjtype noprint new_value uobjtype
set term off feed off
select ('3') cobjtype from dual;
set term on feed on
set serverout on size 100
prompt
prompt
prompt
set feed off
declare
op1 number;
op2 number;
op3 number;
op4 number;
op5 number;
op6 number;
op7 number;
objname varchar2(30);
objtype varchar2(10);
objuser varchar2(30);
free_blocks number :=0;
begin
select upper('uobjuser') into objuser from dual;
select
decode(upper('uobjtype'),
'T','TABLE',
'I','INDEX',
NULL
) into objtype from dual;
select upper('uobjname') into objname from dual;
dbms_space.unused_space(objuser,
objname, objtype,
op1,op2,op3,op4,op5,op6,op7);
dbms_output.put_line('schema= ' || objuser);
dbms_output.put_line('object name = ' || objname);
dbms_output.put_line('object type = ' || objtype);
dbms_output.put_line('-');
dbms_output.put_line(' total_blocks = '||op1);
dbms_output.put_line(' total_bytes = '||op2);
dbms_output.put_line(' unused_blocks = '||op3);
dbms_output.put_line(' unused_bytes = '||op4);
dbms_output.put_line(' last_used_extent_file_id = '||op5);
dbms_output.put_line(' last_used_extent_block_id = '||op6);
dbms_output.put_line(' last_used_block = '||op7);
end ;
/
prompt
prompt
prompt
set feed on
undef 1 2
-Original Message-
Sent: Wednesday, October 16, 2002 10:39 PM
To: Multiple recipients of list ORACLE-L
Hi All,
It has previously been discussed in this list regarding the
actual size of a
table. Can someone re-post the SQL to get this?
I've got a table that has been ANALYZED. It has only 1
extent (INITIAL
50M). There's been a lot of deletes and insert on it. I
wanted to know how
much of the 50M has data in it?
On 817.
Rgds,
Ross
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ross Collado
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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: Deshpande, Kirti
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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: Ross Collado
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
Re: How to calculate table size in 8i
CHAN Chor Ling Catherine (CSC) wrote: Hi Gurus, I found an article in metalink 105765.1 How to Determine Approximate Hard Drive Space Needed for a Specific Table. The formula for disk space is simply multiplying the average row length (by analyzing the table) * the number of rows in the table. It's very different from Metalink 10640.1 Extent and Block Space Calculation and Usage in V7 Database where it takes the block header etc in considerations but of course, article 10640.1 is for Version 7. How do you gurus calculate table space in Version 8 ? Please advise. Thanks. Regds, New Bee Catherine, IMHO having a *rough* idea of the size of a table, and more precisely about its rate of growth is more than enough. AFAIK the block structure, for regular, heap organized tables at least, has not changed much since V7 so the V7 recipes still hold. If you have data to analyze, it's probably much easier to have a look at 'blocks', compute how many rows you have per block on average, and then derive the size needed in some distant future. If you have no significant data to talk off, you can roughly consider that the space available in a block is the block size minus 100 to 150 bytes of header, from which you must take PCTFREE off. This gives you a number of bytes ready to store data, which you can divide by an estimate of your row length plus 5 bytes of row overhead to get an approximate number of rows per block. Personally, I don't find this exercise very interesting. You have so many incertainties at all levels (what is the *average* length of this VARCHAR2(500) or number column?), not least the number of lines expected (I have seen estimates wrong by 60%) that I find it safer to label tables 'small', 'medium', 'big', 'huge', have enough disks (do not forget 60% for indexes, rollback segments and comfortable temp space) and then, at least in the beginnings, check whether everything goes as expected. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to calculate table size in 8i
Regarding this question, I like to use the following story... One day, the king ordered that a census be taken. He wanted to know how much money he could expect to collect in taxes. He called together all the dukes and duchesses in the kingdom and ordered them each to count the subjects in their lands. Each of the dukes and duchesses went home and called together all of their barons and baronesses, ordering them to count each subject in their lands. The barons went home and called together all of the counts and countesses, ordering them to count each subject in their lands. ...and so on, and so on, and so on, until the orders arrived at the local village level... In each village, the local constable, bearing orders to count the heads of all subjects in the village, went into the pub. Ordering a beer, each constable wrote down the number "50" on his census form, and handed the form back to his supervisor. Who tabulated the results and returned the results to his superior. And so on... The numbers were tabulated accuratelyand the king had his census. Was the census accurate? After all, the process was accurate and reliable and well thought out and logical... Why is this story relevant? The earlier, very detailed formulas from the Oracle7 note in MetaLink was utterly accurate in how it described how space was allocated in database blocks, but completely irrelevant in actual practice. The reason is that those formulas had at their core a guess: "avg(length(column-name))". In essence, a guess. Therefore, as accurate as the king's census... --- In short, the method in the latter is more accurate. It is extrapolation based on a sample of data, not guesswork. Almost every datatype in Oracle is variable-length (i.e. NUMBER, VARCHAR2, NVARCHAR2, RAW, LONG, LOB), while the DATE, CHAR, and NCHAR datatypes are fixed-length. With variable-length datatypes, formulas just don't work well... Hope this helps... - Original Message - From: "Jack Silvey" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Tuesday, July 09, 2002 11:03 PM Subject: Re: How to calculate table size in 8i New, No guru here, but that is the way that I do it - average row size vs estimated number of rows * fudge factor of 30% plus a little room for underestimation. Also, don't forget room for indexes. I have found this method to be quite successful, since the major hurdle is figuring out how many rows the customers will have. Often, even they don't know for sure, so you have to help them not hurt themselves by being liberal in your space estimations. Remember, if you overestimate, no one will know except you and other dbas, since no one really cares after as long as the system runs well in production. I have had systems that were 50 megs sitting on 50 gig Veritas clusters and everone was happy. Underestimate space, though, and say hello to long periods of data shuffling and constant firefighting. As the old saying goes, "Goofups are forever." hth, Jack --- "CHAN Chor Ling Catherine (CSC)" [EMAIL PROTECTED] wrote: Hi Gurus,I found an article in metalink 105765.1 "How to Determine Approximate Hard Drive Space Needed for a Specific Table". The formula for disk space is simply multiplying the average row length (by analyzing the table) * the number of rows in the table. It's very different from Metalink 10640.1 "Extent and Block Space Calculation and Usage in V7 Database" where it takes the block header etc in considerations but of course, article 10640.1 is for Version 7.How do you gurus calculate table space in Version 8 ? Please advise. Thanks.Regds, New Bee Doc ID /help/usaeng/Search/search.html : Note:105765.1 Content Type: TEXT/PLAIN Specific Table Creation Date: 18-APR-2000 Type: PROBLEM Last Revision Date: 26-DEC-2000 Status: PUBLISHED Problem Description --- How can you determine how much disk space is needed for a table? Solution Description You can use SQL to determine how much space is needed for the table based upon the average row length.1. compute statistics about this table: analyze CEUSER.CE_STATEMENT_LINES compute statistics; Now determine the average row length in bytes: select avg_row_len from dba_tables where table_name=' CE_STATEMENT_LINES'; AVG_ROW_LEN === 98 2. Multiple the average row length in bytes by the number of rows you believe you will need: 98 (bytes) x 1 records = 98 bytes neededReferences -- For more information on the ANALYSE command, you may wish to refer to: Oracle8 SQL Reference Release 8.0, Part No.A58225-01Also: Oracle8i SQL Reference Release 8.1.5
How to calculate table size in 8i
Hi Gurus, I found an article in metalink 105765.1 How to Determine Approximate Hard Drive Space Needed for a Specific Table. The formula for disk space is simply multiplying the average row length (by analyzing the table) * the number of rows in the table. It's very different from Metalink 10640.1 Extent and Block Space Calculation and Usage in V7 Database where it takes the block header etc in considerations but of course, article 10640.1 is for Version 7. How do you gurus calculate table space in Version 8 ? Please advise. Thanks. Regds, New Bee Doc ID /help/usaeng/Search/search.html : Note:105765.1 Content Type: TEXT/PLAIN Specific Table Creation Date: 18-APR-2000 Type: PROBLEM Last Revision Date: 26-DEC-2000 Status: PUBLISHED Problem Description --- How can you determine how much disk space is needed for a table? Solution Description You can use SQL to determine how much space is needed for the table based upon the average row length. 1. compute statistics about this table: analyze CEUSER.CE_STATEMENT_LINES compute statistics; Now determine the average row length in bytes: select avg_row_len from dba_tables where table_name=' CE_STATEMENT_LINES'; AVG_ROW_LEN === 98 2. Multiple the average row length in bytes by the number of rows you believe you will need: 98 (bytes) x 1 records = 98 bytes needed References -- For more information on the ANALYSE command, you may wish to refer to: Oracle8 SQL Reference Release 8.0, Part No.A58225-01 Also: Oracle8i SQL Reference Release 8.1.5, Part Number: A67779-01 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to calculate table size in 8i
New, No guru here, but that is the way that I do it - average row size vs estimated number of rows * fudge factor of 30% plus a little room for underestimation. Also, don't forget room for indexes. I have found this method to be quite successful, since the major hurdle is figuring out how many rows the customers will have. Often, even they don't know for sure, so you have to help them not hurt themselves by being liberal in your space estimations. Remember, if you overestimate, no one will know except you and other dbas, since no one really cares after as long as the system runs well in production. I have had systems that were 50 megs sitting on 50 gig Veritas clusters and everone was happy. Underestimate space, though, and say hello to long periods of data shuffling and constant firefighting. As the old saying goes, Goofups are forever. hth, Jack --- CHAN Chor Ling Catherine (CSC) [EMAIL PROTECTED] wrote: Hi Gurus, I found an article in metalink 105765.1 How to Determine Approximate Hard Drive Space Needed for a Specific Table. The formula for disk space is simply multiplying the average row length (by analyzing the table) * the number of rows in the table. It's very different from Metalink 10640.1 Extent and Block Space Calculation and Usage in V7 Database where it takes the block header etc in considerations but of course, article 10640.1 is for Version 7. How do you gurus calculate table space in Version 8 ? Please advise. Thanks. Regds, New Bee Doc ID /help/usaeng/Search/search.html : Note:105765.1 Content Type: TEXT/PLAIN Specific TableCreation Date: 18-APR-2000 Type: PROBLEM Last Revision Date: 26-DEC-2000 Status: PUBLISHED Problem Description --- How can you determine how much disk space is needed for a table? Solution Description You can use SQL to determine how much space is needed for the table based upon the average row length. 1. compute statistics about this table: analyze CEUSER.CE_STATEMENT_LINES compute statistics; Now determine the average row length in bytes: select avg_row_len from dba_tables where table_name=' CE_STATEMENT_LINES'; AVG_ROW_LEN === 98 2. Multiple the average row length in bytes by the number of rows you believe you will need: 98 (bytes) x 1 records = 98 bytes needed References -- For more information on the ANALYSE command, you may wish to refer to: Oracle8 SQL Reference Release 8.0, Part No.A58225-01 Also: Oracle8i SQL Reference Release 8.1.5, Part Number: A67779-01 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: CHAN Chor Ling Catherine (CSC) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: table size.
Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: table size.
Sorry : table is 8 million rows and I deleted 5 million rows. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, July 08, 2002 12:20 PM Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: table size.
500 mill? How did you do your export? Try compress=n -Original Message- Sent: Monday, July 08, 2002 1:23 PM To: Multiple recipients of list ORACLE-L Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ji, Richard INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: table size.
mitchell wrote: Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell exp and imp do not resize. Wht is allocated stays allocated. Check the manual, ALTER TABLE to see how to deallocate unused storage. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: table size.
When using delete , the highwater mark does not change, so the table still use what was allocated. export/import also does noty resize the table. One way to do it, would be to precreate the table with a smaller size then import the data. --- mitchell [EMAIL PROTECTED] a écrit : Sorry : table is 8 million rows and I deleted 5 million rows. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, July 08, 2002 12:20 PM Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: table size.
Title: RE: table size. Hi Godlwski Thanks for your reply. This is not locally managed tablespace and num_rows are the same. Thefollowing result fromdba_tables. This big different is empty_blocks. I still confused why the size is the same but with much more empty_blocks. I think the empty_block means never used. If whole block data is deleted, what happed to this column? used but emptied? Mitchell 1. exported table TABLE_NAME EMPTY_BLOCKS CHAIN_CNT BLOCKS AVG_SPACE-- -- -- --STATEMENT_LINE 30 0 40401 421 2. imported table TABLE_NAME EMPTY_BLOCKS CHAIN_CNT BLOCKS AVG_SPACE-- -- -- --STATEMENT_LINE 46099 0 35225 421 - Original Message - From: Godlewski, Melissa To: '[EMAIL PROTECTED]' Sent: Monday, July 08, 2002 12:54 PM Subject: RE: table size. Mitchell, Is this table in a Locally Managed Tablespace? If so your extents might be the issue. Check blocks and empty blocks, and num_rows in dba_tables. (after an analyze of course) -Original Message- From: mitchell [mailto:[EMAIL PROTECTED]] Sent: Monday, July 08, 2002 1:28 PM To: Multiple recipients of list ORACLE-L Subject: Re: table size. Sorry : table is 8 million rows and I deleted 5 million rows. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, July 08, 2002 12:20 PM Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: table size.
This is expected behaviour. COMPRESS=Y doesn't make reference to the amount of space occupied by rows, just the amount of blocks allocated to the object as a whole. It's one of the great misunderstood parameters; really it should have been called COALESCE. Richard's suggestion will probably work (depending on your INITIAL storage definition for that table). Alternatively, pre-create the table at the required size before importing the table. Use IGNORE=Y on the import. Also, a quick thought; if you really deleted 500 million rows, leaving 8 million behind it would have far quicker to copy those 8 million out and drop the table. Regards, Mike Hately -Original Message- Sent: 08 July 2002 18:59 To: Multiple recipients of list ORACLE-L 500 mill? How did you do your export? Try compress=n -Original Message- Sent: Monday, July 08, 2002 1:23 PM To: Multiple recipients of list ORACLE-L Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hately Mike INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: table size.
Ji, Richard£¬ÄúºÃ£¡ hi, i think doing it with exp compress=N and It should use less space. If you are in oracle8i+, i think doing : alter table move tablespace new_ts storage(new storage clause) is faster, and then do: alter index index_on_the table rebuild (nologging if possible). Good luck. Good luck! chaos [EMAIL PROTECTED] zhu chao DBA of Eachnet.com 86-021-32174588-667 ÔÚ 2002-07-08 09:58:00 You wrote: 500 mill? How did you do your export? Try compress=n -Original Message- Sent: Monday, July 08, 2002 1:23 PM To: Multiple recipients of list ORACLE-L Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ji, Richard INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: chaos INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: table size.
Dear Mitchell, If iam not mistaken the delete command does not free up the space..but it can be used in the future 4 the other records that r inserted... but incase of Truncate statement the tablesapce is freed of space u can try it out on a temporary table regards, Ams. -Original Message- Sent: Monday, July 08, 2002 9:29 PM To: Multiple recipients of list ORACLE-L Sorry : table is 8 million rows and I deleted 5 million rows. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, July 08, 2002 12:20 PM Hi all I have a table with 8 millions rows and I deleted the 500 million. Then I exported tables (300 mb) and imported into another schema. After that, the table size is still the same. I thought table size should be taken much less space. the storage clause for both table is the same: pct10,pctused 40. any idea. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Amjad INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
How TO KNOW TABLE SIZE
Hi How can I know how much space a table occupied? Can BLOCKS columns in dba_tables helps? Or I have to use dba_extents and dba_segments tables? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
TABLE SIZE?
Hi If I have value num_rows and avg_row_len then can I know the size of tables? Table size(Bytes)=num_rows*avg_row_len Thx -seema _ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How TO KNOW TABLE SIZE
This is what I use. Hope it helps. spool table_space_usage.log set pagesize 0 set linesize 80 select substr (segment_name,1,20) ,bytes / 1024 kbytes ,extents ,blocks from sys.dba_segments where owner = 'LAWSON' and segment_name like 'AR%' and segment_type = 'TABLE' order by segment_name / spool off; Bill Gentry DBA Allina Health System Minneapolis, MN 55403 612-775-1190 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, March 04, 2002 1:08 PM Hi How can I know how much space a table occupied? Can BLOCKS columns in dba_tables helps? Or I have to use dba_extents and dba_segments tables? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Bill Gentry INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How TO KNOW TABLE SIZE
I usually do select sum(bytes) from dba_Segments where segment_name = 'TABLENAME'; HTH --- Seema Singh [EMAIL PROTECTED] wrote: Hi How can I know how much space a table occupied? Can BLOCKS columns in dba_tables helps? Or I have to use dba_extents and dba_segments tables? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = __ Do You Yahoo!? Yahoo! Sports - sign up for Fantasy Baseball http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gene Gurevich INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: TABLE SIZE?
This would be true if and only if you ignored block header space and ignored free space in blocks. What do you want to know The number of blocks under the high water mark? The total size of extents allocated to the table segment? The space allocated within those blocks? The total number of blocks allocated to the table? Any one of these could be answered none of them would be the answer you arrived at. John John [EMAIL PROTECTED] wrote: Hi If I have value num_rows and avg_row_len then can I know the size of tables? Table size(Bytes)=num_rows*avg_row_len Thx -seema _ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How TO KNOW TABLE SIZE
Check this note from metalink. *** Title: Script to compute table size Disclaimer: This script is provided for educational purposes only. It is NOT supported by Oracle Support Services. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors, email packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. Abstract: It is sometimes necessary to calculate the exact size (exact number of bytes) of a table. The function 'VSIZE' gives the exact number of bytes allocated by Oracle for data. For example, if you have a table called 'table1' with n columns say, 'col1', 'col2', , 'coln', execute the following SQL statement to find the exact size of the table: SELECT SUM(VSIZE(col1)) + SUM(VSIZE(col2)) + ... + SUM(VSIZE(coln)) FROM table1; In the scott/tiger schema, the following SQL statement can be executed to find the exact table size of the 'DEPT' table: SQL SELECT SUM(VSIZE(deptno)) + SUM(VSIZE(dname)) + SUM(VSIZE(loc)) FROM dept; SUM(VSIZE(DEPTNO))+SUM(VSIZE(DNAME))+SUM(VSIZE(LOC)) 88 Requirements: None Version Testing: This script was tested on Oracle 7.3.3 to 8.1.6 and SQL*Plus *** ATT Latin America Alex Tomita Koga - ITS e-mail: [EMAIL PROTECTED] phone: (51) 1 610- extension 2619 Av. Larco 1301 Torre Parque Mar - Miraflores -Mensaje original- De: Seema Singh [mailto:[EMAIL PROTECTED]] Enviado el: Lunes, 04 de Marzo de 2002 02:08 p.m. Para: Multiple recipients of list ORACLE-L Asunto: How TO KNOW TABLE SIZE Hi How can I know how much space a table occupied? Can BLOCKS columns in dba_tables helps? Or I have to use dba_extents and dba_segments tables? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tomita Koga, Alex - (Per) INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to find the table size?
To calculate the storage needed for a table (assuming that all rows will contain all of the data) use the following formula. TO DETERMINE THE SIZE OF A TABLE STORAGE step 1. NUMBER OF ROWS IN TABLE. --- step 2. NUMBER OF ROWS IN BLOCK. NUMBER =COLUMN SIZE. 21 (maximum) CHAR = COLUMN SIZE VARCHAR = COLUMN SIZE DATE = 7 Z=3+(SUM OF COLUMN SIZES)+(# OF COLUMNS 250)+(3* # OF COLUMNS 250) X= DBBLK SIZE / Z - Y=DBBLK-(%FREE(DBBLK-(52+4(X))) Y=X*Z ADJ X TO GET TRUE - step 3. TOTAL BLOCKS NEEDED. TOT BLOCKS=ROWS IN TABLE/ROWS PER BLOCK TABLE SIZE =DBBLK SIZE * TOT BLOCKS / 1024 GIVES TABLE SIZE IN K. = I always calculate storage for 2 years of data. ROR mª¿ªm [EMAIL PROTECTED] 06/19/01 03:50PM I need to calculate the size of the table assuming one of the rows is having all the columns maxsize possible. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to find the table size?
I think what he wants to do is find out how big a row could possibly be, for example CREATE TABLE mytable ( column1 VARCHAR2(40), column2 VARCHAR2(30), column3 VARCHAR2 (20)); select table_name, sum(data_length) from user_tab_columns group by table_name; TABLE_NAME SUM(DATA_LENGTH) -- MYTABLE 90 It could be that the avg_row_len is less than this, but he's planning enough storage all variable length data types to be filled. Cheers, g -Original Message- Sent: Tuesday, June 19, 2001 8:51 PM To: Multiple recipients of list ORACLE-L I need to calculate the size of the table assuming one of the rows is having all the columns maxsize possible. I don't understand your question. Do you want to plan in advance to make an estimate of how large a table will be, or are you looking at an existing database and wanting to find out how large the tables are right now? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guy Hammond INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
How to find the table size?
Dear DBA Gurus, Assume I have an emp table under Scott Schema with the following structure. Name Null?Type - - --- EMPNO NOT NULL NUMBER(4) ENAMEVARCHAR2(10) JOBVARCHAR2(9) MGRNUMBER(4) HIREDATE DATE SALNUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) I need to calculate the size of the table assuming one of the rows is having all the columns maxsize possible. Say, 4+10+9+4+7+7+7+2=50. I need to do this for all the tables under a particular schema. How do I do this using Sql or Pl/Sql? Any help in this regard will be very much appreciated. TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: How to find the table size?
I need to calculate the size of the table assuming one of the rows is having all the columns maxsize possible. I don't understand your question. Do you want to plan in advance to make an estimate of how large a table will be, or are you looking at an existing database and wanting to find out how large the tables are right now? - Greg -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Table size
select * from user_segments is a good start.. hth connor --- Viraj Luthra [EMAIL PROTECTED] wrote: Hello all, How do I come to what tables are present in a particular tablespace and then when I found the table, can I come to know what amount of space it consumes? eg. I want to know what tablespace does the table emp (user is scott) belong to and the space it consumes (eg 5M)? Please help. Thanks and Regards, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Table size
Hello all, How do I come to what tables are present in a particular tablespace and then when I found the table, can I come to know what amount of space it consumes? eg. I want to know what tablespace does the table emp (user is scott) belong to and the space it consumes (eg 5M)? Please help. Thanks and Regards, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Table size
hi viraj, here's the method which'll let u know about the tables in a tablespace and thier occupied spaces. u can modify this according to your need by adding whatever more columns and condition u want. select segment_name,tablespace_name, sum(bytes)/1024 KB from dba_segments where segment_type='TABLE' and owner='name' group by segment_name / this will give u tables of a particular user and their sizes. hope it help. saurabh - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 30, 2001 10:45 AM Hello all, How do I come to what tables are present in a particular tablespace and then when I found the table, can I come to know what amount of space it consumes? eg. I want to know what tablespace does the table emp (user is scott) belong to and the space it consumes (eg 5M)? Please help. Thanks and Regards, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Saurabh Sharma INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
