Did You Know? -- December 2002

By Hervé Deschamps, Oracle Corporation.


Database Admin

A quick way to check for the last time statistics were run in a given schema:
  1. connect to that scheman using SQL*Plus
  2. issue these:
col table_name format a35
select table_name, last_analyzed
from   user_tables;

Reports Server

When using the CGI, the reports are saved in the reports server cache directory:
806\reports60\server by default. You can find reports history there.

There is a handy config file for Reports Server: cgicmd.dat in 806\reports60\server. Make sure you leave blank lines between each commands or they will be ignored...


A few things I picked up on the Oracle FAQ about ODBC.
A working Oracle ODBC stack looks like this: 

      +--------------------+  Visual Basic, Excel, Access,
      |  ODBC Application  |  Oracle Power Objects, etc.
      |   Driver Manager   |  This is ODBC.DLL
      +--------------------+  This is the Oracle ODBC Driver
      |    ODBC Driver     |  (SQORA7x.DLL or SQO32_7x.DLL) or
      +--------------------+  driver for a different data source/ vendor
      +--------------------+  Oracle's SQL*Net (only for remote database)
      | Database Transport |  or other database transport
      +--------------------+  TCP/IP or
      |  Network Transport |  other protocol driver
      +--------------------+  (optional - only for remote connections)
      +--------------------+  Your Oracle database or
      |    Data Source     |  other data source you connect to

For Oracle's ODBC drivers, you need SQL*Net to connect to Oracle via ODBC.
Some multi-tier ODBC drivers (eg. Openlink and Visionware) do not require SQL*Net on the client as one has to install an 'ODBC server component' on the server. The ODBC drivers then use the underlying network protocol (eg. TCP/IP) without using SQL*Net.
The Oracle ODBC drivers can be downloaded, free of charge, from the Oracle home page.
There is also a free, open source ODBC driver available from http://www.easysoft.org/.

To define a data source, open the ODBC manager by double clicking on the ODBC icon in the Control Panel. Select the "add" button, then select the ODBC Driver for the database you want to connect to. The data source definition screen will appear. Define the data source name and other information as it pertains to your configuration.

Reports Server

There are 4 important layers in Reports Server architecture which should be understood in order to clearly understand the error "Unable to communicate with the Reports Server" :

  1. The Runtime Engines : This is the main layer where the reports are actually executed. On unix systems there are "rwrun60" and on NT systems there are "rwrun60.exe" processes.
  2. The Reports Server : The Reports Server recieves the job request and queues it. When one of its runtime engines becomes available, it sends the command line to that runtime engine for execution. On Unix systems it is the "rwmts60" executable and on NT systems it is "rwmts60.exe".
  3. The Reports CGI : The Reports Web CGI passes the request to the Reports Server. The Reports CGI or Servlet converts the request to a command line that can be executed by the Reports Server and submits it to the specified Reports Server. On Unix platforms this executable is "rwcgi60" and on NTs it is "rwcgi60.exe.
  4. The Reports Servlet : Its functionality and function is just the same as "Reports CGI", but it is a program written in Java as difference to the CGI program (which is written in C programming language). .

CGI looks for reports server using TNSNAMES and URL. The trick is ... which TNSNAMES?
The answer is ... the one in the last oracle home... so if installed other product after reports server, don't forget to make sure that the TNS data for the reports server will be in there.

To use the Oracle Apps views from SQL*Plus

To select data from Oracle Apps views it is necessary to initialize an environment by placing oneself in the context of an organization, reponsibility and user.

You can do this with the following command:
which is information that you can find in menu Help->Diagnonis->Examine

Performance Collection Services

To activate Performance Collection Services, include `record=performance' in the command line argument (for runtime in a client/server environment), or as a part of `serverArgs' parameter in the HTML file (for web deployment).

For example, if running in the client/server mode, use:

ifrun60  module=.. userid=.. record=performance log=yourlogname

The results are written onto the file yourlogname. If the file name is not specified, a file with a unique filename is created. This name is in the format `perf_xxxx' where `xxxx' is the ProcessId of the runtime process running.

In the HTML file, this invocation will be:

<param name= "serverArgs" value = "module=.. userid=.. record=performance

The following events are collected by Performance Services:

The Forms Server will create a log file if you start the server using the log option, as follows:

ifsrv60 -install Forms60Server log=<\PathName\LogFileName> port=<portNum>

Importing Java in Forms

The Java Importer allows Forms developers to generate PL/SQL packages to access Java classes and then program with the generated PL/SQL in their Forms applications. The PL/SQL generated by the Java Importer is robust, offering support for the original Java class' constructors, methods, and fields.

Beyond simply mapping static methods to PL/SQL functions and procedures, the Java Importer provides support for persistent Java objects, with support for type mapping and array objects.

Forms developers can conveniently access the imported Java through the generated PL/SQL using the new ORA_JAVA package and its built-ins. Internally, the generated PL/SQL packages use the Java Native Interface (JNI) standard and an internal JNI package to act as the bridge between PL/SQL and Java.

The Java Importer is installed with Forms 6i Release 2. When installed, the JAR file containing the Java Importer, importer.java, must be in CLASSPATH. By default, the filesystem location for importer.java is ORACLE_HOME/TOOLS/COMMON60/JAVA/importer.jar.

To enable importer operation, however, JDK or JRE 1.2 or higher must be installed on both the builder and server machines.

Resource Management on an Oracle Database

Resource management consists in defining rules that gauarantee a certain percentage of CPU to certain groups of users. This is useful if you don't want that people running heavy queries on a transactional database to grind the system to a halt for others (100% CPU).  If you do not want to or cannot use Enterprise Manager, here are a few queries that show you the main data dictionary elements for this.

 select plan, cpu_method, status from dba_rsrc_plans;
 -------------- ------------- --------------

Resource consumer groups
 select consumer_group, cpu_method, status,mandatory
 from dba_rsrc_consumer_groups;
 ------------------------ -------------- ----------- ---
 SYS_GROUP                ROUND-ROBIN    ACTIVE       NO
 LOW_GROUP                ROUND-ROBIN    ACTIVE       NO
Resource plan directives
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status
from dba_rsrc_plan_directives
order by 8,1,2,3,4,5,6;
 -------------- ----------------- -------------- ------- ------- ------- ------- -------------
 SYSTEM_PLAN    LOW_GROUP         CONSUMER_GROUP       0       0     100       0 ACTIVE
 SYSTEM_PLAN    OTHER_GROUPS      CONSUMER_GROUP       0     100       0       0 ACTIVE
 SYSTEM_PLAN    SYS_GROUP         CONSUMER_GROUP     100       0       0       0 ACTIVE
 select * from dba_rsrc_consumer_group_privs;
 GRANTEE        GRANTED_GROUP                  GRA INI
 -------------- ------------------------------ --- ---
 PUBLIC         LOW_GROUP                      NO  NO
 SYSTEM         SYS_GROUP                      NO  YES

Default consumer group for users
 select username, initial_rsrc_consumer_group from dba_users;
 ------------- ------------------------------
 SYS           SYS_GROUP
Note that both SYS and SYSTEM are assigned to the resource consumer group SYS_GROUP. This consumer group is assigned 100% CPU at level 1. It is of course very important that all DBA users have sufficient CPU allocated.

Hervé Deschamps is a Technical Manager with Oracle Corporation. Over the years he has developed a number of applications using Oracle Development Tools and others.  He has an extensive experience of all phases of the development life cycle. He is also known in the technical community for his article publications centered around best custom development practices and for his user group presentations. You can reach him by e-mail at herve.deschamps@.oracle.com. He also maintains a web site full of articles, scripts, tips and techniques at http://www.iherve.com.