DBA_WR_SQLTEXT

Purpose

The DBA_WR_SQLTEXT view displays the SQL texts collected by WR in the current tenant.

Columns

| SQL_TYPE | NUMBER(38) | NO | Statement type |

The following table describes the values in the SQL_TYPE column of the view and the corresponding statements. | Number | Statement | Number | Statement | |----------|-------------------------------------|----------|----------------------------------------| | 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

obclient [SYS]> SELECT * FROM SYS.DBA_WR_SQLTEXT WHERE ROWNUM = 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

References

oceanbase.CDB_WR_SQLTEXT