• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » find segment name from block in oracle

find segment name from block in oracle

March 22, 2023 by techgoeasy Leave a Comment

Many times, you might need to determine the segment name given the file id and block number. The below query can be used for that

SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = < file> AND
<block> BETWEEN block_id and block_id + blocks - 1;

Example

SELECT segment_name, segment_type FROM dba_extents WHERE file_id = 101 AND 8758758 BETWEEN block_id and block_id + blocks - 1;

You might want to use this query in conjunction with waits events to find the segment name

SELECT event, p1text,p1 “FILE”, p2text,p2 “BLOCK”, p3text,p3 “ID”
FROM gv$session_wait
WHERE event ='gc current request';
or
SELECT event, p1text,p1 “FILE”, p2text,p2 “BLOCK”, p3text,p3 “ID”
FROM gv$session_waitWHERE event ='gc buffer busy acquire';
Or
SELECT event, p1 “FILE”, p2 “BLOCK”, p3 “ID”
FROM v$session_wait
WHERE event = 'buffer busy waits';

Once block number and file id is known, we can find the segment using the below query
SELECT segment_name, segment_type
FROM dba_extents
WHERE file_id = <file id> AND
<block number> BETWEEN block_id and block_id + blocks - 1;

I Hope you like this short post on find segment name from block in oracle

Related Articles

  1. How to find the High Water mark of the Oracle Table
  2. Downtime Reduction during Patching
  3. How to find metadata of any objects in oracle
  4. How to find table whose statistics are locked

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • how to create sql baseline from Sql tuning set in Oracle
  • How to create sql baseline from cursor cache in Oracle
  • how to create sql baseline in oracle 19c
  • Sql plan Management in Oracle
  • SQL Tuning Set in Oracle

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us
x
x