Oracle’s BI Publisher which is part of the Oracle Business Intelligence Suit has at least within Enterprise Computing environment established as a reliable and preferable choice when it comes to replace the somewhat aged Oracle Reports.

Once being familiar with it you will ask yourself about why switching tool when it comes to the need of creating reports in a normal web design world mostly using MySQL Databases within a “LAMP” environment.

Written as an universal reporting tool BI Publishes does offer us a more or less universal data interface based on JDBC connections. And being able to act itself as a callable web service offering WEBDAV services based on XML data exchange it anyway seems to be an excellent choice for this world.

Already Oracle’s installation instructions does give you a quick idea about setting up BI Publisher working against some MySQL Database. But acting  in a Web environment you might find it cool integration the BI Publisher within an already existing Apache Tomcat environment and for exactly this environment I will give you a short explanation about getting things together and ready for work.

First we will pick the BI Publisher software itself by copying the xmlpserver.war file from the distribution DVD and throwing it into the webapps folder of our Apache Tomcat. Waiting a few seconds until the self-deploying mechanism of the Apache Tomcat has extracted the files for us, we need to specify the access path to our project and development repository within the file xmlp-server-config.xml.

Having done that, we need to add the MySQL JDBC driver to our system and making it known within the environment of the Apache Tomcat. (The MySQL JDBC driver can be downloaded from: http://www.mysql.com/products/connector/j/.)

So with the downloaded fileready, we have to extract it to any place we like onto our disk. Just I would recommend you to put it somewhere close to your MySQL installation for you being able to later on remember it again. Finally extracted and ready to use, we have to add the information about the mysql-connector-java- -bin.jar file to the Java Classpath of the Apache Tomcat and finally restarting it:

Having this set, as a last step we have to invoke the BI Publisher itself, in which we then will configure the new JDBC data source within the Admin region of it:

And now with this tweaking ready, MySQL DB should connect, open and work for you like a charm.

When you want to use SQL*Plus or exp/imp on the same server on which you have installed your database you might get errors like these:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

or

Error 6 initializing SQL*Plus
Message file sp1us.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Syntax:
$ sqlplus username/password

You get this error only when you execute ‘sqlplus’ as a user outside of the dba or Oracle group, because in Oracle Database 10g Release 2 the Oracle Home was locked down by setting umask to 007 prior to installation, so that the files that sqlplus needs to execute do not have read/execute permissions for group “others” and some directories under $ORACLE_HOME have no world read nor execute permission (rwxr-x—)

Warning , don’t use the following command to lower the permissions, this may corrupt your Oracle-Installation:
chmod -R 755 $ORACLE_HOME/

The better way is to use an script which is provided by oracle:
$ORACLE_HOME/install/changePerm.sh

This script will relax the permissions on most files and directories in your Oracle Home, and allow “others” to execute them.

Oracle Version 10g comes along with a new feature called Recyclebin. It is what it’s name is promising you – a bin collecting all the dropped objects and data for you. This recylebin is a prerequisite in order to have FLASHBACK DROP functionality and therefore provided to every Oracle user.

SQL> desc recyclebin
 Name                                      Null?    Type
 —————————————– ——– ————
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 ORIGINAL_NAME                                      VARCHAR2(32)
 OPERATION                                          VARCHAR2(9)
 TYPE                                               VARCHAR2(25)
 TS_NAME                                            VARCHAR2(30)
 CREATETIME                                         VARCHAR2(19)
 DROPTIME                                           VARCHAR2(19)
 DROPSCN                                            NUMBER
 PARTITION_NAME                                     VARCHAR2(32)
 CAN_UNDROP                                         VARCHAR2(3)
 CAN_PURGE                                          VARCHAR2(3)
 RELATED                                   NOT NULL NUMBER
 BASE_OBJECT                               NOT NULL NUMBER
 PURGE_OBJECT                              NOT NULL NUMBER
 SPACE                                              NUMBER

Perhaps important to know is that the recyclebin is a public synonym and it is based on the view user_recyclebin which in turn is based on sys.recyclebin$ table and does not work for SYS objects.

Comparing behavior as in 8i / 9i, there is no issues with DROPping the table behaviour wise in 10g . The space is not released immediately and is accounted for within the same tablespace / schema after the drop, but there is NO recycling of the objects when dropping a tablespace or user.

 SQL> DROP TABLE EMP;
Table dropped.

SQL> SELECT owner,original_name,operation,type
  2  FROM dba_recyclebin
  3  /

OWNER                          ORIGINAL_NAME                    OPERATION TYPE
—————————— ——————————– ——— ——
SCOTT                          EMP                              DROP      TABLE

Note: We can also create a new table with the same name at this point.

Freeing used space though:
In order to completely remove the table from the DB and to release the space the new PURGE command is used.

From SCOTT user:
SQL> PURGE TABLE EMP;
Table purged.

SQL> SHOW recyclebin
SQL>

But we can also DROP and PURGE a table with a single command

From SCOTT user:
SQL> DROP TABLE EMP PURGE;
Table dropped.

SQL> SELECT *
  2  FROM recyclebin
  3  /
no rows selected

So there will be no need to PURGE the table out of Recylebin later on and space is directly marked as free again.

Recylebin is for sure a usefully new feature of Oracle 10g, but rarely some Oracle user is used working with it today.  Based on your own or customers needs it might appear meaningful to deactive it though.

As for on Oracle 10gR2 recyclebin is an initialization parameter and by default its ON, but you can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

Or you are going to change the initialization values within SPFILE or INIT.ORA permanently.

But be aware about  the dropped objects, when recyclebin was ON will remain in the recyclebin even if you set the recyclebin parameter to OFF for your session or the even the whole system.

For freeing this allocated space also, you can use one of the following two commands:

From SCOTT user:
PURGE recyclebin; (Purge all objects in Recyclebin)

From DBA user:
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)