As we know, some ALTER SYSTEM commands are allowed to run from the PDB and they are applied to the objects owned by this PDB only. E.g.
| ALTER SYSTEM FLUSH SHARED_POOL | Only for cursors of the PDB |
| ALTER SYSTEM FLUSH BUFFER_CACHE | Only for blocks of the PDB |
| ALTER SYSTEM ENABLE/DISABLE RESTRICTED SESSION | Only for sessions of the PDB |
| ALTER SYSTEM KILL SESSION | Only for sessions of the PDB |
| ALTER SYSTEM SET parameter=value | Only for parameter of the PDB |
But the ALTER SYSTEM CHECKPOINT command is done for the entire CDB even if the command was run from the PDB. You can ensure by running the query below from the CDB$ROOT and “ALTER SYSTEM CHECKPOINT” from the PDB.
SELECT CHECKPOINT_CHANGE# "CHECKPOINT",p.open_mode PDB_MODE,
count(distinct d.con_id) cnt_pdb, count(*) cnt_files
FROM V$DATAFILE d join v$pdbs p on p.con_id=d.con_id
group by p.open_mode,CHECKPOINT_CHANGE#
order by PDB_MODE, CHECKPOINT;
You’ll see that CHECKPOINT_CHANGE# was increased for all PDBs which were in “READ WRITE” mode while ALTER SYSTEM CHECKPOINT command was run.
There is no explanation of this behavior neither in “Database Administrator’s Guide“, not in “Multitenant Administrator’s guide“.
The only hint can be found in the “Database Concepts“:
The checkpoint position is determined by the oldest dirty buffer in the database buffer cache. The checkpoint position acts as a pointer to the redo stream and is stored in the control file and in each data file header.
My interpretation is both redo stream and control file are entities common for all PDBs within the CDB, so Oracle decided it’s impossible to perform the checkpoint for single PDB only.
Why they didn’t restricted usage of the “ALTER SYSTEM CHECKPOINT” command to the CDB DBA then? I don’t know.
Tweaking Oracle 