---
# System prepended metadata

title: Orcale 12c

---

# 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 

![image](https://user-images.githubusercontent.com/14309741/154801221-6f868406-c1fa-47b6-a7e4-ccaa2b52c61e.png)


```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;
```

![](https://i.stack.imgur.com/RbULW.png)


## 第四周(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;
```

![JoinExample](https://user-images.githubusercontent.com/13542041/156883767-f5564e07-6867-4787-828d-fcbc98e413ee.PNG)
![DeleteExample](https://user-images.githubusercontent.com/13542041/156883444-20f06561-bde9-4aa6-a6d0-683d4d4ab312.PNG)
![TransactionExample](https://user-images.githubusercontent.com/13542041/156883446-7916f180-559b-4bcc-8b7a-22a5fa8cdf01.PNG)

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/)


![圖片](https://user-images.githubusercontent.com/14309741/158018285-218b291f-b0f7-460f-8fa3-2a7b8e618c08.png)

下堂帶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