About vmstat (Virtual Memory Statistics)

The vmstat helps you to identify bottlenecks on your server. It displays real-time performance information about processes, memory, paging, disk I/O, and CPU usage.

Here’s sample output (Oracle Linux 5.6):

Descriptions of the columns:

r: The number of processes waiting for run time
b: The number of processes in uninterruptible sleep
swpd: Total virtual memory (swap) in use (KB)
free: Total idle memory (KB)
buff: Total memory used as buffers (KB)
cache: Total memory used as cache (KB)
si: Amount of memory swapped in from disk (KB/s).
so: Amount of memory swapped to disk (KB/s).
bi: Blocks received from a block device (blocks/s).
bo: Blocks sent to a block device (blocks/s).
in: The number of interrupts per second, including the clock.
cs: The number of context switches per second.
us: User-level code time as a percentage of total CPU time
sy: System-level code time as a percentage of total CPU time
id: Idle time as a percentage of total CPU time.
wa: Time spent waiting for IO.
st: Time stolen from a virtual machine.

How to Add Swap Space in Linux

If the swap space is not enough, you can add a new swap file to your server. Login as root user and then run the following commands to add 10GB of swap space:

You can verify if new swap space was added successfully:

If you want to remove the swap file, run the following commands:

If you want to add this swap space permanently, you need to include it in your /etc/fstab file. Edit the fstab file and add an entry that looks like this:

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: