# Orcale 12c
## 第一周(2月12)
### 考證
OBA = SQL + fun1
OBP = OBA + fun2
### 行業
1. Business (SQL+python+SAS)
2. Admin DBA
3. DATA (SSIS)
### database 類型
1. MySQL
2. Orcale
3. SQL server
4. IMB DB2
### 生產力admin
dom-admin
3256
### 帳密
#### Windows login:
> 帳 : cm603-02-2022-c
> 密1224
#### VM ware oracle liunx
> oracle
> cpttm2018
> root
> cpttm2018
#### Oracle database
> sys
> oracle_4U
### 啟動 listener
```shell
lsnrctl start
```
### Database Configuration Assistant
```shell
dbca
```
### 查詢/排查
```shell
netmgr
lsnrctl stop
lsnrctl start
lsnrctl status
```
### 連線
```shell
sqlplus hr/oracle_4U@orcl
```
### 關機
```shell
. oraenv
?orcl
# sqlplus sys/oracle_4U@orcl as sysdba
sqlplus sys/oracle_4U as sysdba
SQL> shutdown immediate
lsnrctl stop
```
### 開機
```shell
lsnrctl start
. oraenv
?orcl
sqlplus sys/oracle_4U as sysdba
SQL> startup
```
## 第二周(2月19)
### 更改 ip
```shell
ifconfig
> 192.168.19.128
su -
gedit /etc/hosts #與 vim /etc/hosts 同理
ping oem.example.com
```
```shell
sqldeveloper
```
### connect to DB
```shell
sqlplus hr/oracle_4U@orcl
```
### basic data
```shell
select * from hr.employees
```
### unlcok and reset
```shell
. oraenv
orcl
sqlplus sys/oracle_4U as sysdba
alter user hr account unlock;
alter user hr identified by oracle_4U;
```
### SQL Tutorial
http://csyue.nccu.edu.tw/ch/bigdata1081(SQL&R).pdf
### oracle sql need to COMMIT
```sql
desc hr.employees;
select employee_id, department_id from hr.employees;
select 1+1 from dual; /*cannot no table, so dual*/
select sysdate from dual; /* dd-mm-year ex: 20-FEB-22 */
select last_name "NamE" , salary "Annual Salary"
from employees;
select first_name||' '||last_name|| q'{'s a }' || JOB_ID "Full Name" from hr.employees;
```
```sql
select last_name, job_id, department_id
from employees
where last_name = initcap('WhaLeN'); /*initcap means not case sentsitive */
select last_name, hire_date from employees where hire_date = '2003-06-17'; /*Error*/
select last_name, hire_date from employees where hire_date = '17-Jun-03'; /*Ok*/
select first_name from employees where initcap(first_name) like 'S%';
select first_name from employees where first_name like 'S%' or first_name like 's%'; /*should be faster when large data*/
select employee_id, last_name, salary*12 annsal
from employees
where annsal > 120000 /*cannot use alias name in WHERE or group by*/
order by annsal; /*but alias name can use in ORDER BY*/
select employee_id, first_name
from employees
order by employee_id
offset 5 rows fetch next 6 rows only; /*skip the first 5 rows result and select top 6*/
select employee_id, last_name, salary, department_id
from employees
where employee_id = &employee_num; /*can input variable, quite useless*/
select lower('SQL Course') from dual;
select upper('SQL Course') from dual;
select INSTR('HelloWorlddasdfasfdaWWWWdsafsdf','W') from dual; /*will find the first 'W'*/
select CURRENT_TIMESTAMP + 100 from dual; /*today + 100 days*/
NVL /*if NULL*/
select employee_id, hire_date, to_char(hire_date,'fmDay fmMonth-dd, yyyy') from employees;
select employee_id, salary, to_char(salary, '$9,999.99')
from hr.employees;
select * from employees where hire_date>= to_date('2003-June-17','YYYY-MONTH-dd');
```
> Ctrl + enter 執行 SQL
## 第三周(2月26日)
### example:
```sql
select employee_ID, upper(first_name||' '||last_name) "Full Name",
to_char(hire_date, 'fmDAY, YYYY-Month-dd'),
to_char(salary, '$999,999.00')
from employees
--where hire_date = '17-sep-03';
where hire_date = to_date('2002-June-7','YYYY-month-dd');
select employee_id, first_name, nvl(to_char(manager_id),'N/A')
from employees;
select employee_id, salary * (1 + nvl(commission_pct,0)), nvl2(commission_pct, salary * (1 + commission_pct), salary), coalesce(salary * (1+commission_pct), salary, 5000)
from hr.employees;
```
```sql
update hr.employees
set salary = NULL
where employee_id = 100;
commit;
```
```sql
desc hr.employees; --查詢
```
```sql
select nvl(commission_pct, 0) -- same as sql server isnull()
select nvl2(expr1, expr2, expr3) -- if expr1 not null, then expr2, else expr3
NULLIF(expr1, expr2) -- if expr1 == expr2, then null, else expr1
```
```sql
-- case when
select employee_id, (first_name ||' '||last_name) full_name, job_id, salary as original_salary,
case when job_id = 'FI_MGR' then salary * 0.9
when job_id = 'AD_PRES' then salary * 0.7
when job_id = 'AD_VP' then salary * 0.8
else salary * 0.95
end as new_salary
from hr.employees
order by employee_id;
-- decode, same as case when but can't use "<" or ">"
select employee_id, (first_name ||' '||last_name) full_name, job_id, salary as original_salary,
decode(job_id, 'FI_MGR', salary * 0.9,
'AD_PRES',salary * 0.7,
'AD_VP', salary * 0.8,
salary * 0.95) new_salary
from hr.employees
order by employee_id;
```
### left outer join table example
```sql
select e.employee_id, e.first_name, d.department_name, j.job_title, l.city, c.country_name, salary
from hr.employees e
left outer join hr.departments d on (e.department_id = d.department_id)
join hr.jobs j on (e.job_id = j.job_id)
left outer join hr.locations l on (d.location_id = l.location_id)
left outer join hr.countries c on (l.country_id = c.country_id)
order by e.employee_id;
```
## 第四周(3月5日)
oracle default table no lock
insert, update, delete can be rollback
but truncate can't rollback
auto commit? DDL (Data Definition)
--> Transaction Control
--> end transaction cause auto commit
```sql
create table hr.employee_bak
as select * from hr.employees;
select count(*) from hr.employee_bak;
select count(*) from hr.employees;
delete from hr.employee_bak;
rollback;
```
BLOB --> Binary Long Object, 一串勁長0同1, 加解碼器可以變作何檔案, 例如圖片
can alter table read only sql server?
```
```
### flashback table after drop table:
```sql
select * from hr.employee_bak;
drop table hr.employee_bak;
flashback table hr.employee_bak to before drop;
select * from recyclebin;
```
### search column name:
```sql
select *
from user_tab_columns
where column_name like '%NAME%';
```
### search table name
```sql
select *
from user_tables
where table_name like '%EMP%';
```
### search dictionary
```sql
select * from dictionary
where table_name like '%VIEW%';
```
### search view definition
```sql
select * from user_views;
```
### search which column has constraint
```sql
select * from USER_CONS_COLUMNS
where owner = 'HR' and table_name ='EMPLOYEES';
```
### search HR role and privilege
```sql
select * from USER_ROLE_PRIVS;
select * from USER_CODE_ROLE_PRIVS;
select role, privilege from ROLE_SYS_PRIVS
union
select role, privilege from ROLE_TAB_PRIVS;
```
### Sequence
政府工會考, SQL Server都有
問兩題, 一題叫你CREATE Sequence, 另一題叫你攞個Sequence出黎
### Oracle SQL Exercise (Not execute yet, only FF)
1. ok
```sql
SELECT last_name, hire_date, TO_CHAR(ADD_MONTHS(hire_date, 6), 'fmDAY, MON DD, YYYY' )AS REVIEW
FROM hr.employees;
```
2. ok
```sql
SELECT employee_id, first_name, last_name,
DECODE (Job_id, 'A', 'AD_PRES',
'B','ST_MAN',
'C','IT_PROG',
'D','SA_REP',
'E','ST_CLERK',
'0' ) Grade
FROM hr.employees;
```
3. ok
```sql
SELECT employee_id, last_name
FROM hr.employees
WHERE salary >= (SELECT AVG(salary) FROM hr.employees)
ORDER BY salary;
```
4. ok
```sql
CREATE TABLE hr.my_employee (
id number(4) NOT NULL,
name varchar(25),
userid varchar(8),
salary number(9,2)
);
```
5. ok
```sql
INSERT INTO hr.my_employee VALUES (1, 'Peter Chan', 'Pchan', 800);
```
6. ok
```sql
CREATE VIEW hr.EMPLOYEES_VU AS
SELECT employee_id, CONCAT(last_name, first_name) AS full_name, department_id
FROM hr.employees
WITH READ ONLY;
```
7. ok
```sql
CREATE SEQUENCE DEPT_ID_SEQ
START WITH 1000
INCREMENT BY 10
MAXVALUE 2000;
```
8. ok
```sql
INSERT INTO hr.departments VALUES (DEPT_ID_SEQ.NEXTVAL, 'Education', NULL, NULL);
```
9. Wrong
```sql
CREATE INDEX IDX_DEPARTMENTS_DEPARTMENT_ID
ON hr.departments (department_id2);
CREATE INDEX IDX_DEPARTMENTS_LOCATION_ID
ON hr.departments (location_id);
```
10.
```sql
.oraenv
orcl
sqlplus sys/oracle_4U as sysdba
create user lab_exam identified by oracle_4U;
```
```sql
alter user lab_exam account unlock;
alter user lab_exam identified by oracle_4U;
```
--### not know how to assign access
11.
--### not know how to assign access
12. ok
```sql
CREATE TABLE hr.EMP2 (
ID number(7),
name varchar(25),
dept_id number(7),
remark number(10)
);
ALTER TABLE hr.EMP2
MODIFY name varchar(50);
ALTER TABLE hr.EMP2
MODIFY remark varchar(50);
```
13. ok
```sql
ALTER TABLE hr.EMP2
ADD CONSTRAINT my_emp_dept_id_fk
FOREIGN KEY (dept_id) REFERENCES hr.departments (department_id);
```
14. Error: missing key word
```sql
CREATE TABLE hr.IT_PROG_EMP AS
SELECT *
FROM hr.employees
WHERE job_id = 'IT_PROG';
```
15.
```sql
select *
from user_tab_columns
where column_name LIKE '%NAME%';
```
16.
dictionary
17. ok
```sql
SELECT employee_id AS Emp_id, last_name AS Emp_last_name, Manager_id, NULL as Manager_Last_Name
FROM hr.employees
WHERE (UPPER(first_name) LIKE '%KING%' OR UPPER(last_name) LIKE '%KING%')
AND (manager_id IS NULL)
ORDER BY employee_id;
```
### exercise by Ivan
1.
```sql
SELECT last_name, hire_date, TO_CHAR(ADD_MONTHS(hire_date, 6), Fm'DAY, MON DD, YYYY' )AS REVIEW
FROM hr.employees;
```
2.
```sql
SELECT job_id as Job,
DECODE (Job_id, ‘A’, ‘AD_PRES’,
‘B’,‘ST_MAN’,
‘C’,‘IT_PROG’,
‘D’,‘SA_REP’,
‘E’,‘ST_CLERK’,
‘0’ ) Grade
FROM hr.employees;
```
3.
```sql
SELECT employee_id, last_name
FROM hr.employees
WHERE salary >= (SELECT AVG(salary) FROM hr.employees)
ORDER BY salary;
```
4.
```sql
CREATE TABLE hr.my_employee (
id number(4) NOT NULL,
name varchar(25),
userid varchar(8),
salary number(9,2)
);
```
5.
```sql
INSERT INTO hr.my_employee VALUES (1, 'Peter Chan', 'Pchan', 800);
```
6.
### 考試相關:
OCA - [Oracle Database 12c Administrator Certified Associate Certification Overview ](https://education.oracle.com/oracle-database-12c-administrator-certified-associate/trackp_248)
OCP - [Oracle Database 12c Administrator Certified Professional Certification Overview](https://education.oracle.com/%E7%94%A2%E5%93%81%E5%9E%8B%E9%8C%84-outrackpath-trackp_249/trackp_249)
#### 1.1z0-071 - [Oracle Database SQL Exam Number: 1Z0-071](https://education.oracle.com/products/pexam_1Z0-071)
> [1z0-071 題庫](https://www.examtopics.com/exams/oracle/1z0-071/)
#### 2.1Z0-062 - [Oracle Database 12c Administration 1Z0-062](https://education.oracle.com/%E7%94%A2%E5%93%81%E5%9E%8B%E9%8C%84-ouexam-pexam_1z0-062/pexam_1Z0-062)
> [1z0-062 題庫](https://www.examtopics.com/exams/oracle/1z0-062/)
#### 3.1Z0-063 - [Oracle Database 12c: Advanced Administration Exam Number: 1Z0-063](https://education.oracle.com/%E7%94%A2%E5%93%81%E5%9E%8B%E9%8C%84-ouexam-pexam_1z0-063/pexam_1Z0-063)
> 注意: 考前需要 [Summit a course form](https://catalog-education.oracle.com/pls/apex/f?p=1010:26) (UDP)
> [1Z0-063 題庫](https://www.examtopics.com/exams/oracle/1z0-063/)
下堂帶Hard Disk, NTFS
Oracle Linux 7.X - 5G
Oracle 12c r2 for linux - 4G
Oracle grip infrastructure - 4G
SQl developer (windows) - 1G
[Ken 的題庫 for 071](https://onedrive.live.com/?authkey=%21AEIdai3FNrA2IEY&id=1596B3130879530A%2114426&cid=1596B3130879530A)
### 3月19日
Enterprise Manager
1. eKit
2. oracle 12c
3. oracle linux
4. oracle grid control (Enterprise Manager)
Linux:
帳號: orcale
密碼: oracle_4U
```
Oracle Enterprise Manager
https://<ip>:7803/em/faces/sdk/
login: sysman
word: oracle_4U
```
ekit D78846GC20_ag 有教學
EM 有幾百個參數, 呢幾個可以set大d
open_cursors = 4000
processes = 5000
sessions = 6000
use net manager (netmgr) to create a new listener, add to database for load balancing / high availablty
```shell
tnsping hr2
```
```shell
netmgr
lsnrctl start listener4
sqlplus hr/oracle_4U@192.168.55.129:1524/hr2
```
[Ken 的題庫 for 062](https://onedrive.live.com/?authkey=%21AMVntI5CY%5FJOvJ0&id=1596B3130879530A%2114484&cid=1596B3130879530A)
### 開啟和關閉 EM 的方法:
```shell
/***how to fully stop the EM cloud and the HR2 db **/
cd $ORACLE_MIDDLEWARE
./emctl stop oms -all
cd $ORACLE_AGENT
./emctl stop agent
. oraenv
emrepdb
sqlplus sys/oracle_4U as sysdba;
shutdown immediate;
. oraenv
hr2
sqlplus sys/oracle_4U as sysdba;
shutdown immediate;
/***how to fully start the EM cloud and the HR2 db **/
cd $ORACLE_MIDDLEWARE
./emctl stop oms -all
lsnrctl start
. oraenv
emrepdb
sqlplus sys/oracle_4U as sysdba;
startup;
cd $ORACLE_MIDDLEWARE note: 6 to 10 min
./emctl start oms
cd $ORACLE_AGENT
./emctl start agent
. oraenv
hr2
sqlplus sys/oracle_4U as sysdba;
startup;
```
## 最後一課
記憶體不足問題，要從 os 去改 PARAMETER FILE
```
lsnrctl start
. oraenv
hr2
sqlplus sys/oracle_4U as sysdba
startup (記憶體不足)
```
paramenter path:
`cd $ORACLE_HOME/dbs`
這個路徑底下有以下檔案:
spfileHR2.ora (針對這個檔案修改)
spfileemrepdf.ora
spfileorcl.ora
`gedit spfileHR2.ora` (2進位無法直接改)
### 使用 sqlplus 轉換成 ASCII (pfile指ASCII, spfile係Binary)
```
sqlplus sys/oracle_4U as sysdba
SQL> create pfile from spfile;
```
轉換後有檔案 inithr2.ora
```
gedit inithr2.ora
```
把
`hr2.__sga_target=2147483648`
更改成
`hr2.__sga_target=4147483648`
### 再從 ASCII(pfile) 轉去 Binary(spfile)
```
mv spfileHR2.ora to spfileHR2.bak
sqlplus sys/oracle_4U as sysdba
SQL> create spfile from pfile;
SQL> startup force
```
EM戈度 Recovery Setting 有個Archievelog mode, 要剔咗佢
#### Default database file path:
`cd $ORACLE_BASE/oradata`
``` loader.ctl, import csv file
load data
infile 'mydata2.csv'
badfile 'bad.log'
discardfile 'discard.log'
replace
into table hr.emp_load
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
```
```bash
sqlldr system/oracle_4U control=loader.ctl
```
### 第16課、如何使用 sql ldr 將 csv 檔讀入 database
sqlldr.cmd (run the loader.ctl):
```
sqlldr system/oracle_4U control=loader.ctl
```
loader.ctl (kick off controler):
```
load data
infile 'mydata2.csv'
badfile 'bad.log'
discardfile 'discard.log'
replace
into table hr.emp_load
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
```
## 1z071 Ken題庫註解
[Ken 的題庫 for 071](https://onedrive.live.com/?authkey=%21AEIdai3FNrA2IEY&id=1596B3130879530A%2114426&cid=1596B3130879530A)
01. 因為Salary同Commission冇 NOT NULL, 所以AVERAGE()會有NULL值
15. 題目打錯字, 問題是 emp_mgr_fk. 由於 disable咗PK CASCADE, FK指住PK 會停埋
22. MERGE INTO, 指令等同 update同insert綜合, 符合條件就update, 唔符合就 insert
27. 圖片是拖時間, 直接看題目即可
30. 不明白為甚麼選 CHAR 而非 VARCHAR2
31. SYSDATE --目前日期
LAST_DAY(SYSDATE) --月尾
NEXT_DAY(LAST_DAY(SYSDATE),'MON') --月尾之後的星期一
34. B、D語法有錯, 另外CREATE VIEW需要COLUMN名稱, 所以C正確
35. equijoin即係 join on a = b 呢種 , non-equijoin即係 join on a > b呢種
38. DROP TABLE products PURGE; 因為有PURGE所以無法復原
40. TRUNC(int)為無條件退位
ROUND(156.00, -1) --> 160
TRUNC(160,-1) --> 160
44. 題目group functions, 指 MAX(), MIN(), SUM() 這類型
54. 由於CUST_CREDIT_LIMIT data type為 NUMBER, 所以只有NVL(TO_CHAR(),‘Not Available’)正確
59. 英文題, 題目問哪三項是Build-in function
Finding the lowest value -- MIN()
Finding the quotient -- 商數
Raising to a power -- 次方 POWER()
Subtraction -- 減法
Addition -- SUM()
65. 找出金額最大是哪個ORDER_ID
66. 題目寫Choose two但只有一個答案, 不知道正解
72. escape
84. "&" 這個符號是甚麼?
87. WHERE與HAVING的問題, 答案應該是 DE
90. 答案應該係D, one-to-many --> teacher-to-student
96. 因為COMM有NULL值, 計漏咗幾個人
101. https://www.examtopics.com/discussions/oracle/view/10888-exam-1z0-071-topic-1-question-164-discussion/
104. 張圖可以skip咗佢,直接睇選項KeyWord判斷, 見到who同whose機會比較大
105. 答案全錯, 最接近應該是C, 但Select salary漏咗逗號
131. NEXT_DAY( , 1) 是下星期日
77 題 研究一下有無錯
70 題 咩係 signal-row function
Sign in with Wallet
Connect another wallet