Most of
Oracle's SQL statements (as well as probably of other vendors' too) consist of a combination of
verbs and
nouns. Because
Oracle exposes the available SQL statements in
v$sqlcommand, it is possible to parse the statements and extract their verbs and statements.
Create a view
In order to implement the command parsing only once, I create a
view that I can then use multiple times.
create or replace view sqlcommand_verb_noun as
select
lower(command_name) sql_statement,
regexp_substr(lower(command_name), '[^ ]+' , 1, 1 ) verb,
ltrim(regexp_substr(lower(command_name), ' ([^ ])+', 1, 1)) ||
regexp_substr(lower(command_name), ' ([^ ])+', 1, 2) noun
from
v$sqlcommand
where
command_name is not null and
command_name not like 'Do not use%' and
command_name not in ('NO-OP', 'PL/SQL EXECUTE')
;
select
substrb(sql_statement, 1, 28) sql_statement,
substrb(verb , 1, 12) verb,
substrb(noun , 1, 20) noun
from
sqlcommand_verb_noun fetch first 5 rows only;
--
-- SQL_STATEMENT VERB NOUN
-- ---------------------------- ------------ --------------------
-- create table create table
-- insert insert
-- select select
-- create cluster create cluster
-- alter cluster alter cluster
The view has three columns: sql_statement, verb and noun. Selecting from the view, it can be seen how it splits create table (value of sql_statement) into the verb create and the noun table.
In order to be able to create the view, a user must be granted select on the underlying v_$sqlcommand view:
connect sys as sysdba
grant select on sys.v_$sqlcommand to rene;
Count of verbs
Of course, it's also interesting to see how many verbs there are:
select
count(*),
verb
from
sqlcommand_verb_noun
where
verb is not null
group by
verb
order by
verb
;
| 1 | administer |
| 48 | alter |
| 3 | analyze |
| 1 | associate |
| 1 | audit |
| 1 | call |
| 1 | change |
| 1 | comment |
| 1 | commit |
| 51 | create |
| 1 | declare |
| 1 | delete |
| 1 | disassociate |
| 48 | drop |
| 1 | explain |
| 2 | flashback |
| 1 | grant |
| 1 | insert |
| 1 | lock |
| 1 | noaudit |
| 5 | purge |
| 1 | rename |
| 1 | revoke |
| 1 | rollback |
| 1 | savepoint |
| 1 | select |
| 3 | set |
| 2 | truncate |
| 1 | undrop |
| 3 | update |
| 1 | upsert |
| 1 | validate |
Unsurprisingly, the most used verbs are alter, create and drop.