I’m a bit of a database pureist. I believe that if you are building a system that will capture information for a business or organization, and that information matters to the business, then you should build the tables that will support the insert, update, and delete of information in third normal form (at least, there are additional normal forms) and the best way to figure out how those tables should be built is to work directly with the business by building an Entity Relationship Diagram (note: a Table Diagram is NOT an ERD!) and getting business by-in before you start creating tables.
A frequent business requirement is “We’d like to know by who and when information was created or updated.” Traditionally, we have added four columns to our tables to do this: CREATED, CREATED_BY, UPDATED, and UPDATED_BY. As an aside (I have a feeling there will be a lot of them in this post), I despise when folks do CREATED_DATE, and UPDATED_DATE. You don’t do NAME_VARCHAR2!
Oracle Quick SQL, built into Oracle APEX, builds these same traditional columns on your tables if you ask it to, and then they build a BUI (before update or insert) trigger on the table. Note, I think that a BUI trigger is “wrong” since we’ve had compound triggers in the database since Oracle 11g, and that every table that has normal triggers (basically, if you are not using Edition Based Redefinition) should only have a single trigger, and it should be a compound trigger.
That said, quick SQL builds a trigger that looks like this:
create or replace trigger employee_biu
before insert or update
on employee
for each row
begin
if inserting then
:new.created := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updated := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end employee_biu;
/
As a purist, I have always hated that the trigger always populates the UPDATED and UPDATED_BY columns even on the first insert. To me, those columns should only be populated if the row was actually updated, since that is the name of the columns. By populating them all the time, if you want to answer the question “How many rows have actually been updated?”, you will need to compare (let’s use a table with a hundred million rows) a hundred million values:
select count(*)
from sometable
where created != updated;
In addition, you are going to store extra stuff that you don’t really need. Instead, I believed (notice the past tense!), those columns should be nullable and should only be populated if you actually update the column. A hundred million row table with only seven updated rows and an index on updated would be worlds faster than a table that stores all these needless UPDATED and UPDATED_BY by values.
Now, because we have a nullable column, we do introduce the “issues” that come along with a nullable column. You’ll need to use NVL or, even better, COALESCE every time you look at the column, and if you forget, you can end up with bugs. The number of bugs that nulls give us in systems is pretty large. But, because you did things “right” (you didn’t needlessly populate a column that didn’t need to be populated), you would be aware of this, and you wouldn’t fall prey to those bugs.
However, a recent conversation with Anton Nielsen convinced me to update my perspective.
At first, Anton tried to make the argument that every insert is an update too. I wasn’t buying that argument at all. He, of course, brought up all the issues with a nullable column and indexes on virtual columns (Why not add a virtual column that is a COALESCE(UPDATED,CREATED) as LAST_TOUCHED?), etc. But the thing that really convinced me was this:
“In all my years of building applications the business people always ask ‘Who touched this row last?’, they never ask ‘How many rows were touched after they were created?’.”
Anton Nielsen
Maybe those columns should have been called LAST_TOUCHED and LAST_TOUCHED_BY (and, indeed, if you are going to populate them on insert, they really should be), but we can fix this by adding a comment to the UPDATED and UPDATED_BY columns:
comment on column employee.updated is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED column.';
comment on column employee.updated_by is 'Populated during row creation and whenever the row is updated. Effectively that makes this column the LAST_TOUCHED_BY column. This is set to the current APEX user or the database user depending on the context.';
Indexes are now owned by the table owner rather than the DBA running the script.
All missing indexes are created in a single tablespace named “missing_foreign_key_indexes”. Obviously, this tablespace needs to exist for the statements to work. You might want to change this clause to use “your user’s index tablespaces”, and you should drop this if you are on Oracle Autonomous on Oracle Cloud, since you only get one tablespace for your stuff and everything will automatically go to it.
There are suggestions for making shorter index names if needed/wanted.
The “local” clause was added for partitioned tables.
You can optionally require validated foreign keys, valid indexes, and visible indexes. Today’s invalid or invisible index can turn into tomorrow’s index, so I left the default to show all indexes.
We are now sorting numerically.
Change “select *” into “select username” for the not in clause that eliminates users.
Added a where clause to find missing indexes by default.
Again, here’s the formatted code that doesn’t look great, but if you copy it, it should be formatted as it is in the above picture.
-- Created by Rich Soule of Talan's Oracle Group in collaboration with Lance Eaton.
--
-- Notes:
-- * Can ignore unusable/invisible/bitmap indexes; prefers NORMAL (and function-based NORMAL) b-trees
-- * Emits LOCAL for partitioned child tables
-- * change 'missing_foreign_key_indexes' tablespace to taste (or drop on Autonomous)
-- * if you want to shorten the index names, you can replace the two lines with comments below with something like: 'missing_fk_index'||rownum
-- * designed to be run by a DBA with access to the DBA views, but can also be run by a regular user by replacing the dba_ views with
-- all_ views or user_ views (search and replace dba_ with all_ or user_)
with owner_exclusion_list as ( select username from dba_users where oracle_maintained = 'Y'
union all select 'ORDS_METADATA' from dual
union all select 'ORDS_PUBLIC_USER' from dual )
, constraint_column_list as ( select owner
, table_name
, constraint_name
, listagg(column_name, ', ') within group (order by position) as constraint_column_list
from dba_cons_columns
join dba_constraints using (owner, table_name, constraint_name)
where constraint_type = 'R'
and status = 'ENABLED'
-- and validated = 'VALIDATED' -- uncomment to require validated fks
and owner not in (select username from owner_exclusion_list)
group by owner, table_name, constraint_name )
, index_column_list as ( select di.owner
, di.table_name
, di.index_name
, listagg(dic.column_name, ', ') within group (order by dic.column_position) as index_column_list
from dba_indexes di
join dba_ind_columns dic on (dic.index_owner = di.owner and dic.index_name = di.index_name)
where di.owner not in (select username from owner_exclusion_list)
-- and di.status = 'VALID' -- uncomment to require valid indexes
-- and di.visibility = 'VISIBLE' -- uncomment to require visible indexes
and di.index_type in ('NORMAL','FUNCTION-BASED NORMAL')
group by di.owner, di.table_name, di.index_name )
, foreign_key_index_query as (select decode(icl.table_name, null, 'Missing', 'Exists') as index_existence
, dt.num_rows as last_analyzed_row_count_number
, to_char(dt.num_rows, '999,999,999,999,999') as last_analyzed_row_count
, dt.last_analyzed
, ccl.owner as table_owner
, ccl.table_name
, ccl.constraint_name as foreign_key_name
, ccl.constraint_column_list as foreign_key_column_list
, coalesce(icl.index_name, '*** Missing Index ***') as index_name
, coalesce(icl.index_column_list, '*** Missing Index ***') as index_column_list
, decode(icl.table_name, null,'create index "'||ccl.owner||'".'||
lower(ccl.table_name||'_foreign_key_index_on_'|| -- Shorten these two lines to have
replace(replace(ccl.constraint_column_list,',','_'),' '))|| -- smaller index names
' on "'||ccl.owner||'"."'||ccl.table_name||'"('||
replace(replace(ccl.constraint_column_list,',','","'),' ')||')'||
decode(dt.partitioned, 'YES', ' local', '')||
' tablespace missing_foreign_key_indexes;'
,'*** supporting index already exists ***') as create_index_ddl
from constraint_column_list ccl
join dba_tables dt on (dt.owner = ccl.owner and dt.table_name = ccl.table_name)
left join index_column_list icl on ( icl.owner = ccl.owner and icl.table_name = ccl.table_name
and icl.index_column_list like ccl.constraint_column_list || '%' ))
select index_existence
, last_analyzed_row_count
, last_analyzed
, table_owner
, table_name
, foreign_key_name
, foreign_key_column_list
, index_name
, index_column_list
, create_index_ddl
from foreign_key_index_query
where index_existence = 'Missing' -- comment to see both Exists & Missing
order by last_analyzed_row_count_number desc nulls last, table_owner, table_name, foreign_key_column_list;
TLDR: I’ve been running into an issue where my Oracle Base Database on Oracle Cloud running Oracle 23ai appears to be ‘automatically locking accounts at random times’. To potentially prevent one of these random locks from stopping APEX from working, try this unsupported but working adjustment to your APEX_PUBLIC_USER account: alter user apex_public_user account unlock no authentication;
The background: My database is what is currently called on Oracle Cloud, an “Oracle Base Database”. Unlike the Oracle Autonomous Database, where you get a pluggable database in a container database that someone else manages, here you get full access to the database file system and full access to everything about the database (root container, full sys user access, etc.). I say “currently called” because we actually put this database on Oracle Cloud way back in Sept of 2021. That’s when this database was migrated from an on-premises Oracle Database Appliance to Oracle Cloud.
Oracle Cloud has changed a bunch since then, but overall, I couldn’t be happier with the migration. With Oracle Base Database, you “let” Oracle manage the software locations and database locations (Oracle uses Automatic Storage Management for the database and fast recovery area storage). Patches and upgrades (we started with 19c, but are now on 23ai) are straightforward and controlled at your own pace, implemented by simple choices in the Oracle Cloud UI.
For many years, this database “just worked”. The business ran its processes, and the APEX application we built for them just did its thing. On July 22nd, I got a call from the business saying “APEX isn’t working”. When I went and looked, the APEX_PUBLIC_USER account was locked. This is strange because there wasn’t a reason for the account to be locked. Nobody did anything. The database profile for the APEX_PUBLIC_USER has a password life time of unlimited, so it wasn’t a profile thing. I unlocked the account, APEX started working again, and life was good. An investigation into the unified audit trail didn’t show anything. This was a “mystery”. Anyone in tech would agree that a mystery isn’t good.
On August 11th, I got the same call. Again, the APEX_PUBLIC_USER account was locked. I again unlocked it. This time I did a bigger investigation with a coworker. He’s been struggling with the same random locking behavior for the APEX_PUBLIC_USER in his DEV, TEST, and PROD environments for the last 4 months (he’s had many Oracle SRs open and closed on this while he’s been bounced around various teams within Oracle, and his random locks have happened much more frequently than mine). As we looked at things, we realized that there is an amount of correlation between database patches being applied and accounts getting locked. It’s not exact, but here are some of the queries that we looked at:
select cdb$name as container -- Awesome hidden colum on CDB_ views!
, target_build_description
, action_time
from cdb_registry_sqlpatch
order by action_time desc;
select username
, cdb$name as container
, lock_date
, last_login
, created
, cu.*
from cdb_users cu
order by cu.lock_date desc nulls last;
select cdb$name as container
, cp.*
from cdb_profiles cp
where resource_name = 'INACTIVE_ACCOUNT_TIME';
Obviously, if you don’t have access to the root container, you can change the above queries to use the DBA views in your own pluggable (or non-container) database if you eliminate the pdb_name column.
Something very interesting was that there were a LOT of accounts getting locked at the “same time”, but that time was different for different pluggable databases in the same container database.
I’ve got two “opportunities for future enhancement” logged against the APEX product and APEX documentation. This is the current slide in my latest (award-winning!) APEX & ORDS for DBAs and System Admins presentation (an earlier version of this can be found on YouTube).
A while back, I had shared that with my coworker, and he had implemented it in his dev and test environment:
alter user apex_public_user account unlock no authentication;
Since implementing this, he has not had the locking issue for the APEX_PUBLIC_USER his 23ai environments.
I went ahead and implemented this in DEV, TEST, and PROD. We’ll see what happens, and if any of the SRs my coworker has filed with Oracle Support get an actual resolution, I’ll update this post!
Cars and Code 