How to add SWAP Space in Linux

Sometimes we need to add extra swap space in our Linux machine, so in this article, I’m going to show you how to add swap space to a Linux server.

We have two ways to adding swap space in Linux environment.

  1. Create swap space using the file.
  2. Use Hard disk to adding swap space.

Check existing swap details using free -m command.

free -m

Check swap partition details using swapon -s.

swapon -s

1. Create swap space using file

Creating swap space using the dd command, here we are going to create a swap file named swap_pfile as ROOT user.

dd if=/dev/zero of=/swap_file bs=1G count=1

The above command will create a swap file with the name swap_file inside the root directory with the size of 1024MB (1GB).

Now we need to change the permission of swap file so that only root user can access the swap file.

chmod 600 /swap_file

Enable swap area using mkswap command.

mkswap /swap_file

Add the below entry in the fstab file so that swap file become persistent across every reboot.

vi /etc/fstab

/swap_file  		swap  			swap  	defaults  	0 0

Finally enable the newly created swap file using swapon command.

# swapon /swap_file

Now check the swap details, swap increased from 4GB to 5GB.

free -m
swapon -s

2. Use Hard disk to adding swap space

If you have free space in your existing hard drive or you have any extra hard disk then create a partion using fdisk command and use this partition as swap space.

Read – how to add new hard disk in linux

After adding new hard disk in linux, just follow the below steps.

Check existing disk partition using fdisk -l command, in my case the /dev/sdb1 is free which is 10GB size.

#fdisk -l

How to use existing mount point as swap space

Using mkswap command we can convert existing disk partition in swap space.

#mkswap /dev/sdb1

Enable swap

Now enable swap space for newly created swap file just using swapon command.

#swapon /dev/sdb1

Make sure the newly created swap file entry must be available in /etc/fstab so that the swap file is available automatically after reboot the system.

#vi /etc/fstab

dev/sdb1 swap swap defaults 0 0

Check existing swap details

Using swapon -s command will help us to find the available swap space information.

Check existing memorty and swap details

The free -k command show the memory (RAM) and swap space information.

ORA-12919: Can not drop the default permanent tablespace

Hi, Today I am getting the “ORA-12919: Can not drop the default permanent tablespace” error. Actually, I tried to drop users tablespace which is the default tablespace for the database user and I’m getting the above error.

ORA-12919 Can not drop the default permanent tablespace

SQL> drop tablespace datatbs;
drop tablespace datatbs
*
ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

Cause – We tried to drop default tablespace.

Action – Make another tablespace as the default tablespace for the database and drop.

You should firstly change the default tablespace of database to new tablespace as follows.

SQL> alter database default tablespace data;

Database altered.

Now tried to drop tablespace.

SQL> drop tablespace datatbs;

Tablespace dropped.

Read – How To Export Tablespaces Using Expdp Data Pump Utility?

How to change default tablespace for a user

Use the below command to check default tablespace for a user.

select username,default_tablespace from dba_users where username = 'SHRIPAL';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
SHRIPAL  USERS

In the above query the shripal user has the tablespace USERS as default.

Change default tablespace

SQL> alter user SHRIPAL default tablespace SHRIPAL_TBS;

User altered.

Check the user details.

select username,default_tablespace from dba_users where username = 'SHRIPAL';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
SHRIPAL  SHRIPAL_TBS

Now your default tablespace has been changed.

How To Export Tablespaces Using Expdp Data Pump Utility?

Hi, In this article we are going to learn how to export tablespaces using expdp data pump utility practically.

What is tablespace?

A tablespace is a logical unit of storage that is used by the database to store database objects like tables and PL/SQL codes.

We can export tablespaces using the expdp utility which is also called logical backup of the tablespace in the Oracle database. The export backup of tablespace contained all the object’s data with metadata. So let’s do the practical how we can take the export backup of tablespace using the expdp utility.

Before taking export backup of tablespace we need to check some information about the tablespaces like how many tablespaces we have with the help of “v$tablespace”. Login in your database as sysdba then run the following command which displays all tablespace names.

SQL> select name from v$tablespace;

Steps to take export backup using data pump utility

There are only four steps to take backup.

  1. Create OS level Directory
  2. Create directory object
  3. Grant mandatory privileges
  4. Export the tablespace

1. Create OS level Directory

To taking the export backup we need a place where our export backup saves, so you can create a folder on any location in your system. The directory must be available on the database server not on the client machine. In my case, I create a directory (folder) by the name “shripal” inside “/u01/” so my directory location is: “/u01/shripal

2. Create Directory object

Login in your database as sys user run the following command to create a logical directory object in database.

$sqlplus / as sysdba

SQL> create directory exp_tab as '/u01/shripal';

In the above query, the exp_tab is the name of a directory object, you can give any name here as per your requirement. During the export, we are using the same name as our directory which is pointing to the physical directory (/u01/shripa).

3. Grant mandatory privileges

If you are taking the export backup using sys user then you do not need to grant any privileges to anyone. But if you are going to take export backup using another schema then you should grant read, write and export, import privileges to that user.

Suppose in my case i have a user “DIGITAL” so we need to grant below privileges to this user.

SQL> grant read, write on exp_tab to digital;

Grant Export and Import Privileges to user

SQL> grant exp_full_database, imp_full_database to digital;

4. Export the Tablespace

At this point we have done all the required settings, run the following command to take an export backup of the tablespace.

$expdp digital/digital@db12c directory=exp_tab dumpfile=tablespace_bkp.dmp logfile=tablespace.log tablespaces=users

The above command must be run on $ prompt.

Lets understand above export tablespace query:

EXPDP – expdp is a command which is called the datapump utility with the user credentials through which user you want to perform the export followed by SID (@db12c), SID is an optional but that is a good practice.

DIRECTORY – The directory parameter specifies the name of the directory object which we created in the second step. Which confirms the location where all the backup files store.

DUMPFILE – The dumpfile parameter specified the name of the dumpfile which stores all the exported data, like in my case I give the name of the dumpfile as “tablespace_bkp.dmp“. The dumpfile is a binary file.

LOGFILE – The logfile parameter defines the export log name, you can give any name here. The logfile is a text file that is human readable.

TABLESPACE – The tablespace parameter has identified the name of the tablespaces which you want to take export backup and it’s confirmed to data pump utility run in tablespace mode. Here you can specify multiple tablespace’s names separated by comma (,).

Read – Check Datapump dump file is corrupted or not

SQL Character Function

What is Character Functions

Hello, friends in this article we are going to discuss how to use Character Functions in SQL. The character functions accept only Character type values and return in character and number both.

Type of Character Functions

There are Eight types of Character Functions in SQL.

  1. CONCAT
  2. SUBSTR
  3. LENGTH
  4. INSTR
  5. LPAD
  6. RPAD
  7. REPLACE
  8. TRIM

So, let’s understand the working of each function one by one.

1. CONCAT

The CONCAT function joint two or more strings and display as a single string. We can use a minimum of two strings with the CONCAT function. For example, we have two strings like “OCP” and “TECHNOLOGY” and we want to display both strings together.

SQL> SELECT  CONCAT('OCP','TECHNOLOGY') FROM DUAL;

The output from the above query is “OCPTECHNOLOGY“.

2. SUBSTR

The SUBSTR function Retrieve a part of the string as specified the length in function, as you can see in the below output. As we have a string like “OCPTECHNOLOGY” and we just want to display only the first three characters of the string.

SQL> SELECT SUBSTR('OCPTECHNOLOGY',1,3) FROM DUAL;

So, from the above query, we get the “OCP” as output.

3. LENGTH

The LENGTH function calculates the length of a string as a numeric value. Suppose we have a string like “OCPTECHNOLOGY” and we just want to count the letters of the sting, so you can use the length function.

SQL> SELECT LENGTH('OCPTECHNOLOGY') FROM DUAL;

The output is 13.

4. INSTR

The INSTR function shows the position of a given character in number. For example, you want to find out the position of “T” later from the “OCPTECHNOLOGY” string.

SQL> SELECT INSTR('OCPTECHNOLOGY','T') FROM DUAL;

Output from above query is 4.

5. LPAD

The LPAD (Left Pad) function will change the length of your column as you want for the session like you want your salary column with 10 digits. So LPAD solve your problem for the current session. The LPAD shows the character value just right justified.

SQL> SELECT LPAD(SAL,10,'*') FROM EMP;

In my case like my salary is 35000 rupees, so above query will return like *****35000 as output.

6. RPAD

The RPAD funcation (Right pad) is just opposite of LPAD function which shows the character value just left justified.

SQL> SELECT RPAD(SAL,10,'*') FROM EMP;

Suppose the salary is 35000 rupees, so above query will return like 35000***** as output.

7. REPLACE

The REPLACE function just replace the value, it is work same like MS WORD, Notepad etc.

SQL> SELECT REPLACE('BLACK AND BLUE','BL','J') FROM DUAL;

In above query i just replace the ‘BL’ with ‘J’ and the output is “JACK AND JUE“.

8. TRIM

The TRIM function just trim a character or heading or both from a string.

SQL> SELECT TRIM('O' FROM 'OCPTECHNOLOGY') FROM DUAL;

From the above query, the TRIM function just trims the ‘O‘ letter from ‘OCPTECHNOLOGY‘ and you get ‘CPTECHNOLOGY‘ as output.

Thanks for the Visit

SQL String Functions.
Substring Function in SQL.
Character functions in SQL.

Read – Single Row Functions

Single Row Functions

Single row functions operate on a single row only then return one result each row, there are many types of single-row functions.

Following are the types of Single Row Functions.

  1. Number Function.
  2. Date Function.
  3. Character Function.
  4. Conversion Function.
  5. General Function.

Number Function

Always accept only numeric values and also return always numeric values. Number Function has three sub-functions like ROUND, TRUNC, MODE.

ROUND:- It’ll be round a value to specified decimal point.

Ex1:- SELECT ROUND(456.5) FROM DUAL; 

This query will return 457 as an output because of the round function round that value which comes after the decimal point and 1 add-in before the decimal point value.

Ex2: SELECT ROUND(456.526,2) FROM DUAL; 

This query will return 456.53 as an output, in this case, 2 is the length of the decimal point that we want to print with value.

Ex3: SELECT ROUND(456.526,-1) FROM DUAL; 

This query will return 460 as a output, in this case -1 is a length of before decimal point what we want to print.

TRUNC:- It’ll be truncates a values specified decimal point.

Ex1. SELECT TRUNC(456.5) FROM DUAL; 

This query will return  456 as a output, trunc never be increment in value.

Ex2. SELECT TRUNC(456.426,2) FROM DUAL; 

This query will return 456.42 as output, 2 is a length of after decimal point value.

Ex3. SELECT TRUNC(456.426,-1) FROM DUAL; 

This query will return 450 as output, -1 is a length of before decimal point value.

MOD:- It’ll be always return reminder of division.

Ex1: SELECT MOD(100,3) FROM DUAL; 

Mod always return that value which is remain after divination this query will return 1.

Date Function: 

Always work on DATE type Data type except MOTHS_BETWEEN function because it return a number.

These functions comes under in Date Function.

  • MONTHS_BETWEEN (Count number of months between two dates).
Ex1: SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) RESULT FROM OCP; 

This will show you the number of months between in sysdate and hiredate.

Ex2. SELECT MONTHS_BETWEEN('06-SEP-2014','06-JAN-1989') FROM DUAL; 

This will show you number of months between these two dates.

  • ADD_MONTHS (Add number of months in date).
Ex: SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL; 

This will show you a date which is after six months.

  • NEXT_DAY (It’ll show next day of calendar date specified).
Ex1: SELECT NEXT_DAY(SYSDATE,'SUNDAY') FROM DUAL;
Ex2: SELECT NEXT_DAY('06-SEP-2014','MONDAY') FROM DUAL;
  • LAST_DAY (It’ll show the last day of the month).
Ex1: SELECT LAST_DAY(SYSDATE) FROM DUAL;
Ex2: SELECT LAST_DAY('06-JAN-1989') FROM DUAL;
  1. Conversion Function: It converts a value from one data type to another data type.
  2. General Function: It has many types of sub-functions like NULLIF, NVL, NVL2, COALESCE, CASE, DECODE, these all functions have different functionality.

Read – How to create a SEQUENCE step by step

How to create a SEQUENCE step by step

In this article we are going to learn how to create and use SEQUENCE.

What is SEQUENCE?

SEQUENCE is an oracle shareable object which is generated numeric values and it can be unique. you can use this value as a primary key and unique value. Let see how to create SEQUENCE and how to use sequence step by step.

How to create SEQUENCE?

To create sequence use below query.

SQL> create sequence ocp
     minvalue 10
     start with 10
     increment by 10
     maxvalue 100
     nocycle
     nocache;

Sequence created.

After creating the sequence now I’m going to create a table and insert some records into this table using the above-created sequence step by step.

Use below query to create a table.

SQL> create table acer(roll_num number,stu_name varchar2(11));

Table created.

Now we have done table creation and sequence creation after doing this, Now I’m going to show you how to insert records in the above table using sequence, for more clarification you can watch a video on my YouTube channel.

How to insert records in table using sequence?

Using following query we can insert records automatically in a particular column using sequence.

SQL> insert into acer values(ocp.nextval,'&stu_name');
Enter value for stu_name: AMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AMIT'1 row created.SQL> /
Enter value for stu_name: SUMIT
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SUMIT')

1 row created.

SQL> /
Enter value for stu_name: ARSH
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ARSH')

1 row created.

SQL> /
Enter value for stu_name: DEEP
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'DEEP')

1 row created.

SQL> /
Enter value for stu_name: SACHIN 
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SACHIN')

1 row created.

SQL> /
Enter value for stu_name: VIKRAM
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'VIKRAM')

1 row created.

SQL> /
Enter value for stu_name: RAHUL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'RAHUL')

1 row created.

SQL> /
Enter value for stu_name: AJAY
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'AJAY')

1 row created.

SQL> /
Enter value for stu_name: TANIYA
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'TANIYA')

1 row created.

SQL> /
Enter value for stu_name: SHRIPAL
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'SHRIPAL')

1 row created.

After inserting 10 rows in this table, if we are trying to inserting the 11th row it’ll be showing an error that look like this. because of that maxvalue exceeds.

SQL> /
Enter value for stu_name: ANKUR
old 1: insert into acer values(ocp.nextval,'&stu_name')
new 1: insert into acer values(ocp.nextval,'ANKUR')
insert into acer values(ocp.nextval,'ANKUR')
 *ERROR at line 1:
ORA-08004: sequence OCP.NEXTVAL exceeds MAXVALUE and cannot be instantiated

If you select records from your table, it will be showing like this, using below query.

SQL> SELECT * FROM ACER;

ROLL_NUM STU_NAME
------   --------
 10      AMIT
 20      SUMIT
 30      ARSH
 40      DEEP
 50      SACHIN
 60      VIKRAM
 70      RAHUL
 80      AJAY
 90      TANIYA
 100     SHRIPAL

10 rows selected.

So, if you want to insert more records into this table, then you need to increase maxvalue parameters, using the below query.

SQL> ALTER SEQUENCE OCP
          MAXVALUE 200;

Sequence altered.

After updating the maxvalue parameter, you can insert more rows into your table and if you want to check your sequence current value and coming future value then you can use the below query.

CURRVAL is help us to find current value of the sequence.

SQL> SELECT OCP.CURRVAL FROM DUAL;

CURRVAL
----------
 100

NEXTVAL is help us to find NEXT future VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

NEXTVAL
----------
 110

Read – ORACLE ALTER TABLE MODIFY

ORACLE ALTER TABLE MODIFY

Hello friends, In this article, we are going to discuss, how to use the ALTER TABLE ORACLE MODIFY column. Using this command we can modify table structure if required after table creation.

Alter Table Oracle Modify

In this practical we are using following table which has three columns.

SQL> desc ocptech;

  Name          Null?    Type
 ------------  ------   ----------
 ID                            NUMBER
 DETAILS                  VARCHAR2(60)
 J_DATE                    DATE

Modify Column

Suppose, I want to change the datatype of DETAILS columns in the above table, so we are using the following query.

SQL> alter table ocptech modify details number;

 Table altered.

Now check table structure, it is changed.

SQL> desc ocptech;

 Name          Null?    Type
----------  --------   --------
 ID                      NUMBER
 DETAILS                 NUMBER
 J_DATE                  DATE

Alter table Add Column

Using the alter command we can add or remove columns from the table, using the following query we can add a new column in ocptech table.

SQL> alter table ocptech add xyz number;

Alter table Drop Column

As we discuss above, Using following query we can remove a column.

SQL> alter table ocptech drop column xyz;

Table altered.

Read – What is SQL, DBMS, and RDBMS

How To Find CPU And Memory Information Of Oracle Database Server

Hello friends, In this article we are going to find cpu and memory information of oracle database server.

The below script will help us to get CPU, memory and core, socket information of an Oracle database server from SQL prompt.

set pagesize 299
set lines 299

SQL> select STAT_NAME,to_char(VALUE) as VALUE ,COMMENTS from v$osstat where stat_name IN  
            ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
      select STAT_NAME,VALUE/1024/1024/1024 || ' GB' ,COMMENTS from v$osstat where stat_name IN ('PHYSICAL_MEMORY_BYTES');

Output from above query.

Read: How To Find Execution History Of An sql_id

How To Find Execution History Of An sql_id

Hello friends, in this article I’m going to show you how to find execution history of an sql_id.

The below script will display the execution history of a sql_id from AWR. It will join the dba_hist_sqlstat and dba_hist_sqlsnapshot table to get the required information.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

Read: ORA 25153 Temporary Tablespace is Empty

ORA 25153 Temporary Tablespace is Empty

Here is the solution of ORA 25153.

If you faced the above error just add more space in the temporary tablespace or add a new tempfile in the temporary tablespace.

How to check temporary tablespace in oracle

Check the temporary tablespace size using the below query.

SQL> select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

Or you can use following query.

SQL> select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE IN MB" from dba_temp_files;

TABLESPACE_NAME                FILE_NAME                                SIZE IN MB
------------------------------ ---------------------------------------- ----------
TEMP                           /u01/app/oracle/oradata/ocppr/temp01.dbf        60

Change the the size of tempfile

To change the tempfile size use the below query.

SQL> alter database tempfile '/u01/app/oracle/oradata/ocppr/temp01.dbf' resize 100m;

Database altered.

Now check again the size of tempfile.

SQL> select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

     FILE# NAME                                     SIZE IN MB
---------- ---------------------------------------- ----------
         1 /u01/app/oracle/oradata/ocppr/temp01.dbf        100

You can see here the size of tempfile is changed from 60 MB to 100 MB.

How to add new tempfile

If your tempfile size if fully used then you need to add new tempfile, use the below command to add new tempfile.

SQL> alter tablespace temp
  2  add tempfile '/u01/app/oracle/oradata/ocppr/temp02.dbf' size 100m;

Tablespace altered.

After adding new tempfile, now you can see the two tempfiles in your database.

SQL>  select FILE#,NAME,BYTES/1024/1024 "SIZE IN MB" FROM V$TEMPFILE;

     FILE# NAME                                     SIZE IN MB
---------- ---------------------------------------- ----------
         1 /u01/app/oracle/oradata/ocppr/temp01.dbf        100
         2 /u01/app/oracle/oradata/ocppr/temp02.dbf        100

So, this is the solution of ORA 25153 error.

I hope you found this article useful, if yes please subscribe our YouTube channel.

Read: How To Get Tablespace Quota Details Of An User In Oracle