Select on dba_extents

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

lmt?

What is "lmt" stands for?

Quote:

---

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

---

He is asking if you are using locally managed tablespaces.

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

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

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

Quote:

---

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.

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

Quote:

---

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

Try this

PHP Code:

---

select /*+ use_hash(b) */ a.*
from  ( select /*+ NO_MERGE */
         *
        from dba_segments) a,
      ( select /*+ NO_MERGE */
               tablespace_name , max(bytes) as free_bytes
         from  dba_free_space
        group by tablespace_name ) b
where  a.tablespace_name = b.tablespace_name and
       a.next_extent > b.free_bytes ; 


---

Tamil