HELP: Import generates too many space allocation

[Turin]

HEEEELLLLPPPP!

[marist89]

First, create some tablespaces:

SQL> create tablespace dmt datafile 'd:\oracle\oradata\nt817\dmt01.dbf'
  2  size 10M autoextend on next 10M maxsize 1000M
  3  default storage (pctincrease 0);
Tablespace created.

SQL> create tablespace lmt datafile 'd:\oracle\oradata\nt817\lmt01.dbf'
  2  size 10M autoextend on next 10M maxsize 1000M
  3  extent management local;

Tablespace created.

Next, create some tables:

SQL>  create table my_dmt_tab (x number(10),
  2      y number(10), 
  3      z number(10))
  4   tablespace dmt
  5   storage (minextents 129);

Table created.

SQL>  create table my_lmt_tab (x number(10), 
  2      y number(10),
  3      z number(10))
  4   tablespace lmt
  5   storage (minextents 129);

Table created.

Now, you would expect the tables to be the same size, right?

SQL> col segment_name for a20
SQL> col mb for 999999999.00
SQL> select segment_name, sum(bytes/1024/1024) mb
  2  from dba_segments
  3  where segment_name like 'MY%'
  4  group by segment_name
  5  /

SEGMENT_NAME                    MB
-------------------- -------------
MY_DMT_TAB                    5.04
MY_LMT_TAB                  129.00

Hmmm, that's odd. Wonder what's happening here?

  1  select segment_name, blocks, count(*)
  2  from dba_extents
  3  where segment_name like 'MY%'
  4  group by segment_name, blocks
  5* order by segment_name, blocks
SQL> /

SEGMENT_NAME             BLOCKS   COUNT(*)
-------------------- ---------- ----------
MY_DMT_TAB                    5        129
MY_LMT_TAB                  128         49
MY_LMT_TAB                 1024         10

Ah ha. While the DMT tablespace had all 129 extents as the same extent size (because my pctincrease was 0), the AUTOALLOCATE LMT only had 59 extents but some were 1024 blocks and some were 128 blocks.

[mike9]

Hi Turin,

You say that:
"a schema that allocates 50Mb in 8i, now allocates almost 100Mb in 9i"

It may be a stupid question (sorry if it is so), but I was wondering which space exactly you are comparing. Is it the space used by the datafiles, the space used by the extents of a segement or the real amount of used blocks of a segement?

Regards
Mike

[mike9]

If saving disk space is important and you are not updating but only inserting/selecting data from your table you may have a look at the compression option available in 9iR2. If you would use it for your tables I recommand to insert the data by ordering the rows by columns which have only a few distinct values, this will help you to get a better compression ratio.

HTH
Mike

[tamilselvan]

Because of LMT, even small tables that used less space in a DMT might occupy more spaces in LMT.

First test whether you created LMT with correct storage parameters like uniform size etc.
Create a test table and verify in DBA_EXTENTS view.

You have provided a little info. That is why nobody helped you so far.