Note
- This view is available starting with V4.3.5 for V4.3.x.
- This view is available starting with V4.2.2 for V4.2.x.
Purpose
The DBA_WR_SQLTEXT view displays the SQL texts collected by the WR module of the current tenant.
Columns
Column |
Type |
Nullable? |
Description |
|---|---|---|---|
| SNAP_ID | bigint(20) | NO | Snapshot ID |
| SQL_ID | varchar(32) | NO | SQL statement ID |
| QUERY_SQL | longtext | NO | SQL text |
| SQL_TYPE | bigint(20) | NO | Statement type |
The values in the SQL_TYPE column of the view and their corresponding statement types are described in the following table.
Number |
Statement type |
Number |
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 |
Sample query
Query the SQL text collected by the current tenant from the WR module and display the first record.
obclient [oceanbase]> SELECT * FROM oceanbase.DBA_WR_SQLTEXT LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
SNAP_ID: 1
SQL_ID: 007A9340F57820A7B16750F99E8718B3
QUERY_SQL: INSERT INTO __all_monitor_modified (tenant_id, table_id, tablet_id, inserts, updates, deletes) VALUES (?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?),(?, ?, ?, ?, ?, ?)ON DUPLICATE KEY UPDATE inserts = inserts + values(inserts),updates = updates + values(updates),deletes = deletes + values(deletes);
SQL_TYPE: 2
1 row in set (0.004 sec)