oracle resource manager

1. check current running resource plan
select * from v$rsrc_plan

2. create a simple plan
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'BEN_SIMPLE_PLAN',
   CONSUMER_GROUP1 => 'BEN_GROUP1', GROUP1_PERCENT => 80,
   CONSUMER_GROUP2 => 'BEN_GROUP2', GROUP2_PERCENT => 20);
END;

2. swith plan (INTERNAL_PLAN is equal to no plan
alter system set resource_manager_plan='BEN_SIMPLE_PLAN';
alter system set resource_manager_plan='DEFAULT_MAINTENANCE_PLAN';
alter system set resource_manager_plan='';

3. check available plans
select plan_id, plan from dba_rsrc_plans;

4. check session consumer group
select username, sid, serial#,  resource_consumer_group from v$session;

5. check session state under current plan
select sid, CURRENT_CONSUMER_GROUP_ID, state from V$RSRC_SESSION_INFO;

6. check plan drectives
SELECT group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, mgmt_p4
 FROM dba_rsrc_plan_directives WHERE plan = 'BEN_SIMPLE_PLAN';

7. change session consumer group
BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('100', '64491',
   'SYS_GROUP');
END;
/

8. change all session of a user consumer group
BEGIN
  DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('BEN',
    'BEN_GROUP1');
END;
/

9. resource manager views.
View Description

DBA_RSRC_CONSUMER_GROUP_PRIVS

USER_RSRC_CONSUMER_GROUP_PRIVS


DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.

DBA_RSRC_CONSUMER_GROUPS


Lists all resource consumer groups that exist in the database.

DBA_RSRC_MANAGER_SYSTEM_PRIVS

USER_RSRC_MANAGER_SYSTEM_PRIVS


DBA view lists all users and roles that have been granted Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.

DBA_RSRC_PLAN_DIRECTIVES


Lists all resource plan directives that exist in the database.

DBA_RSRC_PLANS


Lists all resource plans that exist in the database.

DBA_RSRC_GROUP_MAPPINGS


Lists all of the various mapping pairs for all of the session attributes.

DBA_RSRC_MAPPING_PRIORITY


Lists the current mapping priority of each attribute.

DBA_HIST_RSRC_PLAN


Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.

DBA_HIST_RSRC_CONSUMER_GROUP


Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.

DBA_USERS

USERS_USERS


DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.

V$RSRC_CONS_GROUP_HISTORY


For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.

V$RSRC_CONSUMER_GROUP


Displays information about active resource consumer groups. This view can be used for tuning.

V$RSRCMGRMETRIC


Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.

V$RSRCMGRMETRIC_HISTORY


Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.

V$RSRC_PLAN


Displays the names of all currently active resource plans.

V$RSRC_PLAN_HISTORY


Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.

V$RSRC_SESSION_INFO


Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.

V$SESSION


Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.


Reference:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm013.htm#CHDJAJHE
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm003.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm

NirCmd: turn off monitor in windows by shortcut

ISSUE:
The shortcut to lock screen is pretty convenient. A shortcut to turn off monitor will be nice to save power.

SOLUTION:
1. download freeware NirCmd from http://www.nirsoft.net/utils/nircmd.html

2. created a shortcut for nircmd

3. added property of the shortcut to update command as "nircmd monitor off" and shortcut "Crl + Alt + m"

4. press "Crl + Alt + m" to test it.

Reference:
http://www.nirsoft.net/utils/nircmd.html

Oracle Account Locked by multiple failed login

ISSUE:
An account was locked because of multiple failed logins. Need find out where those failed logins came from.

SOLUTION:
if audit is turned on for failed login sessions, login information can be extracted from audit trails.

EXAMPLE:
Audit it turned on for sessions and saved to xml file outside of database.

Go to audit directory $ORACLE_BASE/admin/SID/adump
ls -ltr | tail -10 | awk '{print $9}' | xargs grep -i SCHEMA_NAME | grep "<Returncode>1017</Returncode>"

ATTN: return code 1017 -- invalide usename/password
            return code 28000 -- account is locked
            return code 0 -- login successfuly
            action 100/101 -- login

microsoft msconfigure

msconfigure: 

system configuration utility can be used to configure service and start up programs.

mstsc /console:

remote login command.

enq: TM - contention

PROBLEM:
One of user session is running for a long time without return.

CAUSE:
The running session is waiting on event: enq: TM - contention. The table is locked by another session.

SOLUTION:
1. Log out of blocking session if task is completed.
2. create a index for foreign key column if it is related to lock parent table.


reference:
http://www.confio.com/logicalread/solving-oracle-enq-tm-contention-wait-events/

Drop all objects of a schema

Purpose:
login as schema owner and cleanup all objects.

SQL:

set pagesize 0
spool ydropobj.sql
select 'drop table ' || table_name || ' cascade constraints purge;' from user_tables
;
spool off
--@ydropobj.sql
spool ydropobj.sql
select 'drop ' || object_type ||' ' || object_name || ' ;' from user_objects
;
spool off
--@ydropobj.sql
set pagesize 9999
select count(*) from user_objects;

ORA-12520: TNS:listener could not find available handler for requested type of server

ISSUE:
cannot connect to database through listener though tnsping is good.

SYMTUM:
lsnrclt services

Service "TEST" has 1 instance(s).
  Instance "TEST", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:704169 refused:0 state:blocked
         LOCAL SERVER

SOLUTION:
1.reloaded listener.
lsnrctl reload

2. register database instance

sqlplus / as sysdba

alter system register

reference:

http://www.orafaq.com/forum/t/60019/2/

https://forums.oracle.com/forums/thread.jspa?threadID=722511

ORA-39726: unsupported add/drop column operation on compressed tables

ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.

CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.

FAILED STATEMENT:
alter table yt add new_column number(3) default 0;

WORK AGROUND:
Case 1: old records not need have default vaule:
alter table yt add new_column number(3);
alter table yt modify new_column default 0;

Case 2: old record need have default value ;
this has exact same effect on compress table as on uncompress table.
alter table yt add new_column number(3) default 0 not null;
alter table yt modify new_column null;

DROP COLUMN FROM COMPRESS TABLE:
alter table yt_compress set unused column new_column;
if you want to get ride of the data from space, do a table move.  And
alter table yt_compress drop unused columns;



Example:

test@TEST.test> create table yt_compress compress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> create table yt_uncompress as select owner, table_name from dba_tables where owner='SYS';

Table created.

test@TEST.test> select count(*) from yt_compress;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_uncompress;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3) default 0;
alter table yt_compress add new_column number(3) default 0
                            *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_uncompress add new_column number(3) default 0;

Table altered.

test@TEST.test> select count(*) from yt_uncompress where new_column is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_uncompress where new_column =0;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column number(3);

Table altered.

test@TEST.test> alter table yt_compress modify new_column default 0;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column =0;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> alter table yt_compress add new_column_1 number(3) default 0 not null;

Table altered.

test@TEST.test> alter table yt_compress modify new_column_1 null;

Table altered.

test@TEST.test> select count(*) from yt_compress where new_column is null;

  COUNT(*)
----------
       985

test@TEST.test> select count(*) from yt_compress where new_column_1 is null;

  COUNT(*)
----------
         0

test@TEST.test> select count(*) from yt_compress where new_column_1 =0;

  COUNT(*)
----------
       985


test@TEST.test> alter table yt_compress drop column new_column;
alter table yt_compress drop column new_column
                                    *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


test@TEST.test> alter table yt_compress set unused column new_column;

Table altered.

test@TEST.test> desc yt_compress
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 NEW_COLUMN_1                                                   NUMBER(3)

test@TEST.test>

reference:

http://www.dba-oracle.com/oracle11g/sf_Oracle_11g_Data_Compression_Tips_for_the_DBA.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:69076630635645