paparao03@gmail.com

• Oct 27th, 2006

 

the following code will help to u to know the exact values i.e. used, unused, Actually used etc.,

set linesize 121
---------------------------------------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
----------------------------------------------------------------------------------------

column          dummy noprint
column          pct_used        format 999.9                    heading "%|Used"
column          name            format a19                      heading "Tablespace Name"
column          Kbytes          format 999,999,999              heading "Kbytes"
column          used            format 999,999,999              heading "Used"
column          free            format 999,999,999              heading "Free"
column          largest         format 999,999,999              heading "Largest"
column          max_size        format 999,999,999              heading "MaxPoss|Kbytes"
column          pct_max_used    format 999.                     heading "%|Max|Used"
break           on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select (select decode(extent_management,'LOCAL','*',' ') ||
               decode(segment_space_management,'AUTO','a ','m ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/
                          kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(kbytes_max,kbytes_alloc) Max_Size,
       decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024 Kbytes_alloc,
              sum(maxbytes)/1024 Kbytes_max,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/

Imran Khan

• Oct 28th, 2006

 

Hi

can u plz explain me in detail abt the size of tablespace , not the code

but i need a theoritical explanation

im waiting for ur reply

paparao03@gmail.com

• Oct 30th, 2006

 

according to my knowledge concern, that is depend on Organization data size ( by estimating the no of tables and No. of rows in one Year, two year, Three year i.e.

 Step 1 : categorize total Data table you want to create ie. Dynamic, Semi-dynamic, Static. Etc,. -This is you can by observing Data Table behavior in different time spans with in a year.

 

Step: 2:  Estimate the tables an find the Average Row Length (ARL). Using pl/sql library.

 

Step 3: No of rows estimate with in the 1 ,2 and 3 years.

 

Step 4: Calclulate the Total Size (tables x Row Length x No of rows require in 3 years)

 

Step 5: Depending on that we can come to the point to estimate the size of tablespace require.

 

manish hoiyani

• Dec 8th, 2006

 

we can estimate the size of the tablespace from the number of datafiles going to attach with it or you  can use dba_data_files view  to find the size for the particular tablespace....

varinder

• Jan 17th, 2007

 

Size of tablespace means how much space is left in tablespace for insertion of data.there is a concept of high water mark which identifiesd the point after the all blocks belong to tablespace are never used.there are two types of tablespace one is LOCALLY MANAGER and other is DICTIONARY MANAGED. In locally Managed free extends are managed automatically by bitmap.whereas in DICTIONARY managed DBA has to managed the free extends also coalescing is required in DICTIONARY managed tabelespace

ram

• Mar 6th, 2007

 

this command lists all tablespaces with size in the database!

 select owner ,  sum(bytes/1024/1024/1024) from dba_segments group by owner

SIDHIK

• Mar 13th, 2007

 

it depends on how many   .dbf    files u r going to store ,it will be ur size of data
oracle recommends if one .dbf file size exceeds size of 2gb then we have to create a new .dbf etension file . it is the physical file while tablespace is logical storage area containing segments

cmanne

 Profile Answers by cmanne 

• Sep 2nd, 2009

 

select tablespace_name, sum(bytes)/1024/1024
from
dba_data_files
group by tablespace_name
order by tablespace_name;

cmanne

 Profile Answers by cmanne 

• Sep 2nd, 2009

 

During the design phase of database, The tablesapce size is estimated by calculating the size of typical row of various tables that are going to reside in that tablespace multiplied by the number of expected rows inside the table. If any of the index segments defined on the tables are going to reside in the tablespace, one should also take the size of the index segments to estimate the size of the tablespace.