OraFAQ Forum: Server Administration » Understanding segment size

Home » RDBMS Server » Server Administration » Understanding segment size (Linux 2.6.18-92.1.13.el5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Understanding segment size [message #537421]

Wed, 28 December 2011 04:45

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Hello Expert,

I am working to understand the space allocation of table with the value we provided with the data type. For that I have created a table with varchar2 and length 50.

Size of table created is of 65536 Bytes. This is when we don't have any insertion in the table.

Later when we insert some rows, total size if the segment still remain same that is 65536 bytes.

Now again when I created table with varchar2 and length this time is 500 but still it is created with same size that is 65536.

So can you just explain, on what values segment size depends on and how the length effect the size & space allocation.

db_block_size is 8192.

Attachment: varchar2.txt
(Size: 14.39KB, Downloaded 1231 times)

Report message to a moderator

Re: Understanding segment size [message #537427 is a reply to message #537421]

Wed, 28 December 2011 05:23

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Database Concepts, chapter 12:
http://docs.oracle.com/cd/E11882_01/server.112/e25789/logical.htm#i8531
And here's a more comprehensive test for you:

conn / as sysdba
drop user jon cascade;
grant dba to jon identified by jon;
conn jon/jon

create table t1(c1 varchar2(10));

select segment_name,extents,blocks,bytes from user_segments;
select SEGMENT_NAME,EXTENT_ID,blocks,bytes  from user_extents;

insert into t1 (select '0123456789' from dual connect by level < 2000);

select segment_name,extents,blocks,bytes from user_segments;
select SEGMENT_NAME,EXTENT_ID,blocks,bytes  from user_extents;

insert into t1 (select '0123456789' from dual connect by level < 2000);

select segment_name,extents,blocks,bytes from user_segments;
select SEGMENT_NAME,EXTENT_ID,blocks,bytes  from user_extents;

Does that help explain what is going on?

Report message to a moderator

Re: Understanding segment size [message #537430 is a reply to message #537427]

Wed, 28 December 2011 05:40

dba_7722
Messages: 197
Registered: August 2010
Location: Delhi

Senior Member

Thanks John, for sharing the chapter 12 Logical storage. Once done, i will update forum.

Great help.

Report message to a moderator

Previous Topic:	sql plan issue
Next Topic:	archive mode in oracle database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:23:02 CDT 2024