Bulk insert/update with Cursor in oracle PL/SQL

ISSUE:
table is locked when insert/update millions records and commit at the end. Run time will be very long if commit after every record change.

SOLUTION:
Oracle provide bulk insert/update by fetch multiple records first and perform insert/update on them with commit.

example:

CREATE OR REPLACE PROCEDURE test_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM all_objects;

BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_proc;
/            

reference:
http://www.akadia.com/services/ora_bulk_insert.html

Search text stored in long column through like operation in Oracle PL/SQL

ISSUE:
Text stored in long column can not used directly in like clause in sql statement.

WORKAROUND:
use anonymous PL/SQL with corsor  to do the like search.

Example:
check trigger body with string "TEST"


declare
l_text long;
tname varchar2(100);
cursor c_tri is select trigger_body, trigger_name from all_triggers where owner='TEST';
begin
open c_tri;
loop
 fetch c_tri into l_text, tname;
 exit when c_tri%NOTFOUND;
 if ( Upper(l_text) like '%TEST%' )
 then
    dbms_output.put_line(tname);
end if;
end loop;
close c_tri;
end;
/

reference:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:315118200346261192
http://www.dbanotes.com/database-development/introduction-to-oracle-11g-cursors/

Find (Search) in CMD (DOS command prompt)

The old way to search files containing a string pattern in DOS command prompt.

find /i "test" *.sql

Spool file from oracle database for microsoft EXCEL

ISSUE:
Business user would like to view reports generated from database through microsoft EXCEL. So tab-separated txt file with xls as file extension name is used to generate reports automatically. However, some cell is displayed in anticipated format like 1.2E+12 (12345678912)or 123456789198000000000 (123456789198,12345567788)

CAUSE:
EXCEL does auto-convert for number from tab-separated text files.

SOLUTION:
I didn't find the way to turn off this auto-convert function to save the number as stored text.

WORKAROUND:
You can add something, which is tolerated by end user, to avoid auto-convert. For example, add a comma ',' at the end of the column from query.

Reference:
http://office.microsoft.com/en-us/excel-help/three-ways-to-convert-numbers-to-text-HA001136619.aspx
http://office.microsoft.com/en-us/excel-help/format-numbers-as-text-HP001216512.aspx

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