Friday May 28, 2010

Oracle RDBMS : Flushing a Single SQL Statement out of the Object Library Cache

It is well known that the entire shared pool can be flushed with a simple ALTER SYSTEM statement.


SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

What if the execution plan of a single SQL statement has to be invalidated or flushed out of the shared pool so the subsequent query execution forces a hard parse on that SQL statement. Oracle 11g introduced a new procedure called PURGE in the DBMS_SHARED_POOL package to flush a specific object such as a cursor, package, sequence, trigger, .. out of the object library cache.

The syntax for the PURGE procedure is shown below.


procedure PURGE (
        name VARCHAR2, 
        flag CHAR DEFAULT 'P', 
        heaps NUMBER DEFAULT 1)

Explanation for each of the arguments is documented in detail in $ORACLE_HOME/rdbms/admin/dbmspool.sql file.

If a single SQL statement has to be flushed out of the object library cache, the first step is to find the address of the handle and the hash value of the cursor that has to go away. Name of the object [to be purged] is the concatenation of the ADDRESS and HASH_VALUE columns from the V$SQLAREA view. Here is an example:


SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

ADDRESS 	 HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';

no rows selected

Note to Oracle 10g R2 Customers

The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

10.2.0.2 and 10.2.0.3 customers can download and install RDBMS patch 5614566 to get access to these enhancements in DBMS_SHARED_POOL package.

Also see:

Tuesday Feb 02, 2010

Extracting DDL Statements from a PeopleSoft Data Mover exported DAT file

Case in hand: Given a PeopleSoft Data Mover exported data file (db or dat file), how to extract the DDL statements [from that data file] which gets executed as part of the Data Mover's data import process?

Here is a quick way to do it:

  1. Insert the SET EXTRACT statements in the Data Mover script (DMS) before the IMPORT .. statement.

    eg.,
    
    % cat /tmp/retrieveddl.dms
    
    ..
    SET EXTRACT OUTPUT /tmp/ddl_stmts.log;
    SET EXTRACT DDL;
    ..
    
    IMPORT \*;
    
    

    It is mandatory that the SET EXTRACT OUPUT statement must appear before any SET EXTRACT statements.

  2. Run the Data Mover utility with the modified DMS script as an argument.

    eg., OS: Solaris

    
    % psdmtx -CT ORACLE -CD NAP11 -CO NAP11 -CP NAP11 -CI people -CW peop1e -FP /tmp/retrieveddl.dms
    
    

    On successful completion, you will find the DDL statements in /tmp/retrieveddl.dms file.

Check chapter #2 "Using PeopleSoft Data Mover" in Enterprise PeopleTools x.xx PeopleBook: Data Management document for more ideas.

---

Updated 07/16/2010:

It appears PeopleSoft introduced a bug in Data Mover functionality on \*NIX platforms somewhere in PeopleTools 8.49 releases. If Data Mover repeatedly fails with "Error: Unable to open OUTPUT: " when extracting statements or actions using "SET EXTRACT", run the same DMS script on any Windows system as a workaround. For more information, check Oracle Support Document "E-DM: 'Error: Unable to open OUTPUT:' when attempting to extract DDL with Data Mover (Doc ID 887792.1)". From the same document: the fix to this bug on \*NIX platforms is targeted to fix after PeopleTools 8.51 release.

Wednesday Dec 23, 2009

Accessing MySQL Database(s) from StarOffice / OpenOffice.org Suite of Applications

This blog post is organized into two major sections and several sub-sections. The major sections focus on the tasks to be performed at the MySQL server and the \*Office client while the sub-sections talk about the steps to be performed in detail.

To show the examples in this exercise, we will be creating a new MySQL database user with user ID SOUSER. This new user will be granted read-only access to couple of tables in a MySQL database called ISVe. The database can be accessed from any host in the network. ben10.sfbay is the hostname of the MySQL server.

Tasks to be Performed at the MySQL Server

This section is intended only for the MySQL Server Administrators. If you are an end-user, skip ahead to Tasks to be Performed at the Client side.

Create a new MySQL user and grant required privileges.

eg.,

% mysql -u root -p
Enter password: \*\*\*\*\*
Server version: 5.1.25-rc-standard Source distribution
..

mysql> CREATE USER SOUSER IDENTIFIED BY 'SOUSER';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> USE ISVe;
Database changed

mysql> show tables;
+--------------------------+
| Tables_in_ISVe           |
+--------------------------+
| CustomReport             | 
| CustomSQL                | 
| ISVeOldProjects          | 
| ISVeOrg                  | 
| ISVeProject              | 
| ISVeProjectExecution     | 
| ISVeProjectGoalAlignment | 
| ISVeProjectMiscInfo      | 
| ISVeProjectScoping       | 
| ISVeProjectStatus        | 
| ISVeProjects             | 
| ISVeProjectsVW           | 
| ISVeSearchLog            | 
| LastRefreshed            | 
+--------------------------+
14 rows in set (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeOldProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON ISVe.ISVeProjects TO 'SOUSER'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for SOUSER;
+-------------------------------------------------------------------------------------------------------+
| Grants for SOUSER@%                                                                                   |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON \*.\* TO 'SOUSER'@'%' IDENTIFIED BY PASSWORD '\*8370607DA2602E52F463FF3B2FFEA53E81B9314C' | 
| GRANT SELECT ON `ISVe`.`ISVeOldProjects` TO 'SOUSER'@'%'                                              | 
| GRANT SELECT ON `ISVe`.`ISVeProjects` TO 'SOUSER'@'%'                                                 | 
+-------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> quit
Bye

Check the database connectivity and the accessibility from a remote location.


% mysql -h ben10.sfbay -D ISVe -u SOUSER -pSOUSER
Server version: 5.1.25-rc-standard Source distribution

mysql> show tables;
+-----------------+
| Tables_in_ISVe  |
+-----------------+
| ISVeOldProjects |
| ISVeProjects    |
+-----------------+
2 rows in set (0.03 sec)

mysql> select count(\*) from ISVeOldProjects;
+----------+
| count(\*) |
+----------+
|     2880 |
+----------+
1 row in set (0.04 sec)

mysql> select count(\*) from ISVeProjects;
+----------+
| count(\*) |
+----------+
|     4967 |
+----------+
1 row in set (0.33 sec)

mysql> delete from ISVeOldProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeOldProjects'

mysql> delete from ISVeProjects;
ERROR 1142 (42000): DELETE command denied to user 'SOUSER'@'vpn-192-155-222-19.SFBay.Sun.COM' for table 'ISVeProjects'

mysql> quit
Bye


Tasks to be Performed at the Client side (End-User's Workstation)


StarOffice and OpenOffice suite of applications can access the MySQL Server using JDBC or native drivers.

MySQL Connector/J is a platform independent JDBC Type 4 driver that is developed specifically to connect to a MySQL database. Using Connector/J, it is possible to connect to almost any version of MySQL Server from any version of StarOffice or OpenOffice.org

Sun|MySQL recently developed a native MySQL driver to facilitate connecting from StarOffice / OpenOffice.org suite of applications to a MySQL database. The new native driver is called MySQL Connector/OpenOffice.org. However the current version of the MySQL Connector for OO.o is compatible only with OpenOffice 3.1, StarOffice 9.1 or newer and it can connect only to MySQL Server 5.1 or later versions. This native connector is supposed to be faster in comparison with the Java connector.

We will explore both MySQL connectors in this section.

Note:
As an end user, you need not be concerned about the internal workings of these MySQL connectors. You just need to worry about installing and configuring the drivers so the \*Office applications can connect to the MySQL database in a seamless fashion.

I. Connector/J approach

  1. Installation steps for MySQL Connector/J

    Using the following navigation, find the location of the JRE that is being used by StarOffice / OpenOffice.org

    • Launch StarOffice / OpenOffice.org
    • Tools Menu -> Options
    • In the 'Options' window, StarOffice / OpenOffice.org -> Java

    Here is a sample screen capture from a Mac running StarOffice 9.

    In the above example, /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home is the location of the JRE. Here onwards, this location will be referred as JRE_HOME.

    1. Download the connector from the following web page:

              http://dev.mysql.com/downloads/connector/j/

      As of this writing, 5.1.10 is the current version for Connector/J

    2. Extract the driver and the rest of the files from the compressed [downloaded] archive

      eg.,
      
      % gunzip -c mysql-connector-java-5.1.10.tar.gz | tar -xvf -
      
      
    3. Locate the jar file that contains the driver --- mysql-connector-java-5.1.10-bin.jar, and copy it into the <JRE_HOME>/lib/ext directory with 'root' privileges.

      eg.,
      
      % sudo cp mysql-connector-java-5.1.10-bin.jar /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext
      
      % ls -l /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/\*connector\*jar
      /System/Library/Frameworks/JavaVM.framework/Versions/1.5.0/Home/lib/ext/mysql-connector-java-5.1.10-bin.jar
      
      
    4. Restart StarOffice / OpenOffice.org

    This concludes the installation of MySQL Connector/J.



    2. Configuration steps for Connector/J
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select JDBC by clicking on "Connect using JDBC (Java Database Connectivity)" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database using JDBC" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections.

      MySQL JDBC driver class text field will be automatically filled with the string com.mysql.jdbc.Driver. Leave that string intact, and click on "Test Class" button to make sure that the relevant class can be loaded with no issues. Unless the driver class is loaded successfully, you will not be able to connect to the MySQL database. In case of unsuccessful class loading, double check the installation steps for MySQL Connector/J.

      Click on "Next >>" button

      Note:
      In the above screenshot, notice that the "Name of the database" was filled with ISVe?zeroDateTimeBehavior=convertToNull (It is not completely visible in the above screen capture, but you just have to believe me). In this example, ISVe is the database name and zeroDateTimeBehavior is the configuration property which was set to a value of convertToNull. Without this configuration property, Connector/J throws an exception when it encounters date values such as 0000-00-00. In such cases, the error message will be something similar to java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date.

      Configuration properties define how Connector/J will make a connection to a MySQL server. The list of Connector/J configuration properties are documented in the following web page:

              http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html

      If you have more than one configuration property, you can define all of those properties in the "Name of the database" field. The syntax would be:
          <MySQL_DB_Name>?<Config_Property1=Value>&<Config_Property2=Value>&..&<Config_Propertyn=Value>

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.




    II Connector/OpenOffice.org approach

    MySQL Connector for OpenOffice.org is a MySQL driver for OpenOffice suite of applications. Even though it appears to be a native driver, MySQL Connector/OpenOffice.org has no implementation for the MySQL Client Server protocol. It is in reality a proxy on the top of MySQL Connector for C++ aka MySQL Connector/C++.

    Unlike MySQL Connector/J, Connector/OpenOffice.org has no dependency on JRE, and it can easily be installed using the OpenOffice.org Extension Manager. Due to the underlying native code, Connector/OpenOffice.org may outperform Connector/J in performance.

    1. Installation steps for MySQL Connector/OpenOffice.org

    Before installing the connector, make sure that you have OpenOffice.org 3.1 [or later] -OR- StarOffice 9.1 [or later] suite installed, and the version of the MySQL server on which the database is hosted is at least 5.1. If any of these requirements are not met, skip this entire section and check the I. Connector/J approach for the instructions that may work with your current versions of StarOffice / OpenOffice and MySQL server.

    1. Download the connector for your platform from the following location:

              http://extensions.services.openoffice.org/project/mysql_connector
    2. Launch StarOffice / OpenOffice.org

    3. Bring up the "Extension Manager" by clicking on Tools Menu -> Extension Manager ...

    4. Click on "Add" button, then locate the OpenOffice connector that you downloaded in step #1 (see two steps above). Click on "Open" button. The name of the connector will be something similar to mysql-connector-ooo-....oxt.

    5. Choose appropriate response to the question "For whom do you want to install the extension?". In this example, I chose the option "Only for me".

    6. Read the "Extension Software License Agreement" and accept the agreement to install the Connector/OpenOffice.org as an extension to StarOffice / OpenOffice.org

    7. Restart StarOffice / OpenOffice.org to complete the installation.



    2. Configuration steps for MySQL Connector/OpenOffice.org
    1. Launch StarOffice / OpenOffice.org

    2. In the Welcome screen, click on "Database". A database wizard pops up to help us create, open or connect to an existing database from StarOffice / OpenOffice.org.

    3. Since our main interest is only to connect to an existing database in this exercise, click on "Connect to an existing database" radio button and select "MySQL" from the drop-down menu that is underneath the selected radio button.

      Click on "Next >>" button

    4. In the next screen, select "Connect native" radio button

      Click on "Next >>" button

    5. In "Set up connection to a MySQL database" screen, provide the name of the database, hostname or IP address of the MySQL database server (server URL) that you want to connect to along with the port# on which the MySQL server is actively listening for new database connections. If the MySQL Server is running on the same machine as that of the StarOffice / OpenOffice.org application, you can provide the location of the socket under "Socket" field. If not, leave it blank.

      Click on "Next >>" button

    6. Provide the database user name and the password details in "Set up the user authentication" screen. Click on "Password required" check box if there is a password setup for the database user.

      Click on "Test Connection" button to ensure a successful connection to the MySQL database using the credentials provided in this window.

      Click on "Next >>" button

    7. In the final screen, simply accept the default values and click on 'Finish' button.

      "Yes, register the database for me" and "Open the database for edition" are the defaults selected in this screen.

      When you click on the 'Finish' button, you will be prompted to provide a name to the database to save it as a file on your local machine. The saved file contains information about the database including the queries, reports and forms that are linked to the MySQL database. The actual data remain in the MySQL database. Hence you need not worry about the size of the file that is being saved on your local machine. It will be small in size.

    8. Ultimately the Database area of the Base main window appears as shown in the following screen capture.

      Notice the RDBMS name, Type of Connectivity, MySQL Database Name along with the configuration properties, Database user name and the Database server hostname at the bottom of the window.

      You will be able to query the database, create new forms/reports etc., from this window. Unfortunately discussion around those topics is beyond the scope of this blog post - so, we will stop here.


    That is all there is to it in installing and configuring the MySQL connectors for \*Office suite of applications. Now enjoy the flexibility of fetching the data from your favorite office productivity software.

Thursday Dec 10, 2009

Oracle RDBMS: Extracting the Table, Index & View Definitions (DDL) and Indexed Columns

(Reproducing a 30 month old blog post from my other blog at blogger. Source URL:
    http://technopark02.blogspot.com/2007/05/oracle-how-to-get-tableview-definition.html
)

Q: How to extract the table definition (DDL statement) from an Oracle database without having to go through a stack of dictionary views?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

eg.,
SQL> set long 1000
SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL('TABLE','PERSON') from DUAL;

  CREATE TABLE "FS890"."PERSON"
   (    "SSN" VARCHAR2(12),
        "FIRST_NAME" VARCHAR2(25),
        "LAST_NAME" VARCHAR2(25),
        "STREET" VARCHAR2(40),
        "CITY" VARCHAR2(30),
        "STATE" VARCHAR2(30),
        "ZIP" VARCHAR2(15),
        "COUNTRY" VARCHAR2(35)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
)  TABLESPACE "PSDEFAULT"



Q: How to extract the index definition (DDL statement) from an Oracle database for a given index nam?

A: By calling the GET_DDL() function of metadata package DBMS_METADATA.

Syntax:
select DBMS_METADATA.GET_DDL('INDEX','<index_name>') from DUAL;

eg.,
SQL> create index PERSON_IDX on PERSON ( SSN ); 

Index created.

SQL> set long 1000
SQL> set pagesize 0

SQL> select  DBMS_METADATA.GET_DDL('INDEX','PERSON_IDX') from DUAL;

  CREATE INDEX "FS890"."PERSON_IDX" ON "FS890"."PERSON" ("SSN")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PSDEFAULT"

Note:

If the interest is only to get the indexed column names for an index, simply query COLUMN_NAME of table USER_IND_COLUMNS.

Syntax:
select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = '<index_name>';

eg.,
SQL> column COLUMN_NAME format A15
SQL> select COLUMN_NAME from USER_IND_COLUMNS where INDEX_NAME = 'PERSON_IDX';

COLUMN_NAME
---------------
SSN



Q: Given a view name, how do we get the definition of the view? i.e., how to get the corresponding DDL statement that was used to create the view?

A: Query the TEXT column of table DBA_VIEWS.

Syntax:
SQL> set long 10000

SQL> select TEXT
  2  FROM DBA_VIEWS
  3  where OWNER = '<owner_name>'
  4  and VIEW_NAME  = '<view_name>';
Here is an example:
% sqlplus fs890/fs890@fs890

SQL> create table PERSON (
  2  SSN        VARCHAR2(12),
  3  FIRST_NAME VARCHAR2(25),
  4  LAST_NAME  VARCHAR2(25),
  5  STREET     VARCHAR2(40),
  6  CITY       VARCHAR2(30),
  7  STATE      VARCHAR2(30),
  8  ZIP        VARCHAR2(15),
  9  COUNTRY    VARCHAR2(35));

Table created.

SQL> create view PERSON_VW as
  2  select SSN, FIRST_NAME, LAST_NAME from PERSON;

View created.

SQL> set long 1000
SQL> select TEXT
  2  from DBA_VIEWS
  3  where OWNER = 'FS890'
  4  and VIEW_NAME  = 'PERSON_VW';

TEXT
--------------------------------------------------------------------------------
SELECT SSN, FIRST_NAME, LAST_NAME FROM PERSON



Q: How to find the schema name and the DB user name from an active session?

A: Run the following query:

select sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

Alternatively run select USER from DUAL; to find the current {session} user name.

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

eg.,
SQL> column SESSION_USER format A15
SQL> column CURRENT_SEHEMA format A15

SQL> select sys_context('USERENV', 'SESSION_USER') SESSION_USER, 
  2  sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA from dual;

SESSION_USER    CURRENT_SCHEMA
--------------- ---------------
FS890           FS890

SQL> column USER format A6

SQL> select USER from DUAL;

USER
------
FS890



Note:

Be aware that there are multiple ways of extracting the same piece of information from an Oracle database. I just provided the ones that I frequently use as part of my work.

About

Benchmark announcements, HOW-TOs, Tips and Troubleshooting

Search

Archives
« July 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
  
       
Today