Installation steps
1. Install dependent packages
Operating user:root
yum -y install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*i686 compat-libstdc++-33*.devel compat-libstdc++-33 compat-libstdc++-33*.devel gcc gcc-c++ glibc glibc*.i686 glibc-devel glibc-devel*.i686 ksh libaio libaio*.i686 libaio-devel libaio-devel*.devel libgcc libgcc*.i686 libstdc++ libstdc++*.i686 libstdc++-devel libstdc++-devel*.devel libXi libXi*.i686 libXtst libXtst*.i686 make sysstat unixODBC unixODBC*.i686 unixODBC-devel unixODBC-devel*.i686
Note: If you encounter a choice (y/n) halfway through, just type Y
2. Validate dependent packages
Operating user:root
rpm -q binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libaio libaio-devel libgcc libstdc++ libstdc++-devel libXi libXtst make sysstat unixODBC unixODBC-devel
Normally all packages will show installed, check for uninstalled packages
3. Create oracle users
Operating user:root
groupadd -g 502 oinstall groupadd -g 503 dba groupadd -g 504 oper groupadd -g 505 asmadmin useradd -u 502 -g oinstall -G oinstall,dba,asmadmin,oper -s /bin/bash -m oracle passwd oracle
You will be prompted to set your password at the last step
Note: After 500 is the average user
4. Unzip the Oracle installation package
Operating user: oracle
Note: Switch operation user using su-username
Store a directory of your choice, mine at root/oradata
unzip linux.x64_11gR2_database_1of2.zip unzip linux.x64_11gR2_database_2of2.zip
You may be prompted that you do not have permission to operate with
chown -R oracle:oinstall /oradata
That's it
Unzip to get a directory of database s
5. Modify system configuration
Operating user:root
vi /etc/security/limits.conf
Add the following configuration item at the end of the file
oracle soft nproc 2047 oracle hard nproc 16384 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft stack 10240
6. Create Oracle installation directory
Operating user: oracle
Depending on the size of the partition you choose, my/oradata/soft/oracle11g
7. Modify environment variables
Operating user: oracle
vi /etc/profile
Add the following configuration item at the end of the file
export ORACLE_BASE=/oradata/soft/oracle11g
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/dbhome_1
export ORACLE_SID=prod
export ORACLE_UNQNAME=prod
export NLS_LANG=.AL32UTF8
export PATH=${PATH}:${ORACLE_HOME}/bin/:$ORACLE_HOME/lib64
Making environment variables work
source /etc/profile
8. Modify oracle configuration file
Operating user: oracle
Operations Directory: /oradata
Copy File Template
cp /oradata/database/response/db_install.rsp .
There's one last one. Can't leak. Will db_install.rsp file from copy to current directory
For db_install.rsp file for editing
vi db_install.rsp
The configuration items that need to be modified are as follows
oracle.install.option=INSTALL_DB_AND_CONFIG ORACLE_HOSTNAME=localhost #You can actually modify it to your own hostname or domain name (IP) UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/oradata/soft/oraInventory SELECTED_LANGUAGES=en,zh_CN ORACLE_HOME=/oradata/soft/oracle11g/product/11.2.0.3/dbhome_1 ORACLE_BASE=/oradata/soft/oracle11g oracle.install.db.InstallEdition=EE oracle.install.db.DBA_GROUP=dba oracle.install.db.OPER_GROUP=oper oracle.install.db.config.starterdb.type=GENERAL_PURPOSE oracle.install.db.config.starterdb.globalDBName=prod oracle.install.db.config.starterdb.SID=prod oracle.install.db.config.starterdb.characterSet=AL32UTF8 oracle.install.db.config.starterdb.memoryOption=true oracle.install.db.config.starterdb.memoryLimit=1024 oracle.install.db.config.starterdb.installExampleSchemas=false oracle.install.db.config.starterdb.password.ALL=oracle #Or not set oracle.install.db.config.starterdb.control=DB_CONTROL oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false oracle.install.db.config.starterdb.dbcontrol.emailAddress=test@qq.com #You can fill in your own mailbox address oracle.install.db.config.starterdb.automatedBackup.enable=false oracle.install.db.config.starterdb.storageType=FILE_SYSTEM_STORAGE oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=/oradata/soft/oracle11g/data oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=/oradata/soft/oracle11g/fast_recovery_area oracle.install.db.config.starterdb.automatedBackup.enable=false DECLINE_SECURITY_UPDATES=true
9. Install the database
Operating user: oracle
Operations Directory: /oradata/database
./runInstaller -silent -ignoreSysPrereqs -responseFile /oradata/db_install.rsp -ignorePrereq
Note: Some warnings will terminate the installation, -ignoreSysPrereqs and-ignorePrereq can skip warnings
Now wait for it to install itself, and you can use the tail-f command to open another interface to see the progress of the installation as prompted. It's just to see if you're stuck
When the following happens, it means that it is installed
/oradata/soft/oraInventory/orainstRoot.sh
/oradata/soft/oracle11g/product/11.2.0.3/dbhome_1/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
4. Return to this window and hit "Enter" key to continue
Successfully Setup Software.
10. Installation complete
Operating user:root
Execute the following two lines of commands, depending on where you installed them, as prompted by the information from the previous step.
/oradata/soft/oraInventory/orainstRoot.sh /oradata/soft/oracle11g/product/11.2.0.3/dbhome_1/root.sh
11. Verify installation results
Operating user: oracle
sqlplus / as sysdba
Enter SQL command line successfully
12. Start the database
Both listening and database are open after installation. If the server restarts, start listening using lsnrctl start, and the administrator logs in and opens the database using startup
Oracle uses
SELECT table_name FROM user_tables;
SELECT * FROM dba_users;
ALTER USER scott account unlock;
---modify scott User Password
ALTER USER scott IDENTIFIED BY tiger;
---Delete tablespace
DROP tablespace ts1;
---Create a tablespace
CREATE tablespace ts1 datafile '/opt/oracleTableSpace/ts1.dbf' SIZE 50M;
---Modify the default tablespace
ALTER DATABASE DEFAULT tablespace users;
---Create Table
CREATE TABLE kgc(id NUMBER,name varchar2(32));
SELECT * FROM kgc;
---Create Sequence
CREATE SEQUENCE user_seq;
SELECT user_seq.nextval FROM dual;
CREATE SEQUENCE user_seq2 START WITH 100;
SELECT user_seq2.nextval FROM dual;
CREATE SEQUENCE user_seq3 MINVALUE 5 MAXVALUE 100;
SELECT user_seq3.nextval FROM dual;
CREATE SEQUENCE user_seq4 INCREMENT BY 3;
SELECT user_seq4.nextval FROM dual;
CREATE SEQUENCE kgc_seq MINVALUE 1 nomaxvalue INCREMENT BY 1 START WITH 1 nocache;
---Create trigger
CREATE OR REPLACE TRIGGER kgc_trigger_insertid BEFORE INSERT ON KGC FOR EACH ROW
BEGIN
SELECT kgc_seq.nextval INTO:NEW.id FROM dual;
END;
INSERT INTO KGC(name) VALUES('lisi');
------------Create two tables and id Self-Increasing Trigger--------------------
CREATE SEQUENCE tbook_seq MINVALUE 1 nomaxvalue INCREMENT BY 1 START WITH 1 nocache;
CREATE OR REPLACE TRIGGER tbook_trigger_insertid BEFORE INSERT ON T_BOOK FOR EACH ROW
BEGIN
SELECT tbook_seq.nextval INTO:NEW.id FROM dual;
END;
CREATE SEQUENCE tbooktype_seq MINVALUE 1 nomaxvalue INCREMENT BY 1 START WITH 1 nocache;
CREATE OR REPLACE TRIGGER tbooktype_trigger_insertid BEFORE INSERT ON T_BOOKTYPE FOR EACH ROW
BEGIN
SELECT tbooktype_seq.nextval INTO:NEW.id FROM dual;
END;
CREATE TABLE T_BOOK(
id NUMBER,
bookName varchar(32),
typeId NUMBER,
PRIMARY KEY(id)
);
CREATE TABLE T_BOOKTYPE(
id NUMBER,
typeName varchar2(32),
PRIMARY KEY(id)
);
INSERT INTO T_BOOKTYPE(typeName) VALUES('Computer');
SELECT * FROM T_BOOKTYPE;
INSERT INTO T_BOOK(bookName,TYPEID) VALUES('Longbo English',1);
INSERT INTO T_BOOK(bookName,TYPEID) VALUES('30 Sky Mastery Java',2);
SELECT * FROM T_BOOK;
SELECT * FROM v$session;
CREATE OR REPLACE TRIGGER tr_book BEFORE DELETE ON T_BOOK FOR EACH ROW
BEGIN
IF USER!='ZHANGPENG' THEN
raise_application_error(-20001,'Insufficient privileges');
END IF;
END;
CREATE TABLE T_BOOK_OPLOG(
actionuser varchar2(32),
actionname varchar2(32),
actiontime DATE
);
CREATE OR REPLACE TRIGGER tr_op_tbook AFTER INSERT OR UPDATE OR DELETE ON T_BOOK FOR EACH ROW
BEGIN
IF updating THEN
INSERT INTO T_BOOK_OPLOG VALUES(USER,'update',SYSDATE);
ELSE IF inserting THEN
INSERT INTO T_BOOK_OPLOG VALUES(USER,'insert',SYSDATE);
ELSE IF deleting THEN
INSERT INTO T_BOOK_OPLOG VALUES(USER,'delete',SYSDATE);
END IF;
END IF;
END IF;
END;
INSERT INTO T_BOOK(bookName,TYPEID) VALUES('7 Sky Mastery scala',2);
UPDATE T_BOOK SET BOOKNAME='3 Sky Mastery scala' WHERE ID=3;
DELETE FROM T_BOOK WHERE ID=3;
SELECT * FROM T_BOOK_OPLOG;
---Create function
CREATE OR REPLACE FUNCTION GETOPCOUNT RETURN NUMBER AS
BEGIN
DECLARE OP_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO OP_COUNT FROM T_BOOK_OPLOG WHERE actionname='insert';
RETURN OP_COUNT;
END;
END GETOPCOUNT;
CREATE OR REPLACE FUNCTION GETOPCOUNTBY(opName varchar2) RETURN NUMBER AS
BEGIN
DECLARE OP_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO OP_COUNT FROM T_BOOK_OPLOG WHERE actionname= + opName;
RETURN OP_COUNT;
END;
END GETOPCOUNTBY;
CREATE OR REPLACE FUNCTION GETOPCOUNTBY2(opName varchar2) RETURN NUMBER AS
BEGIN
DECLARE OP_COUNT NUMBER;
query_sql varchar2(300);
BEGIN
query_sql:='SELECT COUNT(*) FROM T_BOOK_OPLOG WHERE actionname=' || ''''||opName ||'''';
EXECUTE IMMEDIATE query_sql INTO OP_COUNT;
RETURN OP_COUNT;
END;
END GETOPCOUNTBY2;
SELECT GETOPCOUNTBY2('insert') num FROM dual;
SELECT * FROM KGC;
------------------------------------------------------String function--------------------------------------------------------------
---Complete Left
SELECT name,LPAD(NAME,6,'a') FROM KGC;
---Complete right
SELECT name,RPAD(NAME,5,'0') FROM KGC;
---Uppercase
SELECT name,UPPER(NAME) FROM KGC;
---To lower case
SELECT LOWER('ASD') FROM dual;
---title case
SELECT name,INITCAP(NAME) FROM KGC;
---String Length
SELECT name,LENGTH(NAME) FROM KGC;
---substr
SELECT name,SUBSTR(NAME,1,3) FROM KGC;
---String contains(Returns the position containing the string)
SELECT name,INSTR(NAME,'s') FROM KGC;
---Strip Spaces(Both ends)
SELECT TRIM(' aabb ') FROM dual;
---Left Despace
SELECT LTRIM(' aabb') FROM dual;
---Right Despace
SELECT RTRIM('aabb ') FROM dual;
---StringBuilder
SELECT CONCAT('hello ',name) FROM KGC;
---Character inversion
SELECT REVERSE(name) FROM KGC;
----------------------------------------------------Mathematical Functions----------------------------------------------------------
---Rounding
SELECT ROUND(3.1415926,2) FROM dual;
---ceil
SELECT CEIL(123.456) FROM dual;
---Rounding Down
SELECT FLOOR(123.556) FROM dual;
---Modeling
SELECT MOD(32,7) FROM dual;
---Compared to zero, greater than zero is 1, less than zero is-1，Zero equals zero
SELECT SIGN(-12) FROM dual;
SELECT SIGN(12) FROM dual;
SELECT SIGN(0) FROM dual;
---Power function
SELECT POWER(3,2) FROM dual;
--To open up
SELECT SQRT(9) FROM dual;
---Decimal places
SELECT TRUNC(1234.5678,3) FROM dual;
SELECT TRUNC(1234.5678,0) FROM dual;
SELECT TRUNC(1234.5678,-1) FROM dual;
---Convert Number to String
SELECT TO_CHAR(123.45) FROM dual;
SELECT TO_CHAR(123.45，'000.00') FROM dual;
SELECT TO_CHAR(123123123.45,'999,999,999.99') FROM dual;
--------------------date------------------------
SELECT SYSDATE FROM dual;
SELECT SYSTIMESTAMP FROM dual;
SELECT ADD_MONTHS(SYSDATE,3) FROM dual;
SELECT LAST_DAY(SYSDATE) FROM dual;
-----View Creation Call
SELECT * FROM T_BOOK_OPLOG;
CREATE OR REPLACE VIEW v_tbook_log AS
SELECT * FROM T_BOOK_OPLOG WHERE ACTIONNAME='update' WITH READ ONLY;
SELECT * FROM v_tbook_log;
CREATE OR REPLACE FUNCTION myfunction RETURN varchar AS
BEGIN
DECLARE num NUMBER:=1000;
v varchar(32):='hello oracle';
BEGIN
dbms_output.put_line('hello '||num||v);
RETURN 'hello '||num||v;
END;
END;
SELECT myfunction() FROM dual;
SELECT * FROM SCOTT.EMP WHERE SAL>=3000;
CREATE OR REPLACE FUNCTION getEmpCountBy(salary NUMBER) RETURN varchar AS
BEGIN
DECLARE emp_count NUMBER;
query_sql varchar2(500);
BEGIN
SELECT COUNT(*) INTO emp_count FROM SCOTT.EMP WHERE SAL>=salary;
---query_sql:='SELECT COUNT(*) FROM SCOTT.EMP WHERE SAL>='||salary;
---EXECUTE IMMEDIATE query_sql INTO emp_count;
IF emp_count>0 THEN
RETURN 'Yes'|| emp_count|| 'One employee's salary is greater than or equal to'|| salary;
ELSE
RETURN 'No pay greater than or equal to'|| salary|| 'Employees of';
END IF;
END;
END;
SELECT getEmpCountBy(3000) FROM dual;
DECLARE emp_count NUMBER;
RESULT_str varchar2(200);
salValue NUMBER:=4000;
BEGIN
SELECT COUNT(*) INTO emp_count FROM SCOTT.EMP WHERE SAL>=4000;
IF (emp_count=1) THEN
dbms_output.put_line('One person is paid more than'||salValue);
ELSIF (emp_count>1) THEN
dbms_output.put_line('Yes'||emp_count||'Salary greater than'||salValue);
ELSE
dbms_output.put_line('No,');
END IF;
END IF;
END;
DECLARE orinum NUMBER:=1;
sumvalue NUMBER:=0;
BEGIN
LOOP
IF (orinum>5) THEN
EXIT;
END IF;
sumvalue:=sumvalue+orinum;
orinum:=orinum+1;
dbms_output.put_line(sumvalue);
END LOOP;
END;
DECLARE emp_count NUMBER;
salvalue NUMBER:=3000;
BEGIN
SELECT COUNT(*) INTO emp_count FROM SCOTT.EMP WHERE SAL>= + salvalue;
CASE emp_count
WHEN 0 THEN
dbms_output.put_line('No employee is paid more than'||salvalue);
WHEN 1 THEN
dbms_output.put_line('One employee is paid more than'||salvalue);
WHEN 2 THEN
dbms_output.put_line('Two employees are paid more than'||salvalue);
WHEN 3 THEN
dbms_output.put_line('Three employees are paid more than'||salvalue);
ELSE
dbms_output.put_line('More than three employees are paid more than'||salvalue);
END CASE;
END;
DECLARE orinum NUMBER:=1;
sumvalue NUMBER:=0;
BEGIN
WHILE orinum<6 LOOP
sumvalue:=sumvalue+orinum;
dbms_output.put_line(sumvalue);
orinum:=orinum+1;
END LOOP;
END;
DECLARE orinum NUMBER:=1;
sumvalue NUMBER:=0;
BEGIN
FOR orivalue IN 1..5 LOOP
sumvalue:=sumvalue+orivalue;
dbms_output.put_line(sumvalue);
END LOOP;
END;
-------------cursor----------------
DECLARE
--Define Cursor
CURSOR c_job IS
SELECT empno,ename,job,sal FROM SCOTT.EMP WHERE JOB='MANAGER';
--Define cursor variables to accept c_job Data for each row in
c_row c_job%rowtype;
BEGIN
FOR c_row IN c_job LOOP
dbms_output.put_line(c_row.empno||'--'||c_row.ename||'--'||c_row.job||'--'||c_row.sal);
END LOOP;
END;
-------------------------------------------------------------------------------
DECLARE
--Define Cursor
CURSOR c_job IS
SELECT empno,ename,job,sal FROM SCOTT.EMP WHERE JOB='MANAGER';
--Define cursor variables to accept c_job Data for each row in
c_row c_job%rowtype;
BEGIN
OPEN c_job; ---open
LOOP
--Grab a row of data in the cursor and assign it to c_row
FETCH c_job INTO c_row;
EXIT WHEN c_job%notfound;
dbms_output.put_line(c_row.empno||'--'||c_row.ename||'--'||c_row.job||'--'||c_row.sal);
END LOOP;
CLOSE c_job; ---Close Cursor
END;
-------------------------------------------------------------------------------
BEGIN
IF SQL%isopen THEN
dbms_output.put_line('sql Cursor is open');
ELSE
dbms_output.put_line('sql Cursor not open');
END IF;
END;
CREATE TABLE SCOTT.EMP1 AS SELECT * FROM SCOTT.EMP;
DECLARE
CURSOR csr_update IS
SELECT * FROM SCOTT.EMP1 FOR UPDATE OF SAL;
empInfo csr_update%rowtype;
salInfo SCOTT.EMP1.sal%TYPE;
BEGIN
FOR empInfo IN csr_update LOOP
IF empInfo.sal<1500 THEN
salInfo:=empInfo.sal*1.2;
ELSE IF empInfo.sal<2000 THEN
salInfo:=empInfo.sal*1.5;
ELSE IF empInfo.sal<3000 THEN
salInfo:=empInfo.sal*2;
END IF;
END IF;
END IF;
--UPDATE SCOTT.EMP1 SET sal=salInfo WHERE empno=EMPINFO.empno;
UPDATE SCOTT.EMP1 SET sal=salInfo WHERE CURRENT OF CSR_UPDATE;
END LOOP;
END;
SELECT * FROM SCOTT.EMP;
SELECT * FROM SCOTT.EMP1;
-----------stored procedure--------------
CREATE OR REPLACE PROCEDURE testloop(num IN NUMBER,sumvalue OUT NUMBER) AS
BEGIN
DECLARE orinum NUMBER:=0;
sumVal NUMBER:=0;
BEGIN
LOOP
IF orinum>num THEN
sumvalue:=sumVal;
EXIT;
END IF;
sumVal:=sumVal+orinum;
orinum:=orinum+1;
END LOOP;
END;
END;
DECLARE sumVal NUMBER;
BEGIN
testloop(5,sumVal);
dbms_output.put_line('Result:'||sumVal);
END;
CREATE OR REPLACE PROCEDURE addBook(bookName IN varchar2,typeID IN NUMBER) AS
BEGIN
DECLARE maxID NUMBER;
BEGIN
SELECT MAX(id) INTO maxID FROM T_BOOK;
INSERT INTO T_BOOK(id,bookname,typeid) VALUES(maxID+1,bookName,typeID);
END;
END;
BEGIN
addBook('30 Sky Mastery scala',2);
END;
SELECT * FROM T_BOOK;
----------Partition table-----------
CREATE TABLE range_table(
id NUMBER,
done_date DATE,
DATA varchar2(32)
)
PARTITION BY RANGE(done_date)
(
PARTITION part_1 VALUES less than(TO_DATE('20200712','yyyymmdd')),
PARTITION part_2 VALUES less than(TO_DATE('20210712','yyyymmdd')),
PARTITION part_3 VALUES less than(MAXVALUE)
)
INSERT INTO RANGE_TABLE VALUES(1,TO_DATE('20200611','yyyymmdd'),'hehe');
INSERT INTO RANGE_TABLE VALUES(2,TO_DATE('20210612','yyyymmdd'),'hello');
INSERT INTO RANGE_TABLE VALUES(3,TO_DATE('20210713','yyyymmdd'),'world');
I
SELECT * FROM user_tab_partitions WHERE table_name='HASH_TABLE';
SELECT * FROM RANGE_TABLE;
------delete a partition----------
ALTER TABLE RANGE_TABLE TRUNCATE PARTITION part_2;
--------Hash partitioning---------
CREATE TABLE hash_table(
id NUMBER,
done_date DATE,
DATA varchar2(32)
)
PARTITION BY hash(done_date)
(
PARTITION part_1,
PARTITION part_2
)