Home » SQL & PL/SQL » SQL & PL/SQL » sys.dba_segments and LMT
icon5.gif  sys.dba_segments and LMT [message #598523] Tue, 15 October 2013 11:49 Go to next message
srobins
Messages: 3
Registered: October 2013
Location: Houston TX
Junior Member
Can someone tell me what information is invalid/valid within sys.dba_segments when using LMT?

Thank you

Steve
Re: sys.dba_segments and LMT [message #598524 is a reply to message #598523] Tue, 15 October 2013 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Can someone tell me what information is invalid/valid within sys.dba_segments when using LMT?
HUH?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sys.dba_segments and LMT [message #598528 is a reply to message #598524] Tue, 15 October 2013 12:06 Go to previous messageGo to next message
srobins
Messages: 3
Registered: October 2013
Location: Houston TX
Junior Member

I have been told that when using LMT the information, such as next_extent, within sys.dba_segments is not reliable.
Was looking for some assistance.
Re: sys.dba_segments and LMT [message #598529 is a reply to message #598523] Tue, 15 October 2013 12:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Why would Oracle show you any invalid information at all? What purpose would be solved by doing so?

DBA_SEGMENTS

srobins wrote on Tue, 15 October 2013 22:36

I have been told that when using LMT the information, such as next_extent, within sys.dba_segments is not reliable.


Told by whom? Oracle does not say that.

[Updated on: Thu, 06 March 2014 07:07] by Moderator

Report message to a moderator

Re: sys.dba_segments and LMT [message #598534 is a reply to message #598528] Tue, 15 October 2013 12:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have been told that when using LMT the information, such as next_extent, within sys.dba_segments is not reliable.
How will you act differently if NEXT_EXTENT is reliable or not?

SQL> select next_extent, count(*) from dba_segments group by next_extent;

NEXT_EXTENT   COUNT(*)
----------- ----------
      16384         15
      24576          1
      57344          1
    2097152          2
     106496         37
       8192          5
    1048576       7783
     204800          4
      65536         23


what difference does the value of NEXT_EXTENT mean to you?
Re: sys.dba_segments and LMT [message #598539 is a reply to message #598528] Tue, 15 October 2013 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
srobins wrote on Tue, 15 October 2013 19:06

I have been told that when using LMT the information, such as next_extent, within sys.dba_segments is not reliable.
Was looking for some assistance.


Because it is meaningful ONLY for DMT.


Re: sys.dba_segments and LMT [message #598541 is a reply to message #598528] Tue, 15 October 2013 12:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
This OraFAQ blog might help you in better understanding :

Locally vs. Dictionary Managed Tablespaces
Re: sys.dba_segments and LMT [message #598547 is a reply to message #598541] Tue, 15 October 2013 13:18 Go to previous messageGo to next message
srobins
Messages: 3
Registered: October 2013
Location: Houston TX
Junior Member
Thanks to everyone but we had an issue where we ran out of 64m long extents on an LMT, because the segment_size was 1g or greater. It is my understanding that when using LMT the next extent size is based on the segment size.

Such as

Segment Size Next Extent Size
less than 1m 64k
1m to 63m 1m
64m to 1023m 8m
1g or more 64m

So I am making sq1 calls to look for this possibility so this does not happen again.
So for reporting, I am looking to find out what data within sys.dba_segments is valid for my report so I would know what data not to include.
I wanted to report the following
BYTES, MAX_EXTENTS, EXTENTS, INITIAL_EXTENT, NEXT_EXTENT.

Re: sys.dba_segments and LMT [message #598551 is a reply to message #598547] Tue, 15 October 2013 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
It is my understanding that when using LMT the next extent size is based on the segment size.


It depends on the type of LMT.
For SYSTEM/AUTOALLOCATE ones, I put the formula there

I put scripts there and there

Re: sys.dba_segments and LMT [message #598553 is a reply to message #598551] Tue, 15 October 2013 13:35 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel, that's a really good information.

For the scripts part, not everybody would be able to follow, since, it will need dba-village login credentials. Request you to paste it here too, people browsing OraFAQ would be benefited too.

Regards,
Lalit
Re: sys.dba_segments and LMT [message #598564 is a reply to message #598553] Tue, 15 October 2013 14:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Everyone can register to DBA-Village as he can do it in OraFAQ.
Register form is there.

Previous Topic: Selectim with rownum=1 taking time
Next Topic: Connect by to Get Parent and Supervisor Details in Oracle
Goto Forum:
  


Current Time: Fri Mar 29 03:10:36 CDT 2024