checkpoint_interval — Configure database checkpointing


integer checkpoint_interval ( in minutes integer );


This function changes the database checkpointing interval to the given value in minutes. It may also be used to disable checkpointing in two ways: By setting checkpoint interval to 0, the checkpoint will only be performed after roll forward upon database startup. A setting of -1 will disable all checkpointing. Main use for this function is to ensure a clean online backup of the database slices. Copying of the database may take long and checkpointing would modify those files in mid-copy, thus rendering the resulting copy unusable. In case the system should, for some reason or another, become unstable, it is sometimes better to disable checkpointing after a database restart to resume backing up from where it was left prior to a system crash. Disabling all checkpointing by giving checkpoint_interval the value of -1 will do just that.

The interval setting will be saved in the server configuration file as value of CheckpointInterval in section [Parameters], thus it will persist over consecutive server shutdown/restart cycles. A long checkpoint_interval setting will produce longer transaction logs, which in turn prolongs the time it takes for the database to perform a roll forward upon restart in case it was shut down without making a checkpoint.



integer number of minutes between checkpoints.

Return Types

Previous value of CheckpointInterval in the configuration file as an integer.


Example 24.41. Simple examples

Disable checkpoints:

SQL> checkpoint_interval(-1);

Done. -- 25 msec.

Re-enable checkpoints (every 2 hrs):

SQL> checkpoint_interval(120);


Done. -- 4 msec.