# Oracle Database

## 目錄
> :::spoiler 清單
> [TOC]
> :::
---
### 基本觀念
> 適合在Windows、Linux、Unix運作
> 支援networking或離線作業

#### Online Transaction Processing (OLTP, 線上交易處理)
> 一種數據處理類型，執行多筆同時發生的交易，例如網路銀行、購物、訂單輸入或發送文本消息
> 傳統上被稱為經濟或金融交易，並被記錄和保護，以便可以進行查詢和分析

#### Data Warehouse (資料倉儲)
> 一種資訊的中央儲存庫，將來自不同來源的數據以中心化統一管理

### 登入環境
- 本機Login：`/ as sysdba`

### Basic Command
- CREATE (創建)
> e.g,
> `CREATE TEMPORARY TABLESPACE temp_0812 TEMPFILE 'C:\\temp01.dbf' SIZE 20M;`

- DROP (刪除)
> e.g,
> `DROP TABLESPACE temp_0812 INCLUDING CONTENTS AND DATAFILES;`

> tablespace group不能用DROP，只能用ALTER清空內容
> ALTER TABLESPACE temp1 tablespace group

- ALTER (修改)
> e.g,
> `ALTER TABLESPACE temp1 TABLESPACE GROUP temp_group1;`
> `ALTER TABLESPACE temp2 TABLESPACE GROUP temp_group2;`
> `ALTER TABLESPACE temp3 TABLESPACE GROUP temp_group2;`
> `ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group1;`

- SELECT (查詢)
> `SELECT group_name, tablespace_name FROM dba_tablespace_groups;`

- DESC
> 將查詢的結果排序
> `DESC table_name`

### 資料型態 (Data Type)
| Datatype | Description |
|-|-|
| VARCHAR2(size) | Variable-length character data |
| CHAR(size) | Fixed-lenght character data |
| NUMBER(p,s) | Variable-lenght numeric data |
| DATE | Data and time values |
| TIMESTAMP | extension to DATE |
| INTERVAL | extension to DATE |
| LONG | Variable-length character data up to 2G |
| CLOG | Single-byte character data up to 4G |
| RAW | Raw binary data |
| BLOB | Binary data up to 4G |

### Oracle的Tables
- User Tables
- Collection of tables created and maintained by the user
- Contain user information
- Data Dictionary
- Collection of tables created and maintained by the Oracle server
- Contain database information

### 操作主題

#### Resizing a Smallfile Tablespace Using ALTER DATABASE
- CREATE TABLESPACE temp01 DATAFILE 'C:\\test01.dbf' SIZE 20M;

- alter database
datafile C:\\test01.dbf' resize 15m;
- alter database
datafile 'C:\\test01.dbf' resize 10m;
- alter database
datafile 'C:\\test01.dbf' resize 1m;
- alter database
datafile 'C:\\test01.dbf' resize 100t;
- alter database
datafile 'C:\\test01.dbf' resize 50g;

##### autoextent, next, maxsize
- 避免手動調整tablespace時報錯
> alter database datafile C:\\test01.dbf' autoextend on next 20m maxsize 1g;

- 若disk space 不足 volume使用
> create bigfile tablespace dmarts datafile 'C:\dmarts.dbf' size 7m autoextend on next 10m maxsize unlimited extent management local segment space management auto;

> alter tablespace dmarts resize 5m;

#### 查詢資料
- Describe tables owned by the user.
> SELECT * FROM user_tables;

- View distinct object types owned by the user.
> SELECT DISTINCT object_type FROM user_objects;

- Tables, views, synonyms, and sequences owned by the user.
> SELECT * FROM user_catalog;

---
###### tags: `Database`