Oracle 表結構查看與常用管理命令大全：DESC、建表空間、用戶、角色、權限、分區交換

從查看表結構開始，延伸至表空間、用戶、角色、權限、建表、主外鍵、字串函數、PL/SQL 迴圈、SELECT INTO 等完整管理命令集，適合 DBA 與開發快速參考。

查看表結構

DESC table_name;  -- SQL*Plus 內建
-- 或查詢字典
SELECT column_name, data_type, data_length, data_precision, data_scale, nullable
FROM user_tab_columns WHERE table_name = 'TABLE_NAME';

修改表結構

ALTER TABLE test MODIFY (addd VARCHAR2(10) NULL);
ALTER TABLE test ADD (age VARCHAR2(5));

登入與切換用戶

SQLPLUS /NOLOG
CONN sys/password AS SYSDBA;
CONN username/password;

建立表空間

CREATE TABLESPACE ts_name
  DATAFILE 'C:\ts_name.dbf' SIZE 15M
  AUTOEXTEND ON NEXT 10M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

建立用戶

CREATE USER shi IDENTIFIED BY scj
  DEFAULT TABLESPACE ts_name
  TEMPORARY TABLESPACE temp
  PROFILE DEFAULT
  QUOTA UNLIMITED ON ts_name;

建立角色與授權

CREATE ROLE role_name IDENTIFIED BY pwd;
GRANT CREATE SESSION TO role_name;
GRANT role_name TO username;

-- 直接授權用戶
GRANT CONNECT, RESOURCE TO shi;
GRANT DBA TO shi;              -- 謹慎使用
GRANT CREATE TABLE TO shi;

查詢當前用戶物件

SELECT table_name FROM user_tables;
SELECT tablespace_name FROM user_tablespaces;
SELECT username FROM dba_users;  -- 需 SYS 登入

建表含表空間與儲存參數

CREATE TABLE t (
  id INT NOT NULL,
  name VARCHAR2(20) NOT NULL
) TABLESPACE ts_name
STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED);

主鍵與索引

ALTER TABLE users ADD CONSTRAINT pk PRIMARY KEY (id);

外鍵 (含級聯刪除)

ALTER TABLE users ADD CONSTRAINT fk_roleid
  FOREIGN KEY (roleid) REFERENCES role(role_id) ON DELETE CASCADE;

字串操作

-- 連接
SELECT CONCAT(name, id) FROM table_name;

-- 移除特定字串 (Oracle 無 REPLACE 於 SELECT 中直接用)
SELECT REPLACE(name, '李', '') FROM table_name;  -- 正確寫法

PL/SQL 基礎環境

SET SERVEROUTPUT ON;  -- 必須，否則 DBMS_OUTPUT 無輸出

WHILE 迴圈範例

DECLARE
  ccc NUMBER := 1;
  a   NUMBER := 0;
BEGIN
  WHILE ccc <= 100 LOOP
    IF MOD(ccc, 3) = 0 THEN
      DBMS_OUTPUT.PUT_LINE(ccc || ',');
      a := a + ccc;
    END IF;
    ccc := ccc + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Sum: ' || a);
END;
/

SELECT INTO (單行結果集)

DECLARE
  v_name VARCHAR2(30);
BEGIN
  SELECT username INTO v_name FROM users WHERE id = 2;
  DBMS_OUTPUT.PUT_LINE('姓名：' || v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('查無資料');
  WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('資料超過一筆');
END;
/

分區交換修改 Table Owner (進階)

-- 目標用戶建分區表
CREATE TABLE temp (id NUMBER, name VARCHAR2(20))
  PARTITION BY RANGE (id)
  (PARTITION p0 VALUES LESS THAN (-1), PARTITION p1 VALUES LESS THAN (MAXVALUE));

-- 交換分區瞬間移動表所有權
ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE source_user.t1
  INCLUDING INDEXES WITHOUT VALIDATION;

這份命令集從基礎查看到進階分區交換，涵蓋 Oracle 日常運維 95% 場景，建議存檔備查。