Oracle PL/SQL/System Tables Views/DBA EXTENTS

Query DBA_EXTENTS for Extent number in the segment, Starting block number for the extent, Size of the extent in bytes, Size of the extent, in Oracle blocks

 

SQL> select
  6        Extent_ID,         /*Extent number in the segment*/
  7        Block_ID,          /*Starting block number for the extent*/
  8        Bytes,             /*Size of the extent, in bytes*/
  9        Blocks             /*Size of the extent, in Oracle blocks*/
 10   from DBA_EXTENTS
 11  where Segment_Name = "segment_name"
 12  order by Extent_ID;
SQL>
SQL>

Query DBA_EXTENTS for Name of the segment, Type of segment (ex. TABLE, INDEX)

 

SQL> select
  4        Segment_Name,      /*Name of the segment*/
  5        Segment_Type      /*Type of segment (ex. TABLE, INDEX)*/
 10   from DBA_EXTENTS
 11  where Segment_Name = "segment_name"
 12  order by Extent_ID;
SQL>
SQL>

Query DBA_EXTENTS for Tablespace name,Owner of the segment

 

SQL> select
  2        Tablespace_Name,   /*Tablespace name*/
  3        Owner             /*Owner of the segment*/
 10   from DBA_EXTENTS
 11  where Segment_Name = "segment_name"
 12  order by Extent_ID;
SQL>
SQL>

Query to Find Out the File and Block IDs

  
SQL>
SQL> SELECT SEGMENT_NAME,
  2     FILE_ID,
  3     BLOCK_ID
  4     FROM DBA_EXTENTS
  5    WHERE OWNER = "OE"
  6   AND SEGMENT_NAME LIKE "ORDERS%"
  7  /
no rows selected
SQL>
SQL> --