tag:blogger.com,1999:blog-5754968806133645742024-03-20T03:00:20.443+05:30Oracle Experiments...“Knowledge comes with experience and experimentation” by Tom Kyte.Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-575496880613364574.post-55524854768119581352009-06-18T14:15:00.017+05:302009-06-18T15:22:48.202+05:30Oracle 11g R1: DDL_LOCK_TIMEOUT<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
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.
<pre class="source-code">
<code>
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.
</code>
</pre>
<i>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 </i>
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
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.
<br>
<br>
In Oracle Database 11g, we have a solution for the above problem: <i>DDL Wait option</i>
<pre class="source-code">
<code>
SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;
Session altered.
</code>
</pre>
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.
<pre class="source-code">
<code>
SQL> ALTER TABLE T ADD STATUS CHAR(1);
</code>
</pre>
The above statement hangs and does not give any error, instead of trying repeatedly for an exclusive lock to exeucte alter command.
<br>
Globally (for all sessions) also you can enable this feature by the below command;
<pre class="source-code">
<code>
SQL> ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30;
System altered.
</code>
</pre>
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
<pre class="source-code">
<code>
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.
</code>
</pre>
Cheers,
<br>
Hope you have enjoyed the experiment.
<br>
Post your comments (if any).
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com0tag:blogger.com,1999:blog-575496880613364574.post-65526701723529985282009-06-09T11:51:00.025+05:302009-06-09T14:02:22.769+05:30All about crontab<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
crontab is a unix utility that allows tasks (shell programs/commands) to be automatically run in the background at according to the specified intervals.
<br>
It is one of essential utility which has been used by DBAs on day to day basis.
I have used <i>Solaris 5.9</i> for examples posted in this article.
<br>
<br>
Here is my standpoint on crontab (based on my practical and theoretical knowledge),
<pre class="source-code">
<code>
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
<br>
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
<br>
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.
<br>
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)
<br>
Symbols:
~~~~~~~
- Used to specify the range between two integers
, Used to specifiy the multiple integers
<br>
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
</code>
</pre>
Hope you have a gained some knowledge about Unix Job Scheduler. Have a nice day.
<br>
<br>
Please post your comments (if any).
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com0tag:blogger.com,1999:blog-575496880613364574.post-16657108253934172142009-06-01T09:32:00.057+05:302009-06-02T12:11:21.515+05:30Beware of ALTER TABLE MOVE Command<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
Yesterday, i read about <b>Rebuilding of Table</b> in "Practical Oracle 8i" By Jonathan Lewis, it was well explained. Very good book, yes its my all time favorite book.
<br>
I would like to post an article about the possible ways of rebuilding table (mainly with the help of MOVE command) and its impacts.
<br>
<br>
Oracle provides many ways to reorganize/rebuild a table,
<dir>
<li>alter table move
<li>dbms_redefinition
<li>ctas (ie. create table as select...)
<li>exp/imp
<li>expdp/impdp (datapump)
</dir>
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.
<br>
<br>
<i> 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.
</i>
<br>
<br>
Now we will see an impact of MOVE command,
<br>
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.
<br>
<br>
<b>ALTER INDEX index_name REBUILD;</b>
<br>
<br>
Here is my experiment,
<br>
<pre class="source-code">
<code>
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
</code>
</pre>
The conclusion is, after execution of move command, the dependent indexes should be rebuilt immediately.
<br>
<br>
Hope you have enjoyed the experiment.
<br>
<br>
Please post your comments (if any).
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com2tag:blogger.com,1999:blog-575496880613364574.post-86049906537176943632009-05-14T12:59:00.014+05:302009-05-14T14:36:32.731+05:30Oracle 11g - Alert Log (X$DBGALERTEXT)<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
Today, i would like to share one new feature of Oracle 11g which i have come across recently.
<br>
<br>
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.
<pre class="source-code">
<code>
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
</code>
</pre>
Out of all attributes from x$dbgalertext, MESSAGE_TEXT attribute is the key here. It contains the alert log conent. Have nice time.
<br>
<br>
Please post your comments (if any).
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com1tag:blogger.com,1999:blog-575496880613364574.post-13998945285137371992009-05-04T18:20:00.018+05:302009-05-05T16:09:37.830+05:30Experiment on EXECUTE IMMEDIATE<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
Today, We will do an interesting experiment on <b>EXECUTE IMMEDIATE</b> 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.
<br>
<br>
Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement.<br> Lets see,
<pre class="source-code">
<code>
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.
</code>
</pre>
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.
<br>
<br>
<b>Privilege:</b>
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).
<br>
<br>
<b>Role:</b>
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
<pre class="source-code">
<code>
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.
</code>
</pre>
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.
<pre class="source-code">
<code>
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
</code>
</pre>
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.
<pre class="source-code">
<code>
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)
</code>
</pre>
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.
<br>
<br>
Hope you have enjoyed the experiment :-)
<br>
<br>
Post your suggestions (if any).
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com4tag:blogger.com,1999:blog-575496880613364574.post-29698721135543042842009-05-04T10:02:00.013+05:302009-05-04T11:32:29.049+05:30Reverse Engineer - Database Schema<div align="justify"><span style="font-family:trebuchet ms;"><p align="justify">
There are lots of tools available in the market to reverse engineer your existing database schema objects and creates the Data Model from it <br>(Eg. Microsoft Visio, JDeveloper etc).<br>
<br>
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.
<br>
<br>
I have executed the below script in SCOTT Schema, Please verify the output.
<pre class="source-code">
<code>
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
</code>
</pre>
Hope this will be useful for you.
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com3tag:blogger.com,1999:blog-575496880613364574.post-65858684338123671812009-02-11T13:02:00.006+05:302009-02-11T13:14:03.969+05:30Tom Kyte's DBA Resolutions for 2009<p align="justify"><span style="font-family:trebuchet ms;">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... </span></p><p align="justify"><span style="font-family:Trebuchet MS;">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. </span></p><p align="justify"><span style="font-family:Trebuchet MS;">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. </span></p><p align="justify"><span style="font-family:Trebuchet MS;">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.</span></p><p align="justify"><span style="font-family:Trebuchet MS;">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.</span></p><p align="justify"><span style="font-family:Trebuchet MS;">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.</span></p>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com1tag:blogger.com,1999:blog-575496880613364574.post-72554436110663659732008-11-15T16:24:00.027+05:302009-01-18T13:20:46.493+05:30Chicken-Egg Problem<div align="justify"><span style="font-family:trebuchet ms;">
Sometimes, it is very important to DEFER the checking of certain integrity constraints. “Chicken-and-Egg” is the best example for this.
<br/>
<br/>
Experiment,
<div align="justify"></div>
<img id="BLOGGER_PHOTO_ID_5268901888845596770" style="DISPLAY: block; MARGIN: 0px auto 10px; WIDTH: 320px; CURSOR: hand; HEIGHT: 84px; TEXT-ALIGN: center" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoY0-o5Y9ire0L7WHvZSCcAvHNoWlrzRZdMP59cLSMdCqYVNDXAkrUyvqqFVCd7yGiaKPWm8gkozv0aJ92go1vN9GPFQa9t1BCKcy4c6VTjjADusv5o8RysA9B3e3E4eHZ1V7EzA3wBrKr/s320/chicken-egg3.gif" border="0" />
<p align="justify"><span style="font-family:trebuchet ms;">
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.</span>
</p>
To give solution to this problem, we need SQL Schema modification commands. Consider the following solution,
<pre class="source-code"><code>
SQL> -- Create CHICKEN and EGG table without FOREIGN KEY Constraint
SQL> create table chicken(
2 chicken_id number primary key,
3 egg_id number);
<br/>
Table created.
<br/>
SQL> create table egg(
2 egg_id number primary key,
3 chicken_id number);
<br/>
Table created.
<br/>
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;
<br/>
Table altered.
<br/>
SQL> alter table egg add constraint egg_ref_chicken
2 foreign key(chicken_id) references chicken(chicken_id)
3 initially deferred deferrable;
<br/>
Table altered.
<br/>
SQL> /* NOTE: “INITIALLY DEFERRED DEFERRABLE” clause in the ALTER command tells
SQL> oracle to do deferred constraint checking */
<br/>
</code></pre>
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,
<pre class="source-code"><code>
SQL> –- <strong>Case 1: CONSISTENT Records</strong>
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.
</pre></code>
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
<pre class="source-code"><code>
SQL> – <strong>Case 2: INCONSISTENT Records.</strong>
<br/>
SQL> insert into chicken(chicken_id,egg_id) values (101,102);
<br/>
1 row created.
<br/>
SQL> insert into egg(egg_id,chicken_id) values(103,104);
<br/>
1 row created.
<br/>
SQL>-- Hmm, Records are inconsistent but it was inserted.
SQL>-- Validation Done for inconsistent records? We need to wait till COMMIT point
<br/>
SQL> select * from chicken;
<br/>
CHICKEN_ID EGG_ID
---------- ------
1 2
101 102
<br/>
SQL> select * from egg;
<br/>
EGG_ID CHICKEN_ID
------ ----------
2 1
103 104
<br/>
SQL> commit;
<br/>
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
<br/>
SQL> --Oops, INCONSISTENT records has been Rolled Back. Verify the result,
<br/>
SQL> select * from chicken;
<br/>
CHICKEN_ID EGG_ID
---------- ------
1 2
<br/>
SQL> select * from egg;
EGG_ID CHICKEN_ID
------ ----------
2 1
</code></pre>
Hope, you have enjoyed the experiment.
Share your comments.
</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com0tag:blogger.com,1999:blog-575496880613364574.post-72198800722970958792008-10-09T11:43:00.020+05:302009-01-18T12:37:24.710+05:30Experiment On Primary Key<div align="justify"><span style="font-family:trebuchet ms;">Today, We will do the experiment on Primary Key.
<br/>
<br/>
<strong>What is Primary Key? </strong>
<br/>
<em>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.</em>
<br/>
<br/>
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.
<br/>
<br/>
Consider the following example,
<br/>
<pre class="source-code">
<code>
SQL> -- Experiment On Primary Key…
SQL> -- Creating Customer Table
<br/>
SQL>create table customer(
2 cust_id number,
3 cust_name varchar2(15));
<br/>
Table created.
<br/>
SQL> -- Note: While creating the table PK was not defined.
SQL> -- Now populate the records for customer table
<br/>
SQL> insert into customer values(101,’John Peter’);
1 row created.
<br/>
SQL> insert into customer values(102,’Tom Kyte’);
1 row created.
<br/>
SQL> insert into customer values(102,’Tom Kyte’);
1 row created.
<br/>
SQL> commit;
Commit complete.
<br/>
SQL> select * from customer;
<br/>
CUST_ID CUST_NAME
------- ---------
101 John Peter
102 Tom Kyte
102 Tom Kyte
<br/>
<br/>
SQL>/* Note: In the CUSTOMER Table, CUST_ID - 102 was repeated, Okay becuase PK is not yet defined. */
<br/>
SQL> -- Now, We are going to define the PK Constraint for CUSTOMER Table
<br/>
SQL> alter table customer add constraint customer_pk
2 primary key(cust_id) disable;
<br/>
Table altered.
<br/>
SQL> create index cust_id_idx on customer(cust_id);
<br/>
Index created.
<br/>
SQL> alter table customer enable novalidate primary key;
<br/>
Table altered.
<br/>
SQL> -- Now PK Constraint has been defined and enabled for CUSTOMER
SQL>
SQL> -- Verifying the Data Dictionary…
SQL>
<br/>
SQL>select table_name,constraint_name,constraint_type,status
2 from user_constraints
3 where table_name=’CUSTOMER’;
<br/>
TABLE_NAME CONSTRAINT_NAME C STATUS
---------- --------------- - -------
CUSTOMER CUSTOMER_PK P ENABLED
<br/>
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.*/
<br/>
SQL> select * from customer;
<br/>
CUST_ID CUST_NAME
------- ----------
101 John Peter
102 Tom Kyte
102 Tom Kyte
<br/>
</code></pre>
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.
<pre class="source-code">
<code>
SQL> — After enabling the constraint, We will try to insert the duplicate record
SQL> — Lets see what it is telling…
<br/>
SQL> insert into customer values(101,’John Peter’);
<br/>
insert into customer values(101,’John Peter’)
*
ERROR at line 1:ORA-00001: unique constraint (DEMO.CUSTOMER_PK) violated
<br/>
</pre>
</code>
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.
<br/>
<br/>
What about the existing duplicate records?
<br/>
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.
<br/>
<br/>
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.
<br/>
<br/>
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.
<br/>
<br/>
Hope you have enjoyed the experiment :-)
<br/>
</span></div></span></span>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com0tag:blogger.com,1999:blog-575496880613364574.post-86952917307915866672008-10-04T19:29:00.038+05:302009-01-18T12:39:50.731+05:30Validating your Data Entry for Trailing Blanks<div align="justify"><span style="font-family:trebuchet ms;">
<p align="justify">
Today, I would like to experiment the use of CHECK constraint to
validate the trailing blanks in the data.
<br/>
Example,
<pre class="source-code">
demo$Vino%orcl> create table t(name varchar2(20)
2 check (length(name)-length(trim(name))=0));
<br/>
Table created.
<br/>
demo$Vino%orcl> insert into t values('Tom');
1 row created.
<br/>
demo$Vino%orcl> insert into t values('Tom Peter');
1 row created.
<br/>
demo$Vino%orcl> -- Name with trailing blanks
<br/>
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
<br/>
demo$Vino%orcl> select * from t;
<br/>
NAME
----------
Tom
Tom Peter
</pre>
Hope this will be useful for you :-)</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com1tag:blogger.com,1999:blog-575496880613364574.post-92183376161997829942008-09-29T16:21:00.012+05:302009-06-02T09:55:14.606+05:30Welcome Aboard...<div align="justify"><span style="font-family:trebuchet ms;">This is Vinoth from India. I have been using Oracle since the year 2006. I have started my career as a Oracle PLSQL Developer. Now I am practicing as a Oracle DBA and willing to Learn, Research, Teach and Share Oracle related stuff.
<br><br>
My all time favorite hero is Tom Kyte (Vice President, Oracle Corporation). Learnt lot of things from him and still lot more are there.
<br><br>
In this blog, i will be posting oracle related experiments which i have experienced, and i am open to your commends/feedbacks. Please send your suggestions at </span><a href="mailto:vinothkumar.srinivasan@gmail.com"><span style="font-family:trebuchet ms;">vinothkumar.srinivasan@gmail.com</a>
<br>
<br>
“Knowledge comes with experience and experimentation” by Tom Kyte
Have a nice time.</span></div>Vinothhttp://www.blogger.com/profile/05483891509388027622noreply@blogger.com2