-- 完全複製結構+數據+約束(不含 FK/Index/Trigger)
CREATE TABLE book_1 AS SELECT * FROM book;
-- 指定欄位別名複製
CREATE TABLE book_2 (title, price) AS SELECT book_name, price FROM book;
-- 僅複製結構 (WHERE 1=2)
CREATE TABLE book_3 (title, price) AS SELECT book_name, price FROM book WHERE 1=2;CREATE TABLE book (
book_id NUMBER(10) PRIMARY KEY,
book_name VARCHAR2(30) DEFAULT '未知',
pub_id VARCHAR2(2) DEFAULT NULL,
borrow_cnt NUMBER(3) DEFAULT 0,
create_time DATE DEFAULT SYSDATE
);| 操作 | 語法範例 |
|---|---|
| 新增欄位 | ALTER TABLE book ADD (author VARCHAR2(20) DEFAULT '佚名'); |
| 修改欄位類型/長度/預設值 | ALTER TABLE book MODIFY (price NUMBER(10,2) DEFAULT 0); |
| 重命名欄位 | ALTER TABLE book RENAME COLUMN pub_id TO publisher_id; |
| 刪除欄位 | ALTER TABLE book DROP COLUMN author; (10g+) |
| 設定未使用 (標記後異步物理刪除) | ALTER TABLE book SET UNUSED (author); ALTER TABLE book DROP UNUSED COLUMNS; |
| 新增約束 | ALTER TABLE book ADD CONSTRAINT pk_book PRIMARY KEY (book_id); |
| 啟用/停用約束 | ALTER TABLE book ENABLE/DISABLE CONSTRAINT pk_book; |
| 移動表空間 | ALTER TABLE book MOVE TABLESPACE users; |
| 縮減高水位線 (HWM) | ALTER TABLE book ENABLE ROW MOVEMENT; ALTER TABLE book SHRINK SPACE; |
DROP TABLE book_1; -- 刪除結構+數據+索引+約束 (進入回收站 PURGE 可跳過)
TRUNCATE TABLE book_2; -- 保留結構，清空數據，重置 HWM，釋放空間 (DDL, 不可回滾)
DELETE FROM book_3; -- 保留結構+空間，可回滾，觸發 TriggerRENAME book_old TO book_new; -- 僅限表擁有者，不可跨 Schema
-- 或
ALTER TABLE book_old RENAME TO book_new; -- 10g+ 同義-- 核心字典視圖
SELECT table_name, tablespace_name, num_rows, blocks, last_analyzed
FROM user_tables
ORDER BY table_name;
-- 通用對象視圖 (含 View/Index/Sequence 等)
SELECT object_name, object_type, created, last_ddl_time
FROM user_objects
WHERE object_type = 'TABLE'
ORDER BY object_name;CREATE TABLE t ("Sales_ID#" NUMBER); SELECT "Sales_ID#" FROM t;CREATE TABLE ext (...) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ... LOCATION (...));CREATE TABLE sales (...) PARTITION BY RANGE (sale_date) INTERVAL (NUMTODSINTERVAL(1,'DAY')) (PARTITION p0 VALUES LESS THAN (DATE '2024-01-01'));CREATE GLOBAL TEMPORARY TABLE temp_session (id NUMBER) ON COMMIT PRESERVE ROWS;DROP TABLE 進入回收站佔用空間，生產環境大表建議 DROP TABLE big_table PURGE; 或定期 PURGE RECYCLEBIN;。TRUNCATE 會觸發隱式提交，且若有外鍵引用需先 DISABLE。ALTER TABLE ... MODIFY 擴大 VARCHAR2 長度瞬時完成，縮小需確保現有數據符合新長度。ANY 系統權限 (如 DROP ANY TABLE)，遵循最小權限原則。熟練掌握表對象全生命週期操作，配合字典視圖查詢，是 DBA 與開發人員日常高效作業的基本功。
