Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

Chapter 5 Oracle Tablespace Design


We now have a table named test_frag in a 2-KB tablespace. The next step is to populate 4,000 rows, with only a single spec in the VARCHAR2 column:

myint integer := 1;
  insert into test_frag
    ' '
  myint := myint+1;
  if myint > 4000 then exit; end if;
end loop;

Now that we have the rows inserted, let’s take a look at how many rows are stored on the data block in DBA_TABLES:

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000           9          0

In DBA_SEGMENTS, we see that the table is in a single extent. We also see that we used 32 data block, at 2 KB per block, to store 4,000 rows. This works out to 500 data rows per block.

Table             Tablespace      Buffer                       
name              Name            Pool          Bytes   Blocks  Extents
----------------- --------------- ---------- -------- -------- -----   
TEST_FRAG         ASM_TEST        DEFAULT      65,536       32     1

Now let’s make a mess and expand a large VARCHAR2 column from one byte to 2,000 bytes. After the update, we see in DBA_SEGMENTS that the table is much larger:

Table         Tablespace      Buffer                                 
name          Name            Pool              Bytes   Blocks Extents 
------------- --------------- ---------- ------------ -------- --------
TEST_FRAG     ASM_TEST        DEFAULT       9,437,184    4,608     24 

Now our table is on 4,608 blocks, and the table has taken 24 extents. When we examine DBA_TABLES, we see that the table now has an average row length of 1,378, and every single row has chained!

Table                    % Free   NUM_ROWS AVG_ROW_LEN  CHAIN_CNT
-------------------- ---------- ---------- ----------- ----------
TEST_FRAG                    10       4000        1378       4000

Row chaining is a serious problem for the database administrator (DBA), and it appears that Automatic Space Management is not appropriate for tables where you need to reserve space for large row expansions with PCTFREE.

Got Oracle Scripts?

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 690 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.