Oracle 資料庫基礎操作圖解：安裝、連線、用戶權限、表空間、備份

一、SQL*Plus 命令列工具啟動

方式 1：開始選單 → 程式 → Oracle → Application Development → SQL*Plus
方式 2：CMD 執行 `sqlplus`

二、連接 Oracle 資料庫

連接身分區別

身分	權限	典型用途
SYSDBA	資料庫管理員 (最高)	啟動/關閉、備份恢復、參數修改
SYSOPER	資料庫操作員	啟動/關閉、備份、不含結構變更
NORMAL	普通用戶	業務開發、查詢

三、常用字典查詢

-- 當前用戶所有表
SELECT DISTINCT table_name FROM user_tab_columns;
-- 或
SELECT * FROM tab;

四、監聽器配置 (listener.ora)

路徑：`$ORACLE_HOME/NETWORK/ADMIN/listener.ora`

客戶端透過 TNS 別名連線遠端資料庫。

五、用戶與權限管理

1. 建表

2. 插入資料

3. 建立用戶

CREATE USER new_user IDENTIFIED BY password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

4. 授權角色

-- CONNECT：允許連線、建立對象
GRANT CONNECT TO new_user;

-- RESOURCE：允許使用儲存空間 (建表、序列等)
GRANT RESOURCE TO new_user;

-- DBA：最高權限 (謹慎授予)
GRANT DBA TO new_user;

5. 修改用戶密碼/鎖定

ALTER USER new_user IDENTIFIED BY new_password;
ALTER USER new_user ACCOUNT LOCK/UNLOCK;

6. 連線測試

六、查詢所有資料庫用戶

SELECT username, account_status, default_tablespace
FROM dba_users
ORDER BY username;

七、表空間管理

建立表空間

CREATE TABLESPACE tbs_data
  DATAFILE 'D:\oradata\tbs_data01.dbf' SIZE 100M
  AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

切換日誌文件

ALTER SYSTEM SWITCH LOGFILE;

八、DDL 常用操作

操作	語法	圖示
查詢表欄位	DESC table_name / SELECT * FROM user_tab_columns WHERE table_name=...
時間轉換	TO_DATE('2017-01-01','YYYY-MM-DD')
修改欄位類型	ALTER TABLE t MODIFY (col VARCHAR2(100));
新增外鍵	ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (pid) REFERENCES parent(id);

九、刪除表三種方式差異

指令	特性	可回滾	空間釋放	圖示
DROP TABLE	刪除結構+資料+索引+授權	否	是
DELETE	刪除資料，保留結構	是	否 (高水位線不變)
TRUNCATE	刪除資料，重置高水位線	否	是

十、儲存結構查詢

-- 作業系統區塊大小
-- (需 OS 指令或 DBMS_SPACE_ADMIN)

-- SYSTEM 表空間位元組
SELECT SUM(bytes) FROM dba_data_files WHERE tablespace_name = 'SYSTEM';

-- 表空間所有段
SELECT * FROM dba_segments WHERE tablespace_name = 'USERS';

-- 表空間資料檔
SELECT * FROM dba_data_files WHERE tablespace_name = 'USERS';

-- 段類型分布
SELECT segment_type, COUNT(*), SUM(bytes)/1024/1024 AS mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
GROUP BY segment_type;

-- 區間分布
SELECT * FROM dba_extents WHERE tablespace_name = 'USERS';

十一、實戰：STUDENT 表測試

-- 查詢 student 表
SELECT * FROM student;
-- 

主鍵操作

-- 查詢 SC 表主鍵
SELECT * FROM user_constraints WHERE table_name = 'SC' AND constraint_type = 'P';
-- 

-- 刪除 SC 表主鍵
ALTER TABLE sc DROP PRIMARY KEY;
-- 

批量插入 1000 筆測試資料

BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO student (id, name, age) VALUES (i, 'Student_'||i, MOD(i,50)+18);
  END LOOP;
  COMMIT;
END;
/
-- 

查詢插入後表大小

SELECT SUM(bytes)/1024 AS kb FROM user_segments WHERE segment_name = 'STUDENT';
-- 

修改用戶預設表空間

ALTER USER student DEFAULT TABLESPACE tbs_data;
-- 

查詢不同表空間

SELECT tablespace_name, SUM(bytes)/1024/1024 AS mb FROM dba_data_files GROUP BY tablespace_name;
-- 

修改密碼 / 刪除表空間

ALTER USER student IDENTIFIED BY newpass;
DROP TABLESPACE tbs_data INCLUDING CONTENTS AND DATAFILES;
-- 
--