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 F"To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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" @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. Copyin
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" @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).
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, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
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 = ; SUM(BYTES)/(1024*1024*1024) --- 18 select partition_name, sum(bytes)/(1024*1024*1024) from dba_segments where segment_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).
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(),1)+nvl(vsize(),1)+..
)/1073741824
from ;
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
Then the easy answer is: select sum(nvl(vsize(),1)+nvl(vsize(),1)+.. )/1073741824 from ; 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, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
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" @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
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('') AND SEGMENT_NAME = UPPER('');
-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
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
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, 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]
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
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
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
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, 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]
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, Could somebody 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
Try:
SELECT segment_type,
segment_name,BLOCKS*8192/1024 "Kb"
FROM DBA_SEGMENTS
WHERE OWNER=UPPER('') AND SEGMENT_NAME =
UPPER('');
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,
Could somebody 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, Could somebody help me in finding the actual size of an oracle table in GB. TIA, Rajesh
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: 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).
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
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).
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.
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).
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.
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: table size.
Title: RE: table size. Hi Godlwski Thanks for your reply. This is not locally managed tablespace and num_rows are the same. The following result from dba_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.
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.
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.
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.
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.
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 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: 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).
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).
