My aspires in 2010

Now when snow is taking place in my town and when weekend is at the end, I had a time to think more easy about year that ends and what should I'd aspire in new incoming 2010 Year.

To be honest, after some thinking and making some initial list, I've decided to search around the Internet to see what other people want.

Even thought this may be somehow unfair and maybe looks as a little cheating I see all common knowledge as the power to be shared to everyone...So why those ideas would not have affects on me in best positive way? After all I do choose them whether I like them or not-I see them as my decision.

After some time here is the final list (in no particular order) where I compressed the most important points:

• Try to be a better person, parent, husband, friend and colleague.
• Past Oracle certification (finally it is time to do that).
• Try to smile at least twice a day from the heart.
• Write better technical posts.
• Loose in weight.
• Find a more satisfying role.

With the same wishes to you ... until next topic in 2010 ...

Happy New 2010!

Cheers!

IsSQLValid function-check your queries before you run them

As I announce in my previous topic Query by Form (QBF) for Apex in dynamic SQL execution (regardless SQL is "hidden as a string" in some PLSQL part or composed from several string parts to one SQL command) checking of SQL before execution is something that I really often use. In this way any exception is prevented and sent through p_error parameter as regular varchar message.

Implementation

Implementation is compound of one function which should be created in any schema with create procedure privilege:

CREATE OR REPLACE  FUNCTION isSqlValid (
           p_Select IN  VARCHAR2
         , p_Error  OUT VARCHAR2
        ) RETURN BOOLEAN
IS
/*------------------------------------------------------------------------------------
 NAME    : isSqlValid.sql

 PURPOSE : Functions which parse SQL and doesn't execute it. Good for checking
           regularity of SQL

 Date    : 11.02.2004.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : create in some "tools" like schema, create public synonym and grant execute
           to public so any user can use it!

 Changes (DD.MM.YYYY, Name, CR/TR#):
------------------------------------------------------------------------------------ */
  vCursor NUMBER;
BEGIN
  p_Error := 'Unhandled exception. Not regular function output. Please check isSQLValid function code!!';
  vCursor := DBMS_Sql.open_cursor;
  DBMS_Sql.parse(vCursor, p_Select, DBMS_Sql.native);
  DBMS_Sql.close_cursor(vCursor);
  p_Error := null;
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      p_Error := SQLERRM;
      IF DBMS_Sql.is_open(vCursor) THEN
        DBMS_Sql.close_cursor(vCursor);
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
         NULL;
    END;
    RETURN FALSE;
END isSqlValid;
/

After that create public synonym and grant execute to public. In mine case owner schema is damirv:

SQL> grant execute on damirv.issqlvalid to public;

Grant succeeded.

SQL> create or replace public synonym issqlvalid for damirv.issqlvalid ;

Synonym created.

SQL>

isSqlValid in action

Let us first show several SQLs how they look in ordinary SQL prompt. For this example only first one is intentionally correct and all others has some errors:

SQL> select 1 from dual;

1
----------
1

SQL> selct 1 from dual;
SP2-0734: unknown command beginning "selct 1 fr..." - rest of line ignored.
SQL> select * from sys.v$database;
select * from sys.v$database
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create or replace public synonym emp for hr.emp;
create or replace public synonym emp for hr.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> create table xxx(c1 varchar2(4200));
create table xxx(c1 varchar2(4200))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL>

Now let us see how isSqlValid function deals with all kinds of error:

declare
  o_msg varchar2(1000);
BEGIN
  dbms_output.put_line('select 1 from dual');
  if isSQlValid('select 1 from dual',o_msg) then
    dbms_output.PUT_LINE (chr(9)||'OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('"slct" is instead of "select" at the beggining');
  if isSQlValid('selct 1 from dual',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('grant select is missing');
  if isSQlValid('select * from sys.v$database',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('Not enough privileges ');
  if isSQlValid('create or replace public synonym emp for hr.emp',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;

  dbms_output.put_line(' ');
  dbms_output.put_line('Bad DDL ');
  if isSQlValid('create table xxx(c1 varchar2(4200))',o_msg) then
    dbms_output.PUT_LINE ('OK');
  else
    dbms_output.PUT_LINE (chr(9)||'NOT OK: '||o_msg);
  end if;
END;
/

And here is the result:

select 1 from dual
OK
"slct" is instead of "select" at the beginning
     NOT OK: ORA-00900: invalid SQL statement
grant select is missing
     NOT OK: ORA-00942: table or view does not exist
Not enough privileges
     NOT OK: ORA-01031: insufficient privileges
Bad DDL
     NOT OK: ORA-00910: specified length too long for its datatype

PL/SQL procedure successfully completed.

SQL>

As you can see wide range of SQL errors are predicted. Regardless I have in mine 99.99% of cases used this function for "select type" of query (as you can see p_select is the name of the first function parameter), you see that DDL are also supported.

DDL in action

According your mails I have reedit this blog and post easy DDL test, which work as well. First test that create table is working:

SQL> declare
  2    o_msg varchar2(1000);
  3  begin
  4    if not isSqlValid('create table xx (c1 varchar2(3))',o_msg) then
  5      dbms_output.put_line(o_msg);
  6    else
  7      dbms_output.put_line('OK!');
  8    end if;
  9  end;
 10  /
OK!

PL/SQL procedure successfully completed.

SQL>

Now let us see if this object exists:

SQL> desc xx
ERROR:
ORA-04043: object xx does not exist

So far so good. Let's wide example and try to run IsSqlValid against previously created xx table (now should be some errors raised!):

SQL> create table xx (c1 varchar2(3));

Table created.

SQL> declare
  2    o_msg varchar2(1000);
  3  begin
  4    if not isSqlValid('create table xx (c1 varchar2(3))',o_msg) then
  5      dbms_output.put_line(o_msg);
  6    else
  7      dbms_output.put_line('OK!');
  8    end if;
  9  end;
 10  /
ORA-00955: name is already used by an existing object

PL/SQL procedure successfully completed.

SQL>

Work as hell!

But I still haven't tested if DDL in isSqlValid function has any influence on any previously started transaction (as DDL really do if you perform them normally). Event thought I do think it should not have in percussion, such a task use on your risk...

Cheers!

Query by Form (QBF) for Apex

We are all aware of very nice feature in Oracle forms called "Query by Form" where user has ability to enter several patterns which automatically generate filter in data search. From a time when I was a programmer on Apex 2.2, one of mine primary problems was to solve search capability in a as far as possible easiest way. Problem is how to generate SQL query against entered values from user input for any form and any table. Second target was a request that implementation must be easy and compatible with any present and incoming Oracle version. This is how mine QBF for Apex was invented.
So similar feature was a nice to have in Apex. In Apex 3.x there is a component "Interactive Report" with large functionality and customization and very great query capabilities. For all of you that use older version of Apex or because of some other reasons do want to use "Interactive Report", I’ll show you mine solution which I have called Query By Form which was in the time when I created an enhanced version of QBF from Oracle web site.

Implementation

The main idea was to make a solution that can automatically and quickly be implemented on any Apex page.

1. On page create HTML region
2. In that region place "Edit Text” controls which will represent filter for query. Each "Edit Text" control must have name in UPPER CASE and named in a form:
   
   P+PageId_FieldName
   
   Example:
   
   P1_EMPNO P1_ENAME P1_SAL P1_DEPTNO
   
3. According that, QBF can search through page (defined with PageId) and look if there is a control with such a name. If exists, QBF knows that this control define value for searching that Field in Table/Viev and put in search part of resulting query. So names standard is need that function can search through the form and look for certain controls.
   For date fields range can be used. For such fields field items on WEB page name must have "_to" and "_from" suffixes. Order of WEB items is not important!
   Example:
   
   P108_DATE_BIRTH_FROM
   P108_DATE_BIRTH_TO
   
   where "P" is constant and "108" is PageId, "DATE_BIRTH" is field in table and "_from" and "_to" determine date range.
4. In HTML region, where previous controls are placed, one "Radiogroup" control must exist. It’s purpose is to define the way patterns will be used in query. Name (in UPPER CASE also) must be defined as
   
   P+PageId_SEARCH_TYPE
   
   Example:
   
   P1_SEARCH_TYPE
   
5. Place a report based as "SQL Query (PLSQL function body returning SQL Query)" (usually bellow the HTML region with search condition). In "region Source" place following code:
   
   declare
     l_sql varchar2 (32567 char);
   begin
     l_sql := qbf (
            ln_page_id=>:APP_PAGE_ID
           ,ls_table=>'EMP'
           ,ls_select=>'SELECT EMPNO   
                    ,ENAME
                    ,JOB
                    ,MGR
                    ,HIREDATE
                    ,SAL
                    ,COMM
                    ,DEPTNO'
           ,ln_search_type=>:P1_SEARCH_TYPE
           ,ls_date_format=>'DD-MON-RR');
     return l_sql;
   EXCEPTION
     WHEN OTHERS THEN
       -- some logging here?
       RAISE;
   end;
   

You can also try live example of QBF in action on Apex demo web page. The same application can be downloaded as an export Apex file.

How it works

Regardless function has 7 parameters only first two are required:

create or replace FUNCTION QBF (
        ln_page_id     NUMBER
      , ls_table    IN VARCHAR2
      , ls_select   IN VARCHAR2 DEFAULT NULL
      , ls_where    IN VARCHAR2 DEFAULT NULL
      , ls_order_by IN VARCHAR2 DEFAULT NULL
      , ln_search_type IN NUMBER DEFAULT 3
      , ls_date_format IN VARCHAR2 DEFAULT 'DD.MM.YYYY'
      , ls_scope_mode IN VARCHAR DEFAULT 'U'
);

• ln_page_id-Apex page number where report is placed
• ls_table-name of query table/view which is defined with ls_scope_mode parameter
• ls_select-which fields you want to show in report. If is null all fields from ls_table in definition order are shown
• ls_where-initial where part which is not compound automatically from the page but you want to have it in query. Example:
  
  where userid IN ('USER_A', 'USER_B') and
  
  what would at the end produce return WHERE part as:
  
  where userid IN ('USER_A', 'USERB') and P1_EMPNO like ‘9%’
  
  where P1_EMPNO like ‘9%’ part was generated by QBF from field from Apex page
• ls_order_by-field names which define order part of resulting SQL
• ls_date_format-date format in which query will be performed
• ln_search_type-how patterns are understood
  
  • 1-means search in all fields with
    
    UPPER("%search_value%") type
    
    This is default option
  • 2-Start with defined pattern
    
    UPPER("search_value%") type
    
  • 3-Original Oracle based search which uses SQL search characters ("%" and "_") and sample is as is (no upper values are used)
• ls_scope_mode-represent which dictionary view will be queried for finding source field names. Possible values are "U" (define USER_TAB_COLUMNS view–default) and "A" (define ALL_TAB_COLUMNS view)
  Code for that checking is very easy:
  
  FOR x IN (
       SELECT column_name, data_type FROM USER_TAB_COLUMNS WHERE table_name = UPPER(ls_table) AND ls_scope_mode = 'U'
     UNION
       SELECT column_name, data_type FROM ALL_TAB_COLUMNS WHERE table_name = UPPER(ls_table) AND ls_scope_mode = 'A') 
  

When user enter some search patterns and click on submit button, these values are passed to server where QBF function process them. QBF according previously explained names rules, search on whole Apex page for existence of such an field name. If it finds it, it place it in where condition according ln_search_type parameter.
After queering on all possible fields finally function adds (if is anything to add) all passed parameters that define on SQL sentence which come after "where" part.

Benefits

• Acclimatization especially generating on many forms. Less inconsistency on presentation part. Pages are very standardized and looks the same. Easy learning curve for end user.
• As you can see implementation is pretty straight forward and I was able to create more complex WEB pages (http://www.skdd.hr/portal/f?p=100:23 or http://www.skdd.hr/portal/f?p=100:41) in less then 5 minutes.
• If generated query is not OK (parsing is happened on client side only), in QBF function source code there is already implemented part which in debug mode prints some additional information which will help you to solve the problem, quicker. Look for V('DEBUG') = 'YES' part in QBF source.
  
  
• QBF method is bug free and with some extra coding you can prevent any SQL error.
  This can be done by adding IsSQLValid function which can be implemented in "Conditional Display" as main method to run Report (for true results) or show some message region (for false as a result).
  In this example this function doesn’t exists so function IsSQLValid will be explained in my next post IsSQLValid function-check your queries before you run them in a few days.
• Because report is based on data got from server side some additional security is implemented in the same way.

Deficits

• Only on standard column types (date, number/integer, varchar2 and char) are supported.
• Only "Edit Text" and Combox controls can be used as input values for query (no radiogroup or other type are not allowed in this version)
• No lookup values are possible to be used as source pattern
• Operator between all search patterns is AND.
• There is no "null" based operation possible from the user side-but can be added in WHERE part. is null or is not null are supported in combobox with -100 value which is treat as null.
• Required search condition (field that must have value to avoid full table scan) - easy to implement adding at the end of control name "XX" for required or "YY" for non required value. This is not not implemented!

Conclusion

Friend of mine, Denes Kubicek, has created on his Apex demo site his view of the same solution in a different approach.

I’m interested if anyone make any improvement in the future to notice me as well and share the Apex knowledge wider. I think QBF has potential for that.

Improvements

In the meantime I have added ls_date_format as parameter so now any date range search is 100% customizable according user date format and search condition (look at the picture)

SQLA-Top SQLs in HTML

Need to analyze SQL performance in your database with html output without additional codding? Tired of STATSPACK or too much information for your need? Do not have license for Oracle AWR? Still want to get quick results?

Maybe this is right tool for you-SQLA!

How it starts?

Somewhere in the same time (precisely 21-NOV-2003 as written is in the file header of most source code) as AWR was coming to our lives, in the same time when STATSPACK ruled in most Oracle instances, group of Oracle core programmers introduced new way of analyzing and presenting top SQLs-SQLA. SQLA stands for SQL Area, Plan and Statistics for Top DML!!

However such a reports were not ideal because beside straight result, creating any additional information (sql plan for an example) was not so easy to implement. The worst comes in presentation level where plain text files are really something not too easy to maintain as html for an example. Remember that STATSPACK was in that time best in plain output-half formatting "report like" text file. This is where SQLA takes place...

We all know many ways to find Top SQLs in our database. here is one example which shows Top 10 SQLs order by buffer_gets:

set linesize 170;
set pagesize 80;
col sql_text for a80;
SELECT * FROM (
select round(buffer_gets/(executions+1)) as buffer_gets_per_exec,
       executions,
       buffer_gets,
       elapsed_time,
       elapsed_time / executions time_per_exec,
       sql_text
  from v$sqlarea
where buffer_gets/(executions+1) >= 20
   and executions >= 1
order by buffer_gets desc
)
WHERE ROWNUM<=10
;

and the output is like (shorten output version):

BUF_PER_EXEC	EXEC	BUF_GETS	SQL_TEXT
------------	-----	--------	--------------------------------------------------
233337	1	466674	delete from sys.wri$_optstat_histgrm_history where nvl(:1, savtime) <= savtime    and savtime < nvl(:2, savtime+1)    and obj# = nvl(:3, obj#)    and intcol# = nvl(:4, intcol#)
113171	1	226341	delete from WRH$_SYSMETRIC_HISTORY tab    where (    :beg_snap <= tab.snap_id          and tab.snap_id <= :end_snap          and dbid = :dbid)         and not exists (           select 1              from WRM$_BASELINE b             where (tab.dbid = b.dbid)               and (tab.snap_id >=b.start_snap_id)              and (tab.snap_id <= b.end_snap_id)          )
…
10788	2	32364	BEGIN prvt_advisor.delete_expired_tasks; END;
SQL>

How it works?

Basically SQLA runs query against V$SQLAREA, V$SQL_PLAN, V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA views for retrieving "Top x" DML commands according to BUFFER_GETS and DISK_READS (logical and physical reads).
Once it identifies top SQL in terms of logical or physical reads (usually top 10 or something like that), it reports the actual SQL statements (as full text), as well as their explain plan. If "execution plan" if used (in 9i and certainly not in 8i this is not mandatory way of executing sql!) SQLA can run on Oracle 9i but Oracle 10g is also supported (especially Oracle XE-free version). Oracle 11g (which I haven’t tested) should also be in the list-I do not see any problems but for real results need additional testing. It also reports CBO stats for accessed objects and indexed columns.
When used on an Oracle Apps database, it may report the actual application user who issued the expensive SQL through a FORM or Concurrent Program.

How to install?

1. Unzip file SQLA.rar into dedicated directory on db server preserving case on all scripts names in UPPER CASE (i.e. SQLAREAT.SQL).
2. Run sqlplus from that directory all the time.
   
3. If you are using script SQLAREAT.SQL for the first time, connect as main application user (APPS if using Oracle EBS or any other privileged user in all other cases whith access to most schema objects
   including DBA and V$ views) and execute:
   
   sqlplus apps/apps_passwd (priv_user/priv_passwd);
   
   Use that kind of starting point for all other scripts running SQLA.
   
   SQL> START SQLACREA.SQL;
   
   This step creates a staging repository that is used by SQLAREAT.SQL.
   If not sure if the staging repository has been created or not, simply execute SQLACREA.SQL and it will re-create it.
4. If you get PLS-00201 errors, execute SQLAGRNT.SQL as SYSTEM, SYS or INTERNAL

How to use?

1. Once the staging repository is created, execute
   SQL> START SQLAREAT.SQL;
2. SQLAREAT.SQL creates an HTML spool file with most expensive SQL. Execute this script manually or within a cron job.
   Suggested frequency is every 15 minutes during peak time (high system load window). Executing this script in cron job is very similar idea like one expressed in my previous blog topicwhich deals with pure AWR reports Automated AWR reports in Oracle 10g/11g
3. If SQLAREAT.SQL is used over a period of time (i.e. peak hours), use included SQLAREAR.SQL scipt to extract most expensive SQL observed during a range of snapshots captured previously by previously mentioned SQLAREAT.SQL
   SQL> START SQLAREAR.SQL
   i.e.
   SQL> START SQLAREAR.SQL LR 1 4;
   Where p_process_type is LR for logical reads or PR for physical reads.
4. Regardless it is not a real parameter, p_top is hard coded in SQLAREAT.SQL script itself to default value "10". This means that only top 10 SQLs will be extracted from range of snaps. Changing this parameter allows you to wide/narrow behavior of SQLA reports.
5. In addition to SQLAREAT.SQL and SQLAREAR.SQL, use the SQLAREAS.SQL if you want to report additional statistics
   SQL> START SQLAREAS.SQL;

How to uninstall?

1. If you need to uninstall this tool, execute commands below and remove scripts SQLA* from dedicated directory
   SQL> START SQLADROP.SQL
2. If you has previously executed SQLAGRNT.SQL, then in uninstall process use SQLAREVK.SQL for proper when uninstalling

For the end...

In the time I was writing this post I realize that Note:238684.1 from Oracle support, which was the basic link where you can see all original information about SQLA (as well as how to download them) are no longer available. Instead of this I saw short notice:
   Deprecated. Use AWR instead.
So this post is even more interesting because there is no official download now. For many of us...old fashioned DBA or "poor" Oracle user...not to mentioned again Oracle XE users...
P.S.
In the time of writing this post I was not aware of any Oracle copyright or licensing on this product. However use it on your own responsibility or in agreement with official Oracle dealer.

Cheers!

Oracle Metalink is dead-long live Oracle support!

After years of wandering, Oracle has finished it with newest version of Oracle Metalink...pardon Oracle Support. Many of us has different opinions about this version, but have to admit that this is what we have to live with in the future. There are many changes and here they are in brief topics which I use as quick FAQ.

Q: Why am I unable to login to My Oracle Support?

As part of the upgrade and migration to My Oracle Support users may experience login issues if they have not verified or created your Oracle Single Sign-On (SSO) account. Users who logged into Classic MetaLink / My Oracle Support before the upgrade were prompted to verify that they had an SSO account or create a new one.
If users do not have or do not know if they have an SSO account, follow the instructions in the My Oracle Support - Registration FAQ for creating/verifying the SSO account. If creating a new account, the user will go through a re-registration process. During re-registration make sure that the new SSO email address is the same as the user's Classic MetaLink email address. This will ensure they continue to have the same CSIs in your profile and visibility to Service Requests as prior to the upgrade. Please pass along the link above to your colleagues if they are having issues logging in.

Q: After verifying that I have an SSO account, I receive an error when resetting my password saying that the username does not exist, what should I do?

If after verifying that your email address has an SSO account, the Password Finder tool gives you a message indicating that the username does not exist please call Global Customer Support and select the non-technical option. Indicate that when you verified your account your username already existed, but when you reset your password your username did not exist.

Q: What should I do if I have an SSO account but am still receiving an error when trying to log in to My Oracle Support?

Some users may receive a "LoginId and Email don't match", "CallCenterError", "NoSuchUserException", or "An internal error has occurred" message when logging in to My Oracle Support. Please call Global Customer Support and select the non-technical option, and specify the error code to the support representative to enable your account.

Q: What should I do if I see an "Email changing in progress" message when logging in?

Your registration, email change or password change is in progress. If you receive this message for more than 10 minutes, you may need to call Global Customer Support for assistance.

Q: What should I do if my account does not have all my Support Identifiers (CSIs) associated to it and I cannot view my Service Requests?

You can add missing Support Identifiers back to your to profile by clicking More > Settings in the tab menu, then following these instructions:

1. Under the More tab, select Account & Privileges from the Settings area.
2. The Account & Privileges area appears populated with your account information.
3. Enter the Support ID that you want to add in the Type Support Identifier box.
4. Click Send Request.
5. Click Save.

After the Support Identifier has been approved by your Customer User Administrator you will be able to view the Service Requests associated to it.

Q: What should I do if I see application and timeout errors when using My Oracle Support?

Some users are seeing application errors, such as 2032 and 7668277 errors, and timeout errors. Oracle is triaging these for root cause and solution. If you encounter one of these errors, please report it to Oracle Global Customer Support by using the Contact Us link at the top of the screen.

Q: Why can't I find my bookmarks from Classic MetaLink?

Your Classic MetaLink bookmarks have been migrated to My Oracle Support as Favorites. The Favorites feature provides the same access to the Knowledge articles and SRs that you bookmarked in Classic MetaLink. In addition, Favorites enables users to flag additional items such as bugs, systems, targets, and projects.
Note: The migration of Bookmarks to Favorites depends on validation of the customer against their Oracle SSO account. Customers who did not create their SSO account before November 7 will not see their Bookmarks (now Favorites) initially. We are planning to run a weekly script to move Bookmarks to Favorites each week for approximately six weeks after the migration date. Customers who have registered or validated their SSO account after November 7 will see their old Bookmarks reappear as Favorites as we pick up the newly validated customers each week.

Q: Is a non-Flash version of My Oracle Support available?

Customers who require access to My Oracle Support in an environment that does not have Flash Player or who have accessibility needs, can use the HTML Option. The HTML option includes a subset of functionality found on the Flash version. For details on what is included in the HTML option, please see the My Oracle Support FAQ.

Q: How do I view the old Service Request number on my Service Request?

As part of Oracle's move to a single system for tracking SRs, SR numbers were changed.
You can view the Classic MetaLink SR number in the Legacy SR Number field in the SR views on My Oracle Support. To add the Legacy SR Number field to the Service Request list region click the table icon in the upper left hand corner of the list view. Select Columns > Legacy SR. You may sort on this field by clicking the column header.

Q: Why don't I see my Service Request profiles from Classic MetaLink?

Service Request Profiles were not migrated from Classic MetaLink. To create new SR profiles, click on More > Settings in the tab menu and select the Service Request Profiles section.

Q: How can I receive email notifications about SRs, Bugs, and Knowledge documents (by Products and Platforms) that I monitored through Classic MetaLink (through My Headlines) and My Oracle Support (through Hot Topics)?

You should continue to receive Hot Topics notifications that you set up in My Oracle Support before the upgrade. You must create new Hot Topic notifications for any notifications that you created in Classic MetaLink in the My Headlines section. To create new Hot Topics notifications click on More > Settings in the tab menu and select the Hot Topics E-Mail section.

For the end once more Oracle Metalink is dead. Long live Oracle support!

Cheers!

The Most Popular Articles and Downloads of 2009 by OTN

Today I get on mail Top 10 most popular articles and downloads in 2009. Here they are in brief listing:

1. Installing Oracle Enterprise Manager 10g Grid Control Rel 5 on Oracle Database 11g and Linux, by Mike Revitt
2. High-Performance Oracle JDBC Programming, by Yuli Vasiliev
3. Oracle RMAN Backups: Pushing the "Easy" Button, by Porus Homi Havewala (Oracle ACE Director)
4. Tom Kyte: On Dynamic Sampling (from Oracle Magazine)
5. Scripting Oracle RMAN Commands (from Oracle Magazine), by Arup Nanda (Oracle ACE Director)
6. Oracle Enterprise Manager Grid Control Architecture for Very Large Sites, by Porus Homi Havewala (Oracle ACE Director)
7. Taking an Oracle ADF Application from Design to Reality, by Chris Muir (Oracle ACE Director) and Penny Cookson (Oracle ACE)
8. Tom Kyte: On Constraints, Metadata, and Truth (from Oracle Magazine)
9. High Performance and Availability with Oracle RAC and PHP, by John Lim
10. Oracle ADF Development Essentials, by John Stegeman (Oracle ACE Director)

Full article with links to their interesting work may be found here.

Regardless I do not see me or any of people that I know, congratulations to all the authors. BRAVO!!
;-)

Kill session on EBS environment

In Oracle EBS environment, single user has multiple session in all most every moment of its work. So if you want to see details about it or has to kill all of them, finding out is crucial for that task.

Next query can give you answer about current users sessions:

/* ---------------------------------------------------------------------------
 Filename: apps_sess.sql
 CR/TR#  :

 Purpose : Shows all active session on Oracle EBS 11.5.10.2 (10gR2)

 Date    : 19.08.2007.
 Author  : Damir Vadas

 Remarks : Run as APPS or privileged user

 Changes (DD.MM.YYYY, Name, CR/TR#):
--------------------------------------------------------------------------- */

SELECT DISTINCT * FROM (
        select
              usr.user_name user_name
             ,ses.sid||','||ses.serial# sid_serial
        from
             apps.icx_sessions i
            ,apps.fnd_logins l
            ,apps.fnd_appl_sessions a
            ,apps.fnd_user usr
            ,gv$process v
            ,gv$session ses
        where i.disabled_flag = 'N'
          and i.login_id = l.login_id
          and l.end_time is null
          and i.user_id = usr.user_id
          and l.login_id = a.login_id
          and a.audsid = ses.audsid
          and l.pid = v.pid
          and l.serial# = v.serial#
    UNION
        select
           usr.user_name
          ,ses.sid||','||ses.serial#
        from
           apps.fnd_logins l
          ,apps.fnd_login_responsibilities r
          ,apps.fnd_user usr
          ,gv$process v
          ,gv$session ses
        where l.end_time is null
          and l.user_id = usr.user_id
          and l.pid = v.pid
          and l.serial# = v.serial#
          and v.addr = ses.paddr
          and l.login_id = r.login_id(+)
          and r.end_time is null
          and r.audsid = ses.audsid
    UNION
        select
           usr.user_name
          ,ses.sid||','||ses.serial#
        from
           apps.fnd_logins l
          ,apps.fnd_login_resp_forms f
          ,apps.fnd_user usr
          ,apps.fnd_form_tl frm
          ,apps.fnd_form_functions ff
          ,gv$process v
          ,gv$session ses
        where l.end_time is null
          and l.user_id = usr.user_id
          and l.pid = v.pid
          and l.serial# = v.serial#
          and v.addr = ses.paddr
          and l.login_id = f.login_id(+)
          and f.end_time is null
          and f.form_id = frm.form_id(+)
          and f.form_appl_id = frm.application_id(+)
          and f.audsid = ses.audsid
          and ff.form_id = frm.form_id
    UNION
        select
          fu.user_name
         ,vs.SID || ',' || vs.serial#
        FROM
           APPS.fnd_concurrent_requests cr,
           v$process vp,
           v$session vs,
           apps.fnd_user fu
        WHERE 
               cr.phase_code <> 'I'
           AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
           AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
           AND cr.oracle_process_id = vp.spid (+)
           AND cr.oracle_session_id = vs.audsid (+)
           AND fu.user_id = cr.requested_by
           AND vs.sid is not null
)
ORDER BY 1,2
;

If you want to kill them then you will have to add sligthly different start of the script:

SELECT DISTINCT 'ALTER SYSTEM DISCONNECT SESSION '''|| sid_serial || ''' IMMEDIATE ;' kill_me FROM (
select
      usr.user_name user_name
     ,ses.sid||','||ses.serial# sid_serial
from
     apps.icx_sessions i
    ,apps.fnd_logins l
...

...
   AND cr.oracle_process_id = vp.spid (+)
   AND cr.oracle_session_id = vs.audsid (+)
   AND fu.user_id = cr.requested_by
   AND vs.sid is not null
)
ORDER BY 1
;

Please keep in mind that here is intentionally excluded where part:

usr.user_id >= 1110

which doesn't exclude some system and priority users, so in real case you may add that additional condition in every sub query.

Result will be something like:

kill_me
------------------------------------------------------------------------------------
ALTER SYSTEM DISCONNECT SESSION '1006,8580' IMMEDIATE ;                                                                       
ALTER SYSTEM DISCONNECT SESSION '1012,16446' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1063,16467' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1075,12361' IMMEDIATE ;                                                                      
...

...
ALTER SYSTEM DISCONNECT SESSION '1689,13000' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '1699,45171' IMMEDIATE ;                                                                      
ALTER SYSTEM DISCONNECT SESSION '955,3624' IMMEDIATE
;

While executing previous commands you get any kind of error (I have seen such a cases) repeat it with kill session command!
For an example:

ALTER SYSTEM KILL SESSION '1699,45171' IMMEDIATE ;

Reducing query output

Previous example gives you disconnect session command for all active sessions. This situation is in praxis very rare.
In real life you will have to reduce it to some specific user (or group of users). For that you have to add in all of sub queries next where condition:

upper(user_name) in ('USER1','USER2')

In this example this will reduce output for only 2 users-USER1 and USER2.

"Temporary disable APPS users" story

If you have read my topic Temporary disable APPS users, there might be a need to terminate running session from all users except some of them. In that topic I use xxhy_table_ch table which holds the list of users who should not be killed. According that this script should look like:

SELECT DISTINCT 'ALTER SYSTEM DISCONNECT SESSION '''|| sid_serial || ''' IMMEDIATE ;' kill_me FROM (
    select
          usr.user_name user_name
         ,ses.sid||','||ses.serial# sid_serial
    from
         apps.icx_sessions i
        ,apps.fnd_logins l
        ,apps.fnd_appl_sessions a
        ,apps.fnd_user usr
        ,gv$process v
        ,gv$session ses
    where i.disabled_flag = 'N'
      and i.login_id = l.login_id
      and l.end_time is null
      and i.user_id = usr.user_id
      and l.login_id = a.login_id
      and a.audsid = ses.audsid
      and l.pid = v.pid
      and l.serial# = v.serial#
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
       usr.user_name
      ,ses.sid||','||ses.serial#
    from
       apps.fnd_logins l
      ,apps.fnd_login_responsibilities r
      ,apps.fnd_user usr
      ,gv$process v
      ,gv$session ses
    where l.end_time is null
      and l.user_id = usr.user_id
      and l.pid = v.pid
      and l.serial# = v.serial#
      and v.addr = ses.paddr
      and l.login_id = r.login_id(+)
      and r.end_time is null
      and r.audsid = ses.audsid
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
       usr.user_name
      ,ses.sid||','||ses.serial#
    from
       apps.fnd_logins l
      ,apps.fnd_login_resp_forms f
      ,apps.fnd_user usr
      ,apps.fnd_form_tl frm
      ,apps.fnd_form_functions ff
      ,gv$process v
      ,gv$session ses
    where l.end_time is null
      and l.user_id = usr.user_id
      and l.pid = v.pid
      and l.serial# = v.serial#
      and v.addr = ses.paddr
      and l.login_id = f.login_id(+)
      and f.end_time is null
      and f.form_id = frm.form_id(+)
      and f.form_appl_id = frm.application_id(+)
      and f.audsid = ses.audsid
      and ff.form_id = frm.form_id
      and usr.user_name not in (select c1 from xxhy_table_ch)
      and usr.user_id >= 1110
UNION
    select
      fu.user_name
     ,vs.SID || ',' || vs.serial#
    FROM
       APPS.fnd_concurrent_requests cr,
       v$process vp,
       v$session vs,
       apps.fnd_user fu
    WHERE 
           cr.phase_code <> 'I'
       AND (cr.phase_code < 'C' OR cr.phase_code > 'C')
       AND cr.status_code NOT IN ('U', 'X', 'D', 'E', 'I', 'C')
       AND cr.oracle_process_id = vp.spid (+)
       AND cr.oracle_session_id = vs.audsid (+)
       AND fu.user_id = cr.requested_by
       AND vs.sid is not null
       AND usr.user_name not in (select c1 from xxhy_table_ch)
       AND usr.user_id >= 1110
)
ORDER BY 1
;

Hereby, shown SQL is fully non RAC version. For extended information how to kill session on RAC environment, please read the whole "How to" on my previous blog Blocking session ... detect, unblock, notify.

As you can see with this part of story Temporary disable APPS users is finished and completed. Once again here is included usr.user_id >= 1110 condition which was mentioned in that blog topic as important.

Temporary disable APPS users

Idea for this topic came from my original post on Oracle APPS forum where I wanted to see from their gurus if there is something behind the bonnet about this issue.

Main problem is how to temporary disable unnecessary users a large number of users and still leave fully enable application for chosen one. This is real situation in a case of big roll outs or migration events, when some real APPS users need to have 100% of application usage to continue their normal work in APPS (as testing user for an example). And the best of all you do not have to restart any part of APPS...all is going online!

Our situation was even worse, because we have APPS based on several org_id's (represented here with set_of_books_id and we had to take care of those users as well. So you may find this approach pretty universal. This example was fully tested on Oracle EBS 11.5.10.2 for more then dozen times with 100% success.

Requirements

In my praxis I have very often need to temporary save some data. For that I use two kinds of pure Oracle tables defined as:

create table xxhy_table_ch (c1 varchar2(4000) );
create table xxhy_table_num (n1 number); 

As you can see this tables has just one column (VARCHAR2 and NUMBER). Create them in "XX" schema and grant all to APPS user, as you do in usual cases.

Before any work I need to cleanup them:

truncate table xxhy_table_ch drop storage;
truncate table xxhy_table_num drop storage; 

For some less experienced DBA let me express that all scripts must be run as APPS user for what you need certain level of security.

Regardless I wrote here these two steps as required, both of them can be avoid for core functioning but I find it suitable and shown them as mine approach.

Initialization

In this example I'll use mine list2table function, which can be found on of my previous post Treat lists as table. This function will allow me to save initial list of users in plain Oracle table, leaving that all other scripts has static values. Static scripts could be executed by any Oracle related person...not APPS DBA only, what reduce single point of failure in such a situations...

Next initialization step is to insert user names, which should stay enabled, in xxhy_table_ch table. This is the only part which must be changed because not all users are the same for every event so manual editing is needed.

insert into xxhy_table_ch
  SELECT * FROM TABLE(list2table('SCOTTT,JOHNYB,TOMP2,ALENG,DJINAZ,CARLM,ROBERTD')); 

As you can see, parameter for list2table function are user names that should stay enabled so change them as you need.

For the end of initialization it is very important to save(remember) the number of enabled users before we have modify their status. For saving this value I use my second xxhy_table_num table:

INSERT INTO xxhy_table_num
  SELECT count(*) 
     from HR.PER_ALL_ASSIGNMENTS_F P, FND_USER f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE);
COMMIT;

As you can see, in my case I had 3 different org_id's (3 set_of_books_id).

EBS system users

Users whose user_id is bellow 1110 are system ones and they should remain as they were. Really you do not want to mess with them!

SQL> select user_id, user_name 
  2  from fnd_user
  3  where user_id <= 1110;

   USER_ID USER_NAME
---------- ------------------------------
        -1  ANONYMOUS
         0  SYSADMIN
         1  AUTOINSTALL
         2  INITIAL SETUP
         3  FEEDER SYSTEM
         4  CONCURRENT MANAGER
         5  APPSMGR
         6  GUEST
         7  WIZARD
      1003  IEXADMIN
      1004  IBE_GUEST
      1005  OP_SYSADMIN
      1006  OP_CUST_CARE_ADMIN
      1007  ASGUEST
      1008  IBE_ADMIN
      1009  IBEGUEST
      1010  ASGADM
      1011  MOBILEADM
      1012  IRC_EXT_GUEST
      1013  IRC_EMP_GUEST
      1030  PORTAL30
      1031  PORTAL30_SSO
      1050  XML_USER
      1070  AME_INVALID_APPROVER
      1090  MOBADM
      1091  MOBDEV

26 rows selected.

SQL> 

For those who want to be more safe, manual writing number is also a good way...
;-)

Disable users

This is script which will disable all users except mentioned one:

update fnd_user
set end_date = SYSDATE-1
where user_id in
  (select user_id
     from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE)
  )
;
COMMIT;

It is also a good idea to save this number as well but I was not doing this. How ever slightly modify script (add returning clause which you have to save in xxhy_table_num table).

Now chosen users can work(test) with no worry that some other uninvited user may start unexpected concurrent or action... When all is done reverse process can be started....

Enable users

This is reverse script as one in the beginning.

update FND_USER
set end_date = null
where user_id in
  (select user_id
     from HR.PER_ALL_ASSIGNMENTS_F P, fnd_user f
    where upper(user_name) not in (select c1 from xxhy_table_ch) and 
          user_id >= 1110 and 
          assignment_id=employee_id  and  
          (set_of_books_id = '1001' OR set_of_books_id = '1002' OR set_of_books_id = '1003') and
          (EFFECTIVE_END_DATE > SYSDATE)
);
COMMIT;

After this check number of enabled user which was stored in xxhy_table_num first step. For that repeat SELECT count(*) part from the beginning, which is not covered here because it's simplicity.

"Synchronize WF LOCAL tables" concurrent

For the end, last required step is to run Synchronize WF LOCAL tables concurrent program as sysadmin with System Administrator responsibility, which will synchronize WF tables according some recent changes we have made before.

While you are disabling users you have some running session and want to terminate them, you can use scripts explained in my topic "Kill session on EBS environment".

Kill active APPS sessions

If you have need to kill all running APPS session on my blog Kill session on EBS environment you may find fully qualified information for that.

Cheers!