Hi
All,
Could somebody help me in
finding the actual size of an oracle table in GB.
TIA,
Rajesh
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
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
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
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
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.
s 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
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
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
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
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
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
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: [
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
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-
> Goule
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 tota
@nordstrom.com> cc:
Sent by: Subject: RE: Table Size
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
|
| cc:
|
| Subject: 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
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
@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
p by b.value
/
"Pillai, Rajesh"
@nordstrom.com> cc:
Sent by: Subject: RE: Table Size
ml-errors
10/15/2003 04:19
PM
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
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.
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
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
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
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
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
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
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
eted
> 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
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
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
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
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
>
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
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
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
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
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://pho
s 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 recipien
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
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
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
> -Se
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
Dear DBA Gurus,
Assume I have an emp table under Scott Schema with the following structure.
Name Null?Type
- -
---
EMPNO NOT NULL NUMBER(4)
ENAME
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 10
> 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
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
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
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
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 r
"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 r
nal 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
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 &
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)
>
59 matches
Mail list logo