Oracle Database tutorials How To install SQL Developer on windows 7

Start Oracle Enterprise Manager

How to start & how to use OEM (ORACLE ENTERPRISE MANAGER) step by step

for more detail please visit on www.ocptechnology.com

Step to clone a database using RMAN

How to make a clone database using rman step by step

for more detail visit on my official website www.ocptechnology,com

How to recover droped table using flashback

How to recover dropped table using flashback step by step

Oracle Tutroial - Point in time recovery restore sql

Traditional point in time recovery step by step

for more detail please visit on my official website www.ocptechnology.com

Oracle Tutorial - How to use Flashback Technology part -1

How to use oracle flashback technology step by step,

The oracle flashback technology is work like as a rewind button for the oracle database,

it can be used in case of internal logically data logical corruptions which is made by the users.

Oracle Tutorial - Composite partition Part - 4

How to create composite partition in ORACLE database step by step for more detail please visit on my official website www.ocptechnology.com.

Oracle Tutorial - HASH partition step by step part - 3

Oracle Tutorial step by step,

How to create HASH partition in oracle step by step.

Oracle Tutorial - range partition step by step part - 1

In this video i'm going to demonstrate you how to create RANGE partition in ORACLE table step by step,

following all step which is we discus with you in the below video then you can create it's easily.

for more detail please visit on my official website www.ocptechnology,com  then get more stuff of ORACLE.

Oracle Tutorial - range partition step by step part - 1

In this video i'm going to demonstrate you how to create RANGE partition in ORACLE table step by step,

following all step which is we discus with you in the below video then you can create it's easily.

for more detail please visit on my official website www.ocptechnology,com  then get more stuff of ORACLE.

Oracle Tutorial - List partition step by step Part-1

Hello friends in this video i'm going to show you how to create list partition in the oracle.

for more detail please visit on my official website www.ocptechnology.com

Performing RMAN tablespace point in time recovery TSPITR

How to restore tablespace in case of NO backup

In this video i'm going to demonstrate you how to restore tablespace in case of NO backup.

For more detail please visit on my official website www.ocptechnology.com

Query to Select top three Max Salary from Table

How to find to three salaries in oracle 11g step by step.

Permanent Mount Linux Volume Partition File System - RHEL 6

How to mount Linux volume partition permanently step by step

How to create New partition in Linux GUI based

How to add new partition in Linux GUI based step by step

ORACLE TUTORIAL - RMAN backup commands

Oracle RMAN useful commands.

Oracle Tutorial - How to finde duplicate records in table

How to find duplicate records in Oracle Database step by step



For More detail please visit on my official website www.ocptechnology.com

Oracle Tutorial - How to add virtual column in oracle 11g

How to add Virtual column in ORACLE 11g step by step for more detail please visit on my official website www.ocptechnology.com

11g installation step by step in easy way

How to install ORACLE 11g step by step in simple way

for more detail please visit on my official website www.ocptechnology.com

Linux Tutorial - How to change HOSTNAME in LINUX step by step

How to change HOSTNAME step by step in LINUX for more detail please visit on my official website www.ocptechnology.com

ORACLE TUTORIAL - How to install ORACLE LINUX-7 installation

How to install ORACLE LINUX 7 step by step for more detail please visit on my official website www.ocptehcnology.com

Oracle Tutorial - How to configure wallet manager step by step

How to configure Oracle wallet step by step for more detail please visit on my official website www.ocptechnology.com

Oracle tutorial - How to rename a username/schema

Oracle tutorial step by step for more detail please visit on official website www.coptechnology.com

How to transport a tablespace from linux to

Hello Friends

Here a video, In this video i'm demonstrate you how to Transport a tablespace from LINUX to WINDOWS step by step.



For more detail please visit on my official website www.ocptechnology.com.

How to transport a tablespace from linux to

Hello Friends

Here a video, In this video i'm demonstrate you how to Transport a tablespace from LINUX to WINDOWS step by step.



For more detail please visit on my official website www.ocptechnology.com.

How to configure ASM step by step

How to configure Oracle ASM step by step.

How to install ORACLE 10g R2 on Linux 5.5For more detail click here and visit on my official website www.ocptechnology.com
Hello Friends Welcome in OCP Technology, Today I’m going to show you how to install oracle 10g_R2 (10.2.0.1) on Red Hat Linux 5.5. The installation similar to server installation, minimum requirement 1GB RAM and 2GB swap area, Firewall and selinux must be disabled, for more detail please visit on my  YouTube channel. I request to all viewer if want to learn more about ORACLE DATABASE and you want to get my new feature YouTube videos and New posts in your mail box so please subscribe my channel and my website also and please share it in your friends circle.

Important packages for Linux installation. 

• Editors
• GNOME Desktop Environment
• Text based internet
• Graphical internet
• Development Libraries
• Legacy software development
• Development tools
• Server configuration tools
• Administration tools
• Legacy software support
• Base
• X Window system
• System tools

Step1. Make a directory using bellow command.

#mkdir /u01

Step2. Unzip the Oracle Database downloaded file, using following command.

#unzip 10201_database_linux_x86_32 –d/u01

 After unzip you have a single directory which is containing installation file name is database.

Step3. Edit Hosts file using following command. It’s contain ip address and hostname of server machine.

#vi /etc/hosts

<ip address> <full name of server machine> <synonym name of machine>

For example

192.168.1.1       rhel5.localdomain       rhel5

Step4. Now edit sysctl.conf file where set kernel parameters, add below lines.

#vi /etc/sysctl.conf

kernel.sem=250 32000 250 250
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144

save this file and run below command for applying current kernel parameters.

#/sbin/sysctl –p

Step5. Edit redhat-release file and change release version.

#vi /etc/redhat-release

Change the release from 5.5 to 4.5 in the release version.

Step6. Edit (‘/etc/security/limits.conf’) file, add all following lines in end of file.

*     soft  nproc       2047
*     hard  nproc       16384
*     soft  nofile      1024
*     hard  nofile      65536

Step7. Add bellow line in the (‘/etc/pam.d/login’) file.

 Session required pam_limits.so

Step8. Install all the below packages. Going in your DVD or ISO image using following command.

# cd /media/RHEL_5_x86_32/Server

Then run below command for installing .rpm file.

rpm -ivh compat-db-4.2.52-5.1.i386.rpm
 rpm –ivh compat-db-4*
 rpm –ivh gcc-4*
 rpm –ivh compat-gcc-34-3*
 rpm –ivh compat-libstdc++-33-3*
 rpm –ivh libaio-0*
 rpm –ivh glibc-2*
 rpm –ivh setarch-2*
 rpm -ivh compat-gcc-34-c3.4.6-4.i386.rpm
 rpm –ivh make-3*
 rpm -ivh libXp-1.0.0-8.i386.rpm
 rpm -ivh openmotif-2.3.0-0.3.el5.i386.rpm

#exit

Step9. Add new users and groups using following commands.

#groupadd dba
#groupadd oinstall
#groupadd oper
#useradd oracle
passwd oracle     (to set password for oracle user)
#usermod -g oinstall -G dba oracle
#chown -R oracle:oinstall /u01
#chmod -R 777 /u01

Step10. Now login in oracle user and add following line in (‘.bash_profile’)

#su – oracle

#oracle installation settings
  TMP=/tmp; export TMP
  TMPDIR=$TMP; export TMPDIR
  export ORACLE_BASE=/u01/app/oracle
  export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
  export ORACLE_TERM=xterm
  export PATH=$ORACLE_HOME/bin:$PATH
  export ORACLE_SID=orcl
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
  export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/rdbms/jlib
  if [$USER = “oracle” ]; then
  if [$SHELL = “/bin/ksh” ]; then
  ulimit –p  16384
  ulimit –n 65536
  else
  ulimit –u 16384 –n 65536
  fi
  fi

save it end reboot your pc then login as oracle user and following bellow commands.

$cd /u01/database/ (Enter)

$./runInstaller (Enter)

The oracle installation will begin at this point Follow the Graphical installation steps as as asked

Run both (2) the scripts in ROOT user terminal and then exit and enjoy, your database installation is completed.

How to install ORACLE 12c R1 on ORACLE Linux 7 step by step 

In this post i’m going to demonstrate you how to install ORACLE 12c Release 1 (12.1) on ORACLE LINUX-7 64 bit step by step. For ORACLE 12c  installation minimum 2gb swap required. 

STEP-1.
Download ORACLE 12c R1 software form www.oracle.com accept OTN License Agreement, it’s download in two parts and it’s zip file.
After downloading software unzip file using the following command.

# mkdir /u01
# unzip linuxamd64_12102_database_1of2 -d/u01
# unzip linuxamd64_12102_database_2of2 -d/u01

After unzip these zip files now you have a single folder name is “database” which is containing installation file.

STEP-2.

Now edit Hosts file, it’s contain IP Address & Hostname of machine using following command.
set host name using below command

#vi /etc/hostname

ol7.localdomain
 
# vi /etc/hosts

<machine IP address>  <hostname>  <nick name>

After editing hosts file it’ll be lock like this

127.0.0.1   localhost localhost.localdomain localhost4 
192.168.1.105    ol7.localdomain    oracle

STEP-3.

we have two Installation methods

1. Automatic
2. Manual

1. Automatic: For automatic installation run below command but before running below command you must have to configure YUM Server.

# yum install oracle-rdbms-server-12cR1-preinstall -y

this command is set automatically all requirement settings.

STEP-4.

2. Manual setup: 

Add bellow lines in “/etc/sysctl.conf” file using below command.

# vi /etc/sysctl.conf (open this file and past bellow parameters in end of this file).

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

save changes using press Esc then Shift+: then wq then hit Enter

After saving these changes, run below command for applying changes.

# /sbin/sysctl -p

STEP-5.

Add bellow line in ‘/etc/security/limits.conf’ using bellow command.

# vi /etc/security/limits.conf

oracle soft nofile 1024 
oracle hard nofile 65536 
oracle soft nproc 2047 
oracle hard nproc 16384 
oracle soft stack 10240 
oracle hard stack 32768

STEP-6.

Now install Linux packages which is required for ORACLE installation, following bellow command, here we are installing .rpm file using YUM server, for YUM server configuration please visit on my website and to through it. May be some packages are already install.

yum install binutils -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
yum install zlib-devel -y
yum install zlib-devel.i686 -y

STEP-7.

Now create some groups and user, using following commands.

# groupadd -g 54321 oinstall 
# groupadd -g 54322 dba 
# groupadd -g 54323 oper

Add ORACLE user.

# useradd -u 54321 -g oinstall -G dba,oper oracle
set the password for ORACLE user using following command.

# passwd oracle

STEP-8.

Change SELINUX mode using below command.

# vi /etc/selinux/config

SELINUX=permissive

And firewall must be disabled, using bellow command.

# systemctl disable firewalld

STEP-9.

Now create some appropriate Directories where is ORACLE software will be install, and giving privileges, using bellow command.

# mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1 
# chown -R oracle:oinstall /u01 chmod -R 775 /u01

STEP-10.

Now switch in ORACLE user and add bellow lines in end of file, using bellow command.

# su – oracle

$ vi .bash_profile

# Oracle installation Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol7.localdomain
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=cdb1

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

After saving this file, exit from ORACLE user and Restart your pc.

# reboot

STEP-11.

Now login with ORACLE user and start runInstaller using bellow command.

$ cd /u01/database

$ ./runInstaller

Then follow graphically instructions then Enjoy.

ORACLE TUTORIAL - Take full backup with archivelog file using RMAN

oracle tutorial step by step - how to change archivelog mode

How to create sequence step by step

Hello Friends in this tutorial i'm going to demonstrate you how to create sequence and how to use ORACLE - SQL sequence step by step.

ORACLE – SQL TUTORIAL STEP BY STEP.

SEQUENCE is an oracle shareable object which is generate numeric values and it can be unique. you can use this value into primary key and unique values.

Let see how to create SEQUENCE and how to user sequence step by step, following below steps.

STEP 1. To create sequence step by step.

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

Sequence created.

After creating sequence i’m going to create a new table and insert some records into table using this sequence step by step following command.

STEP 2. create a table using following command step by step.

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

Table created.

Now you done table creation and sequence creation after done that now i’m going to show you how to insert records in that table using sequence, for more clarification you can view my video on my YouTube channel and if get any knowledge from my video so subscribe my channel and like my video and if you want to give any suggestion regarding my contents and my videos so please write in comment box and you can also send my E-mails on my E-mail id ( ocptechno@gmail.com ).

STEP 3.

Let see how to insert records into table using sequence following commands.

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 when we are inserting 11th row it’ll be showing an error 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 see records from your table so it’s showing like this, using below command step by step.

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.

STEP 4.

so if you want to insert another some records into this table, then you have to increase maxvalue, using below command step by step.

SQL> ALTER SEQUENCE OCP
     MAXVALUE 200;

Sequence altered.

After setting max-value of sequence, you can insert more rows into your table.

And if you want to check your sequence current value and coming feature value then you can use below command step by step.

CURRVAL is use for the knowing current value of the sequence.

SQL> SELECT OCP.CURRVAL FROM DUAL;

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

NEXTVAL is use for the knowing NEXT feature VALUES.

SQL> SELECT OCP.NEXTVAL FROM DUAL;

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

For more detail please scroll and see YouTube video.

www.ocptechnology.com



www.youtube.com/user/ocptechno

How to use merge command in ORACLE SQL step by step

It was introduce in ORACLE 9i, the ability to conditionally update or insert into a database table. merge statement avoid separate updates, increases the performance and ease of use and is useful in data warehouse applications.

Step 1. create a table using below command then insert some records in this table.

SQL> create table student(id number,name varchar2(22), score number);
Table created.

Step 2. Now insert some record in this table using following command.

SQL> insert into student values(&id,'&name',&A);

 Enter value for id: 1
 Enter value for name: AMIT
 Enter value for a: 232
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(1,'AMIT',232)

 1 row created.

 SQL> /
 Enter value for id: 2
 Enter value for name: RAHUL
 Enter value for a: 2321
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(2,'RAHUL',2321)
 
1 row created.
 
SQL> /
 Enter value for id: 3
 Enter value for name: ANKIT
 Enter value for a: 843
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(3,'ANKIT',843)
 
1 row created.
 
SQL> /
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for a: 452
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(4,'AJAY',452)
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for a: 543
 old 1: insert into student values(&id,'&name',&A)
 new 1: insert into student values(5,'ARUN',543)
 
1 row created.

Then commit all changes using below command.

SQL> COMMIT; 

Commit complete.

Now select record from this table which is you created.

SQL> SELECT *FROM STUDENT;

 ID  NAME      SCORE
 --  ------   -------
 1   AMIT      232
 2   RAHUL     2321
 3   ANKIT     843
 4   AJAY      452
 5   ARUN      543

Step 3.

Now create one more table which is have same structure like student table and also have some same records using below command.

SQL> CREATE TABLE STUDENT_N AS SELECT * FROM STUDENT WHERE 1=2;
 
Table created.
 
SQL> INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S);
 Enter value for id: 4
 Enter value for name: AJAY
 Enter value for s: ''
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(4,'AJAY','')
 
1 row created.
 
SQL> /
 Enter value for id: 5
 Enter value for name: ARUN
 Enter value for s: 600
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(5,'ARUN',600)

 1 row created.
 
SQL> /
 Enter value for id: 6
 Enter value for name: SACHIN
 Enter value for s: 787
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(6,'SACHIN',787)

 1 row created.

 SQL> /
 Enter value for id: 7
 Enter value for name: SHRIPAL
 Enter value for s: 504
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(7,'SHRIPAL',504)
 
1 row created.
 
SQL> /
 Enter value for id: 8
 Enter value for name: SPSINGH
 Enter value for s: 889
 old 1: INSERT INTO STUDENT_N VALUES(&ID,'&NAME',&S)
 new 1: INSERT INTO STUDENT_N VALUES(8,'SPSINGH',889)
 
1 row created.
 
SQL> COMMIT;

Commit complete.

Retrieve records from second table.

SQL> SELECT *FROM STUDENT_N;

 ID   NAME     SCORE
---   ------- ------ 
 4    AJAY
 5    ARUN     600
 6    SACHIN   787
 7    SHRIPAL  504
 8    SPSINGH  889

Step 4. Now merge both table using following command, and get your output together.

SQL> merge into student a
   using (select id,name,score from student_n) b
   on (a.id=b.id)
   when matched then
   update set a.name=b.name,
   a.score=b.score
   when not matched then
   insert (a.id,a.name,a.score)
   values(b.id,b.name,b.score);

5 rows merged.

SQL> select * from student;
 
ID   NAME SCORE
 --  ------  ----- 
 1   AMIT     232
 2   RAHUL   2321
 3   ANKIT    843
 4   AJAY
 5   ARUN     600
 6   SACHIN   787
 7   SHRIPAL  504
 8   SPSINGH  889

 8 rows selected.

NOTE:- For more clarity of merge statement topic please view video, please scroll.

expdp impdp

ACL (access control list)

How to configure yum server in Redhat Linux 6

dns server in hindi

ftp server with uploading configuration in linux step by step

Oracle Installation

OCP TECHNOLOGY presents Oracle Installation on Linux platform step by step. In this tutorial we are using simple Hindi language so that every body can be understand easily. If you want to connect with us for more new videos so please SUBSCRIBE my channel.

HOME

OCP TECHNOLOGY provides online training on Oracle and Linux with 100% Satisfactions. Our course delivered through experienced and highly qualify team. You can connect with us anywhere and anytime for Online Training. Our goal is to provide the beast online training so that you can grow in your career.