DECLARE
v_username VARCHAR2(128) := :1; -- Bind variable for username (NULL for all users)
v_db_name VARCHAR2(9);
v_count NUMBER;
BEGIN
-- Enable DBMS_OUTPUT
DBMS_OUTPUT.ENABLE(1000000); -- Set buffer size for large output
-- 1. Get database name from V$DATABASE
DBMS_OUTPUT.PUT_LINE('=== Database Name ===');
SELECT name INTO v_db_name FROM V$DATABASE;
DBMS_OUTPUT.PUT_LINE('Database Name: ' || v_db_name);
DBMS_OUTPUT.PUT_LINE('');
-- 2. Query DBA_RSRC_GROUP_MAPPINGS for user or all users
DBMS_OUTPUT.PUT_LINE('=== Resource Group Mappings (DBA_RSRC_GROUP_MAPPINGS) ===');
IF v_username IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('Filtering for username: ' || v_username);
SELECT COUNT(*) INTO v_count
FROM DBA_RSRC_GROUP_MAPPINGS
WHERE value = UPPER(v_username) AND attribute = 'ORACLE_USER';
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No mappings found for user ' || v_username);
ELSE
FOR rec IN (
SELECT attribute, value, consumer_group, status
FROM DBA_RSRC_GROUP_MAPPINGS
WHERE value = UPPER(v_username) AND attribute = 'ORACLE_USER'
ORDER BY consumer_group
) LOOP
DBMS_OUTPUT.PUT_LINE(
'Attribute: ' || RPAD(rec.attribute, 20) ||
'Value: ' || RPAD(rec.value, 30) ||
'Consumer Group: ' || RPAD(rec.consumer_group, 30) ||
'Status: ' || rec.status
);
END LOOP;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('Listing all user mappings');
SELECT COUNT(*) INTO v_count
FROM DBA_RSRC_GROUP_MAPPINGS
WHERE attribute = 'ORACLE_USER';
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No user mappings found');
ELSE
FOR rec IN (
SELECT attribute, value, consumer_group, status
FROM DBA_RSRC_GROUP_MAPPINGS
WHERE attribute = 'ORACLE_USER'
ORDER BY value, consumer_group
) LOOP
DBMS_OUTPUT.PUT_LINE(
'Attribute: ' || RPAD(rec.attribute, 20) ||
'Value: ' || RPAD(rec.value, 30) ||
'Consumer Group: ' || RPAD(rec.consumer_group, 30) ||
'Status: ' || rec.status
);
END LOOP;
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('');
-- 3. Query DBA_RSRC_CONSUMER_GROUPS
DBMS_OUTPUT.PUT_LINE('=== Resource Consumer Groups (DBA_RSRC_CONSUMER_GROUPS) ===');
SELECT COUNT(*) INTO v_count
FROM DBA_RSRC_CONSUMER_GROUPS;
IF v_count = 0 THEN
DBMS_OUTPUT.PUT_LINE('No consumer groups found');
ELSE
FOR rec IN (
SELECT consumer_group, mandatory, comments
FROM DBA_RSRC_CONSUMER_GROUPS
ORDER BY consumer_group
) LOOP
DBMS_OUTPUT.PUT_LINE(
'Consumer Group: ' || RPAD(rec.consumer_group, 30) ||
'Mandatory: ' || RPAD(rec.mandatory, 10) ||
'Comments: ' || rec.comments
);
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: No data found in V$DATABASE');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
No comments:
Post a Comment