Hello,

I have two issues that i need some opinions.

Issue 1
I was investigating the export command that i have running on the
preprod and prod environment
of Asset Finance and the impact of the Export parameter COMPRESS=Y
on the tables created on the locally managed tablespace

>From what i have read this concept doesn't work for tables in Locally
Managed
Tablespaces. The COMPRESS=Y operation is ignored for segments in
Locally Managed Tablespaces and does not have any impact on the
INITIAL EXTENT of the table being exported. The INITIAL extent will
remain the same as the table's INITIAL extent.

Our tablespaces are all Locally Managed. Now how can i reduce the
number of extents doing again an export and import to a new
environment?

I know that there is a command to change the locally managed
tablespaces to dictionary managed using

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL

If the above command executes successfully then i will export the db
with compress=y
and then change it backup to locally managed using

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

Is there any other way to bypass this problem?

Issue 2
Our production tablespaces have their Segment Space Management set to
Automatic.
Is there a relation with the above setting (to set it to Manual) and
how the DBMS_STATS command behaves?


My database version is 9i Rel 2 and its running on AIX5.2

thanks

1 Compress=Y in dictionary managed tablespaces is one of the main
reasons for space management related incidents. Your tablespace becomes
fragmented.
In a locally managed tablespace using compress=Y doesn't make sense.
Oracle introduced locally managed tablespaces, because dictionary
managed tablespaces are causing all kind of space management related
incidents.
Changing a locally managed tablespace to dictionary managed doesn't
make sense, and is not possible if your SYSTEM tablespace is locally
managed.
In Oracle 10g dictionary managed tablespaces no longer exist.
So I don't know what you are trying to accomplish, except running into
more problems.
I can only hope you don't still believe more than 1 extent is bad. This
is a MYTH.
2 There is no relation. The configuration of the tablespace is
irrelevant to dbms_stats.

Hth
--
Sybrand Bakker
Senior Oracle DBA

First of all thank for the reply. I am trying to find some
documentation related to the number of extents and how it affects the
performance. For example i have a table which has 150 extents. Does it
make any difference in performance if it is 10 extents?!

That the number of extents affects performance is a MYTH. It has been
discussed extensively in this forum.
You won't gain anything by reducing the number of extents to 1. In a
LMT database the storage is ignored or trasnformed anyway.
In a DMT compress=Y results in fragmentation of the free space. Been
there, done that.

--
Sybrand Bakker
Senior Oracle DBA

DMina@laiki.com wrote:
> First of all thank for the reply. I am trying to find some
> documentation related to the number of extents and how it affects the
> performance. For example i have a table which has 150 extents. Does it
> make any difference in performance if it is 10 extents?!

The number of extents for numbers such as you are discussing are
irrelevant. When you get to tens or hundreds of thousands you may
have reason for concern.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)