fregmentation question

From: Syltrem <syltremspammenot_at_videotron.com>
Date: Wed, 3 Apr 2002 10:48:57 -0500
Message-ID: <HuFq8.6016$a04.30638@tor-nn1.netcom.ca>

Hi

I am doing an import this morning.
First I dropped and re-create the user who owns every object in tablespace FIN_DATA_SMALL then I started the IMPort which recreates all the objects.

Since my datafiles must have been empty when IMP started, I expect to have no fragmentation. Now some tables won;t get created because of ORA-01658.

I checked tablespace fragmentation and indeed it's badly fragmented. How can you explain this?\
Thanks

Below is some info relevant to the issue: Oracle 816

TABLESPACE_NAME                   FILE_ID BYTES/1024 COUNT(BYTES)


------------------------------ ---------- ---------- ------------

FIN_DATA_SMALL                          6       4480            1
                                        6       3680            1
                                        6       3520            2
                                        6       3232            1
                                        6       2720            3
                                        6       2560            1
                                        6       2400            1
                                        6       2240            2
                                        6       2080            4
                                        6       1920            4
                                        6       1760            3
                                        6       1600            4
                                        6       1440           12
                                        6       1280           13
                                        6       1120           10
                                        6       1000            1
                                        6        960           24
                                        6        800           33
                                        6        640           71
                                        6        480          152
                                        6        360            2
                                        6        320          329
                                        6        200            1
                                        6         80            1
                                        6         40            1
                                       13        152            1

select distinct(owner) from DBA_SEGMENTS where tablespace_name='FIN_DATA_SM ALL'; OWNER

---

FINANCE 1 row selected.

FINANCE is the ONLY user having objects there so tablespace must have been empty when IMP started.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE, MIN_EXTLEN from dba_table
spaces where tablespace_name='FIN_DATA_SMALL';

INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE MIN_EXTLEN
-------------- ----------- ------------ ----------

        163840 163840 0 0

1 row selected.

I probably should set MIN_EXTLEN to 163840 but that should not matter now.

SHould I have done a coalesce before the IMP ?

All input appreciated.

--

Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français) To reply to myself directly, remove .spammenot from my address Received on Wed Apr 03 2002 - 09:48:57 CST