Installation Oracle Enterprise Linux OEL 5 on Virtual machine

After Installation Virtual Machine i wanted to share installing OEL 5 on VMware,             

All you need  Download Oracle Enterprise Linux (OEL)Here   the last version.          

The first step is beginning OS-OEL

Click Enter...

if you prefer to check to media CD before installation click OK, however click Skip

Click Next to go to Language setting screen.

Choose for install language then Click Next

Set Keyboard and  Click Next

Say yes.

At this step if you would like to set storage memory by yourself Select "Custom...." but i will set manually before installation Oracle Database 11g that's why Click Next

Say Yes.

Also You may set IP address to connect client click Edit and give ip address. Then give manually host Name.

Click Next

Choose Your Country, I clicked Baku which is i have been for many years :)

The system asking for Root (user) password, write special and strong  password because its very important for security, The password must be at less 8 character
 Then click Next

Here you have to Customize prerequired packages to be installed  
Click Customize now and Click Next

Desktop Environments

GNOME Desktop Environ

Applications 

Graphical Internet

Development 

Development Libraries

Development Tools

GNOME Software Development

Java Development

Legacy Software Development

X Software Development (Select libxpdevel and openmotif)

Servers

Server Configuration Tools

Web Server

Windows File Server

Base System 

Administration Tools

Base

Java

Legacy Software Support (select compat-db)

System Tools (select sysstat)

X Window System

Click Next

Take a cup of coffee and wait for finish packages :) Have a nice drink :)

Click Reboot 

 So you already going to the end But first Welcom :)

 I think you may be Agree to keep Forward :)

Here I setted Firewall Disable

I setted SElinux Disable too, However you can change by command on terminal manually

We don't Need KDump

 Set your correct Date

I didnt create any user here because i prefer to create manually

 So at last you finished

 The Screen of OEL
So we have to do some configuration on VMware to Display screen.
First Log in as User Root Go to VM option and click Install VMware Tool or Eject CD and install the tools

After you finished installation of VMware tools Open A terminal and use following command.

vmware-config-tools.pl
During configuration you will choose Display option , i prefer 3(1024-840).

Here we Go OEL is ready now for Installation Oracle Database. :) Just follow me on next blog to install Oracle Database 11g R2.

How to Create a new Virtual machine

In this Article i would like to show you How Create Virtual Machine For Linux Red hat 5.
So All You need go to website VMware download/ and download to complete it.

Open the VMware for new work

Click Next

Select Typical for configuration by yourself

At this step you may choose a OS, I will install Red Hat 5.. that's why I'm choosing it.

   Set Location where do you want to install                                                                                                 

Here some network connection options, I recommended to select "Use Network address(NAT) in order to set network configuration connect to real OS vi "TOAD,PUTTY etc..)        

Give space memory for OS, If you'll work on big project set disk size as you wish, I'm just setting 20GB for example of  it                                                                                                                  

Now its Ready to start OS but you have to do some setting here, first set ISO image of OS and give RAM(memory) size. Then click to Start the virtual machine..                                 

Next blog will be installing Linux Red hat 5.

SQL Insert options (Single table insert and Multi Table Insert)

In This article I'm going to explain  SINGLE TABLE INSERT And Multi Table Insert.

As we all know basic command of Insert is:

INSERT INTO table_name (column_names) VALUES (columns_values);

Note: The column name must be sett ed. If we want to give NULL value then we would leave empty or we should write NULL at the end

For Example;

INSERT INTO dept (department_id, department_name, manager_id, location_id)                                                            VALUES (300, ‘Engineering’, 150, 1600);

INSERT INTO dept (department_id, department_name, manager_id, location_id)                                                           VALUES (205, ‘Engineering’, NULL, NULL);

INSERT INTO dept (department_id, department_name) VALUES (200, ‘Engineering’);

 An other option insert is Multi row Insert. This option provide us to Insert an other table or more than one.

INSERT INTO table_name(1)  SELECT column_name FROM  table_name(2) WHERE requires……..;

 For Example;

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM   employees

WHERE job_id LIKE ‘%REP%’;

MULTI TABLE INSERT

This option provide us to insert Multi table So, we have 2 insert section

• Conditional Insert
• Unconditional Insert

UNCONDITIONAL INSERT

The insert will be write at the same time and same values to the tables without any conditions and any limits. Here insert command doesn't loot what is column name or table name.

INSERT ALL
INTO Table_name(1) VALUES(column_names(1))
INTO table_name(2) VALUES(column_names (2))

SELECT column_names (3)  FROM  Table_name(3)
WHERE requires ……………..;

Note: At 1 and 2 column_names should be the same with 3 Selection

Consider if in Select we get 30 answers then insert will write 30 answers to 1 and 2 tables. 

For example;

INSERT ALL
INTO Sal_history VALUES (EMPID, HIREDATE, SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)

SELECT employee_id EMPID, hire_date HIREDATE,
salary SAL, manager_id MGR

FROM employees
WHERE employee_id > 100;

CONDITIONAL INSERT

Conditional Insert Also Have two options

• FIRST
• ALL

INSERT ALL

Its look like multi table insert  UNCONDITIONAL INSERT, but INSERT ALL have a condition;

Lets look at the command.

INSERT ALL

WHEN condition(1) THEN

INTO Table_name(1) VALUES(column_name(1))

WHEN condition (2) THEN

INTO Table_name (2) VALUES(column_name(2))

SELECT column_name(3) FROM Table_name(3)

WHERE condition(3)………………….;

For example;

INSERT ALL

WHEN HIREDATE < ’10-FEB-13′ THEN

INTO emp_history VALUES (EMPID, HIREDATE, SAL)

WHEN COMM IS NOT NULL THEN

INTO emp_sales VALUES (EMPID,COMM,SAL)

SELECT employee_id EMPID, hire_date HIREDATE,

salary SAL, commission_pct COMM

FROM  employees;

INSERT FIRST

At this option Insert will keep going until get the first condition in case it will write at first Select table, it doesn't write any others table ;

INSERT FIRST

WHEN condition(1) THEN

INTO Table_name(1) VALUES (column_name(1))

WHEN condition(2) THEN

INTO Table_name(2) VALUES (column_name(2))

ELSE

INTO Table_name(3) VALUES (column_name(3))

SELECT column_name(4)

FROM Table_name(4) WHERE condition(4)…………………;

For example ;

INSERT FIRST

WHEN salary < 4000 THEN

INTO sal_low VALUES (employee_id, last_name, salary)

WHEN salary between 4000 and 10000 THEN

INTO sal_mid VALUES (employee_id, last_name, salary)

ELSE

INTO sal_high VALUES (employee_id, last_name, salary)

SELECT employee_id, last_name, salary

FROM employees;

Moving Tables and using indexes

Tabloların taşınılması işlemleri

Create a tablespace

SQL> create tablespace test_tbs datafile '/u01/app/oracle/oradata/datafile/test01.dbf' size 15M  autoextend on next 1M;

Tablespace created.

Create second tablespace

SQL> create tablespace test_tbs2 datafile '/u01/app/oracle/oradata/datafile/test02.dbf' size 15M  autoextend on next 1M;

Tablespace created.

Create a user 

SQL> create user veysi identified by veysi;

User created.

Grant some roles to user

SQL> grant resource,connect to veysi;

Grant succeeded.

Set a default tablespace to user..

SQL> alter user test_usr default tablespace test_tbs   temporary tablespace temp;

User altered.

SQL> conn veysi/veysi

Connected.

Create table

SQL> create table test_table( t_id number, t_name varchar(30), hire_date  date );

Table created.

Creating Sequence.

SQL> create sequence id_seq

minvalue 1

maxvalue 100

 increment by 1

start with 1

nocycle ;

sequence created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Veysi',sysdate-30);

1 row created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Ali',sysdate-60);

1 row created.

SQL> insert into test_table

  2  values(id_seq.nextval,'Mustafa',sysdate-90);

1 row created.

SQL> select * from test_table;

      T_ID T_NAME                         HIRE_DATE

---------- ------------------------------ ----------

         2 Veysi                         20/01/2012

         3 Ali                            21/12/2011

         4 Mustafa                        21/11/2011

Create an other child table.

SQL> create table test_child_table(id number,month varchar(20),salary number);

Table created.

Set the foreign to parent table.

SQL> alter table test_child_table add constraint fk_id_test_ch_tbl foreign key(id) references test_table(t_id);

Table altered.

Then you can create a index . 

SQL> create bitmap index idx_id_child_tbl  on test_child_table (id);

Index created.

new insert

SQL> insert into test_child_table values(2,'JAN',2000);

1 row created.

Looking our table

SQL> select table_name,tablespace_name from  user_tables;

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEST_TABLE                            TEST_TBS

TEST_CHILD_TABLE               TEST_TBS

Looking a USER'S objects

SQL> select object_name,OBJECT_TYPE from all_objects where 

owner='VEYSI';

OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

ID_MATIC                       SEQUENCE

TEST_TABLE                     TABLE

PK_T_ID_TEST_TBL               INDEX

TEST_CHILD_TABLE               TABLE

Now move table to the other tablespace

SQL> alter table test_child_table move tablespace test_tbs2;

Table altered.

As you se The Test_child_table now on Test_tbs2.

SQL> select table_name,tablespace_name from  user_tables;

TABLE_NAME                           TABLESPACE_NAME

------------------------------ ------------------------------

TEST_TABLE                          TEST_TBS

TEST_CHILD_TABLE                          TEST_TBS2

The table's index is now "unusable" because of i change the tablespaces.

SQL> select index_name,status from user_indexes where table_name='TEST_CHILD_TABLE';

INDEX_NAME                     STATUS

------------------------------ --------

IDX_ID_CHILD_TBL               UNUSABLE

Now i need rebuild the index for valided it by using a simple command.

SQL> alter index IDX_ID_CHILD_TBL  rebuild tablespace test_tbs2 ;

Index altered.

SQL> select index_name,status from user_indexes where table_name='TEST_CHILD_TABLE';

INDEX_NAME                     STATUS

------------------------------ --------

IDX_ID_CHILD_TBL               VALID

lets look at the constraints

SQL> insert into test_child_table values(3,'FEB',4000);

1 row created.

SQL> select * from test_child_table;

        ID MONTH                    SALARY

---------- -------------------- ----------

         2 FEB                        4000

         3 FEB                        4000

Looking the parent table

SQL> select * from test_table;

      T_ID T_NAME                         HIRE_DATE

---------- ------------------------------ ----------

         2 Veysi                           20/01/2012

         3 Ali                          21/12/2011

         4 Mustafa                         21/11/2011

SQL> insert into test_child_table values(1,'FEB',4000);

insert into test_child_table values(1,'FEB',4000)

*

ERROR at line 1:

ORA-02291:  integrity constraint (string.string) violated  (TEST_USR.FK_ID_TEST_CH_TBL)- parent key not found  

 So,as you see i got error because a foreign key value has no matching primary key value

To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.