Note
This view is introduced since OceanBase Database V4.2.2.
Purpose
The oceanbase.DBA_WR_SQLTEXT view displays the SQL texts collected by the workload repository (WR) of the current tenant.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SNAP_ID | bigint(20) | No | The ID of the snapshot. |
| SQL_ID | varchar(32) | No | The ID of the SQL statement. |
| QUERY_SQL | longtext | No | The text of the SQL statement. |
| SQL_TYPE | bigint(20) | No | The type of the SQL statement. |
The following table describes the values of the SQL_TYPE column of the view and the corresponding statement types.
Value |
Statement type |
Value |
Statement type |
|---|---|---|---|
| 1 | SELECT | 2 | INSERT |
| 3 | REPLACE | 4 | DELETE |
| 5 | UPDATE | 6 | MERGE |
| 7 | EXPLAIN | 8 | CREATE TENANT |
| 9 | DROP TENANT | 10 | LOCK TENANT |
| 11 | MODIFY TENANT | 12 | CHANGE TENANT |
| 13 | CREATE RESOURCE POOL | 14 | DROP RESOURCE POOL |
| 15 | ALTER RESOURCE POOL | 16 | SPLIT RESOURCE POOL |
| 17 | CREATE RESOURCE UNIT | 18 | ALTER RESOURCE UNIT |
| 19 | DROP RESOURCE UNIT | 20 | CREATE TABLE |
| 21 | DROP TABLE | 22 | ALTER TABLE |
| 23 | CREATE INDEX | 24 | DROP INDEX |
| 25 | CREATE VIEW | 26 | ALTER VIEW |
| 27 | DROP VIEW | 28 | HELP |
| 29 | SHOW TABLES | 30 | SHOW DATABASES |
| 31 | SHOW COLUMNS | 32 | SHOW VARIABLES |
| 33 | SHOW TABLE | 34 | SHOW SCHEMA |
| 35 | SHOW CREATE DATABASE | 36 | SHOW CREATE TABLE |
| 37 | SHOW CREATE VIEW | 38 | SHOW CREATE PROCEDURE |
| 39 | SHOW CREATE FUNCTION | 40 | SHOW PARAMETERS |
| 41 | SHOW SERVER STATUS | 42 | SHOW INDEXES |
| 43 | SHOW WARNINGS | 44 | SHOW ERRORS |
| 45 | SHOW PROCESSLIST | 46 | SHOW CHARSET |
| 47 | SHOW COLLATION | 48 | SHOW TABLEGROUPS |
| 49 | SHOW STATUS | 50 | SHOW TENANT |
| 51 | SHOW CREATE TENANT | 52 | SHOW TRACE |
| 53 | SHOW ENGINES | 54 | SHOW PRIVILEGES |
| 55 | SHOW PROCEDURE STATUS | 56 | SHOW FUNCTION STATUS |
| 57 | SHOW GRANTS | 58 | CREATE USER |
| 59 | DROP USER | 60 | SET PASSWORD |
| 61 | LOCK USER | 62 | RENAME USER |
| 63 | GRANT OBJECT | 64 | REVOKE OBJECT |
| 65 | PREPARE | 66 | SET |
| 67 | EXECUTE | 68 | DEALLOCATE |
| 69 | START TRANS | 70 | END TRANS |
| 71 | KILL | 72 | ALTER SYSTEM |
| 73 | ALTER SYSTEM SETTP | 74 | CHANGE OBI |
| 75 | SWITCH MASTER | 76 | ALTER SYSTEM SERVER |
| 77 | BOOTSTRAP | 78 | CS DISKMAINTAIN |
| 79 | TABLET | 80 | REPORT REPLICA |
| 81 | SWITCH ROOTSERVER | 82 | SWITCH UPDATESERVER |
| 83 | CLUSTER MANAGER | 84 | FREEZE |
| 85 | FLUSH CACHE | 86 | FLUSH KVCACHE |
| 87 | FLUSH ILOGCACHE | 88 | DROP MEMTABLE |
| 89 | CLEAR MEMTABLE | 90 | PRINT ROOT TABLE |
| 91 | ADD UPDATESERVER | 92 | DELETE UPDATESERVER |
| 93 | CHECK ROOT TABLE | 94 | CLEAR ROOT TABLE |
| 95 | REFRESH SCHEMA | 96 | CREATE DATABASE |
| 97 | USE DATABASE | 98 | ADMIN SERVER |
| 99 | ADMIN ZONE | 100 | SWITCH REPLICA ROLE |
| 103 | RECYCLE REPLICA | 104 | MERGE |
| 105 | ALTER DATABASEE | 106 | DROP DATABASE |
| 107 | CREATE TABLEGROUP | 108 | DROP TABLEGROUP |
| 109 | ALTER TABLEGROUP | 110 | TRUNCATE TABLE |
| 111 | RENAME TABLE | 112 | CREATE TABLE LIKE |
| 113 | SET NAMES | 114 | CLEAR LOCATION CACHE |
| 115 | RELOAD GTS | 116 | RELOAD UNIT |
| 117 | RELOAD SERVER | 118 | RELOAD ZONE |
| 119 | CLEAR MERGE ERROR | 120 | MIGRATE UNIT |
| 121 | UPGRADE VIRTUAL SCHEMA | 122 | RUN JOB |
| 123 | EMPTY QUERY | 124 | CREATE OUTLINE |
| 125 | ALTER OUTLINE | 126 | DROP OUTLINE |
| 129 | SWITCH RS ROLE | 130 | FLASHBACK TENANT |
| 131 | FLASHBACK DATABASE | 132 | FLASHBACK TABLE |
| 133 | FLASHBACK | 134 | PURGE RECYCLEBIN |
| 135 | PURGE TENANT | 136 | PURGE DATABASE |
| 137 | PURGE TABLE | 138 | PURGE INDEX |
| 139 | SHOW RECYCLEBIN | 140 | UPGRADE |
| 141 | CREATE ROUTINE | 142 | DROP ROUTINE |
| 143 | ALTER ROUTINE | 144 | CALL |
| 145 | ANONYMOUS BLOCK | 146 | CREATE PACKAGE |
| 147 | CREATE PACKAGE BODY | 148 | ALTER PACKAGE |
| 149 | DROP PACKAGE | 150 | REFRESH TIME ZONE INFO |
| 151 | CANCEL TASK | 154 | SET DISK VALID |
| 155 | CREATE SYNONYM | 156 | DROP SYNONYM |
| 157 | CLEAR BALANCE TASK | 158 | BUILD INDEX SSTABLE |
| 159 | ANALYZE | 160 | SHOW CREATE TABLEGROUP |
| 161 | LOAD DATA | 162 | STMT TYPE MAX |
| 163 | CREATE FUNCTION | 164 | DROP FUNCTION |
| 165 | CREATE SEQUENCE | 166 | ALTER SEQUENCE |
| 167 | DROP SEQUENCE | 168 | SET TABLE COMMENT |
| 169 | SET COLUMN COMMENT | 170 | SWITCHOVER |
| 171 | CREATE TYPE | 172 | DROP TYPE |
| 173 | ALTER DISKGROUP ADD DISK | 174 | ALTER DISKGROUP DROP DISK |
| 175 | DROP CLUSTER | 176 | CREATE CLUSTER |
| 177 | ALTER SYSTEM SET PARAMETER | 178 | ALTER TABLE |
| 179 | OPTIMIZE TENANT | 180 | OPTIMIZE ALL |
| 181 | CREATE SAVEPOINT | 182 | ROLLBACK SAVEPOINT |
| 183 | RELEASE SAVEPOINT | 184 | CREATE KEYSTORE |
| 185 | ALTER KEYSTORE | 186 | CREATE TABLESPACE |
| 187 | DROP TABLESPACE | 188 | CREATE TRIGGER |
| 189 | DROP TRIGGER | 190 | FLASHBACK TABLE TO SCN |
| 191 | CREATE ROLE | 192 | DROP ROLE |
| 193 | ALTER ROLE | 194 | SET ROLE |
| 195 | SYSTEM GRANT | 196 | SYSTEM REVOKE |
| 197 | USER PROFILE | 198 | ALTER USER PROFILE |
| 199 | AUDIT | 200 | LOGON |
| 201 | LOGOFF | 202 | ALTER CLUSTER |
| 203 | ARCHIVELOG | 204 | BACKUP DATABASE |
| 205 | BACKUP MANAGE | 206 | RESTORE TENANT |
| 207 | REFRESH MEMORY STAT | 208 | PHYSICAL RESTORE TENANT |
| 209 | ALTER TABLESPACE | 210 | ROLLING UPGRADE |
| 211 | GRANT ROLE | 212 | REVOKE ROLE |
| 213 | MERGE RESOURCE POOL | 214 | SHOW TRIGGERS |
| 215 | CREATE DBLINK | 216 | DROP DBLINK |
| 217 | CREATE PUBLIC SYNONYM | 218 | DROP PUBLIC SYNONYM |
| 219 | CREATE PROFILE | 220 | ALTER PROFILE |
| 221 | DROP PROFILE | 222 | ALTER USER |
| 223 | XA START | 224 | XA END |
| 225 | XA PREPARE | 226 | XA COMMIT |
| 227 | XA ROLLBACK | 228 | BACKUP BACKUPSET |
| 229 | BACKUP ARCHIVELOG | 230 | ALTER TRIGGER |
| 231 | ALTER USER PRIMARY ZONE | 232 | SHOW CREATE TRIGGER |
| 233 | DIAGNOSTICS | 234 | BACKUP CLEAN |
| 235 | DELETE BACKUP POLICY | 236 | SHOW QUERY RESPONSE TIME |
| 250 | ENABLE SQL THROTTLE | 251 | DISABLE SQL THROTTLE |
| 252 | BACKUP SET ENCRYPTION | 253 | BACKUP SET DECRYPTION |
| 254 | RECOVERY | 255 | CREATE RESTORE POINT |
| 256 | DROP RESTORE POINT | 257 | RUN UPGRADE JOB |
| 258 | STOP UPGRADE JOB | 259 | FLUSH DAG WARNINGS |
| 261 | BACKUP BACKUPPIECE | 262 | ADD RESTORE SOURCE |
| 263 | CLEAR RESTORE SOURCE | 264 | SHOW RESTORE PREVIEW |
| 265 | SET REGION NETWORK BANDWIDTH | 266 | CREATE DIRECTORY |
| 267 | DROP DIRECTORY | 268 | LOCK TABLE |
| 269 | ALTER RESOURCE TENANT | 270 | ARCHIVE TENANT |
| 271 | DISCONNECT CLUSTER | 272 | WASH MEMORY FRAGMENTATION |
| 273 | CREATE CONTEXT | 274 | DROP CONTEXT |
| 275 | CHECKPOINT SLOG | 276 | REFRESH IO CALIBRATION |
| 277 | INSERT ALL | 278 | PHYSICAL RESTORE STANDBY TENANT |
| 279 | RECOVER | 280 | ADD ARBITRATION SERVICE |
| 281 | REMOVE ARBITRATION SERVICE | 282 | REPLACE ARBITRATION SERVICE |
| 283 | SHOW SEQUENCES | 284 | BACKUP KEY |
| 285 | CREATE STANDBY TENANT | 286 | CANCEL RESTORE |
| 287 | TABLE TTL | 288 | RECOVER TABLE |
| 289 | CANCEL RECOVER TABLE | 290 | CREATE TENANT SNAPSHOT |
| 291 | DROP TENANT SNAPSHOT | 292 | ALTER SYSTEM RESET PARAMETER |
| 293 | CLONE TENANT | 294 | CANCEL CLONE |
| 295 | CREATE MLOG | 296 | DROP MLOG |
Example
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SQLTEXT LIMIT 1;
The query result is as follows:
+---------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
| SNAP_ID | SQL_ID | QUERY_SQL | SQL_TYPE |
+---------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
| 1 | 01058E374A5EF26873BA4D99C4A74582 | SELECT * FROM __all_type_history WHERE tenant_id = ? AND SCHEMA_VERSION <= ? ORDER BY tenant_id desc, type_id desc, schema_version desc | 1 |
+---------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+----------+
1 row in set