Search code examples
sqloracle-databaseoracle19c

Getting `ORA-01031: insufficient privileges` while executing SQL query

When I am trying to execute below SQL query on Oracle 19c DB I am getting error

ORA-01031: insufficient privileges

Code:

alter system flush buffer_cache;

Do I need to grant some privileges to user / schema? As per my understanding I need to grant alter privilege on user/ schema, but I am getting which table has to be granted alter privilege?

Solution

  • Do I need to grant some privileges to user / schema?

    From the ALTER SYSTEM documentation:

    ALTER SYSTEM

    Purpose

    Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

    When you use the ALTER SYSTEM statement in a multitenant container database (CDB), you can specify some clauses to alter the CDB as a whole and other clauses to alter a specific pluggable database (PDB).

    See Also:

    Prerequisites

    To specify the RELOCATE CLIENT clause, you must be authenticated AS SYSASM.

    To specify all other clauses, you must have the ALTER SYSTEM system privilege.

    If you are connected to a CDB:

    • To alter the CDB as a whole, the current container must be the root and you must have the commonly granted ALTER SYSTEM privilege.
    • To alter a PDB, the current container must be the PDB and you must have the ALTER SYSTEM privilege, either granted commonly or granted locally in the PDB.

    Which answers your question, yes, you need the correct privileges.