OceanBase 中常用的查询语句

本文汇总整理了一些 OceanBase 中的常用查询语句，包括租户创建、转储与合并、表相关等场景，希望帮大家解决日常运维操作中的常见的问题。

租户类

• OceanBase支持多租户架构，其中默认存在一个名为sys的租户。为了满足业务使用需求，我们通常需自行创建一个新的租户。创建完整租户的过程遵循一定的顺序：首先创建unit，接着配置resource pool，最后创建tenant。然而，在初始创建租户阶段，可能会遇到的一个常见问题就是资源不足的错误。因此，在着手创建之前，需要先确认资源的可用性情况。

SELECT a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free,

mem_total/1024/1024/1024 mem_total_gb,

mem_assigned/1024/1024/1024 mem_assign_gb,

(mem_total-mem_assigned)/1024/1024/1024 mem_free_gb

FROM __all_virtual_server_stat a

JOIN __all_server b ON (a.svr_ip=b.svr_ip

AND a.svr_port=b.svr_port)

ORDER BY a.zone,

a.svr_ip ;

// 4.0之后:

SELECT SVR_IP ,

SVR_PORT ,

ZONE ,

SQL_PORT ,

CPU_CAPACITY ,

CPU_CAPACITY_MAX ,

CPU_ASSIGNED ,

CPU_ASSIGNED_MAX ,

MEM_CAPACITY/1024/1024/1024 as MEM_CAPACITY_GB ,

MEM_ASSIGNED/1024/1024/1024 as MEM_ASSIGNED_GB,

LOG_DISK_CAPACITY/1024/1024/1024 as LOG_DISK_CAPACITY_GB ,

LOG_DISK_ASSIGNED/1024/1024/1024 as LOG_DISK_ASSIGNED_GB ,

LOG_DISK_IN_USE/1024/1024/1024 as LOG_DISK_IN_USE_GB ,

DATA_DISK_CAPACITY/1024/1024/1024 as DATA_DISK_CAPACITY_GB ,

DATA_DISK_IN_USE/1024/1024/1024 as DATA_DISK_IN_USE_GB,

DATA_DISK_HEALTH_STATUS ,

MEMORY_LIMIT/1024/1024/1024 as MEMORY_LIMIT_GB

FROM GV$OB_SERVERS;

//结果如下面看到 cpu、mem、disk 可使用最大资源和已使用情况，后面创建租户时就知道最大能使用的资源了。:

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

| SVR_IP | SVR_PORT | ZONE | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY_GB | MEM_ASSIGNED_GB | LOG_DISK_CAPACITY_GB | LOG_DISK_ASSIGNED_GB | LOG_DISK_IN_USE_GB | DATA_DISK_CAPACITY_GB | DATA_DISK_IN_USE_GB | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT_GB |

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

| 10.140.118.7 | 2882 | zone3 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |

| 10.140.114.12 | 2882 | zone1 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 19.990234375000 | 1.271484375000 | NORMAL | 10.000000000000 |

| 10.140.60.14 | 2882 | zone2 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

3 rows in set (0.005 sec)

```

• 创建Unit

// 创建Unit

MySQL [oceanbase]> create resource unit S2 max_cpu=2, min_cpu=2, max_memory='4G', min_memory='2G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='50G';

Query OK, 0 rows affected (0.009 sec)

//4.0之后:

obclient