update : 28-06-2017

DBA Script : example_create_resource_plan_multi_resource_plan_9i.sql

-- | FILE : example_create_resource_plan_multi_resource_plan_9i.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : example_create_resource_plan_multi_resource_plan_9i.sql |
-- | CLASS : Examples |
-- | PURPOSE : Example SQL script to create a Multilevel Schema / Resouce Plan |
-- | configuration in Oracle 9i. It will use default plan and |
-- | resource consumer group methods. |
-- | +---+
-- | [TOP PLAN] |
-- | | |
-- | ---------------------------- |
-- | / \ |
-- | 30% @ level 1 70% @ level 1 |
-- | | | |
-- | [SUB PLAN 1] [SUB PLAN 2] |
-- | | | |
-- | | | |
-- | ------------------------- ---------------------- |
-- | / | \ / | \ |
-- | 80% @ 20% @ 100% @ 100% @ 60% @ 40% @ |
-- | level 1 level 1 level 2 level 2 level 1 level 1 |
-- | | | | | | | |
-- | [ONLINE GROUP] [BATCH GROUP ] [OTHER GROUPS] [ONLINE GROUP] [BATCH GROUP] |
-- | [ SUB PLAN 1 ] [ SUB PLAN 1 ] [ SUB PLAN 2 ] [ SUB PLAN 2] |
-- | |
-- | +---+
-- | |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
BEGIN
-- +----------------------------------------------------------------------------+
-- | Create Pending Area |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-- +----------------------------------------------------------------------------+
-- | Create three plans - One "Top Plan" and Two "Sub Plans" |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'TOP_PLAN', COMMENT => 'Top plan');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'SUB_PLAN1', COMMENT => 'Sub plan 1');
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'SUB_PLAN2', COMMENT => 'Sub plan 2');
-- +----------------------------------------------------------------------------+
-- | Create all resource consumer groups that will be attached to the two |
-- | "Sub Plans". There will be two "user defined" resource consumer groups and |
-- | one "Oracle defined" resource consumer group (OTHER_GROUPS) defined for |
-- | each "Sub Plan". |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'ONLINE_GROUP_SUB_PLAN_1', COMMENT => 'Online Group - Sub Plan 1');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'BATCH_GROUP_SUB_PLAN_1', COMMENT => 'Batch Group - Sub Plan 1');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'ONLINE_GROUP_SUB_PLAN_2', COMMENT => 'Online Group - Sub Plan 2');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'BATCH_GROUP_SUB_PLAN_2', COMMENT => 'Batch Group - Sub Plan 2');
-- +----------------------------------------------------------------------------+
-- | We first define the directives that will control CPU resources between the |
-- | "Top Plan" and its two "Sub Plans". The first sub plan (SUB_PLAN1) will |
-- | receive 30% of the CPU at level 1 while the second sub plan (SUB_PLAN2) |
-- | will receive 70% of the CPU at level 1. |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN =>'TOP_PLAN'
, GROUP_OR_SUBPLAN =>'SUB_PLAN1'
, COMMENT=> 'All sub plan 1 user sessions at level 1'
, CPU_P1 => 30);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN =>'TOP_PLAN'
, GROUP_OR_SUBPLAN =>'SUB_PLAN2'
, COMMENT=> 'All sub plan 2 user sessions at level 1'
, CPU_P1 => 70);
-- +----------------------------------------------------------------------------+
-- | Finally, we define the key directives for each of the sub plans and |
-- | resources available for user sessions. |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN1'
, GROUP_OR_SUBPLAN => 'ONLINE_GROUP_SUB_PLAN_1'
, COMMENT => 'Online sub plan 1 users sessions at level 1'
, CPU_P1 => 80
, CPU_P2=> 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN1'
, GROUP_OR_SUBPLAN => 'BATCH_GROUP_SUB_PLAN_1'
, COMMENT => 'Batch sub plan 1 users sessions at level 1'
, CPU_P1 => 20
, CPU_P2 => 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN1'
, GROUP_OR_SUBPLAN => 'OTHER_GROUPS'
, COMMENT => 'All other users sessions at level 2'
, CPU_P1 => 0
, CPU_P2 => 100);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN2'
, GROUP_OR_SUBPLAN => 'ONLINE_GROUP_SUB_PLAN_2'
, COMMENT => 'Online sub plan 2 users sessions at level 1'
, CPU_P1 => 60
, CPU_P2 => 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN2'
, GROUP_OR_SUBPLAN => 'BATCH_GROUP_SUB_PLAN_2'
, COMMENT => 'Batch sub plan 2 users sessions at level 1'
, CPU_P1 => 40
, CPU_P2 => 0);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( PLAN => 'SUB_PLAN2'
, GROUP_OR_SUBPLAN => 'OTHER_GROUPS'
, COMMENT => 'All other users sessions at level 2'
, CPU_P1 => 0
, CPU_P2 => 100);
-- +----------------------------------------------------------------------------+
-- | The preceding call to VALIDATE_PENDING_AREA is optional because the |
-- | validation is implicitly performed in SUBMIT_PENDING_AREA. |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-- +----------------------------------------------------------------------------+
-- | Submit the pending area to Oracle! |
-- +----------------------------------------------------------------------------+
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = top_plan;

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.