How I know table size on the server

Hi , I have ms access database , I would like to move the data to an existing oracle database , after I create tables in oracle and insert the data from access , how I can know the size of the new tables within the current oracle database
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Size can mean different things in Oracle:
There is allocated space, data size, size also used by indexes, etc...

If I had to guess at what you are after, try this:
select bytes from user_segments where segment_name='SOME_TABLE_NAME';
0
NiceMan331Author Commented:
I will post my table name ?
0
slightwv (䄆 Netminder) Commented:
>>I will post my table name ?

I don't need your table name.  Just use it in the query I posted instead of SOME_TABLE_NAME.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
depending on the data types you may need to look at related segments too

in particular  LOBs and XMLTYPE that are stored in their own segments.

you may also want to include the indexes of the table in order to get a fuller representation of how much space the data takes.

all of these can also be found in the USER_SEGMENTS, ALL_SEGMENTS, and DBA_SEGMENTS views.
0
DavidSenior Oracle Database AdministratorCommented:
One of Oracle's features is a real-time optimizer of SQL statements.  Performance can be impacted when table data is not periodically analyzed.  Two of the metrics kept are average row length, and record count.  The product of these two values provides an approximation that some people use for trendlines.  For example, your new table has 1000 rows, each an average of 1024 bytes.  After some period of time, that could become 10,000 rows and 512 bytes -- depending upon the type of data.

Tables are elements of a logical storage unit, the tablespace.  A tablespace must contain one or more (physical) data files.  More often than not, DBAs monitor the free space available within a tablespace, and add/adjust the data files when more space is anticipated.  HTH.
0
Geert GOracle dbaCommented:
I hope you have an oracle dba ...
He should be able to give you the fine details
0
NiceMan331Author Commented:
Yes I have dba , but I would not to add big size on the server ,  however , let say if the size of tables in access is 1000 kb , is may be will be in oracle in approximately same average ?
0
slightwv (䄆 Netminder) Commented:
>> is may be will be in oracle in approximately same average ?

It should be similar but a LOT depends on how the table was created.  

I think I have suggested in your previous questions that you spend some time in the Concepts guide.  I would again suggest that to get an idea of how Oracle works.

There is "used" space, and "allocated" space.  They can be hugely different.

I can create a table with an initial extent size of 100 Meg:
create table tab1(col1 char(1)) storage(initial 100M);

Right now it occupies no space.

As soon as I insert one row:
insert into tab1 values('a');

and you run the query I posted:
select bytes from user_segments where segment_name='TAB1';

You will see it has allocated 100 Meg for a single letter 'a' in the table.

So given some overhead the actual space "used" is just a few bytes (one for the 'a' and overhead for things like the rowid).  Allocated space is 100 Meg.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
well, your dba really needs to get informed about this
you'll get a lot of valuable input back

whenever someone comes to me for an item like that
> we look in what database (and tablespace and schema) the data should go
starting is usually in TEST, then QA then PROD
> we look at what size it will be
> we look at the grow rate
> we look at the number of users (and concurrent ones)
> we help upload the data (if asked, and recommend our assistance in case of massive amounts)
> we follow up for performance

your dba would also be able to tell you how this is all done in your company
and provide you with guidelines for apps, data, backups, etc, etc ...
0
NiceMan331Author Commented:
thanx for all experts , i may be now more understanding about it
but stil one question ,
slightwv

There is "used" space, and "allocated" space.  They can be hugely different.
ok , this is clear now , but :
1- size of tablespace calculated with which one ?
2-also , when take backup , the size of backup calculated with which one ?
3 -  what is you advice to create a table with minimum size of space to save spaces in the database , in other word , how i can determine what is the suitable size of each field i want ,
4- and which fields types consumes more spaces than others ? varchars , numbers , dates ?
0
sdstuberCommented:
1 -  size of tablespace is allocated and is independent (although at least as large) as the sum of the tables, indexes, lobs and other segments within it.  You can see how big a tablespace is by looking at bytes in dba_data_files or dba_temp_files (depending if it's a permanent or temporary tablespace)

2 - depends on type of backup.  if you backup files, then it's the size of the files, if you backup objects, then it's the used space of the object.  Also you can potentially compress your files too.  If using exports (not "real" backups, but used as such sometimes), it's simply an option on the but does require advanced compression license to use.

3 - size the datatypes according to the business needs and no more.   This is really a business requirement not a technical one

4 - clobs and blobs can potentially be the biggest, then long then varchar2 and char and raw  But it really depends on the contents more than the types. A date is always 7 bytes, so it's bigger than a varchar2(4000) with only 1 character in it, but if you extend that varchar2 to be 8 bytes, then it will be bigger than the date.
0
NiceMan331Author Commented:
thanx for all advice's
i distributed the points as per what i got better understanding
and it doesn't necessary means that one answer is better than the other
thanx again for all
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.