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

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.

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...

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;

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;