Purpose
The DBA_SEGMENTS view displays the physical storage allocated for all segments in the current tenant.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OWNER | VARCHAR2(128) | NO | The owner of the segment. |
| SEGMENT_NAME | VARCHAR2(128) | NO | The name of the segment if any. |
| PARTITION_NAME | VARCHAR2(128) | NO | The name of the partition. For non-partitioned objects, the value of this column is NULL. |
| SEGMENT_TYPE | VARCHAR2(18) | NO | The type of the segment. |
| SEGMENT_SUBTYPE | VARCHAR2(10) | NO | At present, this column is not supported and its value is NULL by default. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | At present, this column is not supported and its value is NULL by default. |
| HEADER_FILE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| HEADER_BLOCK | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| BYTES | NUMBER(38) | NO | The size of the segment in bytes. |
| BLOCKS | NUMBER(38) | NO | The number of blocks occupied by the segment block. |
| EXTENTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| INITIAL_EXTENT | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| NEXT_EXTENT | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MIN_EXTENTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MAX_EXTENTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| MAX_SIZE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| RETENTION | VARCHAR2(7) | NO | At present, this column is not supported and its value is NULL by default. |
| MINRETENTION | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| PCT_INCREASE | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| FREELISTS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| FREELIST_GROUPS | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| RELATIVE_FNO | NUMBER | NO | At present, this column is not supported and its value is NULL by default. |
| BUFFER_POOL | VARCHAR2(7) | NO | The buffer pool for segment blocks. |
| FLASH_CACHE | VARCHAR2(7) | NO | The database smart flash cache hint for segment blocks. |
| CELL_FLASH_CACHE | VARCHAR2(7) | NO | The cell flash cache hint for segment blocks. |
| INMEMORY | varchar(8) | NO | This column is not supported and is currently NULL. |
| INMEMORY_PRIORITY | varchar(8) | NO | This column is not supported and is currently NULL. |
| INMEMORY_DISTRIBUTE | varchar(15) | NO | This column is not supported and is currently NULL. |
| INMEMORY_DUPLICATE | varchar(13) | NO | This column is not supported and is currently NULL. |
| INMEMORY_COMPRESSION | varchar(17) | NO | This column is not supported and is currently NULL. |
| CELLMEMORY | varchar(24) | NO | This column is not supported and is currently NULL. |
Sample query
Query the physical storage allocation of the T_SUBPART table in the current tenant.
obclient [SYS]> SELECT OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, BYTES, BLOCKS FROM SYS.DBA_SEGMENTS WHERE SEGMENT_NAME='T_SUBPART';
The query result is as follows:
+-------+--------------+----------------+--------------------+---------+--------+
| OWNER | SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE | BYTES | BLOCKS |
+-------+--------------+----------------+--------------------+---------+--------+
| SYS | T_SUBPART | P1SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P1SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P1SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P1SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P2SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P2SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P2SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P2SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P3SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P3SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P3SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| SYS | T_SUBPART | P3SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
+-------+--------------+----------------+--------------------+---------+--------+
12 rows in set
References
Query USER_SEGMENTS to view the physical storage allocation of segments owned by the current user.