Purpose
Describes the physical storage allocation of segments owned by the current user. The USER_SEGMENTS view displays the storage allocated for the segments of the objects that are owned by the current user.
Applicability
This view applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| 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. |
| BYTES | NUMBER(38) | NO | Segment size, in bytes. |
| BLOCKS | NUMBER(38) | NO | Segment block size, the number of blocks occupied. |
| 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. |
| 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. The default value is NULL. |
| INMEMORY_PRIORITY | varchar(8) | NO | This column is not supported. The default value is NULL. |
| INMEMORY_DISTRIBUTE | varchar(15) | NO | This column is not supported. The default value is NULL. |
| INMEMORY_DUPLICATE | varchar(13) | NO | This column is not supported. The default value is NULL. |
| INMEMORY_COMPRESSION | varchar(17) | NO | This column is not supported. The default value is NULL. |
| CELLMEMORY | varchar(24) | NO | This column is not supported. The default value is NULL. |
Sample query
Query the physical storage allocation of the T_SUBPART table in the current user's schema.
obclient [SYS]> SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, BYTES, BLOCKS FROM SYS.DBA_SEGMENTS WHERE SEGMENT_NAME='T_SUBPART';
The query result is as follows:
+--------------+----------------+--------------------+---------+--------+
| SEGMENT_NAME | PARTITION_NAME | SEGMENT_TYPE | BYTES | BLOCKS |
+--------------+----------------+--------------------+---------+--------+
| T_SUBPART | P1SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P1SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P1SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P1SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P2SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P2SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P2SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P2SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P3SP0 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P3SP1 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P3SP2 | TABLE SUBPARTITION | 2097152 | 16384 |
| T_SUBPART | P3SP3 | TABLE SUBPARTITION | 2097152 | 16384 |
+--------------+----------------+--------------------+---------+--------+
12 rows in set
References
Query the physical storage allocation of all segments in the current tenant by using DBA_SEGMENTS.