Create a PrimaryKey on existing table without unique values

lets assume that there was a table ABC that was already existing in the database and you want to add an additional column with unique primary key values.


sql to create table ABC :

  CREATE TABLE "ABC"
   (    "USERNAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
    "USER_ID" NUMBER NOT NULL ENABLE,
    "CREATED" DATE NOT NULL ENABLE )
   TABLESPACE "USERS" ;

Now we  can add an additional column ID which will be populated with all unique values for PrimaryKey.

alter table abc add(ID NUMBER);

We will now create a sequence and get the values from the seq and insert them into table ABC new ID column:

CREATE SEQUENCE SEQ_ID
START WITH 1
INCREMENT BY 1
MAXVALUE 999999
MINVALUE 1
NOCYCLE;

Now insert the unique values into the database with below sql:
UPDATE abc SET ID = SEQ_ID.NEXTVAL;


now you can add unique constraint (or) add primary key constraint to table,so that it wont take any more duplicate value into the table.

alter table abc add primarykey (ID);

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

In this scenario I am trying to increase the value of parameter memory_max_target. My initial memory_max_target = 804 I want to increase it to 900

SQL> show parameter sga

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga     boolean FALSE
pre_page_sga     boolean FALSE
sga_max_size     big integer 804M
sga_target     big integer 0

SQL> show parameter max_target

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_max_target     big integer 804M

SQL> show parameter memory

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 804M
memory_target     big integer 804M
shared_memory_address     integer 0

SQL> alter system set memory_max_target=900 scope=spfile;

System altered.

SQL> show parameter memory;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 804M
memory_target     big integer 804M
shared_memory_address     integer 0

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup mount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET
SQL> startup nomount;
ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Since we can't login into DB to check the value  that was set. Lets create pfile and check the actual value.

SQL> create pfile from spfile;

File created.

[oracle@Linux01 ~]$ cd $ORACLE_HOME/dbs

[oracle@Linux01 dbs]$ ls -ll

[oracle@Linux01 dbs]$ vi initDB11G.ora

Haha .. here is the problem in my case.

*********************************************************************************
In my case the problem is that, I didn't mention the MEMORY_MAX_TARGET in MB
Changing the value to MB did the trick
*********************************************************************************

[oracle@Linux01 dbs]$ sqlplus  /"AS sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 24 23:46:18 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

Below reboot not needed but since I want to use spfile. I did it

SQL> startup pfile='$ORACLE_HOME/dbs/initDB11G.ora';
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  939495424 bytes
Fixed Size    2218952 bytes
Variable Size  675284024 bytes
Database Buffers  255852544 bytes
Redo Buffers    6139904 bytes
Database mounted.
Database opened.
SQL>


SQL> show parameter memory;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address     integer 0
memory_max_target     big integer 900M
memory_target     big integer 800M
shared_memory_address     integer 0

SQLSERVER QUERIES - SQLSERVER2015

SELECT TOP 1000 [FNAME]
      ,[LNAME]
      ,[ID]
  FROM [TESTDB].[dbo].[USERS]

FNAME    LNAME    ID
Arvind    Reddy    1
Ravi    Reddy    2
Tom        Shawn    3


SELECT TOP 1000 [ORDER_ID]
      ,[USER_ID]
      ,[ORDER_INFO]
      ,[ORDER_AMT]
  FROM [TESTDB2].[dbo].[USER_ORDERS]
 
ORDER_ID    USER_ID    ORDER_INFO    ORDER_AMT
9001            1        BOOKS        10
9002            2        SHOES        20
 

SELECT A.[ORDER_ID]
      ,A.[USER_ID]
      ,A.[ORDER_INFO]
      ,A.[ORDER_AMT],B.ID,A.USER_ID
  FROM [TESTDB2].[dbo].[USER_ORDERS] A ,[TESTDB].[dbo].[USERS] B where A.USER_ID=B.ID ;
 
ORDER_ID    USER_ID    ORDER_INFO    ORDER_AMT    ID    USER_ID
9001            1        BOOKS        10        1        1
9002            2        SHOES        20        2        2


Address    Phone    City    User_id    ID
1234 test    2145524585    Hyd    1    1
52426 test    5246853652    Hyd    2    2
582 test st    5286943568    Bglr    3    3
768 TEST RD    56799976887    OMAHA    4    4
768 TEST RD    56799976887    OMAHA    5    5

chr function and its values - CHR and ASCII values

chr function returns the ascii letter for that integer. We know that there are 255 ascii characters defined.

SQL> select chr(65) as CHR from dual;

CHR
–
A

Below code print all 255 ascii characters

Sample code to check the values :

begin
  for i in 1..255 loop
      dbms_output.put_line( 'CHR('||i||')' ||'=='|| chr(i) );
    end loop;
    end;
    /

This output might differ actually based on the chacterset you have choosen while installing you Database.

DBMS_OUTPUT :


   CHR(1)==
CHR(2)==
CHR(3)==
CHR(4)==
CHR(5)==
CHR(6)==
CHR(7)==
CHR(8)==
CHR(9)==   
CHR(10)==

CHR(11)==
CHR(12)==
CHR(13)==
CHR(14)==
CHR(15)==
CHR(16)==
CHR(17)==
CHR(18)==
CHR(19)==
CHR(20)==
CHR(21)==
CHR(22)==
CHR(23)==
CHR(24)==
CHR(25)==
CHR(26)==
CHR(27)==
CHR(28)==
CHR(29)==
CHR(30)==
CHR(31)==
CHR(32)==
CHR(33)==!
CHR(34)=="
CHR(35)==#
CHR(36)==$
CHR(37)==%
CHR(38)==&
CHR(39)=='
CHR(40)==(
CHR(41)==)
CHR(42)==*
CHR(43)==+
CHR(44)==,
CHR(45)==-
CHR(46)==.
CHR(47)==/
CHR(48)==0
CHR(49)==1
CHR(50)==2
CHR(51)==3
CHR(52)==4
CHR(53)==5
CHR(54)==6
CHR(55)==7
CHR(56)==8
CHR(57)==9
CHR(58)==:
CHR(59)==;
CHR(60)==<
CHR(61)===
CHR(62)==>
CHR(63)==?
CHR(64)==@
CHR(65)==A
CHR(66)==B
CHR(67)==C
CHR(68)==D
CHR(69)==E
CHR(70)==F
CHR(71)==G
CHR(72)==H
CHR(73)==I
CHR(74)==J
CHR(75)==K
CHR(76)==L
CHR(77)==M
CHR(78)==N
CHR(79)==O
CHR(80)==P
CHR(81)==Q
CHR(82)==R
CHR(83)==S
CHR(84)==T
CHR(85)==U
CHR(86)==V
CHR(87)==W
CHR(88)==X
CHR(89)==Y
CHR(90)==Z
CHR(91)==[
CHR(92)==\
CHR(93)==]
CHR(94)==^
CHR(95)==_
CHR(96)==`
CHR(97)==a
CHR(98)==b
CHR(99)==c
CHR(100)==d
CHR(101)==e
CHR(102)==f
CHR(103)==g
CHR(104)==h
CHR(105)==i
CHR(106)==j
CHR(107)==k
CHR(108)==l
CHR(109)==m
CHR(110)==n
CHR(111)==o
CHR(112)==p
CHR(113)==q
CHR(114)==r
CHR(115)==s
CHR(116)==t
CHR(117)==u
CHR(118)==v
CHR(119)==w
CHR(120)==x
CHR(121)==y
CHR(122)==z
CHR(123)=={
CHR(124)==|
CHR(125)==}
CHR(126)==~
CHR(127)==
CHR(128)==€
CHR(129)==
CHR(130)==‚
CHR(131)==ƒ
CHR(132)==„
CHR(133)==…
CHR(134)==†
CHR(135)==‡
CHR(136)==ˆ
CHR(137)==‰
CHR(138)==Š
CHR(139)==‹
CHR(140)==Œ
CHR(141)==
CHR(142)==Ž
CHR(143)==
CHR(144)==
CHR(145)==‘
CHR(146)==’
CHR(147)==“
CHR(148)==”
CHR(149)==•
CHR(150)==–
CHR(151)==—
CHR(152)==˜
CHR(153)==™
CHR(154)==š
CHR(155)==›
CHR(156)==œ
CHR(157)==
CHR(158)==ž
CHR(159)==Ÿ
CHR(160)==
CHR(161)==¡
CHR(162)==¢
CHR(163)==£
CHR(164)==¤
CHR(165)==¥
CHR(166)==¦
CHR(167)==§
CHR(168)==¨
CHR(169)==©
CHR(170)==ª
CHR(171)==«
CHR(172)==¬
CHR(173)==­
CHR(174)==®
CHR(175)==¯
CHR(176)==°
CHR(177)==±
CHR(178)==²
CHR(179)==³
CHR(180)==´
CHR(181)==µ
CHR(182)==¶
CHR(183)==·
CHR(184)==¸
CHR(185)==¹
CHR(186)==º
CHR(187)==»
CHR(188)==¼
CHR(189)==½
CHR(190)==¾
CHR(191)==¿
CHR(192)==À
CHR(193)==Á
CHR(194)==Â
CHR(195)==Ã
CHR(196)==Ä
CHR(197)==Å
CHR(198)==Æ
CHR(199)==Ç
CHR(200)==È
CHR(201)==É
CHR(202)==Ê
CHR(203)==Ë
CHR(204)==Ì
CHR(205)==Í
CHR(206)==Î
CHR(207)==Ï
CHR(208)==Ð
CHR(209)==Ñ
CHR(210)==Ò
CHR(211)==Ó
CHR(212)==Ô
CHR(213)==Õ
CHR(214)==Ö
CHR(215)==×
CHR(216)==Ø
CHR(217)==Ù
CHR(218)==Ú
CHR(219)==Û
CHR(220)==Ü
CHR(221)==Ý
CHR(222)==Þ
CHR(223)==ß
CHR(224)==à
CHR(225)==á
CHR(226)==â
CHR(227)==ã
CHR(228)==ä
CHR(229)==å
CHR(230)==æ
CHR(231)==ç
CHR(232)==è
CHR(233)==é
CHR(234)==ê
CHR(235)==ë
CHR(236)==ì
CHR(237)==í
CHR(238)==î
CHR(239)==ï
CHR(240)==ð
CHR(241)==ñ
CHR(242)==ò
CHR(243)==ó
CHR(244)==ô
CHR(245)==õ
CHR(246)==ö
CHR(247)==÷
CHR(248)==ø
CHR(249)==ù
CHR(250)==ú
CHR(251)==û
CHR(252)==ü
CHR(253)==ý
CHR(254)==þ
CHR(255)==ÿ

Creating oracle response file emocmrsp

*******************************************************************************
Now we can see the oracle response file. This file is a part of OPTACH  :
*******************************************************************************

grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $ ls
emocmrsp
grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $

*******************************************************************************
Now create a response file for oracle home
*******************************************************************************

grid@Linux211:[/u01/app/11.2/grid_home/OPatch/ocm/bin] $ ./emocmrsp   -no_banner
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
The OCM configuration response file (ocm.rsp) was successfully created.

*******************************************************************************
Oracle recommends to run following command to verify consistency of all homes being patched.
*******************************************************************************

$ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

In case there are any issues with oracle inventory , it will report error. if you see any errors please fix the errors first before proceeding with patching.
This will also give the list of currently applied patches.

opatch auto -report - Check if patches are applied on GI HOME

atoorpu@LINUXRAC1:[/u01/app/PATCHES/22191577] $/u01/app/11.2/grid_home/OPatch/opatch auto -report -och $ORACLE_HOME'

Executing /u01/app/11.2/grid_home/perl/bin/perl /u01/app/11.2/grid_home/OPatch/crs/patch11203.pl -patchdir / -patchn root -report -och /u01/app/11.2/grid_home -paramfile /u01/app/11.2/grid_home/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2/grid_home/cfgtoollogs/opatchauto2016-03-22_10-32-42.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:

/u01/app/11.2/grid_home/cfgtoollogs/opatchauto2016-03-22_10-32-42.report.log

2016-03-22 10:32:42: Starting Clusterware Patch Setup

Using configuration parameter file: /u01/app/11.2/grid_home/crs/install/crsconfig_params

opatchauto is running in analyze/report mode. It will make no change to your system

Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes

Enter 'yes' if you have unzipped this patch to an empty directory to proceed  (yes/no):yes

ERROR: This patch is not applicable to GI home.

opatch auto failed. 

LETS CHECK THE LOG FILE

atoorpu@LINUXRAC1:[/u01/app/PATCHES/22191577] $ cat /u01/app/11.2/grid_home/cfgtoollogs/opatchauto2016-03-22_10-32-42.report.log

***********  Configuration Data  ***********

* It shows only those targets that will be patched in this session *

Step by Step installation oracle 12c database on Linux 6 (centos)

Assumptions :

• You have a some flavor of Linux operating system installed (I have used centos 6 in this example).
• If you cant afford a separate machine you can use Virtual box or stemware software to visualize your desktop or laptop.
• Assuming that you have downloaded oracle 12 software onto linux machine. If not you can download from this link Software-Download
• You have full/required privileges on you Linux host.

Oracle Installation Prerequisites

In order to perform the installtion of oracle 12c software on Linux box you need to perform some pre-reqs, which can be done automatically or through manual updates.Please follow below instructions.

Automatic Setup

If you plan to use the "oracle-rdbms-server-12cR1-preinstall" package to perform all your prerequisite setup, issue the following command.

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

It will be a good option to to do an update.

# yum update

************* ***********

MANUAL SETUP

************* ***********

If you have not used the "oracle-rdbms-server-12cR1-preinstall" package to perform all prerequisites, you will need to manually perform the following setup tasks.

Add or amend the following lines in the "/etc/sysctl.conf" 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

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

MANUALLY INSTALL PACKAGES FROM INTERNET OR FROM CD DRIVE(below is to install from INTERNET) :

# From Public Yum or ULN

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

Create the new groups and users as per your requirement. For my case just to keep it simple lets use 3 groups & oracle user.

groupadd -g 54321 oinstall

groupadd -g 54322 dba

groupadd -g 54323 oper

useradd -u 54321 -g oinstall -G dba,oper oracle.

Set SELINUX to permissive or diable it if this is test env.

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/12.1/db_1

chown -R oracle:oinstall /u01

chmod -R 775 /u01

LOGIN AS ORACLE USER AND 

Add the following lines at the end of the "/home/oracle/.bash_profile" file.

# Oracle Settings

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol6-121.localdomain

export ORACLE_UNQNAME=orcl

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/12.1/db_1

export ORACLE_SID=orcl

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

Change your directory to location where you have downloaded oracle software.

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller

I don't want any update to uncheck to receive updates.

Lets create a server class.

To keep this simple I am going to select typical installation.

Make sure you have selected the home path correct. 

After This step you will be notified if you have any pre-req failures. Make sure you have cleared them all. Missing ksh package can be ignored as this is a known bug. Oracle is expecting a specific version of ksh & i have a latest pkg. Assuming that you have all cleared up.

Now select install.

Now you will be prompted to execute shell scripts before the installation of software is complete. I have missed that prompt screen but it will ask you to execute below 2 shell scripts as root user. see below screen

After executing hit OK and it will continue to install oracle DB software.

You will see this screen after installation is complete.

That's it you have completed your oracle 12c database software installation. You can query as below

Please drop your comments below if you found this blog helpful to you.