Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hariharan.

Asked: January 12, 2017 - 2:49 pm UTC

Last updated: April 11, 2018 - 4:30 pm UTC

Version: 12.1.0.1

Viewed 1000+ times

You Asked

Hi All,


Please clarify below doubt, If I use NEXT 10M and UNIFIOM SIZE 1M, which size will my next extent will have 1M or 10M?

if we use this command:
create TABLESPACE ts_cvliste11_20180815 datafile '/ora1/app/oracle/oradata/L11/2018/ts_cvliste11_20180815.DBF' size 400M
autoextend on NEXT 10M MAXSIZE 30000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO;



Thanks in advance,


and Chris said...

The docs are clear to me on this one:

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. The default is the size of one data block.

http://docs.oracle.com/database/121/SQLRF/clauses004.htm#CJADEBBF

So "next 10M" means make the data file 10M bigger when you need more extents. It's easy to verify this yourself.

Create a tablespace with a 2M data file, next 10M and uniform 1M extents:

create tablespace tblsp datafile 'tblsp.dbf' size 2m autoextend on next 10m 
extent management local uniform size 1m segment space management auto;

select bytes/1024/1024 from dba_data_files
where  tablespace_name = 'TBLSP';

BYTES/1024/1024  
2


Create a table in it that's slightly larger than 2M:

create table t tablespace tblsp as
  select rownum x, lpad('x', 4000, 'x') y
  from   dual connect by level <= 300;

select bytes/1024/1024 from user_segments
where  segment_name = 'T';

BYTES/1024/1024  
3


And you can see the data file has grown by 10M:

select bytes/1024/1024 from dba_data_files
where  tablespace_name = 'TBLSP';

BYTES/1024/1024  
12

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hariharan Senthil pandiyan, January 12, 2017 - 4:47 pm UTC

Thanks for your Update Will check .


Some

Alex, April 11, 2018 - 2:15 pm UTC

How would you measure that this query
select rownum x, lpad('x', 4000, 'x') y
from dual connect by level <= 300;
returns slightly over 2MB of data?

My ORCL database is NLS_LENGTHT_SEMANTICS=Byte and NLS_CHARACTERSET AL32UTF8.
There are 300 records here from the above query, 2 columns.
When I run:
select x, vsize(x), dump(x), y, vsize(y), dump(y)
from t
order by x;
I conclude then, I have from the second, y, column 300 records*4000 bytes = 1 200 000 bytes.
From the first column, x, which is a number, I have 101 records*2 bytes + 199 records * 3 bytes.
That gives me 1.145MB. Where is my mistake?

Chris Saxon
April 11, 2018 - 4:30 pm UTC

Remember there are various overheads: each block has its own header + table and row directory. Then there's the row header too. And the default pctfree reserves 10% of the space in each block for future growth.

And in this instance, each row is nearly half a block. So by the time you add in these overheads, you only get one row in each block.

Also *segments reports the allocated space. Some of this may be unused.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.