Interval partitioning : A great feature of oracle 11g

Table partitioning is very much useful for data warehousing. But one problem we face before 11g is have to create the partition manually and it is cumbersome work. If we forget to create partition, the ETL job will be failed.

From 11g, oracle enhance the range partitioning with the feature of auto partitioning. Now along with range partition you only have to mention the interval of partition and create a base partition. The syntax is as follows -

CREATE TABLE SALES_PART
(TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'));

The violet lines are for interval and base partition. In this example, the partitions are created on daily basis. If want to create them on week basis, write INTERVAL (NUMTODSINTERVAL(7,'day')) and INTERVAL (NUMTOYMINTERVAL(1,'month')) for monthly basis.

The automatic created partitions have system generated name and hence not follow naming convention. So to alter partition name issue following sql command -

ALTER TABLE SALES_PART RENAME PARTITION sys_p22 TO p_third

Some fact about oracle listener, tns, service & SID

1. Service_Name and SID are two different things. Service name directly maps to service of database. SID is system identification. The difference will be apparent in RAC environment where SID will be instance name and service name will be database service. This difference will also come into picture if your service name contains domain name. For example, your service name is dw.tmib.net.bd but your SID is dw.
   
   
2. For OS authentication, ORACLE_SID should be set to instance name i.e. SID.
   
   
3. For Listener, SID and Service Name are interoperable.
   
   
4. tnsping command confirms that there is a listener which listens to host:port referred by the tns entry.
   
   
5. In sqlplus connect command, @tnsname refers to tns entry in tnsnames.ora file. tns entry always goes to listener.
   
   
6. If the listener is not running, from sqlplus of server the database can be still connected. In this case, it is recommended to set the ORACLE_SID variable. It is required not to use @tnsname.
   
   
7. In a tns entry, under the description several addresses can be added. This helps the fail over. If any host:port service fails then go to the next host:port.
   DW=
   (DESCRIPTION=
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.21)
   (PORT=1521)
   )
   (ADDRESS=
   (PROTOCOL=TCP)
   (HOST=192.168.8.22)
   (PORT=1521)
   )
   (CONNECT_DATA=
   (SERVER=dedicated)
   (SID=dw2)
   )
   )
   So if 192.168.8.22:1521 fails to serve, the request will be forwarded to 192.168.8.1:1521

Object-Relational Features of Oracle

In my previous post, I showed how to create custom types and that was a type which is a collection of varchar2. You can also create a composite column which is composed by varchar2, number or something else.

For example here is a definition of a point type consisting of two numbers:

     CREATE TYPE PointType AS OBJECT (
        x NUMBER,
        y NUMBER
    );
    /

An object type can be used like any other type in further declarations of object-types or table-types. 
For instance, we might define a line type by:

    CREATE TYPE LineType AS OBJECT (
        end1 PointType,
        end2 PointType
    );
    /


Then, we could create a relation that is a set of lines with ``line ID's'' as:

    CREATE TABLE Lines (
        lineID INT,
        line   LineType
    );

Then you can insert into table Lines in following manner - 

    INSERT INTO Lines
        VALUES(27, LineType(
                        PointType(0.0, 0.0),
                        PointType(3.0, 4.0)
                   )
        );

Here are some other queries about the relation lines. Note that here the table alias is required
as it is treated as the object of a class.

    SELECT ll.line.end1.x, ll.line.end1.y
    FROM Lines ll;

Suppose you want to load data from a flat table to this object relational table. Here is the procedure -

     CREATE TABLE LinesFlat(
        id INT,
        x1 NUMBER,
        y1 NUMBER,
        x2 NUMBER,
        y2 NUMBER
    );

   INSERT INTO Lines
        SELECT id, LineType(PointType(x1,y1), PointType(x2,y2))
        FROM LinesFlat;


Fore more detail, please visit this site. It is really nice.

Split Function with Regular Expression in Oracle 10g

Few days before, I was looking for split function in oracle (like java/C#), but did not find any. Then at first I find that Oracle 10g introduce regular expression support for string operation. You can visit http://www.oracle.com/technology/obe/obe10gdb/develop/regexp/regexp.htm to know about this.

Then I utilize oracle pipelined function (to know more you can visit http://www.oracle-developer.net/display.php?id=207) and build my own Split(str varchar2, regex_of_token varchar2) which takes two arguments. First of which is the input string and the second one is the regular expression of the token. The two steps to build the method are -

1. Build a custom collection type for return type -
   

   CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024);

2. Then the function body -
   

   CREATE OR REPLACE FUNCTION SYS.split
   ( str IN varchar2, regex in varchar2)
   RETURN str_array PIPELINED IS
   next_token varchar2(1024) := '';
   token_index number := 1;
   BEGIN
   LOOP
       select regexp_substr(str,regex,1,token_index)
          into next_token from dual;
       exit when (next_token is null);
       pipe row(next_token);
       token_index := token_index+1;
   END LOOP;
   RETURN;
   END;
   /

Then to test this function, call the function in the following way-

select * from table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','([0-9]+(\.[0-9]+)?|([a-z]|[A-Z])+)'))

The first argument is sample string and second one is regular expression for number or alphabet but not alpha-numeric. If you want to split the string around ',' then the query will be -

select * from
table(split('10,1812965650,42.09,1234,3,0906102214,sadique,Sadique','[^,]+'))

Drop table if it exists in Oralce

If you want to drop a table which is not in existence then surely you will get an error - table or view does not exist. If you put this type of code in a stored procedure then this error halt the whole procedure execution.

So, the better solution for this is to check whether the table exists or not. If it exists then drop that. The logic can be put in a stored procedure (not in a function as function cannot perform DML or DDL) then we can call this procedure from other procedure and we don't have to duplicate this code. So the procedure will be very simple like the following -

CREATE OR REPLACE procedure drop_table_ifexist(tableName varchar2) IS
tableCount NUMBER := 0;
BEGIN
select count(*) into tableCount
from user_tables
where table_name = upper(tableName);

--oracle store all table names in upper cases.

if(tableCount > 0) then
execute immediate 'drop table '||tableName;
end if;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END drop_table_ifexist;
/

This procedure will drop a table of its own if the table exists. Table name can be queried from two other tables user_objects (collection of all objects owned by this user) and all_objects (collection of all objects privileged by this user).

Schedule oralce procedure through crontab

In previous post - Schedule Java application through crontab, I mentioned that the process of scheduling other application is same as Java application.

To accomplish this, you have to set at least two environment variables. The first one is obviously the PATH. The other is ORACLE_HOME. You need to set ORACLE_SID if you want to use a database as default or if you don't have permission in tnsnames.ora file.

PATH=/usr/local/oracle/product/11.2.0/dbhome_1/bin
ORACLE_HOME=/usr/local/oracle/product/11.2.0/dbhome_1

export PATH
export ORACLE_HOME

These should be sufficient if you have permission in tnsnames.ora. Otherwise, the database should be installed in your workstation and ORACLE_SID should be settled to database service name.

Schedule Java Application through crontab

When we want to run java executable (java, javac, etc.) directly (without mentioning full path) then you have to set the path variable of OS. In linux, you can set path by update the path variable in bash profile. This can be done easily by writing the following commands -

PATH=/usr/local/jdk1.6.0/bin:
export PATH

For detail, you can see http://java.sun.com

Unfortunately, the script you schedule in cron will not get this path which you set in bash profile. So to run your script through cron, you have append the above commands in you script too. That is, you script should contain command like this -

PATH=/usr/local/jdk1.6.0/bin:
export PATH
java ...

It is worth mentionable here - this setting of path variable is applicable for all path setting not only for java path settings. For example, if you have to set oracle path for cron, you have to follow the same steps.

For setting java classpath you can see following article -
http://linuxlab.dk/tipsntricks/classpath