— 创建核心应用用户资源配置文件
CREATE PROFILE core_app_profile LIMIT
SESSIONS_PER_USER 20
CPU_PER_SESSION DEFAULT
CPU_PER_CALL 6000
CONNECT_TIME 120
IDLE_TIME 60
LOGICAL_READS_PER_SESSION 100000
LOGICAL_READS_PER_CALL 10000
PRIVATE_SGA 2048K
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 90;

— 创建普通用户资源配置文件
CREATE PROFILE normal_user_profile LIMIT
SESSIONS_PER_USER 5
CPU_PER_SESSION DEFAULT
CPU_PER_CALL 3000
CONNECT_TIME 60
IDLE_TIME 30
LOGICAL_READS_PER_SESSION 10000
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 512K
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60;

— 步骤2：为不同用户分配资源配置文件
ALTER USER sys PROFILE admin_profile;
ALTER USER system PROFILE admin_profile;
ALTER USER fgedu PROFILE core_app_profile;
ALTER USER fgedu01 PROFILE normal_user_profile;
ALTER USER fgedu02 PROFILE normal_user_profile;

— 步骤3：验证资源配置
SELECT
username,
profile
FROM dba_users
WHERE username IN (‘SYS’, ‘SYSTEM’, ‘FGEDU’, ‘FGEDU01’, ‘FGEDU02’);

— 输出结果
USERNAME PROFILE
——– ——————–
SYS ADMIN_PROFILE
SYSTEM ADMIN_PROFILE
FGEDU CORE_APP_PROFILE
FGEDU01 NORMAL_USER_PROFILE
FGEDU02 NORMAL_USER_PROFILE

— 步骤4：监控用户资源使用情况
SELECT
username,
profile,
round(CPU_USED/100, 2) as cpu_used_seconds,
SESSIONS_PER_USER_CURRENT as current_sessions
FROM dba_rsrc_consumer_group_privs
JOIN dba_users USING (username)
WHERE username LIKE ‘FGEDU%’;

— 步骤2：创建会话资源限制配置文件
CREATE PROFILE session_limit_profile LIMIT
CPU_PER_SESSION 3600
CPU_PER_CALL 600
CONNECT_TIME 120
IDLE_TIME 30
LOGICAL_READS_PER_SESSION 100000
LOGICAL_READS_PER_CALL 10000
PRIVATE_SGA 1024K;

— 步骤3：为用户分配资源配置文件
ALTER USER fgedu PROFILE session_limit_profile;

— 步骤4：测试会话资源限制
— 连接到数据库
— sqlplus fgedu/fgedu@fgedudb

— 执行消耗资源的操作
— BEGIN
— FOR i IN 1..1000000 LOOP
— NULL;
— END LOOP;
— END;
— /

— 步骤5：监控会话资源使用
SELECT
sid,
serial#,
username,
program,
status,
cpu_used,
logical_reads,
elapsed_time,
round(cpu_used/100, 2) as cpu_seconds
FROM v$session
WHERE username = ‘FGEDU’
ORDER BY cpu_used DESC;

— 输出结果
SID SERIAL# USERNAME PROGRAM STATUS CPU_USED LOGICAL_READS ELAPSED_TIME CPU_SECONDS
———- ———- ——– —————- ——– ———- ————- ———— ————
123 456 FGEDU sqlplus.exe ACTIVE 1250 1000 15000 12.5

— 步骤6：调整会话资源限制
ALTER PROFILE session_limit_profile LIMIT
CPU_PER_SESSION 7200
CPU_PER_CALL 1200;

— 步骤2：创建资源管理器计划
BEGIN
— 创建资源计划
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => ‘PRODUCTION_RESOURCE_PLAN’,
comment => ‘生产环境资源管理计划’
);

— 创建资源消费者组
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => ‘CRITICAL’,
comment => ‘关键业务组’
);

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => ‘NORMAL’,
comment => ‘普通业务组’
);

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => ‘BATCH’,
comment => ‘批处理组’
);

— 分配资源
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => ‘PRODUCTION_RESOURCE_PLAN’,
group_or_subplan => ‘CRITICAL’,
comment => ‘关键业务资源分配’,
cpu_p1 => 60,
cpu_p2 => 30,
cpu_p3 => 10,
parallel_degree_limit_p1 => 16
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => ‘PRODUCTION_RESOURCE_PLAN’,
group_or_subplan => ‘NORMAL’,
comment => ‘普通业务资源分配’,
cpu_p1 => 30,
cpu_p2 => 50,
cpu_p3 => 20,
parallel_degree_limit_p1 => 8
);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => ‘PRODUCTION_RESOURCE_PLAN’,
group_or_subplan => ‘BATCH’,
comment => ‘批处理资源分配’,
cpu_p1 => 10,
cpu_p2 => 20,
cpu_p3 => 70,
parallel_degree_limit_p1 => 32
);

— 映射用户到消费者组
DBMS_RESOURCE_MANAGER.SET_CONSUME_GROUP_MAPPING(
attribute => ‘ORACLE_USER’,
value => ‘FGEDU’,
consumer_group => ‘CRITICAL’
);

DBMS_RESOURCE_MANAGER.SET_CONSUME_GROUP_MAPPING(
attribute => ‘ORACLE_USER’,
value => ‘FGEDU01’,
consumer_group => ‘NORMAL’
);

DBMS_RESOURCE_MANAGER.SET_CONSUME_GROUP_MAPPING(
attribute => ‘ORACLE_USER’,
value => ‘FGEDU02’,
consumer_group => ‘BATCH’
);
END;
/

— 步骤3：启用资源管理计划
ALTER SYSTEM SET resource_manager_plan = ‘PRODUCTION_RESOURCE_PLAN’ SCOPE=BOTH;

— 步骤4：监控系统资源使用
SELECT
resource_name,
current_utilization,
max_utilization,
initial_allocation,
limit_value
FROM v$resource_limit
WHERE resource_name IN (‘processes’, ‘sessions’, ‘enqueue_locks’, ‘transactions’);

— 输出结果
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
———————- ——————- ————— —————— ———–
processes 50 120 300 300
sessions 65 150 330 330
enqueue_locks 120 250 2048 2048
transactions 35 80 363 363

— 步骤5：监控资源消费者组使用情况
SELECT
consumer_group_name,
cpu_wait_time,
cpu_waits,
consumed_cpu_time,
avg_active_sessions
FROM v$rsrc_consumer_group
ORDER BY consumed_cpu_time DESC;