Oracle PL/SQL/System Tables Views/DBA EXTENTS

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

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>

 </source>
   
  


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

   <source lang="sql">

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>

 </source>
   
  


Query DBA_EXTENTS for Tablespace name,Owner of the segment

   <source lang="sql">

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>

 </source>
   
  


Query to Find Out the File and Block IDs

   <source lang="sql">
 

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> --

 </source>