查询租户详情的SQL语句？？

OceanBase 技术问题
#1

有没有一条SQL语句可以把租户的字符集、模式、资源池和资源规格等信息都列出来，就是在下面的图片中再加一列字符集和一列租户模式。

SELECT c.TENANT_ID, e.TENANT_NAME, concat(c.NAME, ': ', d.NAME) pool:conf,concat(c.UNIT_COUNT, ’ unit: ', d.min_cpu, ‘C/’, ROUND(d.MEMORY_SIZE/1024/1024/1024,0), “G”) unit_info
FROM DBA_OB_RESOURCE_POOLS c, DBA_OB_UNIT_CONFIGS d, DBA_OB_TENANTS e
WHERE c.UNIT_CONFIG_ID=d.UNIT_CONFIG_ID AND c.TENANT_ID=e.TENANT_ID AND c.TENANT_ID>1000
ORDER BY c.TENANT_ID;

image
image999×310 12.2 KB

1 个赞
#3

租户模式加一列就行。租户的字符集没找到在哪个视图呢

SELECT
  c.TENANT_ID,
  e.TENANT_NAME,
  e.COMPATIBILITY_MODE,
  concat(c.NAME, ': ', d.NAME) "pool_conf",
  concat(
    c.UNIT_COUNT,
    ' unit: ',
    d.min_cpu,
    'C/',
    ROUND(d.MEMORY_SIZE / 1024 / 1024 / 1024, 0),
    "G"
  ) unit_info
FROM
  DBA_OB_RESOURCE_POOLS c,
  DBA_OB_UNIT_CONFIGS d,
  DBA_OB_TENANTS e
WHERE
  c.UNIT_CONFIG_ID = d.UNIT_CONFIG_ID
  AND c.TENANT_ID = e.TENANT_ID
  AND c.TENANT_ID > 1000
ORDER BY
  c.TENANT_ID;
2 个赞
#4

是的，我也试过了，模式可以查出来，就差字符集了，我看字符集要进到租户里才能查到

1 个赞
#5

这个可以查看字符集和字符序
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = ‘database_name’;

1 个赞
#6

没有集群级视图查询 字符集和字符序，可分别查看租户的查看字符集和字符序再union

1 个赞
#7

select
v.tenant_id,
t.tenant_name,
case when t.compatibility_mode = ‘0’ then ‘MySQL’ else ‘Oracle’ end as tenant_mode,
c.collation,
c.charset
from
cdb_ob_sys_variables v
inner join __all_tenant t on v.tenant_id = t.tenant_id
and t.tenant_name not like ‘META%’
left join __all_collation c on v.value = c.id
where
v.name = ‘collation_server’