www.dbatoolz.com › ORACLE DBA Forums › Ask DBA › Question Id: 4071 | Permalink

tablespace creation

Question ID: 4071
Created By:	2010-JUL-19 00:54:46 [Swathigo]
Updated By:	2010-JUL-19 16:08:54 [Vitaliy]
Status:	Open
Severity:	Normal
Read Only:	No

11598

2010-JUL-19 00:54:46

Swathigo

User

Registered On: Feb 2010

Total Posts: 25

Hi

While creating tablespace, we give autoextend on next and maxsize unlimited.
If we dont give this clause of maxsize unlimited, what will be impact of it on 
datafile size?upto what size will it grow?
Also autoextend on next also, if we do not give, how will datafile grow??

Regards
Swathi

11609

2010-JUL-19 16:08:54

Vitaliy

Moderator

Registered On: Mar 2006

Total Posts: 289

This is platform specific and an easy way to find out is by using this script:

- - - - - - - - - - - - - - - - CUT HERE - - - - - - - - - - - - - - - - - - - -
clear computes
clear breaks

set lines 132
set trims on

break on tabsp_name skip 1
col file_name	format a40		heading "Data File Name"
col tabsp_name	format a15		heading "Tablespace Name"
col max_size	format 999,999.99	heading "Max|Size MB"
col inc_by	format 999.99 		heading "Inc|By MB"
col unallocated	format 999,999.99	heading "Unalloc|MB"
col cur_size	format 999,999.99	heading "Current|Size MB"
compute sum of max_size cur_size unallocated on tabsp_name


select  /*+ ORDERED */
        tn.name                                            tabsp_name
,       ddf.file_name                                      file_name
,       ddf.bytes/1024/1024                                cur_size
,       decode(fex.maxextend,
                NULL,ddf.bytes/1024/1024
                    ,fex.maxextend*tn.blocksize/1024/1024) max_size
,       nvl(fex.maxextend,0)*tn.blocksize/1024/1024 -
        decode(fex.maxextend,NULL,0,ddf.bytes/1024/1024)   unallocated
,       nvl(fex.inc,0)*tn.blocksize/1024/1024              inc_by
--from  sys.v_$dbfile   fn
from    dba_data_files  ddf
,       sys.ts$         tn
,       sys.filext$     fex
,       sys.file$       ft
where   ddf.file_id = ft.file#
and     ddf.file_id = fex.file#(+)
and     tn.ts# = ft.ts#
and     ddf.tablespace_name = UPPER('&enter_ts_name')
order by tn.name
/

- - - - - - - - - - - - - - - - CUT HERE - - - - - - - - - - - - - - - - - - - -


##
### Example:
##
                                                             Current         Max     Unalloc     Inc
Tablespace Name Data File Name                               Size MB     Size MB          MB   By MB
--------------- ---------------------------------------- ----------- ----------- ----------- -------
MTT_DATA        /u03/oradata/HJPRD/mtt_data01.dbf             101.00   32,767.98   32,666.98    1.25
                /u03/oradata/HJPRD/mtt_data04.dbf             101.00   32,767.98   32,666.98    1.25
                /u03/oradata/HJPRD/mtt_data03.dbf             101.00   32,767.98   32,666.98    1.25
                /u03/oradata/HJPRD/mtt_data02.dbf             101.00   32,767.98   32,666.98    1.25
***************                                          ----------- ----------- -----------
sum                                                           404.00  131,071.94  130,667.94

Permalink