Finding duplicate values in a table in Oracle

When I was trying to add constraint of primary key on existing table with some data i was getting following error:

ORA-02437: cannot validate- primary key violated

Later i realize the error was due to duplicate entries in the column to be assigned as primary key. Then i executed following syntax to find duplicate entries for modification:

Syntax 1:

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Syntax 2:

select my_id, count(*) from my_table group by my_id having count(*) >1

Changing 'Primary Key' in Oracle Table

Changing 'Primary Key' is two steps process as under:

Step 1:DROP existing primary key:

alter table my_table drop constraint my_pk;

or,

An alternative syntax to drop the existing primary key (e.g. if you don't know the constraint name):
alter table my_table drop primary key;

Step 2: Re-create the primary key:

alter table my_table add constraint my_pk primary key (city_id, buildtime, time);

However, if there are other tables with foreign keys that reference this primary key, then you will need to drop those first, do the above, and then re-create the foreign keys with the new column list.

Multi-row insert in Oracle

Today's  discovery:

Inserting multi-rows   

There are various methods for this mentioned as under:

1. Using INSERT ALL:

The syntax for the this:

INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;

Oracle default port list

The following table contains Oracle default ports for different products like Oracle Database or Oracle Application Server.
Changing the default ports can help to stop simple attacks but not real portscans. In the world of Oracle it is very often not possible to change the default port because the port is hardcoded.

At least for the Oracle database (except iasdb) it's is recommended to change the TNS listener port from 1521/1526 to something else.

Defining environment variable ORACLE_SID

While I was trying to open Oracle Enterprise Manager Console in client browser by starting the dbconsole process I got the following error message:

Error:  Environment variable ORACLE_SID not defined. Please define it.

Defining environment variable ORACLE_SID

Steps:

1. Navigate into your ORACLE_HOME/bin directory.
2. set ORACLE_SID = dbname.

Enabling iSQL*Plus DBA Access [ in Oracle 10.2.0 g ]

From past two days i'm trying to login  into iSQL*Plus DBA workspace but was not able to login though i was able to login into Enterprise manager. Each time i was getting "401 unauthorized access message ", and following errors:

ERROR - ORA-12154: TNS:could not resolve the connect identifier specified   

Solution: Enable iSQL*Plus DBA Access first

After googling too much i finally found the solution. To login into  iSQL*Plus DBA workspace first we have to Enable iSQL*Plus DBA Access. Following are the stages: