Tuesday, March 18, 2014

What is the difference between "in line" and "out of line" constraints?

The following examples create an "in line" constraint, since they both declare a constraint in the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER CONSTRAINT STAFF_PK PRIMARY KEY, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20)
);

The following examples create an "out of line" constraint, since they are both declared separately from the columns' definitions:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  PRIMARY KEY (PERSONELL_ID)
);

An anonymous primary key constraint with a system generated name is created on the column PERSONELL_ID.
Optinally, give the constraint a specific name:

CREATE TABLE STAFF (
  PERSONELL_ID NUMBER, 
  LNAME VARCHAR2(30),
  FNAME VARCHAR2(20),
  CONSTRAINT STAFF_PK PRIMARY KEY (PERSONELL_ID)
);
The "ALTER TABLE" equivalent of the "in line" syntax is:
ALTER TABLE STAFF 
MODIFY PERSONELL_ID PRIMARY KEY;
Or with explicitly named constraint:
ALTER TABLE STAFF 
MODIFY PERSONELL_ID CONSTRAINT STAFF_ID PRIMARY KEY;

The "ALTER TABLE" equivalent of the "out of line" syntax is:
ALTER TABLE STAFF
ADD PRIMARY KEY (PERSONELL_ID);
Or with explicitly named constraint:
ALTER TABLE STAFF
ADD CONSTRAINT STAFF_PK PRIMARY KEY(PERSONELL_ID);
Note that NOT NULL constraints cannot be created "out of line".

How to use the different options for DATE datatype in Oracle


Datatypes that support dates and times come in two flavours:

  • Datetime data types, which are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.
  • Interval data types, which are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Interval data types store time durations.

    First, create a test table where the different types of DATE datatypes are bing used:

    CREATE TABLE DATETYPETEST
        (TESTDATE1 DATE,
         TESTDATE2 TIMESTAMP,
         TESTDATE3 TIMESTAMP WITH TIME ZONE,
         TESTDATE4 TIMESTAMP WITH LOCAL TIME ZONE,
         TESTDATE5 INTERVAL YEAR TO MONTH,
         TESTDATE6 INTERVAL YEAR(4) TO MONTH,
         TESTDATE7 INTERVAL DAY TO SECOND,
         TESTDATE8 INTERVAL DAY TO SECOND(2)
         );
    

    First I will test the Datetime data types:

    INSERT INTO DATETYPETEST(TESTDATE1) VALUES(SYSDATE);
    INSERT INTO DATETYPETEST(TESTDATE1) VALUES(TRUNC(SYSDATE));
    TRUNC(SYSDATE)
    17.01.2014 10:15:45
    17.01.2014

    INSERT INTO DATETYPETEST (TESTDATE2) VALUES (SYSTIMESTAMP);
    INSERT INTO DATETYPETEST (TESTDATE2) VALUES (TRUNC(SYSTIMESTAMP));
    TRUNC(SYSTIMESTAMP)
    17.01.2014 10:15:45.978004
    17.01.2014 00:00:00.000000

    Interestingly enough, TRUNC on datatype TIMESTAMP does not strip of the time precision per se, Oracle simply sets it to zero altogether.

    INSERT INTO DATETYPETEST (TESTDATE3) VALUES (SYSDATE);
    TIMESTAMP WITH TIMEZONE
    17.01.2014 10:15:46.000000 +01:00

    INSERT INTO DATETYPETEST (TESTDATE4) VALUES (SYSDATE);
    TIMESTAMP WITH LOCAL TIMEZONE
    17.01.2014 10:15:46.000000

    Remember that TIMESTAMP WITH LOCAL TIME ZONE does not store the time zone offset with the column value; however, when the column value is retrieved in a query, the value is sent in the user's local session time zone.

    Let's move on to the interval data types.

    First, I will insert the interval as a literal:

    INSERT INTO DATETYPETEST (TESTDATE5) VALUES (INTERVAL '2-11' YEAR TO MONTH);
    INTERVAL YEAR TO MONTH
    +2-11

    Below, I am using the TO_YMINTERVAL function to convert a string representing a number of years and a number of months into a INTERVAL YEAR TO MONTH datatype:

    INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('14-01'));
    INTERVAL YEAR TO MONTH
    +14-01

    The statement below, however, fails with ORA-01873: the leading precision of the interval is too small because used the default precision for year(2):

    SQL> INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'));

    INSERT INTO DATETYPETEST (TESTDATE5) VALUES(TO_YMINTERVAL('2014-01'))
    *
    ERROR at line 1:
    ORA-01873: the leading precision of the interval is too small

    So instead, let's insert the values into a column which allows for larger precision for the INTERVAL YEAR(n) TO MONTH datatype:

    SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('2014-01'));

    1 row created.

    Of course, even values of less precision would be accepted here:

    SQL> INSERT INTO DATETYPETEST (TESTDATE6) VALUES(TO_YMINTERVAL('14-01'));

    1 row created.

    INTERVAL YEAR(4) TO MONTH
    +2014-01
    +14-01

    Again, I will first insert the interval as a literal:

    INSERT INTO DATETYPETEST (TESTDATE7) VALUES(INTERVAL '2 6:30:01.222' DAY TO SECOND(3) ));
    INTERVAL DAY TO SECOND
    +02 6:30:01.222

    Use the TO_DSINTERVAL function to convert the string representing a number of days, hours, minutes and seconds into an INTERVAL DAY TO SECOND datatype:

    INSERT INTO DATETYPETEST (TESTDATE7) VALUES(TO_DSINTERVAL('7 12:30:000001'));
    INTERVAL DAY TO SECOND
    +07 12:30:01.000000

    In the next example, I am limiting the number of fractional seconds according to the specifications of the column INTERVAL DAY TO SECOND(2):

    INSERT INTO DATETYPETEST (TESTDATE8) VALUES(TO_DSINTERVAL('21 12:30:000001'));
    INTERVAL DAY TO SECOND(2)
    +21 12:30:01.00

    The TO_DSINTERVAL is very flexible. Here is another post on the same topic.

    An overview of conversion functions used with the INTERVAL data types can be found here

    Sources: Oracle Documentation on intervals
  • Monday, March 17, 2014

    What are AWR and ADDM and how are they related?

    In Oracle 10g, one of the major changes to the database was the AWR and the ADDM.

    AWR (Automatic Workload Repository)
    Purpose: To collect statistics about the database automatically, without DBA intervention.
    Snapshots of the database are taken every hour by default, and statistics derived from these snapshots are saved for 7 days by default.
    The statistics are created through a separate background process called MMON (manageability monitor process).

    ADDM (Automatic Database Diagnostic Monitor)
    Purpose: To analyze the data in the AWR, without DBA intervention. The ADDM will analyze the statistics and save the results in the database. If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL, ADDM is triggered every hour by default, right after a new snapshot has been taken by the AWR.

    How to find the AWR snapshots currently available in the database

    SET LINES 100 PAGES 999
    
    SELECT SNAP_ID,SNAP_LEVEL, TO_CHAR(BEGIN_INTERVAL_TIME, 'dd.mm.yy hh24:mi:ss') "Starttime"
    FROM DBA_HIST_SNAPSHOT 
    ORDER BY 1
    /
    

    Wednesday, March 12, 2014

    How to check that an Oracle database parameter is set in a unix shell script

    Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:
    ###############################################################
    # Check that the db_convert_file_name and log_file_name_convert
    # are both set. If not, abort script.
    ###############################################################
    TRG_DB=${ORACLE_SID}
    cat << EoF > check_${TRG_DB}_convert_params.sql
    set termout off
    set lines 200
    set trimspool on
    set heading off
    set pages 0
    set feedback off
    set echo off
    set verify off
    col name format a30
    col value format a40
    spool check_${TRG_DB}_convert_params.lst
    select name,value
    from v\$parameter
    where name in ('db_file_name_convert','log_file_name_convert');
    exit
    EoF
    sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
    CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
    CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`
    
    case ${CONV_DATA_FILE_SET} in
    '') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
    *) echo db_file_name_convert is set. Continuing...;;
    esac
    
    case ${CONV_LOG_FILE_SET} in
    '') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
    *) echo log_file_name_convert is set. Continuing...;;
    esac
    exit
    

    How to receive user response in a korn shell unix script

    The following is valid for korn shell but not necessarily in other shell dialects, such as bash:


    print -n "Do you want to proceed? [Y/N] "
    read answer
    [[ $answer == [yY] ]] || { echo "Exiting..."; exit 3; }

    Monday, March 10, 2014

    How to set up an alias that quickly shows you the running instances on a Solaris server

    Thanks to Mats Strömberg who showed me this little trick on how to use the -o option with ps, to look directly at the command column of the output from ps.

    oracle@testserv1:/home/oracle $ alias ri='ps -ef -o comm|grep -v grep|grep ora_[p]mon|sed '"'"'s/ora_pmon_//'"'"'|sort -n'
    oracle@testserv1:/home/oracle $ ri
    APITDB01
    TESTDB01
    TESTDB02
    TESTDB03