Install Oracle 11gR2 on Linux

Step Installing Oracle 11gR2 on Linux 

Installing Oracle requires prerequisite component packages to installed. If the computer is connected with the internet, run the following command on Linux prompt.

[root@hostname]# yum install oracle-rdbms-server-11gR2-preinstall

The above line install the additional packages onto Linux.

It is probably worth doing a full update as well, but this is not strictly speaking necessary. The update process takes over an hour to complete.

[root@hostname]# yum update

Then, check other configuration values in "etc/sysclt.conf"

[root@hostname]# cat /etc/sysctl.conf

If you run the preinstallation package, the content in the sysctl.conf file shall be updated. To have new kernal parameter values take effect, run the following command

[root@hostname]# /sbin/sysctl -p

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

oracle soft nproc 16384
oracle hard nproc 16384
oracle soft nofile 4096
oracle hard nofile 65536
oracle soft stack 10240

Again, these lines are automatically added if you have run oracle-rdbms-server-11gR2-preinstall package.

Next create the new groups and uses.

[root@hostname]# groupadd -g 54321 oinstall
[root@hostname]# groupadd -g 54322 dba
[root@hostname]# groupadd -g 54323 oper
[root@hostname]# groupadd -g 54324 backupdba
[root@hostname]# groupadd -g 54325 dgdba
[root@hostname]# groupadd -g 54326 kmdba
[root@hostname]# groupadd -g 54327 asmdba
[root@hostname]# groupadd -g 54328 asmoper
[root@hostname]# groupadd -g 54329 asmadmin

Strangely enough, when adding group of "oinstall", dba group is also added, and new user "oracle" is created in Linux. After adding all group, run the following command to create user and assign groups to user. In this case, the user is oracle

[root@hostname]# useradd -u 54321 -g oinstall -G dba,oper oracle

Be sure to type in command as appeared above. Unnecessary space causes error. Then, set the password for user "oracle"

[root@hostname]# passwd oracle

Then, type new password and confirm the password.
Set secure Linux by edit file "/etc/selinux/config" file. The value inside the file = SELINUX=permissive
Once the value is change. Run the following command at prompt
[root@hostname]# setenforce Permissive

Then, disable the Linux firewall with following commands

[root@hostname]# service iptables stop
[root@hostname]# chkconfig iptables off

Create directory for Oracle software installation
[root@hostname]# mkdir -p /u01/app/oracle/product/11.2.0.1/db_1
[root@hostname]# chown -R oracle:oinstall /u01
[root@hostname]# chmod -R 775 /u01

Before running the installer, edit bash_profile for user "oracle". Use gedit to edit file
[root@hostname]# gedit /home/oracle/.bash_profile

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

ORACLE_HOSTNAME=localhost.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID

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

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

Save and exit. Note that ORACLE_BASE and ORACLE_HOME is the directory, created earlier step

If installation is performed directly on Linux server, issue this xhost command

[root@hostname]# xhost +SI:localuser:oracle

Switch to user oracle

[root@hostname]# su oracle

Run the installer command where the runInstaller file is saved.

[root@hostname]# ./runInstaller

Then follow onscreen instruction until installation is complete.

Convert Rows to Columns in SQL Server

This is the simple code to convert records rows to columns:
Let create simple two column table:

 CREATE TABLE [dbo].[LotNo]( [IDNo] [bigint], [LotNo] [varchar](50) NULL )

Now insert some records to the table:
insert into LotNo ([IDNo], LotNo)
select 1, 'A' union all
select 2, 'A' union all
select 3, 'A' union all
select 4, 'A' union all
select 5, 'A' union all
select 6, 'A' union all
select 1, 'B' union all
select 2, 'B' union all
select 3, 'B' union all
select 1, 'C' union all
select 2, 'C' union all
select 3, 'C' union all
select 4, 'C' union all
select 1, 'D' union all
select 2, 'D' union all
select 1, 'E' union all
select 2, 'E' union all
select 3, 'E' union all
select 1, 'F' union all
select 2, 'F' union all
select 3, 'F' union all
select 4, 'F' union all
select 5, 'F' union all
select 6, 'F' union all
select 1, 'G' union all
select 2, 'H' union all
select 3, 'I' union all
select 4, 'J' union all
select 6, 'K'

Now, this is the sql statement to show the the result in columns

select LotNO, max(case when IDNo = 1 then IDNo else '' end) as Col1,
       max(case when IDNo = 2 then IDNo else '' end) as Col2,
       max(case when IDNo = 3 then IDNo else '' end) as Col3,
       max(case when IDNo = 4 then IDNo else '' end) as Col4,
       max(case when IDNo = 5 then IDNo else '' end) as Col5,
       max(case when IDNo = 6 then IDNo else '' end) as Col6
from LotNo
group by LotNO

You can use this approach to produce permutation table.

Grant Select Privilege on SELECT_CATALOG_ROLE to User

In Oracle, after the new user or schema is created, the user cannot query the data dictionary. For example, if you run the below query:

Select * from dba_data_files

you'll see error message as ORA-00942: table or view does not exist

To allow the user to select table from Oracle data dictionary, you have to grant SELECT_CATALOG_ROLE privilege the that users. Use the following statement while logging in as sysdba

sql> grand SELECT_CATALOG_ROLE to user_name;

Amazon Product List Page

Click here to check out product list

Shop Amazon Books - New J.K. Rowling - The Casual Vacancy

Retrieve Table Information in MS SQL Server

Quite often there is a need to check number of records in the table. It can be done with ease with the following SQL statement

Select Count(*) from TableName

However, it would have been better if we can retrieve number of records from the table with other information related to that table. SQL Server provides this store procedure to accomplish this task

sp_spaceused 'Table_Name'

more detail inside this book

The statement above shall return result in the table format. This SQL statement only works with one table in the chosen database. If we want to loop through all tables in the database to display table information, we can perform this step.

• First, create the temporary table to store result from store procedure
• Second, run the store procedure
• Last, use the select statement to display the result

Use the below SQL statement to create the temporary table in SQL Server

CREATE TABLE #tableSize(
TAB_NAME NVARCHAR(128),
RowsNO VARCHAR(30),
sizereserved VARCHAR(30),
sizedata VARCHAR(30),
index_size VARCHAR(30),
unused VARCHAR(30) )

Then, execute the store procedure below

INSERT #tableSize EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

After the the procedure finish running, simply run the select statement.

SELECT * FROM #tableSize order by TAB_NAME

Find more books related to MS SQL Server

How to access table in different schema in Store Procedure

On one Oracle database, there are several users or schemas. Each schema contains tables. These table belong to each user. It is possible for a user to access table in other schema. For instant, if there are three users in Oracle database, such as USER1, USER2, and USER3.

In event that the USER3 needs to access the table which belongs to the USER2. SQL select statement is written as

Select Col1, Col2, Col3 from USER2.TableName

This statement works fine when the SQL statement is run in Toad or SQL-Plus. However, if the SQL statement above is put into the store procedure, it will not work. The store procedure will not compiled correctly.

To correct this problem, the USER2 has to grant select on table privilege the USER3. Follow the step below:

1. Login to the Oracle database as USER2
2. Run this statement Grant Select on USER2.TableName to USER3;

Now the USER3 can put the select statement of granted table in the store procedure.

Rename DATA FILE for Oracle

The data file is created when the Oracle DBA created the table space. If the Oracle DBA wants to rename the data file after the it has been created, he or she can do so by following these steps

1. The table space has to be taken offline; use the following command ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
2. Then data file is rename using the operating system.
3. After that the Oracle DBA renames the data file in SQL Plus enviroment. Supposed that the old data file name is 'USER1.DBF'. The new data file name is 'NEW_USER.DBF'. Following the step 2, issue this SQL comment ALTER TABLESPACE 'tablespace name' RENAME DATAFILE 'C:\USER1.DBF' to 'C:\NEW_USER.DBF'; This assumes that the data file is located in drive C
4. After rename the data file is performed in the Oracle environment, mount the tablespace online again, using this command: ALTER TABLESPACE tablespace_name ONLINE;

Now, the table space contain the data file with the new name.