Sunday, October 30, 2011

Working with Oracle Directory Object

In many situations it is necessary for a session to read or write operating system files. Oracle offers two ways for doing this.

  • UTL_FILE package
  • Oracle directories 

UTL_FILE package contains many PL/SQL procedures for manipulating directory objects. Operating system directories that can be accessed by UTL_FILE are only those listed in UTL_FILE_DIR instance parameter. This parameter default to NULL but can be set to any comma separated list as well as * (wildcard) which means any directory in operating system.

SQL > SELECT   NAME, VALUE
            FROM     V$PARAMETER
            WHERE    UPPER(NAME) = 'UTL_FILE_DIR'

Result
---------------------------------------------------------------------------------------------------
utl_file_dir             NULL

All the directories listed will be visible to all sessions. To give a value for UTL_FILE_DIR parameter file use the following command.

SQL > sqlplus / as sysdba
SQL > ALTER SYSTEM SET UTL_FILE_DIR = 'C:\TEST' SCOPE = SPFILE
SQL > SELECT   NAME, VALUE
            FROM     V$PARAMETER
            WHERE    UPPER(NAME) = 'UTL_FILE_DIR'

Result
---------------------------------------------------------------------------------------------------
utl_file_dir             C:\TEST


Notice the use of SCOPE = SPFILE: it is a static parameter and change can not take effect without restarting the instance.

Oracle directories give a layer of abstraction between the user and the operating system: DBA create a directory within the database, which points to a physical path. Permissions on these Oracle directories can then be granted to individual users. This gives much more finely grained access control than the UTL_FILE_DIR parameter. Oracle does not check if the directory exists or not. Having created the directory read and write permission should be granted to ORACLE users. Directories are always own by SYS user despite who created them.

SQL > GRANT CREATE ANY DIRECTORY TO HR
SQL > CREATE DIRECTORY TEST AS 'C:\Test'
SQL > GRANT READ ON DIRECTORY TEST TO PUBLIC
SQL > GRANT WRITE ON DIRECTORY TEST TO SCOTT

Saturday, October 8, 2011

Understanding TIME INTERVAL Data Types in Oracle

Time Interval data types are part of Oracle database since Oracle 9i. Before, there is no way to store intervals between two dates in such easy way. Designers have to convert the interval into seconds and store that in a numeric format.

INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are two data types added to store interval of two dates. They can be use both in SQL and PL/SQL. The syntax is as below:

INTERVAL YEAR[(year_precision)] TO MONTH
INTERVAL DAY[(day_precision)] TO SECOND[(fractional_seconds_precision)]

There are defaults for precision. Two digest for year and day and six digest for fractional seconds.

SQL>   SELECT INTERVAL '5-9' YEAR TO MONTH FROM DUAL
Result: +05-09
Cover a time interval of 5 years and 9 months

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND FROM DUAL
Result: +07 15:20:07.700000
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

SQL>   SELECT INTERVAL '7 15:20:07.7' DAY TO SECOND(1) FROM DUAL
Result: +07 15:20:07.7
Cover a time interval of 7 days, 15 hours, 20 minutes, 7 seconds and 7 milliseconds.

Intervals can be positive or negative.

Below is another example that create a table with interval data types, insert some records in it, and show up the results.

CREATE TABLE EG_TEST_INTERVAL
(
       ID NUMBER,
       TIME1 INTERVAL YEAR TO MONTH,
       TIME2 INTERVAL DAY TO SECOND
);

INSERT INTO EG_TEST_INTERVAL VALUES (1, INTERVAL '5-9' YEAR TO MONTH, INTERVAL '7 15:20:07.7' DAY TO SECOND);
INSERT INTO EG_TEST_INTERVAL VALUES (2, INTERVAL '-5-9' YEAR TO MONTH, INTERVAL '-7 15:20:07.7' DAY TO SECOND);

COMMIT;

SELECT * FROM EG_TEST_INTERVAL;

Results: 1 +05-09 +07 15:20:07.700000
             2 -05-09  -07 15:20:07.700000

Monday, August 29, 2011

Travel Back In Time - Retrieve Dropped Procedures With Flashback Query

Each procedure is stored in DBA_SOURCE table. Assume that you dropped accidentally the procedure and you don't have the code to retrieve it back. FLASHBACK QUERY can save your life.

CREATE OR REPLACE PROCEDURE EG_TEST()
AS
BEGIN
 FOR NUMB IN 1..1000
  LOOP
   DBMS_OUTPUT.put_line(NUMB);      
  END LOOP;
END;

DROP PROCEDURE EG_TEST;

CONNECT / AS SYSDBA;

SELECT     TEXT
FROM       DBA_SOURCE
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE
WHERE      NAME = 'EG_TEST'
ORDER BY   LINE

TEXT
-----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE EG_TEST()
AS
BEGIN
  FOR NUMB IN 1..1000
  LOOP
     DBMS_OUTPUT.put_line(NUMB);      
  END LOOP;
END;

Just copy and paste to a text file and you have just traveled back in time.

Thursday, August 25, 2011

Oracle - Iterating Over Dates In For Loop

For loop in Oracle does not offer the possibility to use DATE data-type in iteration. It requires NUMBER data-type. The below Oracle procedure can be use to manipulate iteration through dates.

CREATE PROCEDURE DATE_ITERATION(FIRST_DATE IN VARCHAR2,
                                LAST_DATE  IN VARCHAR2) AS
  TEMP NUMBER;
  NUMB NUMBER;
BEGIN
 
  SELECT (TO_DATE(LAST_DATE, 'YYYY-MM-DD') -
         TO_DATE(FIRST_DATE, 'YYYY-MM-DD'))
    INTO TEMP
    FROM DUAL;
  
  FOR NUMB IN 0 .. TEMP LOOP
    DBMS_OUTPUT.put_line(TO_DATE(FIRST_DATE, 'YYYY-MM-DD') + NUMB);
  END LOOP;
 
END DATE_ITERATION;

Invocation of procedure is done as below.

CALL DATE_ITERATION('2011-06-01','2011-07-01')

The output produced.

01-GIU-11
02-GIU-11
03-GIU-11
.
.
.
30-GIU-11
01-LUG-11

Wednesday, August 17, 2011

Optimizing Oracle SQL INSERT Performance

Below are some tips of how to optimize Oracle Sql Insert:
  • Parallelize load
    • Data Manipulation Language (DML) such as INSERT can be parallelize by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it's necessary to maintain large summary or historical tables. Standard INSERT statements using a VALUES clause cannot be parallelizedOracle can parallelize only INSERT . . . SELECT . . . FROM statements. The syntax is as below. 
    • ALTER SESSION ENABLE PARALLEL DML;  
    • CREATE TABLE TEST_M94 AS SELECT * FROM M94 WHERE 0 = 1;            
    • INSERT /* parallel (TEST_M94, 4, 1)*/ INTO TEST_M94 SELECT * FROM M94;  
    • ALTER SESSION DISABLE PARALLEL DML;
  • Use append hint
    • If we use append hint we ensure that Oracle will grab fresh blocks by rising the high water-mark of the table. If we are using the parallel hint the append mode is the default so we don't need to specify it. Before 11g R2 append is supported only in INSERT...SELECT and not in VALUE clause. The syntax is as below:
    • INSERT /* + append */ INTO TEST_M94 SELECT * FROM M94
  • Use nologging
    • The keyword NOLOGGING tells Oracle not to enter anything in the redo log files. Ideally, when any SQL command is executed, Oracle keeps on writing the events in the redo log file so that if anything goes wrong, Oracle can rollback the changes. Hence adding the NOLOGGING parameter will ensure that you save on the time that oracle spends to log events.
  • Disable indexes
    • It's far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.
  • Use large block size
    • If the block size is larger it reduce the I/O because more rows fits in that block before the block gets full.

Thursday, August 11, 2011

CTAS "Create Table As Select" With PARALLEL NOLOGGING

Normally CTAS "Create Table As Select" with give a good performance but if the original table has a large number of rows PARALLEL NOLOGGING method should be consider.

The table below has 1,346,163 records.



The time it takes to create another table with normal CTAS is 41 seconds.



If we use CTAS with PARALLEL NOLOGGING the time it takes is reduce to 22 seconds.



Remember the keyword PARALLEL denotes the number of parallel threads that needs to run to do the job quickly. Ideally you should keep the parallel count between 3 to 5.

The keyword NOLOGGING tells Oracle not to enter anything in the redo log files. Ideally, when any SQL command is executed, Oracle keeps on writing the events in the redo log file so that if anything goes wrong, Oracle can rollback the changes.

Hence adding the NOLOGGING parameter will ensure that you save on the time that oracle spends to log events.

Try it out with this syntax and you will see a marked improvement.

Tuesday, August 9, 2011

How to mount USB Flash drive on Oracle Linux

First step is to create a folder to mount on
   mkdir /home/usb

When USB Flash drive is plugged the below message appear which shows the drive to be mount


Next step is to mount the USB Flash drive
   mount /dev/sdb1 /home/usb

To verify that the mount is done successfully check the content of directory
   ls -al /home/usb

Sunday, August 7, 2011

How to configure IP address on Oracle Linux

Adding IP address in Oracle Linux can be done by editing the network-scripts. While this procedure is tested on Oracle Linux, i'm sure it should work on Redhat Enterprise Linux and other versions of Redhat.

Editing network - scripts
   The network interface configuration files are found in the directory:
   /etc/sysconfig/network-scripts/


 To add an IP address to an interface eth0
 # cd /etc/sysconfig/network-scripts/
 # vi ifcfg-eth0

 Enter the below parameters for static configuration
 DEVICE="eth0"
 BOOTPROTO="static"
 BROADCAST="192.168.2.255"
 HWADDR="00:0C:29:DS:22:26"
 IPADDR="192.168.2.2"
 NETMASK="255.255.255.0"
 

 NETWORK="192.168.2.0"   
 ONBOOT="yes"
 TYPE="Ethernet"



  Save the file by typing ESC :wq

  Restart the network service for the changes to take effect.
  # service network restart