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