Search This Blog

Tuesday, June 25, 2013

Naming and Coding Conventions for SQL and PL/SQL

I have had a look through the document and it is mainly concerned with making code neat and maintainable and NOT with writing efficient code. Although the content is commendable and well intended, I have never actually encountered a site where such an approach succeeds in practice.




Why have standards?



Reference :
  • a discussion on the OTN forums

Monday, June 17, 2013

ORACLE - UNIQUE Constraints and NULL Values

In Oracle UNIQUE constraint allows more than one NULL values to be inserted. ORACLE considers one NULL value is not equal to another NULL value. Consider the following example.
CREATE TABLE test1 (
col1 VARCHAR2(2),
col2 VARCHAR2(2)
);
Table created
ALTER TABLE test1
ADD CONSTRAINT test_unique UNIQUE (col1);
Table altered
INSERT INTO test1 VALUES ('a', 'a');
1 row inserted 
INSERT INTO test1 VALUES ('a', 'a');
ORA-00001: unique constraint (TEST_UNIQUE) violated
INSERT INTO test1 VALUES (NULL, 'a');
1 row inserted 
INSERT INTO test1 VALUES (NULL, 'a');
1 row inserted
Now we will test the UNIQUE constraint with two columns (composite UNIQUE constraint).
ALTER TABLE test1
DROP CONSTRAINT test_unique;

TRUNCATE TABLE table1;

ALTER TABLE test1
ADD CONSTRAINT unique2 UNIQUE (col1,col2);
Insert null values to both columns.
INSERT INTO test1 VALUES (NULL, NULL);
1 row inserted 
INSERT INTO test1 VALUES (NULL, NULL);
1 row inserted
But the results changes when we have only one NULL value for this composite UNIQUE constraint.
INSERT INTO test1 VALUES (NULL, 'a');
1 row inserted
INSERT INTO test1 VALUES (NULL, 'a');
ORA-00001: unique constraint (UNIQUE2) violated
When we create a UNIQUE constraint, ORACLE creates a UNIQUE INDEX for this constraint. The NULL values are not included in the INDEX so ORACLE allows inserting many number of (NULL, NULL) value pairs to this table.

Monday, June 03, 2013

Error: ORA-01033 ORACLE initialization or shutdown in progress


First, You will have to wait until the database was shutdown or start-up properly.
Second, If not successes, I will be assuming that some how a data file was corrupted so try this steps:

SQL> shutdown abort
then
SQL> startup nomount
SQL> alter database mount;
SQL> alter database open;
If there will be some errors let me know...

Sunday, June 02, 2013

kill specific connection on Oracle Database

Once we figure out who is on the system, we will probably want to know what they are doing. In this case, we will join the v$session view we just queried with another view, the V$SQL view. This alter statement kill the selected session
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM KILL SESSION '26,6764' IMMEDIATE;

How to list active / open connections in Oracle

There are times that you may need to issue a “SHUTDOWN IMMEDIATE” command to an Oracle Database. It is critical that you should consider checking who are the users that are currently running sessions on that Database. You don’t want to shutdown on your Boss or VP on Finance. Here’s a simple SQL to find all Active sessions in your Oracle Database:
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where b.paddr = a.addr
  and type='USER'
order by spid;

Saturday, June 01, 2013

Retrieve the table space name for specific user or table

use this syntax to retrive the data about oracle table or user or even by owner.
select *
FROM ALL_TABLES;