怎么删除“资源计划”???急急急。。。。在线等。谢谢 我早上上班做了一件傻事,做了一个资源计划,把CPU的100%分给了AQ用户,,现在公司的数据库是谁也进不去了,用AQ也进不去,说是权限不足,用SYS可以进去不过看不见‘资源计划’请教各位大侠怎么办。救命呀 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 1、创建资源计划,未激活;BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); dbms_resource_manager.create_consumer_group(consumer_group => 'EE', comment => ' '); dbms_resource_manager.submit_pending_area(); dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'EE', false);END;BEGIN dbms_resource_manager.set_initial_consumer_group('SCOTT', 'EE');END;BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); dbms_resource_manager.create_plan('DD', ' '); dbms_resource_manager.create_plan_directive( plan => 'DD', group_or_subplan => 'EE', comment => ' ', cpu_p1 => 0, cpu_p2 => 100, cpu_p3 => 0, cpu_p4 => 0, cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0, parallel_degree_limit_p1 => -1, active_sess_pool_p1 => 0, queueing_p1 => 0, switch_group => '', switch_time => 0, switch_estimate => false, max_est_exec_time => 0, undo_pool => 0 ); dbms_resource_manager.create_plan_directive( plan => 'DD', group_or_subplan => 'SYS_GROUP', comment => ' ', cpu_p1 => 80, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0, cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0, parallel_degree_limit_p1 => -1, active_sess_pool_p1 => 0, queueing_p1 => 0, switch_group => '', switch_time => 0, switch_estimate => false, max_est_exec_time => 0, undo_pool => 0 ); dbms_resource_manager.create_plan_directive( plan => 'DD', group_or_subplan => 'OTHER_GROUPS', comment => ' ', cpu_p1 => 0, cpu_p2 => 0, cpu_p3 => 100, cpu_p4 => 0, cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0, parallel_degree_limit_p1 => -1, active_sess_pool_p1 => 0, queueing_p1 => 0, switch_group => '', switch_time => 0, switch_estimate => false, max_est_exec_time => 0, undo_pool => 0 ); dbms_resource_manager.submit_pending_area();END;2、更新资源计划,CPU100%》》》20%了:BEGIN dbms_resource_manager.clear_pending_area(); dbms_resource_manager.create_pending_area(); dbms_resource_manager.update_plan_directive( plan => 'DD', group_or_subplan => 'EE', new_comment => ' ', new_cpu_p1 => 0, new_cpu_p2 => 20, new_cpu_p3 => 0, new_cpu_p4 => 0, new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 0, new_parallel_degree_limit_p1 => -1, new_active_sess_pool_p1 => 0, new_queueing_p1 => 0, new_switch_group => '', new_switch_time => 0, new_switch_estimate => false, new_max_est_exec_time => 0, new_undo_pool => 0 ); dbms_resource_manager.submit_pending_area();END;3、删除资源计划:SQL> begin 2 dbms_resource_manager.create_pending_area(); 3 end; 4 /PL/SQL procedure successfully completed.SQL> begin 2 dbms_resource_manager.delete_plan( 3 plan => 'DD'); 4 end; 5 /PL/SQL procedure successfully completed.SQL> begin 2 dbms_resource_manager.submit_pending_area(); 3 end; 4 /PL/SQL procedure successfully completed. 请问dbms_resource_manager是什么,我根本进不去SQLPLUS 你的系统是windows么?我实际实验了一下,并查看了一下metalink:只要能修改初始化文件,注释掉*.resource_manager_plan='AA'为*.resource_manager_plan=''并以PFILE启动即可~步骤:1、关闭数据库;2、把现有的数据库备份一下;3、修改一个数据文件的名字,服务中启动数据库;很显然它不能正常启动,只能进入mount状态;sqlplus "/ as sysdba"SQL> select * from v$instance;INSTANCE_NUMBER INSTANCE_NAME--------------- ----------------HOST_NAME----------------------------------------------------------------VERSION STARTUP_TI STATUS PAR THREAD# ARCHIVE LOG_SWITCH_----------------- ---------- ------------ --- ---------- ------- -----------LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST---------- --- ----------------- ------------------ --------- 1 wareLIQ9.2.0.6.0 29-12月-04 MOUNTED NO 1 STARTEDALLOWED NO ACTIVE PRIMARY_INSTANCE NORMALSQL> create pfile from spfile;File created.4、修改pfile中的那*.resource_manager_plan='你的资源计划'为*.resource_manager_plan=''5、回到sqlplus关闭数据库SQL> shutdown immediateORA-01109: 数据库未打开Database dismounted.ORACLE instance shut down.6、手工以PFILE启动数据库;SQL> startup pfile='D:\oracle\ora92\database\INITware.ORA';ORACLE instance started.Total System Global Area 126951372 bytesFixed Size 454604 bytesVariable Size 109051904 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted.Database opened.SQL>7、可以执行前边的删除该资源计划的命令了总结:资源计划存储在SYS模式中,除非数据库打开,否则不能删除;资源计划的激活受控于初始化文件中,只要数据库在mount状态下,修改一下初始化参数,就可以绕开资源计划了~呵呵~ 那你完全可以照着做一下,我刚刚实验过,有别的情况可以msn: [email protected] oracle存储过程问题 有关创建dblink的问题,指点一下 mssql转oracle建表 急,在线等待~ 数据库清除数据为和占用系统空间反而增大? 怎么找出和某个表关联的其它表的名字呢 在ORACLE中怎樣將數據庫A中的表複製到數據庫B中?謝謝大家,指點一下! 安装oracle有空间要求吗? 10g 在rh elas3下手工建库失败,请大家帮手排错! 在一個表中重復查詢中一個fields對應多個value的問題 急急急(分不夠再開貼給分) 先謝了 十万火急,在线等待 主键列排序 跪求:基于目录里实现权限的管理方案
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group => 'EE', comment => ' ');
dbms_resource_manager.submit_pending_area();
dbms_resource_manager_privs.grant_switch_consumer_group('SCOTT', 'EE', false);
END;
BEGIN
dbms_resource_manager.set_initial_consumer_group('SCOTT', 'EE');
END;
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan('DD', ' ');
dbms_resource_manager.create_plan_directive(
plan => 'DD',
group_or_subplan => 'EE',
comment => ' ',
cpu_p1 => 0, cpu_p2 => 100, cpu_p3 => 0, cpu_p4 => 0,
cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,
parallel_degree_limit_p1 => -1,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => '',
switch_time => 0,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 0
);
dbms_resource_manager.create_plan_directive(
plan => 'DD',
group_or_subplan => 'SYS_GROUP',
comment => ' ',
cpu_p1 => 80, cpu_p2 => 0, cpu_p3 => 0, cpu_p4 => 0,
cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,
parallel_degree_limit_p1 => -1,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => '',
switch_time => 0,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 0
);
dbms_resource_manager.create_plan_directive(
plan => 'DD',
group_or_subplan => 'OTHER_GROUPS',
comment => ' ',
cpu_p1 => 0, cpu_p2 => 0, cpu_p3 => 100, cpu_p4 => 0,
cpu_p5 => 0, cpu_p6 => 0, cpu_p7 => 0, cpu_p8 => 0,
parallel_degree_limit_p1 => -1,
active_sess_pool_p1 => 0,
queueing_p1 => 0,
switch_group => '',
switch_time => 0,
switch_estimate => false,
max_est_exec_time => 0,
undo_pool => 0
);
dbms_resource_manager.submit_pending_area();
END;2、更新资源计划,CPU100%》》》20%了:BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DD',
group_or_subplan => 'EE',
new_comment => ' ',
new_cpu_p1 => 0, new_cpu_p2 => 20, new_cpu_p3 => 0, new_cpu_p4 => 0,
new_cpu_p5 => 0, new_cpu_p6 => 0, new_cpu_p7 => 0, new_cpu_p8 => 0,
new_parallel_degree_limit_p1 => -1,
new_active_sess_pool_p1 => 0,
new_queueing_p1 => 0,
new_switch_group => '',
new_switch_time => 0,
new_switch_estimate => false,
new_max_est_exec_time => 0,
new_undo_pool => 0
);
dbms_resource_manager.submit_pending_area();
END;3、删除资源计划:SQL> begin
2 dbms_resource_manager.create_pending_area();
3 end;
4 /PL/SQL procedure successfully completed.SQL> begin
2 dbms_resource_manager.delete_plan(
3 plan => 'DD');
4 end;
5 /PL/SQL procedure successfully completed.SQL> begin
2 dbms_resource_manager.submit_pending_area();
3 end;
4 /PL/SQL procedure successfully completed.
我实际实验了一下,并查看了一下metalink:
只要能修改初始化文件,注释掉
*.resource_manager_plan='AA'为*.resource_manager_plan=''
并以PFILE启动即可~步骤:
1、关闭数据库;
2、把现有的数据库备份一下;
3、修改一个数据文件的名字,服务中启动数据库;很显然它不能正常启动,只能进入mount状态;
sqlplus "/ as sysdba"
SQL> select * from v$instance;INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_TI STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- ---------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 ware
LIQ
9.2.0.6.0 29-12月-04 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
SQL> create pfile from spfile;File created.
4、修改pfile中的那
*.resource_manager_plan='你的资源计划'为*.resource_manager_plan=''
5、回到sqlplus关闭数据库
SQL> shutdown immediate
ORA-01109: 数据库未打开
Database dismounted.
ORACLE instance shut down.
6、手工以PFILE启动数据库;
SQL> startup pfile='D:\oracle\ora92\database\INITware.ORA';
ORACLE instance started.Total System Global Area 126951372 bytes
Fixed Size 454604 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
7、可以执行前边的删除该资源计划的命令了总结:
资源计划存储在SYS模式中,除非数据库打开,否则不能删除;
资源计划的激活受控于初始化文件中,只要数据库在mount状态下,修改一下初始化参数,就可以绕开资源计划了~
呵呵~
我刚刚实验过,有别的情况可以msn: [email protected]