How to Delete Duplicate Rows from Oracle Tables

Let’s see how we can delete the duplicate rows in a table. First we need to find the duplicated records by comparing/grouping columns, then we’ll use ROWID to separate the rows.

Here’s a script to generate the sample table:

In this table, column named “no” is unique, but I’ll use ROWID to identify the rows. I’ve added this column to show which records will not be deleted.

Let’s remove the duplicate rows:

Here’s the result after deleting rows:
1 GOKHAN 232-4446735
4 TOM 800-4446735
7 MIKE 900-4446735

Maximum Datafile Size In an Oracle Database

Each Oracle datafile can contain maximum 4194303 (4 Million) data blocks. So maximum file size is 4194303 multiplied by the database block size.

Block Size | Maximum Datafile Size
2k 4194303 * 2k = 8 GB
4k 4194303 * 4k = 16 GB
8k 4194303 * 8k = 32 GB
16k 4194303 * 16k = 64 GB
32k 4194303 * 32k = 128 GB

In Oracle Database 10g, BIGFILE tablespace was introduced. The BIGFILE tablespace can ONLY have a single datafile, but this datafile can contain maximum 4294967295 (4 billion) data blocks.

Block Size | Maximum Datafile Size
2k 4294967295 * 2k = 8 TB
4k 4294967295 * 4k = 16 TB
8k 4294967295 * 8k = 32 TB
16k 4294967295 * 16k = 64 TB
32k 4294967295 * 32k = 128 TB

How to Select a Random Row

I’ve seen similar questions in programming forums about how to select a random row from a table. Most RDBMS have functions to generate random numbers, so with a simple query we can select a random row(s):

The above command will sort the HR.EMPLOYEES table in random order, so we’ll be able to select a random. If you want to limit the number of returning rows, you can use subquery method:

How to Import Data from XML

One of my blog readers asked me how to import data from XML file. There are lots of ways to do it but I’ll show only a simple one by using DBMS_XMLSTORE.

We will need a simple XML file to import. So I created a folder as “/oracle/xmltest” and a text file named (sample.xml) in this folder. Oracle should be able to access this folder and file, so check OS permissions of them and be sure that they are readable by oracle user.

I entered the followed data into my sample xml file:

As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:

Oracle Table Functions

A table function is a PL/SQL function which will behave like a row source when queried. So you can perform transformations to the data before it is returned in the result set.

To write a table function, first we need to define an object as the row structure:

Because our function will return a table, we’ll also define a type: