Search code examples
oracle-databaseoracle11gdirectoryprivileges

Error while giving grant to USER from SYS in ORACLE

I am connected to SYSDBA. I want to give access to user 'OFFC' to read and write on directory E:\oracle\extract.So,what I tried is:

CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'E:\oracle\extract';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO OFFC;
GRANT EXECUTE ON UTL_FILE TO OFFC;

What i got as error is:

Directory created.
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO OFFC
Error at line 2
ORA-00604: error occurred at recursive SQL level 1
ORA-12899: value too large for column "OT"."SCHEMA_AUDIT"."OBJECT_CREATED" (actual: 16, maximum: 15)
ORA-06512: at line 2

Why is this error coming when I tried to gave the access?

Solution

  • You must have have created the schema level trigger which keeps log of the newly created object in OT.SCHEMA_AUDIT.

    In this table column OBJECT_CREATED stores the schema.object_name and data type of the column is somewhat VARCHAR2(15) or similar.

    In your case, you are creating the object OFFC.EXTRACT_DIR (length:16) which is causing the issue.

    Either disable the trigger or increase the Length of the OBJECT_CREATED column to around (257)

    Cheers!!