Q: Help me understand LMTs please

From: Vince Laurent <vincelaurent_at_REMOVETHISsbcglobal.net>
Date: Thu, 04 May 2006 15:27:58 GMT
Message-ID: <4o6k52t5i2ldube2ih3mmad5ioucb35tr7@4ax.com>

I have a bunch of tables (see below) that have been migrated to LMT from DMT via

exec dbms_space_admin.tablespace_migrate_to_local('tablename');

Why are some USER and some SYSTEM allocation types? MANUAL vs AUTO segment_space_management? Why didn't the above conversion statement make them all the same?

I do plan on doing a clean LMT conversion IF and WHEN I get time/space...

  1  select     tablespace_name Name,
  2     block_size Block,
  3     initial_extent InitExt,
  4     next_extent NextExt,
  5     pct_increase PctInc,
  6     min_extlen MinExt,
  7     extent_management EM,
  8     allocation_type AT,
  9     segment_space_management SSM

 10* from dba_tablespaces

NAME BLOCK INITEXT NEXTEXT PCTINC MINEXT EM AT SSM ----------- ----- -------- -------- ------ ------ ---------- ------- -------

SYSTEM       8192    16384    16384     50      0 DICTIONARY USER    MANUAL
PSAPTEMP     8192  1048576  1048576      0      0 DICTIONARY USER    MANUAL
PSAPSTABI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPSTABD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPBTABI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPBTABD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPPOOLI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPPOOLD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPCLUI     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPCLUD     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDIMI     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDIMD     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPFACTI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPFACTD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPODSI     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPODSD     8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDDICI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDDICD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDOCUI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPDOCUD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPLOADI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPLOADD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPPROTI    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPPROTD    8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPSOURCEI  8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPSOURCED  8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPUSER1I   8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPUSER1D   8192  1048576  1048576      0      0 LOCAL      USER    MANUAL
PSAPEL620I   8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPSTATI    8192    65536                  65536 LOCAL      SYSTEM  AUTO
PSAPES620D   8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPES620I   8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPEL620D   8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPBMCD     8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPUNDO     8192    65536                  65536 LOCAL      SYSTEM  MANUAL
PSAPSTATD    8192    65536                  65536 LOCAL      SYSTEM  AUTO

The last bunch of tables also give an error on our monitoring software (HP IT/Operations) saying there is 2 or less extents available. From what I can see there is no value for NEXT or PCTINC for those tables that gave the error. Is it a valid error? How can you see if there are enough extents?

Here is a more detailed information on a table that is showing the 'error'.

Tablespace Name BYTES_USED BYTES_FREE LARGEST PERCENT_USED
-------------------- ---------- ---------- ---------- ------------

PSAPES620D           2453651456  345505792  344850432        85.92
PSAPES620I           1834999808  262012928  258867200        85.72
PSAPSTATD             209707008  113377280  113377280        45.94
PSAPSTATI               1040384     917504     917504        11.81
PSAPEL620D            733995008  366346240  365821952        50.09

SQL> select   f.tablespace_name
  2          ,sum(floor(nvl(f.bytes,0)/(s.MEXT))) extents
  3  from     sys.dba_free_space   f
  4          ,( select   tablespace_name, max(next_extent) as MEXT
  5             from     sys.dba_segments
  6             group by tablespace_name
  7           ) s

  8 where f.tablespace_name = s.tablespace_name(+)   9 group by f.tablespace_name
 10 /

                     Available
Tablespace Name        Extents


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

PSAPBMCD
PSAPEL620D
PSAPEL620I
PSAPES620D
PSAPES620I
PSAPSTATD
PSAPSTATI
PSAPTEMP

PSAPUNDO                 11978

Thanks in advance...
Vince
p.s. I will GLADLY take any URL pointers to documentation on this.

---

Come race with us!
http://www.mgpmrc.org Received on Thu May 04 2006 - 10:27:58 CDT