Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba extents

Join dba_extents and dba_rollback_segs

SQL>
SQL> SELECT b.segment_name, status, sum(bytes) sum_bytes
  2  FROM   dba_extents a, dba_rollback_segs b
  3  WHERE  a.segment_name = b.segment_name
  4  and    rownum < 50
  5  AND    a.segment_type = "ROLLBACK"
  6  GROUP BY b.segment_name, status
  7  ORDER BY status DESC, b.segment_name;
SEGMENT_NAME                   STATUS            SUM_BYTES
------------------------------ ---------------- ----------
SYSTEM                         ONLINE               393216
SQL>
SQL>

Log dba_extents

SQL>
SQL> CREATE TABLE extents_log
  2  (log_date            DATE,
  3  segment_name         VARCHAR2(81),
  4  segment_type         VARCHAR2(17),
  5  count_extents        NUMBER,
  6  bytes_extents        NUMBER);
Table created.
SQL>
SQL>
SQL>    INSERT INTO extents_log
  2     SELECT TRUNC(SYSDATE), segment_name, segment_type,
  3            COUNT(*), SUM(bytes)
  4     FROM   dba_extents
  5     WHERE  segment_name IN
  6            ("COL$", "DEPENDENCY$", "ERROR$", "IDL_CHAR$",
  7             "IDL_SB4$", "IDL_UB1$", "IDL_UB2$", "LINK$", "OBJ$",
  8             "OBJAUTH$", "SOURCE$", "SYSAUTH$", "TRIGGER$",
  9             "TRIGGERCOL$", "USER$")
 10     GROUP BY    segment_name, segment_type;
13 rows created.
SQL>
SQL>
SQL> SELECT     segment_name, log_date, segment_type,
  2             count_extents, bytes_extents
  3  FROM       extents_log
  4  ORDER BY   segment_name, log_date;
SEGMENT_NAME    LOG_DATE  SEGMENT_TYPE      COUNT_EXTENTS BYTES_EXTENTS
--------------- --------- ----------------- ------------- -------------
DEPENDENCY$     25-JUL-08 TABLE                        17       2097152
ERROR$          25-JUL-08 TABLE                         2        131072
IDL_CHAR$       25-JUL-08 TABLE                        17       2097152
IDL_SB4$        25-JUL-08 TABLE                        18       3145728
IDL_UB1$        25-JUL-08 TABLE                        46      32505856
IDL_UB2$        25-JUL-08 TABLE                        28      13631488
LINK$           25-JUL-08 TABLE                         1         65536
OBJ$            25-JUL-08 TABLE                        17       2097152
OBJAUTH$        25-JUL-08 TABLE                         2        131072
SOURCE$         25-JUL-08 TABLE                        81      83886080
SYSAUTH$        25-JUL-08 TABLE                         1         65536
SEGMENT_NAME    LOG_DATE  SEGMENT_TYPE      COUNT_EXTENTS BYTES_EXTENTS
--------------- --------- ----------------- ------------- -------------
TRIGGER$        25-JUL-08 TABLE                         4        262144
TRIGGERCOL$     25-JUL-08 TABLE                         1         65536
13 rows selected.
SQL>
SQL>
SQL> drop table extents_log;
Table dropped.
SQL>
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

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

SQL>
SQL> COLUMN    segment_name FORMAT a15
SQL> SELECT    segment_name, segment_type, COUNT(*), SUM(bytes)
  2  FROM      dba_extents
  3  WHERE     segment_name IN
  4            ("COL$", "DEPENDENCY$", "ERROR$", "IDL_CHAR$",
  5             "IDL_SB4$", "IDL_UB1$", "IDL_UB2$", "LINK$", "OBJ$",
  6             "OBJAUTH$", "SOURCE$", "SYSAUTH$", "TRIGGER$",
  7             "TRIGGERCOL$", "USER$")
  8             and rownum < 50
  9  GROUP BY  segment_name, segment_type
 10  ORDER BY  segment_type, segment_name;
SEGMENT_NAME    SEGMENT_TYPE         COUNT(*) SUM(BYTES)
--------------- ------------------ ---------- ----------
OBJ$            TABLE                      17    2097152
OBJAUTH$        TABLE                       2     131072
SOURCE$         TABLE                      30   15728640
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>