Thinking Out Loud

August 23, 2013

dbms_metadata and consumer_group Feature or Bug ???

Filed under: 11g — mdinh @ 2:24 am

So there I was, working a simple request. Drop user and recreate which sound simple enough. Use dbms_metadata.

Stole this from asktom.oracle.com site by the way.

$ cat extractuser.sql

set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);
define _filename='cr_user_&1..sql'
spool &_filename
select (case
when ((select count(*)
from dba_users
where username = UPPER('&&1')) > 0)
then dbms_metadata.get_ddl ('USER', UPPER('&&1'))
else to_clob ('--')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('ROLE_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = UPPER('&&1')) > 0)
then dbms_metadata.get_granted_ddl ('OBJECT_GRANT', UPPER('&&1'))
else to_clob ('--')
end ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', UPPER('&&1')) from dual;
spool off
set termout on
prompt ******************************
prompt Execute: &_filename
prompt ******************************
exit

My suggestion, test run the script before you drop the user.
Run everything but the create user.

LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> @gr_user_hr.sql
LAX:(SYS@db01)>    GRANT "CONNECT" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>    GRANT "RESOURCE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>    GRANT "DBA" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>   GRANT UNLIMITED TABLESPACE TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>   GRANT SELECT ON "SYS"."V_$INSTANCE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT SELECT ON "SYS"."V_$TABLESPACE" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT EXECUTE ON "SYS"."ETL_GROUP" TO "HR";
  GRANT EXECUTE ON "SYS"."ETL_GROUP" TO "HR"
                         *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

LAX:(SYS@db01)>   GRANT WRITE ON DIRECTORY "DATA_PUMP_DIR" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT READ ON DIRECTORY "DATA_PUMP_DIR" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>   GRANT EXECUTE ON "SYS"."DEPTREE_FILL" TO "HR";

Grant succeeded.

LAX:(SYS@db01)>
LAX:(SYS@db01)>
LAX:(SYS@db01)>    ALTER USER "HR" DEFAULT ROLE ALL;

User altered.

LAX:(SYS@db01)>

What is that error!

You must have guess it by now from the subject of the post.

LAX:(SYS@db01)> @t.sql
LAX:(SYS@db01)> set echo on
LAX:(SYS@db01)> select object_type from dba_objects where object_name='ETL_GROUP';

OBJECT_TYPE
-------------------
CONSUMER GROUP

LAX:(SYS@db01)> select consumer_group from dba_rsrc_consumer_groups where consumer_group='ETL_GROUP';

CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO
HR                             ETL_GROUP                      NO  YES

LAX:(SYS@db01)> select initial_rsrc_consumer_group from dba_users where username='HR';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)> -- REVOKE
LAX:(SYS@db01)> exec dbms_resource_manager_privs.REVOKE_SWITCH_CONSUMER_GROUP('HR','ETL_GROUP');

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO

LAX:(SYS@db01)> -- GRANT
LAX:(SYS@db01)> exec dbms_resource_manager_privs.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME=>'HR',CONSUMER_GROUP=>'ETL_GROUP',GRANT_OPTION=>FALSE);

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select * from dba_rsrc_consumer_group_privs;

GRANTEE                        GRANTED_GROUP                  GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                         DEFAULT_CONSUMER_GROUP         YES YES
SYSTEM                         SYS_GROUP                      NO  YES
PUBLIC                         LOW_GROUP                      NO  NO
HR                             ETL_GROUP                      NO  YES

LAX:(SYS@db01)> -- set initial group
LAX:(SYS@db01)> exec dbms_resource_manager.SET_INITIAL_CONSUMER_GROUP('HR','ETL_GROUP');

PL/SQL procedure successfully completed.

LAX:(SYS@db01)> select initial_rsrc_consumer_group from dba_users where username='HR';

INITIAL_RSRC_CONSUMER_GROUP
------------------------------
ETL_GROUP

LAX:(SYS@db01)>

SQL used to investigate and grant privileges.

select * from dba_rsrc_consumer_group_privs;
exec dbms_resource_manager_privs.REVOKE_SWITCH_CONSUMER_GROUP('HR','ETL_GROUP');
exec dbms_resource_manager_privs.GRANT_SWITCH_CONSUMER_GROUP(GRANTEE_NAME=>'HR',CONSUMER_GROUP=>'ETL_GROUP',GRANT_OPTION=>FALSE);
exec dbms_resource_manager.SET_INITIAL_CONSUMER_GROUP('HR','ETL_GROUP');
select initial_rsrc_consumer_group from dba_users where username='HR';

I tried datapump export and import as well and it was missing consumer group.

How would you have resolved this?

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 318 other followers

%d bloggers like this: