Home » RDBMS Server » Server Administration » How much size of disk Tablespace can consume in proportion to Data in DB? (Oracle 10g)
How much size of disk Tablespace can consume in proportion to Data in DB? [message #563658] Thu, 16 August 2012 06:51 Go to next message
nitinkalra2000
Messages: 4
Registered: August 2012
Location: India
Junior Member
We are using Oracle 10g
and have 10 tablespaces defined for our Database which have 108 tables.
Size of 108 tables is around 251 MB as seen during importing the dump.
While creating these 10 tablespaces I used below parameters for allocation of space

SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;
which set the initial space for 10 tablespaces to around 1032Kb each.

Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563682 is a reply to message #563658] Thu, 16 August 2012 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many things are missing in your posts:
1/ "Size of 108 tables is around 251 MB as seen during importing the dump": what is this size? what does mean "as seen during import"?
2/ How did you export? (program, parameters)
3/ How did you import? (idem)

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563687 is a reply to message #563682] Thu, 16 August 2012 08:47 Go to previous messageGo to next message
nitinkalra2000
Messages: 4
Registered: August 2012
Location: India
Junior Member
Hi Michel

1/ "Size of 108 tables is around 251 MB as seen during importing the dump": what is this size? what does mean "as seen during import"?
:--> Its the diskspace
2/ How did you export? (program, parameters)
:--> expdp name/pwd schemas=ProjectSchema directory=DBDump dumpfile=DBDump.dmp
3/ How did you import? (idem)
:--> impdp.exe name/pwd DIRECTORY=DBdump DUMPFILE=DBDump.dmp
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563694 is a reply to message #563687] Thu, 16 August 2012 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
1/ "Size of 108 tables is around 251 MB as seen during importing the dump": what is this size? what does mean "as seen during import"?
:--> Its the diskspace

Diskspace of what?

Quote:
3/ How did you import? (idem)
:--> impdp.exe name/pwd DIRECTORY=DBdump DUMPFILE=DBDump.dmp

No other parameters?
Copy and paste the actual command.

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563708 is a reply to message #563687] Thu, 16 August 2012 09:52 Go to previous messageGo to next message
nitinkalra2000
Messages: 4
Registered: August 2012
Location: India
Junior Member
nitinkalra2000 wrote on Thu, 16 August 2012 08:47
Hi Michel

1/ "Size of 108 tables is around 251 MB as seen during importing the dump": what is this size? what does mean "as seen during import"?
:--> Its the diskspace

3/ How did you import? (idem)
:--> impdp.exe name/pwd DIRECTORY=DBdump DUMPFILE=DBDump.dmp


1) Diskspace -251MB- its space/memory on harddisk taken by dump file.
and also -251MB- its total space/memory as shown by individual tables during import for ex:
. . imported "name"."CT_SERVICE" 148.2 MB 307 rows
. . imported "name"."AR_TEMP" 29.21 MB 3498 rows
. . imported "name"."EVENT_PROPERTY" 18.59 MB 83070 rows
. . . ...................................................................
. . . ......................................Total 251 MB

2) impdp.exe name/pwd DIRECTORY=DBdump DUMPFILE=DBDump.dmp
:---> yes its the actual command just with change of username/password rest is same.
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563710 is a reply to message #563708] Thu, 16 August 2012 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah, understand! these are spaces of data, not space used by Oracle, in addition to data you have Oracle overhead (column header, row header, block header, extent header, segment header...), space used by pctfree you reserve for futur updates in each table and free space not used in the latest extent.

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563718 is a reply to message #563710] Thu, 16 August 2012 11:00 Go to previous messageGo to next message
nitinkalra2000
Messages: 4
Registered: August 2012
Location: India
Junior Member
So how can relate the spaces used by data and space used by tablespace ?
As space used by data = 251 MB
and
Space used by TableSpaces = 398 MB

Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563719 is a reply to message #563718] Thu, 16 August 2012 11:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Indexes?
Did you consider the indexes on tables?
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563721 is a reply to message #563719] Thu, 16 August 2012 11:03 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563730 is a reply to message #563718] Thu, 16 August 2012 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nitinkalra2000 wrote on Thu, 16 August 2012 18:00
So how can relate the spaces used by data and space used by tablespace ?
As space used by data = 251 MB
and
Space used by TableSpaces = 398 MB


You cannot directly relate them.
What I mean is that you cannot from 251MB knows that the tablespace used space will be 398MB (in addition to Raj's remark about the indexes).

Regards
Michel

[Updated on: Thu, 16 August 2012 12:24]

Report message to a moderator

Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563761 is a reply to message #563730] Thu, 16 August 2012 18:31 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
The total gigabytes = the gigabytes of sort + the gigabytes of undo + the gigabyes of
redo logs + the gigabytes of tables/index + the gigabytes of free space as can be
seen in the following sql.
 
HOST_NAME    INSTANCE_N Total_gig= gig_sort+ gig_undo+ gig_redo+ gig_tables+ GIG_FREE
------------ ---------- ---------- --------- --------- --------- ----------- --------
proddb04     NDOCP1         96.816    23.906    16.000    10.000      39.803    7.107
proddb05     NDOCP2         96.816    23.906    16.000    10.000      39.803    7.107
proddb06     NDOCP3         96.816    23.906    16.000    10.000      39.803    7.107
proddb07     NDOCP4         96.816    23.906    16.000    10.000      39.803    7.107
proddb04     NWEBP1        126.430    20.000    32.000    10.000      44.672   19.758
proddb05     NWEBP2        126.430    20.000    32.000    10.000      44.672   19.758
proddb06     NWEBP3        126.430    20.000    32.000    10.000      44.672   19.758
proddb07     NWEBP4        126.430    20.000    32.000    10.000      44.672   19.758
csprdesbdb01 CSESBP1       101.887     7.813    11.719     1.465      73.011    7.880
csprdesbdb02 CSESBP2       101.887     7.813    11.719     1.465      73.011    7.880
csstgesbdb01 CSESBS1       102.851     8.789    15.625     2.197      63.039   13.201
csstgesbdb02 CSESBS2       102.851     8.789    15.625     2.197      63.039   13.201

The full total_gig.sql follows:
set lines 120
set wrap off
set feedback off
set termout off
drop table ack_sort;
drop table ack_allo;
drop table ack_undo;
drop table ack_free;
drop table ack_redo;
create table ack_sort as select sum(d.bytes)/1024/1024/1024 gig_sort from dba_temp_files d;
create table ack_allo as select sum(d.bytes)/1024/1024/1024 gig_allo from dba_data_files d 
where tablespace_name not like '%UNDO%';
create table ack_undo as select sum(d.bytes)/1024/1024/1024 gig_undo from dba_data_files d 
where tablespace_name like '%UNDO%';
create table ack_free as select sum(f.bytes)/1024/1024/1024 gig_free from dba_free_space f
where tablespace_name not like '%UNDO%';
create table ack_redo as select sum(v$log.bytes)/1024/1024/1024 gig_redo from v$log;
set termout on
col "%_non_tables" for 999.9
column host_name format a12
column instance_name format a10
column gig_free format 999.999
column "gig_sort+" format 999.999
column "gig_undo+" format 999.999
column "Total_gig=" format 999.999
column "gig_redo+" format 999.999
column "gig_tables+" format 999.999
column "gig_allo" format 999.999
column "%_Sort" format 999.9
column "%_Undo" format 999.9
column "%_Redo" format 999.9
select host_name,instance_name,gig_sort+gig_undo+gig_redo+gig_allo "Total_gig=",
gig_sort  "gig_sort+",gig_undo "gig_undo+",gig_redo "gig_redo+",gig_allo-gig_free "gig_tables+",gig_free,
((gig_sort+gig_undo+gig_redo+gig_allo)-(gig_allo-gig_free))/(gig_sort+gig_undo+gig_redo+gig_allo)*100 "%_Non_Tables",
(gig_sort)/(gig_sort+gig_undo+gig_redo+gig_allo)*100 "%_Sort",
(gig_undo)/(gig_sort+gig_undo+gig_redo+gig_allo)*100 "%_Undo",
(gig_redo)/(gig_sort+gig_undo+gig_redo+gig_allo)*100 "%_Redo"
from ack_sort,ack_allo,ack_undo,ack_free,ack_redo,v$instance;
set termout off
drop table ack_sort purge;
drop table ack_allo purge;
drop table ack_undo purge;
drop table ack_free purge;
drop table ack_redo purge;
set feedback on
set termout on
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563777 is a reply to message #563761] Fri, 17 August 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The total gigabytes = the gigabytes of sort + the gigabytes of undo + the gigabyes of
redo logs + the gigabytes of tables/index + the gigabytes of free space as can be
seen in the following sql.


This is NOT the question.

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563784 is a reply to message #563761] Fri, 17 August 2012 02:06 Go to previous messageGo to next message
John Watson
Messages: 8320
Registered: January 2010
Location: Global Village
Senior Member
Hi - one small point, the calculation here
sum(v$log.bytes)/1024/1024/1024 gig_redo from v$log;
needs to be multiplied by v$log.members to take account of multiplexing.

btw, Alan, if you ever feel like publishing all your monitoring scripts on the blog, I'm sure many people would find them helpful (and you would be famous Smile )
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563789 is a reply to message #563784] Fri, 17 August 2012 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, and it will be in a more appropriate place than some which are irrelevant to the topic.

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563919 is a reply to message #563789] Fri, 17 August 2012 19:34 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
How would I go about publishing all monitoring scripts on the blog?
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #563922 is a reply to message #563919] Sat, 18 August 2012 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Go to http://www.orafaq.com/blog/, create your account or log in then click on "create content".

You can also go to http://www.orafaq.com/wiki/Scripts and add your scripts in the appropriate section (for instance, "Database Performance Tuning Scripts"), follow the instructions in the top light blue strip.

Regards
Michel

[Updated on: Sat, 18 August 2012 00:41]

Report message to a moderator

Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #564008 is a reply to message #563922] Mon, 20 August 2012 01:05 Go to previous messageGo to next message
ranadheerb@gmail.com
Messages: 1
Registered: August 2012
Location: hyderabad
Junior Member

how can we find tablespaces full are not?
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #564009 is a reply to message #564008] Mon, 20 August 2012 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query dba_free_space.

Regards
Michel
Re: How much size of disk Tablespace can consume in proportion to Data in DB? [message #564026 is a reply to message #563718] Mon, 20 August 2012 09:41 Go to previous message
tim2boles
Messages: 38
Registered: August 2008
Location: Clarksburg, WV
Member
One thing you may want to consider is reviewing some of the information out there that shows you the relationship between the logical structures that oracle uses (tablespaces, indexes, tables, data block) and the physical structures that the OS uses (disks, blocks, bytes. You also have to remember that fragmentation can occur within a table and within a tablespace depending on how storage is defined and the delete,drop and creation of logical structures.

Here are a couple of links that might help your overall understanding.
http://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm

I know this does not answer your question, but it should help you to understand that it is not a straight forward calculation.

As for calculation of tablespace usage...there are tons of scripts out there. I did a quick search through google and found this page near the top.

http://gavinsoorma.com/2009/07/script-tablespace-free-space-and-fragmentation/

Regards
Tim Boles
Previous Topic: ASM Diskgroup resize
Next Topic: AUDIT on SYS
Goto Forum:
  


Current Time: Wed Aug 12 04:59:48 CDT 2020