Trying to create a sequence and Trigger on a table to upload data. From the APEX SQL Workshop the table was copied with no issues. But when I attempt the following:
CREATE SEQUENCE "IRA_LEDGER_SEQ" MINVALUE 1 MAXVALUE 9999999999999999 INCREMENT BY 1 START WITH 1 CACHE 10 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL /
In the Workshop or in SQL Developer (Wallet connection) I get the following:
ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to perform a database operation without the necessary privileges. *Action: Ask your database administrator or designated security administrator to grant you the necessary privileges
I am logged into SQL Developer as the schema owner, and of course when I am in the APEX environment as well in the correct Workspace, I am working as the schema owner. What is going on? There are existing sequences in the schema already, so somewhere along the way the owner lost the create sequence privilege...
Thanks
The ORA-01031 states that the logged in DB user has insufficient privileges to perform the operation. Autonomous Databases come with a predefined database role named DWROLE. This role provides the common privileges for a database developer, Depending on the usage requirements you may also need to grant individual privileges to users. If the user is granted with DWROLE that should avoid the error you are facing. Since CREATE SEQUENCE privilege included in the DWROLE.
Please refer this link hoping it helps Reference - https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/manage-users-privileges.html#GUID-50450FAD-9769-4CF7-B0D1-EC14B465B873