Hello,
In our Analytics environment in Oracle 12.2.0.1 Enterprise with latest patches, we have several subject-area schemas created related to a given group/team. Example: SCN_ADMIN, SCN_BNJT, SCN_DON, SCN_KDNY, SCN_MNCY, etc.
Inside a particular schema, like SCN_MNCY, there can be many project members working on the same schema but on different projects, all creating and dropping many tables, on a constant basis, as part of their Analytics-related work. So, the objects are created and dropped on a regular basis at any given time due to several team members working at the same time on one schema. These several users all connect to the same schema using PROXY CONNECT access privileges.
Once a day at night, I have automated a scheduled job in each of the above schemas to run a procedure which will analyze the number objects present as of that instant and start granting appropriate object privileges based on the type of the object it is, by looking up a list of active users (Oracle Usernames) stored in a separate schema in a table named SCN_ADMIN.TB_USERS.
For instance, in SCN_MNCY schema, there are a total of 2,618 objects of various types by querying ALL_OBJECTS filtered by OWNER = 'SCN_MNCY' and filtering out other stuff as you'll see in the predicates below. In SCN_ADMIN.TB_USERS, we have a total of 27 active users. Hence, total number of GRANTS needed to be processed are 2,618 x 27 = 70,686 statements.
When I simply output the display of the GRANT DDL statements to be executed, it takes less than 2 mins to display all 70,686 statements. However, when I comment out the display but activate the EXECUTE IMMEDIATE to actually perform those GRANT DDL statements, it is currently taking over 52 mins!. Sometimes, a user may invoke this procedure manually (not necessarily wait for the scheduled job to kick-in later at night) and it is taking almost an hour to process these GRANTs, which is painfully slow.
BEGIN
dbms_output.ENABLE(NULL);
FOR x IN (
SELECT
sub_qry.row_num,
(CASE WHEN (sub_qry.o_object_type_sort_order IN (1, 2) AND sub_qry.u_is_administrator = 1) THEN
('GRANT ' || 'SELECT ' || 'ON "' || sub_qry.o_object_name || '" TO "' || sub_qry.u_oracle_username || '" WITH GRANT OPTION')
WHEN (sub_qry.o_object_type_sort_order IN (1, 2) AND sub_qry.u_is_administrator = 0) THEN
('GRANT ' || 'SELECT ' || 'ON "' || sub_qry.o_object_name || '" TO "' || sub_qry.u_oracle_username || '"')
WHEN (sub_qry.o_object_type_sort_order IN (3, 4, 5)) THEN
('GRANT ' || 'EXECUTE ' || 'ON "' || sub_qry.o_object_name || '" TO "' || sub_qry.u_oracle_username || '"')
END) AS s_ddl_statement_to_execute
FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY 1) AS row_num,
obj.object_name AS o_object_name,
obj.object_type AS o_object_type,
(
CASE obj.object_type
WHEN 'TABLE' THEN 1
WHEN 'VIEW' THEN 2
WHEN 'FUNCTION' THEN 3
WHEN 'PROCEDURE' THEN 4
WHEN 'PACKAGE' THEN 5
END
) AS o_object_type_sort_order,
usrs.sort_order AS u_sort_order,
usrs.oracle_username AS u_oracle_username,
usrs.is_data_admin_user AS u_is_administrator
FROM all_objects obj,
scn_admin.tb_users usrs
WHERE 1=1
AND obj."OWNER" = sys_context('userenv', 'current_schema')
AND obj.object_type IN ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE')
AND obj.status = 'VALID'
AND obj.object_name NOT LIKE 'SYS%'
AND obj.object_name NOT LIKE '%#%'
AND obj.object_name NOT LIKE '%$%'
AND obj.object_name NOT LIKE 'BIN$%'
AND usrs.is_user_active = 1
AND usrs.oracle_username <> sys_context('userenv', 'current_schema')
ORDER BY
u_is_administrator DESC,
u_sort_order ASC,
o_object_type_sort_order ASC,
o_object_name ASC
) sub_qry
)
LOOP
--dbms_output.put_line('Row [' || to_char(x.row_num) || ']: ' || x.s_ddl_statement_to_execute);
EXECUTE IMMEDIATE x.s_ddl_statement_to_execute;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.ENABLE(NULL);
dbms_output.put_line(SQLERRM);
END;
/
I also tried to convert the above code and make use of COLLECTIONS, especially, use BULK COLLECT INTO an Associative Array with LIMIT clause in a LOOP, however, I'm noticing the same issue -- display of the output to be processed takes less than 2 mins, however, the actual processing of the GRANTs DDL is taking over 30 mins, a slight improvement but still, very slow to only process 70K records.
Any suggestion to make the above code process faster DDL GRANTs would be greatly appreciated.
Thanks in advance.