Oracle 11g R1: DDL_LOCK_TIMEOUT

Whenever you are trying to alter a table to add a new column, exclusive lock will be acquired on the table, if the table is used by any other transaction at the same time oracle will throw the following error.

SQL> DESC T

Name            Null?    Type
--------------- -------- -------------
OBJECT_ID       NOT NULL NUMBER
OBJECT_NAME     NOT NULL VARCHAR2(30)

SQL> ALTER TABLE T ADD STATUS CHAR(1);

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Note: I have opened one more Session #2 and inserted one record (DML) to table t and switched to Session #1 without issuing COMMIT in Session #2

The table 'T' is being used right now by another transaction by someother session, so getting an exclusive lock on the table 'T' may be not impossible at this moment.

Once the user transaction completes, then the user saves tranactions by commit statement, then the acquired lock will be released. Then we can issue the alter command to add a new column.

During busy system time the window for locking the table exclusively does open periodically, but the DBA may NOT be able to perform the alter command exactly at that time.

In Oracle Database 11g, we have a solution for the above problem: DDL Wait option

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;

Session altered.

Now, If a DDL statement in the session does not get the exclusive lock, it will NOT throw an error. Instead, it will wait for 30 seconds. During this period, it continously re-tries the DDL operation until it's successful or the time expires, whichever comes first.

SQL> ALTER TABLE T ADD STATUS CHAR(1);

The above statement hangs and does not give any error, instead of trying repeatedly for an exclusive lock to exeucte alter command.
Globally (for all sessions) also you can enable this feature by the below command;

SQL> ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30;

System altered.

Don't have Oracle 11g R1?, dont worry, still we can achieve this; Here is my PLSQL code for the same functionality, it will work in any verion of oracle

SQL> set serveroutput on
SQL> declare
  2  v_start   date;
  3  v_end     date:= sysdate+(30+1)/(24*60*60); -- adding one second additionally as an overhead
  4  is_locked boolean:=true;
  5  begin
  6    while (true)
  7     loop
  8       begin
  9         execute immediate 'alter table t add column status char(1)';
 10         dbms_output.put_line('Exclusive lock on table T acquired and column has been added successfully.');
 11         exit;
 12       exception
 13         when others then
 14           null;
 15       end;
 16       v_start := sysdate;  -- Assigning current time
 17       if (v_start = v_end) then  -- Checking for exit criteria
 18         is_locked:=false;
 19         exit;  -- breaking the loop
 20       end if;
 21     end loop;
 22     if (is_locked = false) then
 23       dbms_output.put_line('Sorry, Exclusive Lock request on table T has been failed. Please try again...');
 24     end if;
 25  end;
 26  /
Sorry, Exclusive Lock request on table T has been failed. Please try again...

PL/SQL procedure successfully completed.

Cheers,
Hope you have enjoyed the experiment.
Post your comments (if any).

All about crontab

crontab is a unix utility that allows tasks (shell programs/commands) to be automatically run in the background at according to the specified intervals.
It is one of essential utility which has been used by DBAs on day to day basis. I have used Solaris 5.9 for examples posted in this article.

Here is my standpoint on crontab (based on my practical and theoretical knowledge),

Syntax:
~~~~~~
o crontab [filename] -- This will overwrite an existing crontab entry for the user that invokes it.

o crontab -elr [username]
    e - Edits a copy of the current user's crontab file, or creates an empty file to edit if crontab does not exist
    l - Lists the crontab file for the invoking user
    r - Removes a user's crontab from the crontab directory


Supporting Files:
~~~~~~~~~~~~~~~~
o /etc/cron.d             : main cron directory
o /etc/cron.d/cron.allow  : list of allowed users
o /etc/default/cron       : contains cron default settings
o /etc/cron.d/cron.deny   : list of denied users
o /var/cron/log           : accounting information
o /var/spool/cron/crontabs: spool area for crontab


Access permission (Scenarios):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Users: Access to crontab is allowed:
  o if the user's name appears in /etc/cron.d/cron.allow.
  o if cron.allow  does  not  exist  and  the user's name is not in cron.deny

Users: Access to crontab is denied:
  o if cron.allow exists and the user's name is not in it.
  o if cron.allow does not exist and user's name is in cron.deny
  o if neither file exists, only a user with the solaris.jobs.user authorization is allowed to submit a job.
  o If BSM audit is enabled, the user's shell is not audited and the user is not the crontab owner. This can occur if the user logs in via a program, such as some versions of SSH, which does not set audit parameters.



Crontab Pattern:
~~~~~~~~~~~~~~~

*  *  *  *  * [nohup] script (full path) [> logfile location]
_  _  _  _  _
|  |  |  |  |
|  |  |  |  |
|  |  |  |  |
|  |  |  |  |
|  |  |  |  +----------> Day of the week (0-6 with 0=Sun) 
|  |  |  |
|  |  |  +-------------> Month of the year (1-12)
|  |  |
|  |  +----------------> Day of the month (1-31)
|  |
|  +-------------------> Hour (0-23)
|  
+----------------------> Minute (0-59)


Symbols:
~~~~~~~
- Used to specify the range between two integers
, Used to specifiy the multiple integers


Examples:
~~~~~~~~
00 13 * * *
--> Runs at 1 PM on every day

00 13 7 * *
--> Runs at 1 PM on every 7th day of all months

00 13 1-10 1,2 *
--> Runs at 1 PM during 1st to 10th days of Jan & Feb months

00 14 * * 1-5
--> Runs at 2 PM on all business days (Mon to Fri)

00 11 * * 6,0
--> Runs at 11 AM on weekends (Sat & Sun)

0,10,20,30,40,50 * * * *
--> Runs every 10 mins

Hope you have a gained some knowledge about Unix Job Scheduler. Have a nice day.

Please post your comments (if any).

Beware of ALTER TABLE MOVE Command

Yesterday, i read about Rebuilding of Table in "Practical Oracle 8i" By Jonathan Lewis, it was well explained. Very good book, yes its my all time favorite book.
I would like to post an article about the possible ways of rebuilding table (mainly with the help of MOVE command) and its impacts.

Oracle provides many ways to reorganize/rebuild a table,

alter table move

dbms_redefinition

ctas (ie. create table as select...)

exp/imp

expdp/impdp (datapump)

Here some more explanation about alter table move command, The alter table move command allows us to change the tablespace, storage clauses, lowers highest watermark etc.

If you have to rebuild a table, always consider the move command first. Availability can be reduced (During Move command execution, no transaction should refer on the same object else we may get ORA-08103: Object no longer exists), but the benefit to administrative safety and convenience is huge. There are no windows of opportunity for data to go missing.

Now we will see an impact of MOVE command,
Afer the execution of move command, the dependent indexes will become INVALID/UNUSABLE since it changes the ROWIDs of the table rows. So we have to rebuild a dependent indexes immediately by the below command.

ALTER INDEX index_name REBUILD;

Here is my experiment,

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table t
  2  as
  3  select * from all_objects;

Table created.

SQL> insert into t select * from t;

32659 rows created.

SQL> insert into t select * from t;

65318 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t on t(id);

Index created.

SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME       INDEX_TYPE        STATUS
---------------- ----------------- --------
IDX_T            NORMAL            VALID

SQL> set linesize 200;
SQL> set autotrace traceonly explain;

SQL> select * from t where object_id=9;


Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)


SQL> set autotrace off;

SQL> alter table t move;

Table altered.


SQL> select index_name,index_type,status
  2  from user_indexes
  3  where index_name='IDX_T';

INDEX_NAME      INDEX_TYPE    STATUS
--------------- ------------- --------
IDX_T           NORMAL        UNUSABLE

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    24 |  3072 |   478   (2)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    |    24 |  3072 |   478   (2)| 00:00:06 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

SQL> alter index idx_t rebuild;

Index altered.

SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 3868287381

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   512 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   512 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9)

Note
-----
   - dynamic sampling used for this statement

The conclusion is, after execution of move command, the dependent indexes should be rebuilt immediately.

Hope you have enjoyed the experiment.

Please post your comments (if any).

Oracle 11g - Alert Log (X$DBGALERTEXT)

Today, i would like to share one new feature of Oracle 11g which i have come across recently.

In Oracle 11g, gives us a x$dbgalertext fixed table that maps to the alert log file, and allowing us to write SQL queries against it. It is very good news for Oracle Developers; becuase they dont have to depend on DBA's to view the alert log content.

SQL> desc x$dbgalertext

Name                       Null? Type
-------------------------- ----- ---------------------------
DDR                              RAW(4)
INDX                             NUMBER
INST_ID                          NUMBER
ORIGINATING_TIMESTAMP            TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP             TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID                  VARCHAR2(64)
COMPONENT_ID                     VARCHAR2(64)
HOST_ID                          VARCHAR2(64)
HOST_ADDRESS                     VARCHAR2(16)
MESSAGE_TYPE                     NUMBER
MESSAGE_LEVEL                    NUMBER
MESSAGE_ID                       VARCHAR2(64)
MESSAGE_GROUP                    VARCHAR2(64)
CLIENT_ID                        VARCHAR2(64)
MODULE_ID                        VARCHAR2(64)
PROCESS_ID                       VARCHAR2(32)
THREAD_ID                        VARCHAR2(64)
USER_ID                          VARCHAR2(64)
INSTANCE_ID                      VARCHAR2(64)
DETAILED_LOCATION                VARCHAR2(160)
PROBLEM_KEY                      VARCHAR2(64)
UPSTREAM_COMP_ID                 VARCHAR2(100)
DOWNSTREAM_COMP_ID               VARCHAR2(100)
EXECUTION_CONTEXT_ID             VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE       NUMBER
ERROR_INSTANCE_ID                NUMBER
ERROR_INSTANCE_SEQUENCE          NUMBER
VERSION                          NUMBER
MESSAGE_TEXT                     VARCHAR2(2048)
MESSAGE_ARGUMENTS                VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES          VARCHAR2(128)
SUPPLEMENTAL_DETAILS             VARCHAR2(128)
PARTITION                        NUMBER
RECORD_ID                        NUMBER

Out of all attributes from x$dbgalertext, MESSAGE_TEXT attribute is the key here. It contains the alert log conent. Have nice time.

Please post your comments (if any).

Experiment on EXECUTE IMMEDIATE

Today, We will do an interesting experiment on EXECUTE IMMEDIATE statement. This statement allows us to execute DYNAMIC SQL Statements inside the PLSQL Procedure. It is one of the good feature from Oracle. For SYNTAX and more information about this you can find out in Oracle Documentation.

Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement.
Lets see,

SQL> -– Login as SYSDBA and Create the user called ‘DEMO’
SQL> show user
USER is "SYS"

SQL> create user demo identified by demo;

User created.

SQL> grant connect,resource to demo;

Grant succeeded.

Database user ‘Demo’ has been created successfully and granted CONNECT, RESOURCE roles to demo user. Okay, It is time to understand some basics of Oracle User Management.

Privilege: A privilege is a permission to execute a SQL Statement or to access another Database User’s Objects. From Oracle Perspective, there are two types or privileges, SYSTEM Privileges like (CREATE TABLE, CREATE VIEW, CREATE PUBLIC SYNONYM etc) and OBJECT Privileges like (SELECT, UPDATE, DELETE, INSERT on Objects eg. TABLE).

Role: A role is a set or group of privileges that can be granted to Users or another Role. “Role is a set of privileges“. Then, What are the privileges that are grouped in CONNECT and RESOURCE Roles?. Let me query the Data Dictionary Tables

SQL> desc role_sys_privs;
Name                       Null?        Type
-------------              --------     ------------   
ROLE                       NOT NULL     VARCHAR2(30)
PRIVILEGE                  NOT NULL     VARCHAR2(40)
ADMIN_OPTION                            VARCHAR2(3)

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘CONNECT’;

PRIVILEGE
--------------
CREATE SESSION

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘RESOURCE’;

PRIVILEGE
-----------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

Alright. Lets continue the experiment, As, i said earlier the user called ‘DEMO’ has been created and granted some roles. Now connect as DEMO user and Create a PLSQL procedure with EXECUTE IMMEDIATE Statement.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> /*Create a procedure to create table called t and
SQL>  attributes id,name with EXECUTE IMMEDIATE Statement */

SQL> create or replace procedure p
  2  is
  3  begin
  4   EXECUTE IMMEDIATE ‘CREATE TABLE T
  5                     (ID NUMBER, 
  6                      NAME VARCHAR2(20))’;
  7  end;
  8  /

Procedure created.

SQL> -- Created the procedure called “P”. 
SQL> -- Let me execute this procedure
SQL> execute p;
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “DEMO.P”, line 4
ORA-06512: at line 1

Oops!, got an error “insufficient privileges” - Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user (FYI: We have queried the Data Dictionary table to see the privileges that are mapped under RESOURCE role. SEE ABOVE). Then, What is stopping us? Let me grant the CREATE TABLE privilege directly to the DEMO user.

SQL> conn sys@orcl as sysdba
Enter password: *******
Connected.
SQL> show user
USER is "SYS"

SQL> grant CREATE TABLE to DEMO;

Grant succeeded.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> -- Let me execute the procedure now
SQL> execute p;

PL/SQL procedure successfully completed.

SQL> — Wow! Table ‘T’ got created.
SQL> desc t;
Name                Null?    Type
----                -----    ------------
ID                           NUMBER
NAME                         VARCHAR2(20)

The Conculsion is, In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be explicitly granted to the user.

Hope you have enjoyed the experiment :-)

Post your suggestions (if any).

Reverse Engineer - Database Schema

There are lots of tools available in the market to reverse engineer your existing database schema objects and creates the Data Model from it
(Eg. Microsoft Visio, JDeveloper etc).

To do this job, I have prepared a SQL Script; the resultset of this query will tell you the Parent/Child relationships (FK_TABLE, FK_COLUMN referring PK_TABLE, PK_COLUMN). I feel this will be very handy script for DBAs, Application Developers and Database Architect to analyze the Parent-Child relationship for their existing database objects in the schema.

I have executed the below script in SCOTT Schema, Please verify the output.

SQL>column fk_table format a30
SQL>column fk_column format a30
SQL>column pk_table format a30
SQL>column pk_column format a30
SQL>set linesize 140
SQL>
SQL> SELECT p.table_name pk_table, 
  2         p.column_name pk_column,
  3         c.table_name fk_table, 
  4      c.column_name fk_column
  5    FROM (SELECT uc.constraint_name, 
  6                 ucc.table_name, 
  7        ucc.column_name
  8            FROM user_constraints uc, 
  9           user_cons_columns ucc
 10           WHERE uc.constraint_name IN (
 11         SELECT r_constraint_name
 12                 FROM user_constraints
 13                 WHERE constraint_type = 'R')
 14           AND uc.table_name = ucc.table_name
 15           AND uc.constraint_name = ucc.constraint_name) p,
 16         (SELECT uc.r_constraint_name, 
 17           ucc.table_name, 
 18        ucc.column_name
 19            FROM user_constraints uc, 
 20          user_cons_columns ucc
 21           WHERE uc.constraint_name IN (
 22         SELECT constraint_name
 23                 FROM user_constraints
 24                 WHERE constraint_type = 'R')
 25             AND uc.table_name = ucc.table_name
 26             AND uc.constraint_name = ucc.constraint_name) c
 27   WHERE p.constraint_name = c.r_constraint_name;


PK_TABLE  PK_COLUMN  FK_TABLE  FK_COLUMN
--------  ---------  --------  ---------
DEPT      DEPTNO     EMP       DEPTNO

Hope this will be useful for you.

Tom Kyte's DBA Resolutions for 2009

Tom Kyte, Oracle DBA and founder of the Ask Tom blog, gave the Oracle Database Insider his resolutions for 2009 in hopes that other DBAs will follow suit. "In the coming year," says Kyte, "I resolve to...

o Practice a restore at least once a month under different scenarios. DBAs are allowed to make mistakes—with one exception: They cannot make mistakes in recovery. We can fix any other mistake easily, but not recovery mistakes.

o Learn something new. When was the last time you read the "what's new in" chapters in the documentation? Take a couple of minutes to see what's new.

o Participate in user group discussion forums. If I just do the same thing day in, day out, never experiencing what others are doing, I'll stagnate. The easiest way to stay current, to network, and to learn something new is to participate.

o Become a mentor for someone more junior. I'll get two things out of this: First, I will learn a ton of new stuff because they will ask me things I don't fully know. Second, I'll have put in place the next generation of DBAs so that I can move up or move over in the future. Training your replacement is one sure way to be able to progress yourself.

o Be more thoughtful, don’t jump to conclusions, take my time. As the old saying goes, haste makes waste. And that is never more true than when doing something that will take hours or longer to accomplish, as many DBA tasks may take. I will study the options and pick the one that is best suited for the problem at hand—realizing that the approach I take in 2009 might be very different from the one I took in 2008, 2000, 1995, or any other time.

Chicken-Egg Problem

Sometimes, it is very important to DEFER the checking of certain integrity constraints. “Chicken-and-Egg” is the best example for this.

Experiment,

The above diagram shows the Egg and Chicken table, their attributes and their relationship. But, If we try to create the table for the above Data Model we will end up with an error. The reason is that the CREATE TABLE statement for CHICKEN table refers to the EGG table, which has not been created yet. Creating EGG table will not help either, because EGG table referes to CHICKEN table.

To give solution to this problem, we need SQL Schema modification commands. Consider the following solution,

SQL> -- Create CHICKEN and EGG table without FOREIGN KEY Constraint
SQL> create table chicken( 
2    chicken_id number primary key,
3    egg_id number);


Table created.



SQL> create table egg(
2    egg_id number primary key,
3    chicken_id number);


Table created.



SQL> -- Add the FOREIGN KEY Constraint
SQL> alter table chicken add constraint chicken_ref_egg
2    foreign key(egg_id) references egg(egg_id)
3    initially deferred deferrable;


Table altered.



SQL> alter table egg add constraint egg_ref_chicken
2    foreign key(chicken_id) references chicken(chicken_id)
3    initially deferred deferrable;


Table altered.


SQL> /* NOTE: “INITIALLY DEFERRED DEFERRABLE” clause in the ALTER command tells 
SQL> oracle to do deferred constraint checking */

Okay, We have created the CHICKEN and EGG table with CROSS relationship. Now, How we are going to insert a records into these tables. Becuase, if we want to insert a record into CHICKEN table, EGG table should have been already populated and vice versa. Consider the following two scenarios,

SQL> –- Case 1: CONSISTENT Records

SQL> insert into chicken(chicken_id,egg_id) values (1,2);
1 row created.

SQL> insert into egg(egg_id,chicken_id) values(2,1);
1 row created.

SQL> commit;
Commit complete.

Wow, Inserted Successfully. Constraints has been Validated?, Not Yet. When it will validate? Because, We have declared the FOREIGN KEY Constraints as “DEFERRED”, they are only checked at the COMMIT point. Without DEFERRED constraint checking, we cannot insert anything into CHICKEN and EGG table, becuase the first INSERT would always be a Constraint Violation

SQL> – Case 2: INCONSISTENT Records.


SQL> insert into chicken(chicken_id,egg_id) values (101,102);


1 row created.


SQL> insert into egg(egg_id,chicken_id) values(103,104);


1 row created.


SQL>-- Hmm, Records are inconsistent but it was inserted.
SQL>-- Validation Done for inconsistent records? We need to wait till COMMIT point


SQL> select * from chicken;


CHICKEN_ID EGG_ID
---------- ------
1          2
101        102


SQL> select * from egg;


EGG_ID CHICKEN_ID
------ ----------
2      1
103    104


SQL> commit;


commit
*
ERROR at line 1:
ORA-02091: transaction rolled back


SQL> --Oops, INCONSISTENT records has been Rolled Back. Verify the result,


SQL> select * from chicken;


CHICKEN_ID EGG_ID
---------- ------
1          2


SQL> select * from egg;

EGG_ID CHICKEN_ID
------ ----------
2      1

Hope, you have enjoyed the experiment. Share your comments.

Experiment On Primary Key

Today, We will do the experiment on Primary Key.

What is Primary Key?
The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique or it can be generated by the DBMS. Primary keys may consist of a single attribute or multiple attributes in combination.

Assuming we have a CUSTOMER Table with CUST_ID (PK), CUST_NAME columns. Now by any chance this CUST_ID will repeat in the CUSTOMER Table.

Consider the following example,

SQL> -- Experiment On Primary Key…

SQL> -- Creating Customer Table


SQL>create table customer(
2   cust_id number,
3   cust_name varchar2(15));


Table created.


SQL> -- Note: While creating the table PK was not defined.
SQL> -- Now populate the records for customer table


SQL> insert into customer values(101,’John Peter’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> insert into customer values(102,’Tom Kyte’);
1 row created.


SQL> commit;
Commit complete.


SQL> select * from customer;


CUST_ID CUST_NAME
------- ---------
101     John Peter
102     Tom Kyte
102     Tom Kyte




SQL>/* Note: In the CUSTOMER Table, CUST_ID - 102 was repeated, Okay becuase PK is not yet defined. */



SQL> -- Now, We are going to define the PK Constraint for CUSTOMER Table


SQL> alter table customer add constraint customer_pk
2 primary key(cust_id) disable;


Table altered.


SQL> create index cust_id_idx on customer(cust_id);


Index created.


SQL> alter table customer enable novalidate primary key;


Table altered.


SQL> -- Now PK Constraint has been defined and enabled for CUSTOMER
SQL>
SQL> -- Verifying the Data Dictionary…
SQL>


SQL>select table_name,constraint_name,constraint_type,status
2   from user_constraints
3   where table_name=’CUSTOMER’;



TABLE_NAME   CONSTRAINT_NAME  C  STATUS
----------   ---------------  -  -------
CUSTOMER     CUSTOMER_PK      P  ENABLED


SQL> /* All right now we have a table called CUSTOMER with primary key defined for column CUST_ID and the CUSTOMER_PK constraint status is ENABLED now.*/


SQL> select * from customer;


CUST_ID  CUST_NAME
-------  ----------
101      John Peter
102      Tom Kyte
102      Tom Kyte

Oops!, What happened?, for CUSTOMER table table we have defined the PRIMARY KEY and its STATUS is enabled but stil, it has duplicate records, CUST_ID - 102 was repeated and while enabling the Primary Key constraint also Oracle did not show any error, stating that your Existing records are duplicated.

SQL> — After enabling the constraint, We will try to insert the duplicate record
SQL> — Lets see what it is telling…


SQL> insert into customer values(101,’John Peter’);


insert into customer values(101,’John Peter’)
*
ERROR at line 1:ORA-00001: unique constraint (DEMO.CUSTOMER_PK) violated

Now if i am trying to insert a duplicate record, it is throwing an error, so constraint is validating the data while inserting new record.

What about the existing duplicate records?
Hmm, The key here is NOVALIDATE clause in the ALTER TABLE CUSTOMER ENABLE… command, the function of this clause is it will skip the validation for existing records while enabling the constraint.

Where it will be useful?, NOVALIDATE clause it will be frequently used in DWH environment by which the user will be TRUSTING the data (that is no validation is required for this data) and they will be loading into their tables.

Any constraint (PRIMARY KEY/FOREIGN KEY/UNIQUE/CHECK) which is defined in the table, will take extra effort to do the validation while you insert a record into table. But it is always Best Practice to define your constraints in the database level.

Hope you have enjoyed the experiment :-)

Validating your Data Entry for Trailing Blanks

Today, I would like to experiment the use of CHECK constraint to validate the trailing blanks in the data.
Example,

demo$Vino%orcl> create table t(name varchar2(20)
2 check (length(name)-length(trim(name))=0));


Table created.


demo$Vino%orcl> insert into t values('Tom');
1 row created.


demo$Vino%orcl> insert into t values('Tom Peter');
1 row created.


demo$Vino%orcl> -- Name with trailing blanks


demo$Vino%orcl> insert into t values('Tom ');
insert into t values('Tom ')
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.SYS_C005409) violated


demo$Vino%orcl> select * from t;


NAME
----------
Tom
Tom Peter

Hope this will be useful for you :-)