Select on dba_extents

[Nikee]

Hello,

The following sql statement is running for a long time. Where as all other statements are running as normal.

Select
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = &P1
and
&P2 between block_id and block_id + blocks -1;

Is this a normal behaviour whenever we try to pull some information on blocks?

Thanks,
Nikee

[marist89]

lmt?

[Nikee]

What is "lmt" stands for?

[gandolf989]

Originally posted by Nikee
What is "lmt" stands for?

He is asking if you are using locally managed tablespaces.

[Axr2]

Listed on NYSE as Lockheed Martin. Oracle calls'em locally managed tablespaces.

[Nikee]

Hello,

Thanks for the clarification.

Only the TEMP and UNDO tablespaces are Locally managed tablespaces. All other tablespaces are DICTIONARY managed tablespaces.

We are running on HP-UX 11.0 64bit OS.
Oracle 9205 64bit.

Please let me know if you need more information.

Thanks,
Nikee

[davey23uk]

why oh why are you still using DMT - change them to LMT's

[marist89]

Originally posted by Nikee

Only the TEMP and UNDO tablespaces are Locally managed tablespaces. All other tablespaces are DICTIONARY managed tablespaces.

In that case, I'd look at the query plan.

[Nikee]

Its an application restriction. Due to this reason we are still running on DMT mode.

Also I heard that Oracle 92 has a bug related to DBA_SEGMENTS.
---------------------------------------------------------
So what I have done is, I ran the CATSPC2.sql

New views are created:
- SYS_DBA_SEGS_2
- DBA_SEGMENTS_2
- USER_SEGMENTS_2
- DBA_EXTENTS_2
- USER_EXTENTS_2
---------------------------------------------------------

and later on I ran the following sql statements:

---------------------------------------------------------
sqlplus /
create or replace synonym sys_dba_segs for sys.sys_dba_segs_2;
create or replace synonym dba_segments for sys.dba_segments_2;
create or replace synonym user_segments for sys.user_segments_2;
create or replace synonym dba_extents for sys.dba_extents_2;
create or replace synonym user_extents for sys.user_extents_2;
exit;

---------------------------------------------------------
Later on I ran the following sql command to see the results (As an example):

Examining Segments that CANNOT Allocate Next Extents
---------------------------------------------------------
SELECT
a.owner as "Owner", a.tablespace_name as "Tspace",
a.segment_name as "Segment", a.next_extent/1024 "Next(k)",
a.pct_increase "%inc" FROM sys.dba_segments a
WHERE
next_extent > (SELECT MAX(b.bytes)
FROM
sys.dba_free_space b
WHERE
a.tablespace_name = b.tablespace_name) ;
---------------------------------------------------------

Still I see the same old story. System is still processing the same statement.

Thanks,
Nikee

[davey23uk]

Originally posted by Nikee
Its an application restriction. Due to this reason we are still running on DMT mode.

Rubbish, they application is blind to them