Re: migrating to locally managed tablespaces

"Karen Abgarian" <abvk_at_ureach.com> wrote in message news:3E2B8658.A39A45F_at_ureach.com...
> Interestingly, tomorrow I will have to deal with results of such
> a reorg. There is about million of rows in fet$ and if I were to
> fire drop on a tablespace, it would take about two weeks with
> major impact on the database performance. As result, the source
> tablespace is still there, the monitoring (packaged application which
> checks dba_free_space) is not working, and I probably have to rebuild
> the whole database. Ideas how to deal with this are appreciated,
> by the way.

I'm curious, we had a conversation recently where someone else suggested that free space didn't appear in dba_free_space for LMT's. However it most clearly does for me. This is 9.2 and I have both uniform and autoextent allocation going on.

SQL> select s.tablespace_name,t.segment_space_management,sum(s.bytes)   2 from dba_free_space s,dba_tablespaces t   3 where t.extent_management='LOCAL'
  4 and s.tablespace_name=t.tablespace_name   5 group by s.tablespace_name,t.segment_space_management;

TABLESPACE_NAME                SEGMEN SUM(S.BYTES)
------------------------------ ------ ------------
ODM                            AUTO       11206656
XDB                            AUTO         196608
INDX                           AUTO       26148864
DRSYS                          AUTO       10813440
TOOLS                          AUTO        4128768
USERS                          AUTO        1245184
SYSTEM                         MANUAL      4390912
CWMLITE                        AUTO       11141120
EXAMPLE                        AUTO         131072
LOGGING                        MANUAL    210632704
TEST_LOB                       MANUAL       393216
UNDOTBS1                       MANUAL    158007296

12 rows selected.

> The funny part about it is that the project to move to LMT
> from DMT has already completed and reported as successful.

Did you use the built in dbms package for the job?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************