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.

   Note that you must specify the appropriate parameters in the following example command based on your actual database configurations.

   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.

   For example, in the sys tenant, the following query retrieves the disk space usage by the mysql001 tenant on each node, broken down by component types:

   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 result, the columns are described as follows:

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

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

   • DATA_BYTES: the size of data content, in bytes.

   • USAGE_BYTES: the 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 tenant is the root user, and that of an Oracle tenant is the SYS user.
   • To view objects in the recycle bin, log in to the database as the `sys` user.

   Note that you must specify the appropriate parameters in the following example command based on your actual database configurations.

   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.

   • In the sys tenant

     The following statement is used to view the disk space occupied by table t1 in the mysql001 tenant in the sys tenant.

     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
     

   • In a user tenant

     MySQL mode

     Oracle mode

     The following statement is used to view the disk space occupied by tables in a MySQL 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 an Oracle tenant.

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

     A sample query result is as follows:

     +----------+---------------+--------------------------------+-------------+---------------+
     | 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 result, the columns are described as follows:

     • OCCUPY_SIZE: the size of data that is compressed and written to disk, in bytes.
     • REQUIRED_SIZE: the size of disk space actually occupied by the data, in bytes.

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