Re: Q: Help me understand LMTs please

Vince Laurent wrote:
> 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

Use of this procedure has been discussed some time ago in this newsgroup:

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/ae48b261966bb643/5a7e4dc921cf024e?q=tablespace_migrate_to_local&rnum=4#5a7e4dc921cf024e

Read the entire thread as it's quite informative.

David Fitzjarrell Received on Thu May 04 2006 - 11:30:03 CDT