Quick DMLs for Concurrent Programs

DELIVERY OPTIONS OF A CONCURRENT PROGRAM (EMAIL IDs)

SELECT concurrent_request_id,
       (SELECT tl.user_concurrent_program_name
        FROM   applsys.fnd_concurrent_programs_tl tl,
               applsys.fnd_concurrent_requests rq
        WHERE  tl.concurrent_program_id = rq.concurrent_program_id
               AND tl.LANGUAGE = Userenv('LANG')
               AND rq.request_id = concurrent_request_id) program,
       argument2                                          subject,
       argument3                                          MAIL_FROM,
       argument4                                          MAIL_To,
       argument5                                          MAIL_CC
FROM   fnd_conc_pp_actions
WHERE  action_type = 7
       AND concurrent_request_id = :p_request_id; 

UPDATE fnd_conc_pp_actions
SET    argument5 = 'abc@xxx.com'
WHERE  concurrent_request_id = 73616896
       AND action_type = 7;

COMMIT; 

DISABLE A SCHEDULE CONCURRENT PROGRAM 

UPDATE fnd_concurrent_requests
SET    phase_code = 'C',
       status_code = 'X'
WHERE  status_code IN ( 'Q', 'I' )
       AND requested_start_date > SYSDATE
       AND hold_flag = 'N'
       AND request_id = 73616896;

COMMIT; 

Check all privileges of User in Oracle

There are many views (VIEW in the SYS schema) that contain the privileges for a user:

• dba_sys_privs : System privileges granted to users and roles
• dba_tab_privs : All grants on objects in the database
• dba_role_privs : Roles granted to users and roles
• table_privileges : Grants on objects for which the user is the grantor, grantee, owner,
  or an enabled role or PUBLIC is the grantee

Below query shows all privileges, roles on objects present in Oracle Database to a particular user objects where

• TYPE : Type of Privilege or ROLE assigned to User.
• GRANTEE : User who have the permission of the object.
• PRIV : Define what privilege, role or object has
• TABNM : Name of the object on which privilege was granted.
• COLNM : Name of the column (if applicable).
• PRIV : Privilege on the object.
• AD : Whether the privilege was granted with the GRANT option. Equal to YES or NO.
• OWNER : Owner of the object

SELECT 'ROLE'       typ,
       grantee      grantee,
       granted_role priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_role_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'SYSTEM'     typ,
       grantee      grantee,
       privilege    priv,
       admin_option ad,
       '--'         tabnm,
       '--'         colnm,
       '--'         owner
FROM   dba_sys_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'TABLE'    typ,
       grantee    grantee,
       privilege  priv,
       grantable  ad,
       table_name tabnm,
       '--'       colnm,
       owner      owner
FROM   dba_tab_privs
WHERE  grantee = :cp_grantee
UNION
SELECT 'COLUMN'    typ,
       grantee     grantee,
       privilege   priv,
       grantable   ad,
       table_name  tabnm,
       column_name colnm,
       owner       owner
FROM   dba_col_privs
WHERE  grantee = :cp_grantee
ORDER  BY 5; 

Sample O/P:

Note:

--To see which table privileges are granted by you to other users.
SELECT * FROM   user_tab_privs_made;

--To see which table privileges are granted to you by other users
SELECT * FROM   user_tab_privs_recd;

--To see which column level privileges are granted by you to other users.
SELECT * FROM   user_col_privs_made

--To see which column level privileges are granted to you by other users
SELECT * FROM   user_col_privs_recd;

--To see which privileges are granted to roles
SELECT * FROM   user_role_privs;