Installing and using Oracle

1. Download the rpm package:
Ftp: // 195.220.108.108/linux/ASPLinux/i386/updates/12.1/i386/sysstat-7.0.2-3.el5.asp121.i386.rpm
Ftp://rpmfind.net/linux/fedora/releases/9/Everything/i386/ OS /Packages/libaio-0.3.106-4.2.i386.rpm
Ftp://rpmfind.net/linux/fedora/releases/9/Everything/i386/ OS /Packages/libaio-devel-0.3.106-4.2.i386.rpm
Ftp://fr.rpmfind.net/linux/fedora/releases/10/Everything/i386/ OS /Packages/unixODBC-2.2.12-9.fc10.i386.rpm
Ftp://fr.rpmfind.net/linux/fedora/releases/10/Everything/i386/ OS /Packages/unixODBC-devel-2.2.12-9.fc10.i386.rpm

2. Configure Kernel Parameters
Modify/etc/CMDL. conf and add the following parameters:
Kernel. sem = 250 32000 100 128
Fs. file-max = 6815744
Net. ipv4.ip _ local_port_range = 9000 65500
Net. core. rmem_default = 4194304
Net. core. rmem_max = 4194304
Net. core. wmem_default = 262144
Net. core. wmem_max = 1048576
Fs. aio-max-nr = 1048576
 
Run sysctl-p after modification

3. Create a group required for Database Installation
Groupadd oinstall
Groupadd dba
Useradd-g oinstall-G dba Oracle
Passwd Oracle

Modify the/etc/sudoers file
Add a row so that the Oracle user has the sudo permission. Otherwise, you cannot perform some operations when logging on to the Oracle user.
% Oinstall ALL = (ALL) NOPASSWD: ALL

4. Add Oracle restriction Parameters
Modify/etc/security/limits. conf and add
Oracle soft upro 2047
Hard nproc 16384
Oracle soft nofile 1024
Oracle hard nofile 65536
 
Modify/etc/pam. d/login and add
Session required/lib/security/pam_limits.so
Session required pam_limits.so
 
Modify/etc/profile and add
If [$ USER = "Oracle"]; then
If [$ SHELL = "/bin/ksh"]; then
Ulimit-p 16384
Ulimit-n 65536
Else
Ulimit-u 16384-n 65536
Fi
Fi
 
Set Oracle environment variables
Vi/home/Oracle/. bash_profile, modify
Umask 022
TMP =/mnt/Oracle/tmp
TMPDIR =/mnt/Oracle/tmp
Export TMP TMPDIR
 
# Get the aliases and functions
If [-f ~ /. Bashrc]; then
.~ /. Bashrc
Fi

# User specific environment and startup programs

PATH = $ PATH: $ HOME/bin

Export PATH

Export ORACLE_BASE =/mnt/Oracle/app/Oracle
Export ORACLE_HOME = $ ORACLE_BASE/product/11.2.0/dbhome_1
Export ORACLE_UNQNAME = orcl
Export ORACLE_SID = orcl
Export PATH = $ ORACLE_HOME/bin :$ {PATH}
Export ORACLE_PATH = $ ORACLE_HOME: $ ORACLE_HOME/rdbms/admin :.
Export LD_LIBRARY_PATH =$ {LD_LIBRARY_PATH}: $ ORACLE_HOME/lib
Export LIBPATH =$ {LIBPATH}: $ ORACLE_HOME/lib
Export CLASSPATH =$ {CLASSPATH}: $ ORACLE_HOME/rdbms/jlib: $ ORACLE_HOME/network/jlib: $ ORACLE_HOME/jdk
/Jre

5. Create an installation directory
Mkdir-p/mnt/Oracle/app
Chown-R Oracle: oinstall/mnt/Oracle/app
Chmod-R 755/mnt/Oracle/app

Create temporary directory
Mkdir/mnt/Oracle/tmp
Chmod a + wr/mnt/Oracle/tmp

6. Unzip the installation package
Unzip linux_11gR2_database_1of2.zip
Unzip linux_11gr2_database204 f2.zip
Cd database
Sh runInstaller

During the installation process, if the management password cannot be entered, click in the global data name, and then click the management password.

====================== START process ==========================

「 Em Startup Process 」
1. emctl start dbconsole
2. Open the browser and enter https: // localhost: 1158/em.
3. Start the listener
Click Start and enter the username and password of the Operating System: Oracle (000000)
4. Start the database
Host identity: Oracle (000000)
Database ID: sys (Xxxxx000000), connection identity: SYSDBA

Database Startup Process 」
1. Start a database instance
Sqlplus/nolog
SQL> connect/as sysdba
Connected.
SQL> startup
2. Start the Oralce listening process
Lsnrtl start

[Error message]
Environment variable ORACLE_UNQNAME not defined. Please set ORACLE_UNQNAME
Database unique name.
[Solution]
Export ORACLE_UNQNAME = orcl
Emctl start dbconsole

[Error message]
TNS-12541: TNS: no listener
[Liberation measures]
Lsnrctl start

[Error message]
ORA-12505: TNS: listener does not currently know of SID given in connect
Descriptor (dbd error: OCIServerAttach)
[Solution]
To start the database, run https: // localhost: 1158/em to start the database. Click start on the right of the database instance and enter:
Operating system username: Oracle
Password 000000

Username: sys
Password: Nsfocus000000
Connection identity: SYSDBA

====================== Database Operations ================================
Create a database:
Create table "SCOTT". "COUNTRIES" ("COUNTRY_ID" CHAR (2 BYTE) not null, "COUNTRY_NAME" VARCHAR2 (40 BYTE) not null ,"
COUNTRY_SUBREGION "VARCHAR2 (30, BYTE)," COUTRY_REGION "VARCHAR2 (20 BYTE) not null );
Prompt ORA-00907: missing right parenthesis
[Error cause]
A comma is added after 30, indicating a syntax error. Note that an asterisk is prompted when an error occurs.
[Solution]
Create table "SCOTT". "COUNTRIES" ("COUNTRY_ID" CHAR (2 BYTE) not null, "COUNTRY_NAME" VARCHAR2 (40 BYTE) not null ,"
COUNTRY_SUBREGION "VARCHAR2 (30 BYTE)," COUTRY_REGION "VARCHAR2 (20 BYTE) not null );

The following statement is correct:
Create table dept (deptno number (2), loc varchar2 (20), primary key (deptno ));
Data insertion:
Insert into dept VALUES (1, 'beijing ');

[Error message]
Insert into dept VALUES (1, "beijing ");
ORA-00984: column not allowed here
[Solution]
Change double quotation marks to single quotes

[Error message]
Select * from dept;
The data is clearly inserted, but there is no data. The reason may be that no commit exists after the data is inserted and cannot be found in another sqlplus.
[Solution]
Commit after insert

Create table products (product_id INTEGER, product_name VARCHAR2 (60), category varchar2 (60 ));
Insert into products VALUES (1501, 'vivitar 100', 'electrancs ');
Insert into products VALUES (1502, 'olympus is50', 'electrancs ');
Insert into products VALUES (1601, 'play gym', 'toys ');
Insert into products VALUES (1606, 'lamaze', 'toys ');
Insert into products VALUES (1666, 'happy POTTER ', 'dve ');
COMMIT;
Create table newproducts (product_id INTEGER, product_name VARCHAR2 (60), category varchar2 (60 ));
/* This line changes product_name */
Insert into newproducts VALUES (1502, 'olympus cama', 'electrancs ');
Insert into newproducts VALUES (1601, 'play gym', 'toys ');
Insert into newproducts VALUES (1666, 'happy POTTER ', 'dvd ');
Insert into newproducts VALUES (1700, 'Wait interface', 'books ');
COMMIT;

/* Merge -- match */
Merge into products p
USING newproducts np
ON (p. product_id = np. product_id)
WHEN MATCHED THEN
UPDATE
SET p. product_name = np. product_name,
P. category = np. category;

/* Merge -- mismatch */
Merge into products p
USING newproducts np
ON (p. product_id = np. product_id)
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name, np. category );

/* Conditional update and insert statements */
Merge into products p
USING newproducts np
ON (p. product_id = np. product_id)
WHEN MATCHED THEN
UPDATE
SET p. product_name = np. product_name,
P. category = np. category
WHERE p. category = 'dve'
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name, np. category)
WHERE np. category! = 'Books ';

/* Constant filter predicates */
Merge into products p
USING newproducts np
ON (1 = 0)
WHEN NOT MATCHED THEN
INSERT
VALUES (np. product_id, np. product_name, np. category)
WHERE np. category! = 'Books ';

===================== SQL * Plus ==============================
1. SQL * Plus line editing command
List or l list all rows in the buffer
L set N to the current row followed by the number N, for example, l1. Set 1st to the current row.
/Execute the last entered SQL statement
Define_editor = vi: Set the editor to vi. Enter edit or ed to call vi as the editor.
Save file_name: SQL statement used to save the buffer
C/old/new modify the old value of the current row of the buffer to new
C/text delete text
Add text at the end of a text line

2. Notes
REMARK xxx
/* Xxx */
-- Xxx

3. run commands
1) Command Line
Enter a semicolon
2) SQL Buffer Mode
R (un)
/
/The difference with r is that it does not display the SQL statement of the buffer, nor is it the best row of the buffer to become the current row
3) command file Method
START file_name [arg1, arg2]
@ File_name [arg1, arg2]
The difference between START and @ is that @ can be run either within the SQL * Plus session or at the command line level when SQL * Plus is started, while the START command can only run at the SQL * Plus session
Internal Operation. The following parameter arg1 arg2 can be passed to the user variables defined by the following interactive command.

4. Interactive commands
DEFINE User variable define myfriend = SMITH
Use user variable & MYFRIEND
In the same SQL statement, if there are two or more variables with the same name, you only need to enter them once, and then assign values to replace them, for example:
SELECT ename, & col FROM scott. emp order by & col;

SELECT * FROM emp_details_view WHERE job_id = '& 1' AND salary =' & 2 ';
SAVE select_details
START select_details PU_CLERK 3100
The above START command will pass PU_CLERK and 3100 to the & 1 and & 2 parameters

PROMPT prompts user information
ACCEPT accepts user input
PAUSE. Wait for the user to press enter to continue.

Author: "small ocean Enterprises"