CODEWITHSUNDEEP
oracle-database
HAL 9000
HAL 9000

Reputation: 3985

How to perform checkpoint from PL/SQL

I'm performing cleanup operations from PL/SQL which tend to fill up my redo log, causing the database to freeze on the "Cannot allocate new log" condition.

Splitting up the work into smaller chunks didn't solve the problem, because the redo-log files stay at status=ACTIVE and ARCHIVED=YES for too long. Only after "alter system checkpoint" the dirty logs are written to disk and become available again for the next chunk.

Now, how can I do this from PL/SQL. I tried 

create procedure cp as begin execute immediate 'alter system checkpoint'; end;

but it gave me ORA-01031: insufficient privileges

I've got the DBA role but that's not effective in PL/SQL procedures. What is the privilege I need to grant?

I know there are parameters to control checkpointing, but I don't want to change these. I only want to manually checkpoint during the cleanup.

Upvotes: 0

Views: 2113

Answers (1)

WeaponsGrade
WeaponsGrade

Reputation: 878

Make sure the procedure is owned by a privileged user, and alter its permissions to run with owner privileges.

That's kind-of like a setuid program in Unix.

The syntax is CREATE PROCEDURE ... AUTHID DEFINER ....

The alternative, as commented above by @a_horse_with_no_name and @tbone is to grant ALTER SYSTEM privilege to the user running the procedure.

Upvotes: 1

Related Questions