How to fix undo block corruption (UNDOTBS) of database

One of users written today that he got the undo block corruption while he is trying to start the database.  The reason he explain that due to power fluctuation the database is abnormally shutdown. When he checks the alert log file then finds the following error:

ORA-01578: ORACLE data block corrupted (file # 8, block # 39)
ORA-01110: data file 8: 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\SAD1\UNDOTBS01.DBF'

Error 1578 happened during db open, shutting down database
USER: terminating instance due to error 1578
Errors in file E:\oracle\product\10.1.0\admin\sad1\bdump\sad1_arc3_1400.trc:The Primarily you have to check the alert log file for any type of error or corruption or you can use the utility dbv (database Verify utility) which provides a mechanism to validate the structure of oracle data files at the operating system level. You can use it on regular basis to inspect data files for sings of corruption but you can use this utility only with datafiles not with control or redolog file.

DBVERIFY - Verification starting: FILE = E:\ORACLE\PRODUCT\10.1.0\ORADATA\SAD1\UNDOTBS01.DBF

DBV-00200: Block, dba 887245421, already marked corrupted

DBVERIFY - Verification complete

Total Pages Examined         : 104340

Total Pages Processed (Data) : 0

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 101216

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 3124

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Highest block SCN            : 2144459844 (15.24)

Step1: If you have Cold backup

Connect with sys as sysdba

Shutdown immediate;

Startup mount;

Restore datafile 8;

Recover datafile 8;

Alter database open;

Step2: If you have RMAN backup

rman target sys/oracle@sad1.world

blockrecover datafile 8 block 22;

Step3: If you do not have backup

Create another tablespace

Create or edit pfile and change tablespace as:

Create pfile=’d:\sad_pfile.sql’ from spfile’;

undo_management=manual

undo_tablespace=UNDOTBS2
Startup Nomount pfile='d:\sad_pfile.sql';
Startup mount;
alter database open;
Create spfile from pfile=’d:\sad_pfile.sql’;

Now you are able to open the database as well as connect the schema but you are not able to perform any DDL and DML operation. This is due to the old rollback segements are still active and either needs recovery  or offline or drop the those segments. If you try to create any table within the connected schema will return the following error:

SQL> connect hrms/hrms

Connected.

SQL> Create table payment_master_test (ser number);

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace 'HRMS'

ORA-06512: at line 19

Errors in file D:\oracle\ora92\admin\sad1\bdump/sad1_smon_21134.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 238 cannot be read at this time

ORA-01110: data file 238: 'E:\f4\oradata\dwnon\undotbs201.dbf';

Try to find the old online rollback segment which needs recovery and make it offline one by one, thus will be easier to drop it.

SQL> Select segment_name, status from dba_rollback_segs where tablespace_name='UNDOTBS2' and status = 'NEEDS RECOVERY'; 

SEGMENT_NAME                   STATUS

------------------------------ ----------------

_SYSSMU4$                      NEEDS RECOVERY

_SYSSMU5$                      NEEDS RECOVERY

_SYSSMU6$                      NEEDS RECOVERY

_SYSSMU7$                      NEEDS RECOVERY

_SYSSMU8$                      NEEDS RECOVERY

_SYSSMU9$                      NEEDS RECOVERY

_SYSSMU10$                     NEEDS RECOVERY

SQL>alter rollback segment “_SYSSMU4$” offline;

SQL> drop rollback segment "_SYSSMU4$";

SQL> drop rollback segment "_SYSSMU5$";

Once drop all the rollback segments then easily you can drop the old undo tablespace.

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

ORA-02049 time-out: distributed transaction waiting for lock

Today one of the user written to me he faces the below error while trying to insert many records into one table: ORA-02049 time-out: distributed transaction waiting for lock

Finally he has decided to flush the shared pool as a solution to the above issue and asking me using the command:  “Alter system flush shared_pool” is having any other impact on the database.

Cause: The time to wait on a lock in a distributed transaction has been exceeded. This time is specified in the initialization parameterDISTRIBUTED_LOCK_TIMEOUT.

Action: This situation is treated as a deadlock and the statement was rolled back. To set the time-out interval to a longer interval, adjust the initialization parameter DISTRIBUTED_LOCK_TIMEOUT, then shut down and restart the instance.

Normally oracle keeps track of each SQL statement that users execute. It is store parsed in memory so that if a SQL statement has been executed then oracle does not need to reparse it as it is already parsed and stored in shared_pool.

Thus it is not a standard solution to flush shared_pool; you can use it only if you must. It will clear all the data in shared pool along with all your previous snapshot obsolete even your cache sequence number is also flushed.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

Using the following one can implement the functionality in a store procedure.
SQL> CREATE OR REPLACE procedure flush_shared_pool IS
v_cur INTEGER;
v_result INTEGER;
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur, ‘ALTER SYSTEM FLUSH SHARED_POOL’, DBMS_SQL.NATIVE);
v_result := DBMS_SQL.EXECUTE(v_cur);
DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/And in special circumstances one can grant execute access “grant execute on sys.flush_shared_pool to SCOTT;” to this store procedure if there is a need in a development environment.

How to Change DBTIMEZONE after Database Creation

How to Change DBTIMEZONE after Database Creation

DBTIMEZONE is a function which returns the current value of Database Time Zone. It can be queried using the example below:

SELECT DBTIMEZONE FROM DUAL;

DBTIME

------

-07:00

Please note the return type of function is Time Zone Offset. The format ([+|-] TZH: TZM) contains the lead (+) and lag (-) with hour and minutes specifications.
Notes:
1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.
Example:

SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS

--------

-06:00

3. Time zone is set during database creation or using CREATE DATABASE. It can be altered using ALTER DATABASE command. Database time zone cannot be altered if a column of type TIMESTAMP WITH [LOCAL] TIMEZONE exists in the database because TIMESTAMP with LOCAL TIMEZONE columns are stored to normalize the database. Time zone can be set in Location zone format or [+|-]HH:MM format.

In the case when you have any column with TIMESTAMP LOCAL TIMEZONE (TSLTZ) then you have to follow the backup–drop that table–change the timezone then restore that table. To check run the below query and notice the output:

Select u.name || '.' || o.name || '.' || c.name "Col TSLTZ"     

from sys.obj$ o, sys.col$ c, sys.user$ u     

where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

Col TSLTZ

--------------

ASSETDVP.TEST.TSTAMPFor Example follow the below steps:

1- Backup the table that contains this column (ASSETDVP.TEST.TSTAMP Table).

2- Drop the table or the column only

3- Issue again the alter database to change the DB time Zone

4- Add the dropped column and restore the data OR restore the table if it's dropped

Example:

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            -07:00

SQL> ALTER DATABASE SET TIME_ZONE='America/Menominee';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> Shutdown;

SQL> Startup;

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            +06:00

Note: Once the time zone is set, database must be bounced back to reflect this changes because alter database didn’t change the init.ora parameter.
4. Difference between SYSDATE and DBTIMEZONE- SYSDATE shows the date-time details provided by the OS on the server. It has nothing to do with TIMEZONE of the database.
5. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, whileSESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE. 

File Backup using ‘tar’ command

The “tar” command stands for tape archive that generally used by system/database administrator to write archives directly to tape devices or can use it to create archives files on disk. The ‘tar’ program is easy to use and transportable having limits on file name size, won't backup special files, does not follow symbolic links, and doesn’t support multiple volumes. The main advantage is that ‘tar’ is supported every where or can be moved easily from one disk to another disk or machine to machine. It is also useful for copying directories.

The ‘tar’ program takes one of three function command line arguments:

c – to create a tar file

t – table of contents (see the name of all files)

x – extract (restore) the contents of the tar file.

In addition to above function command line argument these argument are useful:

f – Specifies filename

z – Use zip/unzip to compress the tar file or to read from a compressed tar file.

v – Verbose output, show the file being stored into or restored from tar file.

Example:

To tar all .arc and .ctf files into a tar file named data.tgz use:

tar cvzf data.tgz *.arc *.ctf

This will creates (c) a compressed tar file name data.tgz (f) and shows the file being stored into the tar file (v). The .tgz suffix is a convention for gzipped tar file.

To extract files from tar file

tar –xvf filename.tar

To tar up all files and directories under current directories or under PROD1 directory and writes files to filename.tar.

tar cvzf data.tgz shahid123

tar  -cvf  /tmp/filename.tar . 

tar  -cvf  /tmp/filename.tar  PROD1

It is often more useful to tar a directory (which tar all files and subdirectories recursively unless you specify other option)

To see a tar file table of contents

tar tzf data.tgz

To display only the content in tar binary file

tar –tvf filename.tar 

When file size is too large (more than 8GB), use –E option.

 tar -cvfE /data/oradata/tars/PROD1/large_file_blob.tar 

How to Create Oracle database Manually

How to Create Oracle database Manually

There are basically three ways to create database:
Using the database configure Assistance (DBCA)
DBCA can be used to create the new database at the time of oracle installation as well as later at any time as a standalone tool, which provide a graphical interface (GUI) that guide you through the creation of database.
With the SQL create database statement.
You can use the CREATE DATABASE script to create the database manuallly from command prompt. For that you must have created previously your environment as a part of oracle installation (Install oracle software only).
Through upgrading an existing database.
If you are already using a previous release of oracle, You can upgrade your existing database and use it with new release of oracle software
This article basically focusing on the second option (only). It can be completed on the command line that is without any GUI.
Database creation prepares several operating system files to work together as an Oracle database. You need only create a database once, Thus you must carefully plan your database structure before creating a database such as:
1. Plan the database tables and indexes and estimate the amount of space they will require.
2. Plan the layout of underlying operating system. Proper distribution of I/O will improve your database performance. For example: Place redolog files and datafiles on seperate disks. Placing datafiles on seperate disk will reduce contention problem.
3. Consider using OMF feature to create and manage the operating system file that comprise your database storage.
4. Select the global database name, which is the name (DBNAME) and location (DOMAIN_NAME) of database within the network structure.
5. Develop good understanding of Pfile or spfile parameters.
6. You must select the database character set. All characters including data in data dictionary, is stored in database character set
7. Consider what time zones your database must support.
8. Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created. The SYSTEM tablespace and most other tablespaces use the standard block size. Additionally, you can specify up to four
non-standard block sizes when creating tablespaces.
9. Use an UNDO tablespace to manage your undo records, rather than rollback segments.
10. Develop a backup and recovery strategy to protect the database failure.
                                                                **Step to Create Database Manually**
Step1: Create all the necessary directories.
Step2: Prepare the database Script.
Step3: Prepare the init.ora file.
Step4: Startup created database with init.ora file.
Step5: Finally run the catalog.sql and catproc.sql scripts.

Step1: First create all the required directory on the destination server such as: Admin, adump, bdump, cdump, udump, Archive etc.
Step2: Next Prepare the database creation script such as:
Create Database Script on Windows Environment
----------------------------------------------------------------------------------------------------
Create database MY_DB
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
MAXINSTANCES 1 
MAXLOGHISTORY 292
logfile group 1 ('D:\oracle\MY_DB\redo1.log') size 10M, 
group 2 ('D:\oracle\MY_DB\redo2.log') size 10M,
group 3 ('D:\oracle\MY_DB\redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile 'D:\oracle\MY_DB\system_01.dbf'
size 50M autoextend on
next 20M maxsize unlimited
extent management local
sysaux datafile 'D:\oracle\MY_DB\sysaux_01.dbf'
size 10M autoextend on
next 10M maxsize unlimited
undo tablespace undotbs1
datafile 'D:\oracle\MY_DB\undotbs1_01.dbf' size 10M 
default temporary tablespace temp
tempfile 'D:\oracle\MY_DB\temp_01.dbf' size 10M; 
Note: On windows environment you need to create services using oradim such as:
CMD> ORADIM -NEW -SID MY_DB -PFILE='D:\oracle\admin\SADHAN\pfile\initSADHAN.ora';
Create Database Script on Linux Environment
----------------------------------------------------------------------------------------------------
Create database MY_DB
MAXLOGFILES 5 
MAXLOGMEMBERS 5 
MAXDATAFILES 100 
MAXINSTANCES 1 
MAXLOGHISTORY 292
logfile group 1 ('/u01/../redo1.log') size 10M, group 2 ('/u01/../redo2.log') size 10M,
group 3 ('/u01/../redo3.log') size 10M
character set WE8ISO8859P1
national character set utf8
datafile '/u01/../system_01.dbf' size 50M autoextend on next 20M maxsize unlimited
extent management local
sysaux datafile '/u01/../sysaux_01.dbf' size 10M autoextend on next 10M maxsize unlimited
undo tablespace undotbs1
datafile '/u01/../undotbs1_01.dbf' size 10M 
default temporary tablespace temp
tempfile '/u01/../temp_01.dbf' size 10M;
Step3: Prepare the init.ora file such as:
audit_file_dest='/u01/../MY_DB/admin/adump'
background_dump_dest='/u01/../MY_DB/admin/bdump' 
compatible='10.2.0.3.0' 
control_files='/u01/../MY_DB/control01.ctl', '/u01/../MY_DB/control02.ctl','/u01/../MY_DB/control03.ctl' 
core_dump_dest='/u01/../MY_DB/admin/cdump' 
db_block_size=8192 
db_domain='' 
db_file_multiblock_read_count=16 
db_name='MY_DB' 
dispatchers='(PROTOCOL=TCP) (SERVICE=my_dbXDB)' 
job_queue_processes=10 
log_archive_dest_1='LOCATION=/u01/../MY_DB/archive' 
log_archive_format='%t_%s_%r.dbf' 
open_cursors=300 
pga_aggregate_target=220200960 
processes=150 
remote_login_passwordfile='EXCLUSIVE' 
sga_target=629145600 
undo_management='AUTO' 
undo_tablespace='UNDOTBS' 
user_dump_dest='/u01/../MY_DB/admin/udump' 
db_recovery_file_dest='/u02/../MY_DB/backup' 
db_recovery_file_dest_size=230686720 
Step4: Now start the newly created database in nomount phase with the help of init.ora file.
$ export ORACLE_SID=my_db 
$ sqlplus / as sysdba 
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 21 10:26:54 2012 
Copyright (c) 1982, 2006, Oracle. All Rights Reserved. 
Connected to an idle instance. 
SQL> Startup Pfile=/u01/app/oracle/product/10.2.0/db_1/dbs/initmy_db.ora nomount; 
ORACLE instance started. 
Total System Global Area 629145600 bytes 
Fixed Size 1443789 bytes 
Variable Size 168878648 bytes 
Database Buffers 447849588 bytes 
Redo Buffers 7340032 bytes 
SQL> @My_db.sql 
Database created.
Step5: Finally run the catalog.sql and catproc.sql scripts.
Thus the database is created now. you just need to run the catalog.sql and catproc.sql scripts. You will find these script on the location: $ORACLE_HOME/rdbms/admin
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql 
SQL>@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql 
SQL> select name from v$database; 
NAME 
--------- 
MY_DB 
Finally now your database is ready to use. 

Deadlock Detected

A deadlock occurs when two or more session waiting for data locked by each other, resulting in all session being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are causes when transactions explicitly override the default locking of oracle in application design but not every time deadlock causes due to poor application code. Most of the time there are DBA related certain issues.

The most common cause of deadlocks is the normal row level locking, which is relatively easy to find. But that is not the only reason. ITL Shortage, Bitmap Index Locking, Lack of FK Index, Direct Path Load, Primary Key Overlap is also some of the potential causes.

When a deadlock occurs and one of the statements gets rolled back, Oracle records the incident in the alert log (‘D:\oracle\admin\SADHAN\bdump\ora_18301.trc’)and its corresponding tracefile. The tracefile shows more information about this error such as:

·         The session that cause it.

·         The effected session list.

·         The oracle SID and PID of the session.

·         The locked object row.

·         The full SQL statement causing this issue.

·         The system or Machine using this session

From the above information you can get some other useful information using another view like from Object_Id you can later get the object name and other details using DBA_OBJECTS view. Thus you must check primarily tracefile to come to the conclusion or exact cause of the deadlock.

Through this article I am trying to briefly describe the different situation to cause deadlock and the way to avoid them.

Deadlock due to row level locking:

Consider the situation where two different session performing update on particular table. Session1 updated row1 and still not committed and simultaneously Session2 updated row2 and does not committed now if Session1 try to update row2 which is already locked by Session1 (due to not committed), Session2 will wait; but this wait is forever, since Session1 is also waiting and can’t perform a commit or rollback until the wait is over, but the session1 wait will continue to exist until Session2 commits or rollback.

This is most common cause of deadlocks purely depend on application code and can only be solved by reducing the possibilities of occurrence of row locking.

Database lock are FIFO (First in First out) based, i.e. the session first waiting for the lock will get it first, before another session which started waiting for the same resource after the first session. There are two main type of row locking:

·        TM (Table Lock) – It is related to database structure change. Suppose if you executing a ‘SELECT’ query the table structure of that table remain same in that period. This lock protects the table structure being modified. Thus some one cannot add column during that query.

·        TX (Row Lock) – This is the row level locking; when a row is locked by a session this type of lock is acquired.

Deadlock due to ITL wait:

When a session locks a row, it put the information of lock in the header of the block called Interested Transaction List (ITL). Each ITLslot takes up 24 bytes.

Consider the situation when two session performing update operation on the same block. When Session1 update row1 (if there is no more freeITL and no room in block to create more) and simultaneously Session2 update row2 (if there is also no more free ITL and no room in block to create more). Now if you try to update row2 using session1 will hang because ITL slot is packed causing the session to wait on ITL. TheSession2 lock can not be resolved until Session1 release the lock.

Select owner, object_name, value

from v$segment_statistics

where statistic_name = 'ITL waits' and value > 0;

Deadlock due to foreign key

When a key value of parent table is updated or a row is deleted, oracle attempts to create TM lock on the entire child table (in the absence of the index). If an index is present on foreign key column, then oracle locates the corresponding child rows and lock only those rows.

Consider the scenario when foreign key column not being indexed in the table and through one session1 delete child row1 and session2 delete child row2 and now if session1 try to delete parent row1 then there is TM lock of whole table and if now session2 try to delete parent row2 then there is a deadlock on whole table. The below three clues together shows that this deadlock is due to FK contention:

–         TM locks for both the session, instead of TX.

–         The lock type of holder is shared exclusive (SX).

–         And session does not show any row information.

To deal with the above situation you need create indexes on those foreign key.

Deadlock due to direct load:

When a table is loaded with Direct Path, the entire table is locked from further DML, until committed. This lock will cause deadlock when two sessions try to load into the same table and the whole table is already locked by other session. Following are the symptoms for this type of deadlock are:

–         Lock type is TM (as shown in the Resource Name)

–         Lock mode for both the holders and waiters is X (indicating a row lock)

–         No row information (since it is not really row-related)

Deadlock due to Bitmap index Contention:

When a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore update to any of the rows covered by that index piece hangs.

When two sessions update two different rows covered by the same index piece, they wait for each other. Following are some symptoms that show this type of deadlock:

–         The lock type is TX

–         The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.

–         The waiter waits with mode "S" instead of "X"

–         The row information is available but the object ID is not the ID of the table; but the bitmap index.

In the above situation you need to alter the application logic in such a way that the two update will not happen in sequence without commits in between. If it is not possible, then you have to re-evaluate the need for a bitmap index (usually it is only for datawarehouse not for OLTP). 

Deadlock due to Primary key Overlap:

This case of deadlock occurs during inserts only (not updates or deletes). When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Following are some symptoms or this type of deadlock:

–         The lock type is TX (row lock)

–         The holders are holding the lock in "X" (exclusive) mode

–         The waiters are waiting for locks in “S” mode, even when the locks type TX.

–         The subsequent parts of the tracefile don’t show any row information.

Shell Script for Scheduled RMAN Backup

The script will first automatically create the directory and will move the old backup into it then will perform the fresh backup.
The script will manage the full backup of database (Noarchivelog). If you need you can slightly change the scripts to perform datafiles as well as archivelog backup.
################ SHELL SCRIPT FOR RMAN SCHEDULED BACKUP#################
cd /home/oraback
mkdir 'date '+%d%m%y''
mv * /home/oraback/'date '+%d%m%y''
cd /d01/oravis/db/tech_st/11.1.0
. VIS_sadhan.env
rman target /
@/home/oravis/rman_bkp.sql
EOF

rman_bkp.sql
run
{
allocate channel d1 type disk MAXPIECESIZE 5G;
allocate channel d2 type disk MAXPIECESIZE 5G;
backup full tag full_db format '/home/oraback/db_%t_%s_p%p'
(database);
release channel d1;
release channel d2;
BACKUP CURRENT CONTROLFILE FORMAT '/home/oraback/CTL%s_%t_%p';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
}

To Delete Duplicate rows from a table based on column values

This script will take a table name and list of columns as input which you desire to be unique and deletes all rows with the same value in these columns, leaving only the row with the minimum rowid. This script does not deal with NULL values in the columns.

Restrictions: This script will not work if the column(s) to be filtered on having a datatype of LONG, LONG RAW, RAW, CLOB, NCLOB, BLOB, BFILE or an object datatype. It will delete duplicate rows from a table based on supplied columns from a table.

REM Either you save it into a file "delete_dup.sql" and run it ‎later from SQL*plus or you can directly run the below script without saving it into file.

set echo off

set verify off heading off

undefine t

undefine c

prompt

prompt

prompt Enter name of table with duplicate rows

prompt

accept t prompt 'Table: '‎

prompt

select 'Table '||upper('&&t') from dual;‎

describe &&t

prompt

prompt Enter name(s) of column(s) which should be unique. If you want ‎to provide more

prompt than one column, you must separate them with commas.‎

prompt

accept c prompt 'Column(s): '‎

prompt

delete from &&t

where rowid not in (select min(rowid) from &&t group by &&c)

/

Example:

Create table EMP(EMP number(5), NAME VARCHAR2(30), SALARY number (10,2));

DESC EMP;

insert into EMP values(1, 'SHAHID', 6000);

insert into EMP values(1, 'SHAHID', 6000);

insert into EMP values(1, 'SHAHID', 6000);

insert into EMP values(1, 'KHALID', 6000);

insert into EMP values(2, 'SHAHID', 6000);

insert into EMP values(3, 'SHAHID', 4000);

EMP   NAME        SALARY

----- ----------  -------

1     SHAHID      6000

1     SHAHID      6000

1     SHAHID      6000

1     KHALID      6000

2     SHAHID      6000

3     SHAHID      4000

When you run the above script either thruogh file or directly it will prompt you.

SQL> @"D:\Backup\Scripts\delete_dup.sql"‎

Enter name of table with duplicate rows

Table: emp

‎1 row selected.‎

Name                        Null?    Type

-------------------         -----    --------------‎

‎EMP                                  NUMBER‎ (5)

‎NAME                                 VARCHAR2‎ (30)

‎SALARY                               NUMBER‎ (10,2)

Enter name(s) of column(s) which should be unique. If you want to provide more than ‎one column, you must separate them with commas.‎

Column(s): EMP,SALARY

‎3 row deleted.‎

SELECT * FROM EMP;

EMP   NAME        SALARY

----- ----------- -------

1     SHAHID      6000

2     SHAHID      6000

3     SHAHID      4000