Re: locally managed tablespaces

This may be down to the way your export, or the original table, has been defined. For example, you may have used compress=y on export, which results in the storage definition for the table import being set to the sum of the 40 existing extents.

If you want to see the text that was used to create the table on import, use the 'show = y' feature

e.g.
imp jpl/jpl show=y log=implog tables=(emp)

this will dump the SQL without attempting to load the database.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Sathish Balas wrote in message ...


>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

>

>

>