View the disk space used by a tenant or table

You can view the disk space occupied by tenants and tables in OceanBase Database through views.

View the disk space occupied by a tenant

1. Log in to the sys tenant of the cluster as the root user.

   The following is a sample connection. Use the appropriate parameters for your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Execute the following statement to view the disk space occupied by the tenant on each node.

   In the sys tenant, the following statement queries the disk space occupied by tenant mysql001 on each node, broken down by component:

   obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_SERVER_SPACE_USAGE WHERE TENANT_NAME='mysql001' ;
   

   The query result is as follows:

   +-----------+-------------+----------------+-------------+------------+------------+-------------+
   | TENANT_ID | TENANT_NAME | SERVER_IP      | SERVER_PORT | SPACE_TYPE | DATA_BYTES | USAGE_BYTES |
   +-----------+-------------+----------------+-------------+------------+------------+-------------+
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | clog Data  | 5898749661 |  5898749661 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Index Data |       2793 |       24576 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Meta Data  |   28184576 |    28184576 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | slog Data  |   26112000 |    26112000 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Table Data |      25268 |     6438912 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Tmp Data   |          0 |           0 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | clog Data  | 3348203904 |  3348203904 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Index Data |       3936 |       32768 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Meta Data  |   25452544 |    25452544 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | slog Data  |   39931904 |    39931904 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Table Data |    8056455 |     8736768 |
   | 1002      | mysql001    |  xx.xx.xx.xx   |        2882 | Tmp Data   |          0 |           0 |
   +-----------+-------------+----------------+-------------+------------+------------+-------------+
   12 rows in set
   

   In the returned result:

   • SPACE_TYPE: the type of data. The following types are supported:

     • Table Data: data of all partitions of all primary tables, including LOB partitions
     • Index Data: data of all partitions of all index tables
     • Meta Data: all metadata
     • Tmp Data: temporary files
     • clog Data: total size of clogs on the current node
     • slog Data: total size of slogs on the current node

   • DATA_BYTES: size of data content, in bytes.

   • USAGE_BYTES: size of disk space actually occupied, in bytes.

View the disk space occupied by a table

1. Log in to the database as the tenant administrator of the sys tenant or a user tenant.

   Note

   The administrator of a MySQL-compatible tenant is the root user, and that of an Oracle-compatible tenant is the SYS user.

   The following is a sample connection. Use the appropriate parameters for your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Execute the following statement to view the disk space occupied by table data.

   • Sys tenant

     In the sys tenant, the following statement queries the disk space occupied by table t1 in tenant mysql001:

     obclient [oceanbase]> SELECT * FROM oceanbase.CDB_OB_TABLE_SPACE_USAGE WHERE TENANT_NAME='mysql001' AND TABLE_NAME='t1';
     

     The query result is as follows:

     +-----------+----------+-------------+---------------+---------------------+-------------+---------------+
     | TENANT_ID | TABLE_ID | TENANT_NAME | DATABASE_NAME | TABLE_NAME          | OCCUPY_SIZE | REQUIRED_SIZE |
     +-----------+----------+-------------+---------------+---------------------+-------------+---------------+
     | 1002      |   500051 | mysql001    | test          | t1                  |        1233 |          8192 |
     +-----------+----------+-------------+---------------+---------------------+-------------+---------------+
     1 row in set
     

   • User tenant

     MySQL-compatible mode

     Oracle-compatible mode

     The following statement is used to view the disk space occupied by tables in the current MySQL-compatible tenant:

     obclient [oceanbase]> SELECT * FROM oceanbase.DBA_OB_TABLE_SPACE_USAGE;
     

     The following statement is used to view the disk space occupied by tables in the current Oracle-compatible tenant:

     obclient [SYS]> SELECT * FROM SYS.DBA_OB_TABLE_SPACE_USAGE;
     

     A sample query result:

     +----------+---------------+--------------------------------+-------------+---------------+
     | TABLE_ID | DATABASE_NAME | TABLE_NAME                     | OCCUPY_SIZE | REQUIRED_SIZE |
     +----------+---------------+--------------------------------+-------------+---------------+
     |   500011 | test          | wide_table_row_storage2        |  4495925120 |    4613763072 |
     |   500012 | test          | wide_table_column_storage2     |  3915023145 |    4195397632 |
     |   500014 | test          | wide_table_row_column_storage2 |  8410964107 |    9805275136 |
     +----------+---------------+--------------------------------+-------------+---------------+
     3 rows in set
     

     In the returned result:

     • OCCUPY_SIZE: size of data after compression when written to disk, in bytes.
     • REQUIRED_SIZE: size of disk space actually occupied by the compressed data, in bytes.

     You can calculate the disk space utilization from the returned results by using OCCUPY_SIZE / REQUIRED_SIZE.