locally managed tablespaces

From: Sathish Balas <sathishudercor_at_home.com>
Date: Tue, 26 Jun 2001 22:35:16 GMT
Message-ID: <E68_6.119407$qc.14697923@news1.rdc1.va.home.com>

CREATE TABLESPACE MID_DATA11
    DATAFILE 'K:\ORACLEDATAFILES\MD03\MID\DATA\DATA11.DBF' SIZE 409600K AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5120K ONLINE PERMANENT; So each extent is 5120K and the block size being 8k . So each extent is 640 blocks .

Now i create a table

SQL> create table emp(empno varchar2(10)) tablespace mid_data11 ;

Table created.
SQL> select segment_name , extent_id , blocks , bytes/1024/1024 "MBytes"   2 from dba_extents
  3 where segment_name='EMP' ;

EMP
         0 640 5

I imported a table from 7.3.4 db called "sub_groups" . It has 40 extents . SQL> r
  1 select segment_name , extent_id , blocks , bytes/1024/1024 "MBytes"   2 from dba_extents
  3* where segment_name='SUB_GROUPS'

SUB_GROUPS
         0 640 5

( Truncated to make it shorter )

SUB_GROUPS
        39 640 5

40 rows selected.

SQL> analyze table sub_groups compute statistics ;

Table analyzed.

Elapsed: 00:00:00.61
SQL> select blocks , empty_blocks from user_tables   2 where table_name='SUB_GROUPS';

        15 25584

Elapsed: 00:00:00.70

My question is why am i seeing so many empty blocks . When i did the import if the data could fit into 15 blocks then all it would need is one extent . why did it create 40 extents ? where am i conceptually wrong ? where else do i have to look ?

Sathish Balas
Oracle Certified Professional

Desk         : 804-274-3249
Pager       : 804-905-3249
Fax          : 804-274-4648

Text Page : 8042787844.0064616_at_pagenet.net Received on Tue Jun 26 2001 - 17:35:16 CDT