如何查询OceanBase的数据字典或VIRTUAL TABLES？
在oracle数据库中有dictionary(dict)和v$fixed_table，可以查询数据字典表和动态性能视图v$相关的系统内部对象，查询数据库内部对象时如果不记的完整名称，是可以直接从两个”根”对象记录中查找(如TanelPoder‘s d.sql) , 作为初学OceanBase的DBA，我也希望可以找到OB数据库中自带了哪些数据字典或view？或OB中兼容了oracle哪些view数据来源是哪里？如同在oracle中查询V$FIXED_VIEW_DEFINITION.
OceanBase数据库中数据字典可以在SYS租户下，查询”oceanbase”数据库__all_virtual_table 。 老白团队也分享过发现了OB这些表的藏身之处information_schema.tables:
select table_name from information_schema.tables where table_name like '__all_virtual%' order by 1;
该对象有一些约定规则。
# OB use disjoint table_id ranges to define different kinds of tables: # - (0, 100) : Core Table # - (0, 10000) : System Table # - (10000, 15000) : MySQL Virtual Table # - (15000, 20000) : Oracle Virtual Table # - (20000, 25000) : MySQL System View # - (25000, 30000) : Oracle System View # - (50000, 60000) : Lob meta table # - (60000, 70000) : Lob piece table # - (100000, 200000) : System table Index # - (15305, <20000) : Oracle Real Agent table Index # - (500000, ~) : User Table # # Here are some table_name definition principles. # 1. Be defined by simple present tense, first person. # 2. Be active and singular. # 3. System table's table_name should be started with '__all_'. # 4. Virtual table's table_name should be started with '__all_virtual' or '__tenant_virtual'. # Virtual table started with '__all_virtual' can be directly queried by SQL. # Virtual table started with '__tenant_virtual' is used for special cmd(such as show cmd), which can't be queried by SQL. # 5. System view's table_name should be referred from MySQL/Oracle. # 6. Definition of Oracle Virtual Table/Oracle System View can be referred from document: # # 7. Difference between REAL_AGENT and SYS_AGENT: # sys_agent access tables belong to sys tenant only # real_agent access tables belong to current tenant
source ${oceanbase}/src/share/inner_table/ob_inner_table_schema_def.py
如dba_users
view_definition = """
SELECT
B.USER_NAME AS USERNAME,
B.USER_ID AS USERID,
B.PASSWD AS PASSWORD,
CAST(CASE WHEN B.IS_LOCKED = 1 THEN 'LOCKED' ELSE 'OPEN' END as VARCHAR2(30)) AS ACCOUNT_STATUS,
CAST(NULL as DATE) AS LOCK_DATE,
CAST(NULL as DATE) AS EXPIRY_DATE,
CAST(NULL as VARCHAR2(30)) AS DEFAULT_TABLESPACE,
CAST(NULL as VARCHAR2(30)) AS TEMPORARY_TABLESPACE,
CAST(B.GMT_CREATE AS DATE) AS CREATED,
CAST(NULL as VARCHAR2(30)) AS INITIAL_RSRC_CONSUMER_GROUP,
CAST(NULL as VARCHAR2(4000)) AS EXTERNAL_NAME
FROM
SYS.ALL_VIRTUAL_USER_REAL_AGENT B
WHERE
B.TYPE = 0
AND B.TENANT_ID = EFFECTIVE_TENANT_ID()
Oceanbase效仿或兼容了Oracle的命名，当前代码中有178个DBA_, 152个[G]V$ 对象，在 OceanBase 中，虚拟表并不是实际的表，是内存数据，可以理解为在ORACLE中的X$对象， OB V4 社区提供的代码中 如下（注：ob有些对象并未公开与说明，如同oracle 的undocument obj）：
"DBA_ALL_TABLES", "DBA_ERRORS", "DBA_METHOD_PARAMS", "DBA_MVIEW_COMMENTS", "DBA_PROFILES", "DBA_SCHEDULER_JOB_ARGS", "DBA_SCHEDULER_PROGRAM_ARGS", "DBA_TYPE_METHODS", 'DBA_AUDIT_EXISTS', 'DBA_AUDIT_OBJECT', 'DBA_AUDIT_SESSION', 'DBA_AUDIT_STATEMENT', 'DBA_AUDIT_TRAIL', 'DBA_COL_COMMENTS', 'DBA_COL_PRIVS', 'DBA_CONSTRAINTS', 'DBA_CONS_COLUMNS', 'DBA_CONTEXT', 'DBA_DB_LINKS', 'DBA_DEPENDENCIES', 'DBA_DIRECTORIES', 'DBA_INDEXES', 'DBA_IND_COLUMNS', 'DBA_IND_PARTITIONS', 'DBA_IND_STATISTICS', 'DBA_IND_SUBPARTITIONS', 'DBA_JOBS', 'DBA_JOBS_RUNNING', 'DBA_OBJECTS', 'DBA_OBJ_AUDIT_OPTS', 'DBA_OB_ACCESS_POINT', 'DBA_OB_ARBITRATION_SERVICE', 'DBA_OB_ARCHIVELOG', 'DBA_OB_ARCHIVELOG_PIECE_FILES', 'DBA_OB_ARCHIVELOG_SUMMARY', 'DBA_OB_ARCHIVE_DEST', 'DBA_OB_AUTO_INCREMENT', 'DBA_OB_BACKUP_DELETE_JOBS', 'DBA_OB_BACKUP_DELETE_JOB_HISTORY', 'DBA_OB_BACKUP_DELETE_POLICY', 'DBA_OB_BACKUP_DELETE_TASKS', 'DBA_OB_BACKUP_DELETE_TASK_HISTORY', 'DBA_OB_BACKUP_JOBS', 'DBA_OB_BACKUP_JOB_HISTORY', 'DBA_OB_BACKUP_PARAMETER', 'DBA_OB_BACKUP_SET_FILES', 'DBA_OB_BACKUP_STORAGE_INFO', 'DBA_OB_BACKUP_STORAGE_INFO_HISTORY', 'DBA_OB_BACKUP_TASKS', 'DBA_OB_BACKUP_TASK_HISTORY', 'DBA_OB_BALANCE_JOBS', 'DBA_OB_BALANCE_JOB_HISTORY', 'DBA_OB_BALANCE_TASKS', 'DBA_OB_BALANCE_TASK_HISTORY', 'DBA_OB_CLUSTER_EVENT_HISTORY', 'DBA_OB_CONCURRENT_LIMIT_SQL', 'DBA_OB_DATABASES', 'DBA_OB_DATABASE_PRIVILEGE', 'DBA_OB_DATA_DICTIONARY_IN_LOG', 'DBA_OB_DEADLOCK_EVENT_HISTORY', 'DBA_OB_EXTERNAL_TABLE_FILES', 'DBA_OB_FREEZE_INFO', 'DBA_OB_IMPORT_TABLE_JOBS', 'DBA_OB_IMPORT_TABLE_JOB_HISTORY', 'DBA_OB_IMPORT_TABLE_TASKS', 'DBA_OB_IMPORT_TABLE_TASK_HISTORY', 'DBA_OB_KV_TTL_TASKS', 'DBA_OB_KV_TTL_TASK_HISTORY', 'DBA_OB_LOG_RESTORE_SOURCE', 'DBA_OB_LS', 'DBA_OB_LS_ARB_REPLICA_TASKS', 'DBA_OB_LS_ARB_REPLICA_TASK_HISTORY', 'DBA_OB_LS_HISTORY', 'DBA_OB_LS_LOCATIONS', 'DBA_OB_LS_LOG_ARCHIVE_PROGRESS', 'DBA_OB_LS_REPLICA_TASKS', 'DBA_OB_MAJOR_COMPACTION', 'DBA_OB_OUTLINES', 'DBA_OB_OUTLINE_CONCURRENT_HISTORY', 'DBA_OB_RECOVER_TABLE_JOBS', 'DBA_OB_RECOVER_TABLE_JOB_HISTORY', 'DBA_OB_RESOURCE_POOLS', 'DBA_OB_RESTORE_HISTORY', 'DBA_OB_RESTORE_PROGRESS', 'DBA_OB_ROOTSERVICE_EVENT_HISTORY', 'DBA_OB_RSRC_IO_DIRECTIVES', 'DBA_OB_SEQUENCE_OBJECTS', 'DBA_OB_SERVERS', 'DBA_OB_SERVER_EVENT_HISTORY', 'DBA_OB_SERVER_JOBS', 'DBA_OB_TABLEGROUPS', 'DBA_OB_TABLEGROUP_PARTITIONS', 'DBA_OB_TABLEGROUP_SUBPARTITIONS', 'DBA_OB_TABLEGROUP_TABLES', 'DBA_OB_TABLET_REPLICAS', 'DBA_OB_TABLET_TO_LS', 'DBA_OB_TABLE_LOCATIONS', 'DBA_OB_TABLE_OPT_STAT_GATHER_HISTORY', 'DBA_OB_TABLE_STAT_STALE_INFO', 'DBA_OB_TASK_OPT_STAT_GATHER_HISTORY', 'DBA_OB_TENANTS', 'DBA_OB_TENANT_EVENT_HISTORY', 'DBA_OB_TENANT_JOBS', 'DBA_OB_TRANSFER_TASKS', 'DBA_OB_TRANSFER_TASK_HISTORY', 'DBA_OB_UNITS', 'DBA_OB_UNIT_CONFIGS', 'DBA_OB_UNIT_JOBS', 'DBA_OB_USERS', 'DBA_OB_USER_DEFINED_RULES', 'DBA_OB_ZONES', 'DBA_OB_ZONE_MAJOR_COMPACTION', 'DBA_PART_COL_STATISTICS', 'DBA_PART_HISTOGRAMS', 'DBA_PART_INDEXES', 'DBA_PART_KEY_COLUMNS', 'DBA_PART_TABLES', 'DBA_POLICIES', 'DBA_POLICY_CONTEXTS', 'DBA_POLICY_GROUPS', 'DBA_RECYCLEBIN', 'DBA_ROLES', 'DBA_ROLE_PRIVS', 'DBA_RSRC_CONSUMER_GROUPS', 'DBA_RSRC_GROUP_MAPPINGS', 'DBA_RSRC_PLANS', 'DBA_RSRC_PLAN_DIRECTIVES', 'DBA_SCHEDULER_JOBS', 'DBA_SCHEDULER_JOB_CLASSES', 'DBA_SCHEDULER_JOB_RUN_DETAILS', 'DBA_SCHEDULER_PROGRAM', 'DBA_SCHEDULER_WINDOWS', 'DBA_SEC_RELEVANT_COLS', 'DBA_SEGMENTS', 'DBA_SEQUENCES', 'DBA_SQL_MANAGEMENT_CONFIG', 'DBA_SQL_PLAN_BASELINES', 'DBA_STMT_AUDIT_OPTS', 'DBA_SUBPARTITION_TEMPLATES', 'DBA_SUBPART_COL_STATISTICS', 'DBA_SUBPART_HISTOGRAMS', 'DBA_SUBPART_KEY_COLUMNS', 'DBA_SYNONYMS', 'DBA_SYS_PRIVS', 'DBA_TABLES', 'DBA_TABLESPACES', 'DBA_TAB_COLS', 'DBA_TAB_COLS_V$', 'DBA_TAB_COLUMNS', 'DBA_TAB_COL_STATISTICS', 'DBA_TAB_COMMENTS', 'DBA_TAB_HISTOGRAMS', 'DBA_TAB_MODIFICATIONS', 'DBA_TAB_PARTITIONS', 'DBA_TAB_PRIVS', 'DBA_TAB_STATISTICS', 'DBA_TAB_STATS_HISTORY', 'DBA_TAB_SUBPARTITIONS', 'DBA_USERS', 'DBA_WR_ACTIVE_SESSION_HISTORY', 'DBA_WR_CONTROL', 'DBA_WR_SNAPSHOT', 'DBA_WR_STATNAME', 'DBA_WR_SYSSTAT', 'DBA_ARGUMENTS', 'DBA_COLL_TYPES', 'DBA_IND_EXPRESSIONS', 'DBA_IND_PARTITIONS', 'DBA_IND_SUBPARTITIONS', 'DBA_PART_KEY_COLUMNS', 'DBA_PROCEDURES', 'DBA_SOURCE', 'DBA_SUBPART_KEY_COLUMNS', 'DBA_TRIGGERS', 'DBA_TRIGGER_ORDERING', 'DBA_TYPES', 'DBA_TYPE_ATTRS', 'DBA_VIEWS',
和
'GV$OB_PLAN_CACHE_STAT', 'GV$OB_PLAN_CACHE_PLAN_STAT', 'GV$SESSION_EVENT', 'GV$SESSION_WAIT', 'GV$SESSION_WAIT_HISTORY', 'GV$SYSTEM_EVENT', 'GV$SESSTAT', 'GV$SYSSTAT', 'V$STATNAME', 'V$EVENT_NAME', 'V$SESSION_EVENT', 'V$SESSION_WAIT', 'V$SESSION_WAIT_HISTORY', 'V$SESSTAT', 'V$SYSSTAT', 'V$SYSTEM_EVENT', 'GV$OB_SQL_AUDIT', 'GV$LATCH', 'GV$OB_MEMORY', 'V$OB_MEMORY', 'GV$OB_MEMSTORE', 'V$OB_MEMSTORE', 'GV$OB_MEMSTORE_INFO', 'V$OB_MEMSTORE_INFO', 'V$OB_PLAN_CACHE_STAT', 'V$OB_PLAN_CACHE_PLAN_STAT', 'GV$OB_PLAN_CACHE_PLAN_EXPLAIN', 'V$OB_PLAN_CACHE_PLAN_EXPLAIN', 'V$OB_SQL_AUDIT', 'V$LATCH', 'GV$OB_RPC_OUTGOING', 'V$OB_RPC_OUTGOING', 'GV$OB_RPC_INCOMING', 'V$OB_RPC_INCOMING', 'GV$SQL_MONITOR', 'V$SQL_MONITOR', 'GV$SQL_PLAN_MONITOR', 'V$SQL_PLAN_MONITOR', 'GV$SQL_PLAN_STATISTICS', 'V$SQL_PLAN_STATISTICS', 'GV$SESSION_LONGOPS', 'V$SESSION_LONGOPS', 'GV$OB_PX_WORKER_STAT', 'V$OB_PX_WORKER_STAT', 'GV$OB_PS_STAT', 'V$OB_PS_STAT', 'GV$OB_PS_ITEM_INFO', 'V$OB_PS_ITEM_INFO', 'GV$SQL_WORKAREA', 'V$SQL_WORKAREA', 'GV$SQL_WORKAREA_ACTIVE', 'V$SQL_WORKAREA_ACTIVE', 'GV$SQL_WORKAREA_HISTOGRAM', 'V$SQL_WORKAREA_HISTOGRAM', 'GV$OB_SQL_WORKAREA_MEMORY_INFO', 'V$OB_SQL_WORKAREA_MEMORY_INFO', 'GV$OB_PLAN_CACHE_REFERENCE_INFO', 'V$OB_PLAN_CACHE_REFERENCE_INFO', 'GV$OB_SSTABLES', 'V$OB_SSTABLES', 'GV$OB_SERVER_SCHEMA_INFO', 'V$OB_SERVER_SCHEMA_INFO', 'V$SQL_MONITOR_STATNAME', 'GV$OB_MERGE_INFO', 'V$OB_MERGE_INFO', 'V$OB_ENCRYPTED_TABLES', 'V$ENCRYPTED_TABLESPACES', 'GV$OB_TENANT_MEMORY', 'V$OB_TENANT_MEMORY', 'GV$OB_PX_TARGET_MONITOR', 'V$OB_PX_TARGET_MONITOR', 'CDB_TAB_COLS_V$', 'GV$OB_SERVERS', 'V$OB_SERVERS', 'GV$OB_UNITS', 'V$OB_UNITS', 'GV$OB_PARAMETERS', 'V$OB_PARAMETERS', 'GV$OB_PROCESSLIST', 'V$OB_PROCESSLIST', 'GV$OB_KVCACHE', 'V$OB_KVCACHE', 'GV$OB_TRANSACTION_PARTICIPANTS', 'V$OB_TRANSACTION_PARTICIPANTS', 'GV$OB_COMPACTION_PROGRESS', 'V$OB_COMPACTION_PROGRESS', 'GV$OB_TABLET_COMPACTION_PROGRESS', 'V$OB_TABLET_COMPACTION_PROGRESS', 'GV$OB_TABLET_COMPACTION_HISTORY', 'V$OB_TABLET_COMPACTION_HISTORY', 'GV$OB_COMPACTION_DIAGNOSE_INFO', 'V$OB_COMPACTION_DIAGNOSE_INFO', 'GV$OB_COMPACTION_SUGGESTIONS', 'V$OB_COMPACTION_SUGGESTIONS', 'GV$OB_DTL_INTERM_RESULT_MONITOR', 'V$OB_DTL_INTERM_RESULT_MONITOR', 'GV$OB_IO_CALIBRATION_STATUS', 'V$OB_IO_CALIBRATION_STATUS', 'GV$OB_IO_BENCHMARK', 'V$OB_IO_BENCHMARK', 'GV$ACTIVE_SESSION_HISTORY', 'V$ACTIVE_SESSION_HISTORY', 'GV$DML_STATS', 'V$DML_STATS', 'GV$OB_LOG_STAT', 'V$OB_LOG_STAT', 'V$RSRC_PLAN', 'V$OB_LS_REPLICA_TASK_PLAN', 'GV$OB_SQL_PLAN', 'V$OB_SQL_PLAN', 'GV$OB_TRANSACTION_SCHEDULERS', 'V$OB_TRANSACTION_SCHEDULERS', 'V$OB_ARCHIVE_DEST_STATUS', 'GV$OB_OPT_STAT_GATHER_MONITOR', 'V$OB_OPT_STAT_GATHER_MONITOR', 'GV$OB_THREAD', 'V$OB_THREAD', 'GV$OB_ARBITRATION_MEMBER_INFO', 'V$OB_ARBITRATION_MEMBER_INFO', 'GV$OB_ARBITRATION_SERVICE_STATUS', 'V$OB_ARBITRATION_SERVICE_STATUS', 'GV$OB_KV_CONNECTIONS', 'V$OB_KV_CONNECTIONS', 'GV$OB_LOCKS', 'V$OB_LOCKS', 'V$OB_TIMESTAMP_SERVICE', 'GV$OB_PX_P2P_DATAHUB', 'V$OB_PX_P2P_DATAHUB', 'GV$SQL_JOIN_FILTER', 'V$SQL_JOIN_FILTER', 'V$OB_LS_LOG_RESTORE_STATUS', 'GV$OB_FLT_TRACE_CONFIG', 'GV$OB_TENANT_RUNTIME_INFO', 'V$OB_TENANT_RUNTIME_INFO', 'ALL_TAB_COLS_V$', 'DBA_TAB_COLS_V$', 'USER_TAB_COLS_V$', 'V$OB_LS_REPLICA_TASK_PLAN', 'GV$OB_PX_P2P_DATAHUB', 'V$OB_PX_P2P_DATAHUB', 'GV$SQL_JOIN_FILTER', 'V$SQL_JOIN_FILTER', 'GV$OB_SQL_AUDIT', 'V$OB_SQL_AUDIT', 'GV$INSTANCE', 'V$INSTANCE', 'GV$OB_PLAN_CACHE_PLAN_STAT', 'V$OB_PLAN_CACHE_PLAN_STAT', 'GV$OB_PLAN_CACHE_PLAN_EXPLAIN', 'V$OB_PLAN_CACHE_PLAN_EXPLAIN', 'GV$SESSION_WAIT', 'V$SESSION_WAIT', 'GV$SESSION_WAIT_HISTORY', 'V$SESSION_WAIT_HISTORY', 'GV$OB_MEMORY', 'V$OB_MEMORY', 'GV$OB_MEMSTORE', 'V$OB_MEMSTORE', 'GV$OB_MEMSTORE_INFO', 'V$OB_MEMSTORE_INFO', 'GV$SESSTAT', 'V$SESSTAT', 'GV$SYSSTAT', 'V$SYSSTAT', 'GV$SYSTEM_EVENT', 'V$SYSTEM_EVENT', 'GV$OB_PLAN_CACHE_STAT', 'V$OB_PLAN_CACHE_STAT', 'V$NLS_PARAMETERS', 'V$VERSION', 'GV$OB_PX_WORKER_STAT', 'V$OB_PX_WORKER_STAT', 'GV$OB_PS_STAT', 'V$OB_PS_STAT', 'GV$OB_PS_ITEM_INFO', 'V$OB_PS_ITEM_INFO', 'GV$SQL_WORKAREA_ACTIVE', 'V$SQL_WORKAREA_ACTIVE', 'GV$SQL_WORKAREA_HISTOGRAM', 'V$SQL_WORKAREA_HISTOGRAM', 'GV$OB_SQL_WORKAREA_MEMORY_INFO', 'V$OB_SQL_WORKAREA_MEMORY_INFO', 'GV$OB_PLAN_CACHE_REFERENCE_INFO', 'V$OB_PLAN_CACHE_REFERENCE_INFO', 'GV$SQL_WORKAREA', 'V$SQL_WORKAREA', 'GV$OB_SSTABLES', 'V$OB_SSTABLES', 'GV$OB_SERVER_SCHEMA_INFO', 'V$OB_SERVER_SCHEMA_INFO', 'GV$SQL_PLAN_MONITOR', 'V$SQL_PLAN_MONITOR', 'V$SQL_MONITOR_STATNAME', 'GV$OPEN_CURSOR', 'V$OPEN_CURSOR', 'V$TIMEZONE_NAMES', 'GV$GLOBAL_TRANSACTION', 'V$GLOBAL_TRANSACTION', 'V$RESTORE_POINT', 'V$RSRC_PLAN', 'V$OB_ENCRYPTED_TABLES', 'V$ENCRYPTED_TABLESPACES', 'GV$OB_TENANT_MEMORY', 'V$OB_TENANT_MEMORY', 'GV$OB_PX_TARGET_MONITOR', 'V$OB_PX_TARGET_MONITOR', 'GV$DBLINK', 'V$DBLINK', 'V$GLOBALCONTEXT', 'GV$OB_UNITS', 'V$OB_UNITS', 'GV$OB_PARAMETERS', 'V$OB_PARAMETERS', 'GV$OB_PROCESSLIST', 'V$OB_PROCESSLIST', 'GV$OB_KVCACHE', 'V$OB_KVCACHE', 'GV$OB_TRANSACTION_PARTICIPANTS', 'V$OB_TRANSACTION_PARTICIPANTS', 'GV$OB_COMPACTION_PROGRESS', 'V$OB_COMPACTION_PROGRESS', 'GV$OB_TABLET_COMPACTION_PROGRESS', 'V$OB_TABLET_COMPACTION_PROGRESS', 'GV$OB_TABLET_COMPACTION_HISTORY', 'V$OB_TABLET_COMPACTION_HISTORY', 'GV$OB_COMPACTION_DIAGNOSE_INFO', 'V$OB_COMPACTION_DIAGNOSE_INFO', 'GV$OB_COMPACTION_SUGGESTIONS', 'V$OB_COMPACTION_SUGGESTIONS', 'GV$OB_DTL_INTERM_RESULT_MONITOR', 'V$OB_DTL_INTERM_RESULT_MONITOR', 'V$EVENT_NAME', 'GV$ACTIVE_SESSION_HISTORY', 'V$ACTIVE_SESSION_HISTORY', 'GV$DML_STATS', 'V$DML_STATS', 'GV$OB_LOG_STAT', 'V$OB_LOG_STAT', 'GV$OB_GLOBAL_TRANSACTION', 'V$OB_GLOBAL_TRANSACTION', 'GV$OB_TRANSACTION_SCHEDULERS', 'V$OB_TRANSACTION_SCHEDULERS', 'GV$OB_SQL_PLAN', 'V$OB_SQL_PLAN', 'V$OB_ARCHIVE_DEST_STATUS', 'GV$OB_LOCKS', 'V$OB_LOCKS', 'GV$OB_OPT_STAT_GATHER_MONITOR', 'V$OB_OPT_STAT_GATHER_MONITOR', 'GV$SESSION_LONGOPS', 'V$SESSION_LONGOPS', 'GV$OB_THREAD', 'V$OB_THREAD', 'GV$OB_ARBITRATION_MEMBER_INFO', 'V$OB_ARBITRATION_MEMBER_INFO', 'GV$OB_ARBITRATION_SERVICE_STATUS', 'V$OB_ARBITRATION_SERVICE_STATUS', 'V$OB_TIMESTAMP_SERVICE', 'V$OB_LS_LOG_RESTORE_STATUS', 'GV$OB_FLT_TRACE_CONFIG',
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。