您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle DBA 常用命令大全：日誌、表空間、表、索引、備份恢復全覆蓋

編輯：Oracle 資料庫相關知識

第 1 章：重做日誌管理

-- 強制日誌切換
ALTER SYSTEM SWITCH LOGFILE;

-- 強制檢查點
ALTER SYSTEM CHECKPOINT;

-- 新增日誌組
ALTER DATABASE ADD LOGFILE GROUP 4 ('/disk3/log4a.rdo','/disk4/log4b.rdo') SIZE 1M;

-- 新增日誌成員
ALTER DATABASE ADD LOGFILE MEMBER '/disk3/log1b.rdo' TO GROUP 1,
                                   '/disk4/log2b.rdo' TO GROUP 2;

-- 重命名日誌文件
ALTER DATABASE RENAME FILE 'c:/oracle/oradata/oradb/redo01.log'
TO 'c:/oracle/oradata/redo01.log';

-- 刪除日誌組/成員
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER 'c:/oracle/oradata/redo01.log';

-- 清除日誌文件
ALTER DATABASE CLEAR [UNARCHIVED] LOGFILE 'c:/oracle/log2a.rdo';

-- LogMiner 分析日誌
EXEC DBMS_LOGMNR_D.BUILD('oradb.ora','c:\oracle\oradb\log');
EXEC DBMS_LOGMNR.ADD_LOGFILE('c:\oracle\oradata\oradb\redo01.log', DBMS_LOGMNR.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE('c:\oracle\oradata\oradb\redo02.log', DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.START_LOGMNR(DICTFILENAME=>'c:\oracle\oradb\log\oradb.ora');
SELECT * FROM V$LOGMNR_CONTENTS;
EXEC DBMS_LOGMNR.END_LOGMNR;

第 2 章：表空間管理

-- 創建表空間（字典管理）
CREATE TABLESPACE tbs_name DATAFILE 'c:\oracle\oradata\file1.dbf' SIZE 100M,
'c:\oracle\oradata\file2.dbf' SIZE 100M
MINIMUM EXTENT 550K LOGGING
DEFAULT STORAGE (INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 0)
ONLINE PERMANENT;

-- 本地管理表空間（推薦）
CREATE TABLESPACE user_data DATAFILE 'c:\oracle\oradata\user_data01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 臨時表空間
CREATE TEMPORARY TABLESPACE temp TEMPFILE 'c:\oracle\oradata\temp01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 修改存儲參數
ALTER TABLESPACE app_data MINIMUM EXTENT 2M;
ALTER TABLESPACE app_data DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

-- 離線/上線
ALTER TABLESPACE app_data OFFLINE;
ALTER TABLESPACE app_data ONLINE;

-- 唯讀/讀寫
ALTER TABLESPACE app_data READ ONLY;

-- 刪除表空間
DROP TABLESPACE app_data INCLUDING CONTENTS;

-- 數據文件自動擴展
ALTER TABLESPACE app_data ADD DATAFILE 'c:\oracle\oradata\app_data01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 手動調整數據文件大小
ALTER DATABASE DATAFILE 'c:\oracle\oradata\app_data.dbf' RESIZE 200M;

-- 移動數據文件（表空間離線法）
ALTER TABLESPACE app_data RENAME DATAFILE 'c:\oracle\oradata\app_data.dbf'
TO 'c:\oracle\app_data.dbf';

-- 移動數據文件（數據庫級）
ALTER DATABASE RENAME FILE 'c:\oracle\oradata\app_data.dbf'
TO 'c:\oracle\app_data.dbf';

第 3 章：表操作

-- 創建表
CREATE TABLE table_name (col1 datatype, col2 datatype...)
TABLESPACE tbs_name PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) LOGGING;

-- 從子查詢創建表（CTAS）
CREATE TABLE new_table [LOGGING|NOLOGGING] AS SELECT * FROM old_table;

-- 全局臨時表
CREATE GLOBAL TEMPORARY TABLE temp_tbl AS SELECT * FROM src_tbl
ON COMMIT PRESERVE ROWS;  -- 或 DELETE ROWS

-- 修改存儲與塊參數
ALTER TABLE table_name PCTFREE 30 PCTUSED 50 STORAGE (NEXT 500K MINEXTENTS 2 MAXEXTENTS 100);

-- 手動分配擴展
ALTER TABLE table_name ALLOCATE EXTENT (SIZE 500K DATAFILE 'c:/oracle/data.dbf');

-- 移動表到新表空間（重建索引）
ALTER TABLE employee MOVE TABLESPACE users;

-- 釋放未用空間
ALTER TABLE table_name DEALLOCATE UNUSED [KEEP 100K];

-- 截斷表
TRUNCATE TABLE table_name;

-- 刪除表
DROP TABLE table_name [CASCADE CONSTRAINTS];

-- 刪除列
ALTER TABLE table_name DROP COLUMN col_name CASCADE CONSTRAINTS CHECKPOINT 1000;
ALTER TABLE table_name DROP COLUMNS CONTINUE;

-- 標記列為未用（延遲物理刪除）
ALTER TABLE table_name SET UNUSED COLUMN col_name CASCADE CONSTRAINTS;
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 1000;

第 4 章：索引管理

-- 函數索引
CREATE INDEX idx_item_qty ON item (quantity - quantity_shipped);

-- B-Tree 索引
CREATE [UNIQUE] INDEX idx_name ON table_name (col1 ASC, col2 DESC)
TABLESPACE tbs_name PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) LOGGING;

-- 反向鍵索引（緩解右側擴展熱點）
CREATE UNIQUE INDEX idx_rev ON table_name (col) REVERSE
PCTFREE 30 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;

-- 位圖索引（低基數列）
CREATE BITMAP INDEX idx_bmp ON table_name (col)
PCTFREE 30 STORAGE (INITIAL 200K NEXT 200K PCTINCREASE 0 MAXEXTENTS 50) TABLESPACE indx;

-- 修改索引存儲
ALTER INDEX idx_name STORAGE (NEXT 400K MAXEXTENTS 100);

-- 分配/釋放索引空間
ALTER INDEX idx_name ALLOCATE EXTENT (SIZE 200K DATAFILE 'c:/oracle/index.dbf');
ALTER INDEX idx_name DEALLOCATE UNUSED;

第 5 章：約束管理

-- 會話級約束延遲/立即
ALTER SESSION SET CONSTRAINTS = DEFERRED;
SET CONSTRAINTS constraint_name ALL IMMEDIATE;

-- 刪除表/表空間級聯約束
DROP TABLE table_name CASCADE CONSTRAINTS;
DROP TABLESPACE tbs_name INCLUDING CONTENTS CASCADE CONSTRAINTS;

-- 創建表時定義約束
CREATE TABLE xay (id NUMBER(7) CONSTRAINT pk_xay PRIMARY KEY DEFERRABLE
USING INDEX STORAGE (INITIAL 100K NEXT 100K) TABLESPACE indx);

-- 啟用約束（不驗證現有數據/驗證）
ALTER TABLE xay ENABLE NOVALIDATE CONSTRAINT pk_xay;
ALTER TABLE xay ENABLE VALIDATE CONSTRAINT pk_xay;

第 6 章：數據載入

-- 直接路徑插入
INSERT /*+ APPEND */ INTO emp NOLOGGING SELECT * FROM emp_old;

-- 並行直接路徑插入
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(emp,2) */ INTO emp NOLOGGING SELECT * FROM emp_old;

-- SQL*Loader
sqlldr scott/tiger CONTROL=ulcase6.ctl LOG=ulcase6.log DIRECT=TRUE

第 7 章：數據重組

-- Export/Import 重組
exp scott/tiger TABLES=(dept,emp) FILE=c:\emp.dmp LOG=exp.log COMPRESS=N DIRECT=Y
imp scott/tiger TABLES=(dept,emp) FILE=emp.dmp LOG=imp.log IGNORE=Y

-- 可傳輸表空間（TTS）
ALTER TABLESPACE sales_ts READ ONLY;
exp sys/pwd FILE=xay.dmp TRANSPORT_TABLESPACE=Y TABLESPACES=sales_ts TRIGGERS=N CONSTRAINTS=N
-- 複製數據文件到目標機器
imp sys/pwd FILE=xay.dmp TRANSPORT_TABLESPACE=Y DATAFILES=('/disk1/sales01.dbf','/disk2/sales02.dbf')
ALTER TABLESPACE sales_ts READ WRITE;

-- 檢查 TTS 自包含性
EXEC DBMS_TTS.TRANSPORT_SET_CHECK('sales_ts', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

第 8 章：密碼安全與資源限制

-- 解鎖/修改密碼
ALTER USER juncky IDENTIFIED BY oracle ACCOUNT UNLOCK;

-- 創建密碼驗證函數
CREATE FUNCTION verify_fn (userid VARCHAR2, password VARCHAR2, old_password VARCHAR2) RETURN BOOLEAN;

-- 創建配置文件：密碼策略
CREATE PROFILE grace_5 LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_LIFE_TIME 30
PASSWORD_REUSE_TIME 30
PASSWORD_VERIFY_FUNCTION verify_fn
PASSWORD_GRACE_TIME 5;

-- 修改配置文件
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 10;

-- 資源限制配置文件
CREATE PROFILE dev_prof LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
IDLE_TIME 60
CONNECT_TIME 480;

-- 啟用資源限制
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

第 9 章：用戶管理

-- 創建用戶
CREATE USER juncky IDENTIFIED BY oracle
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp
QUOTA 10M ON data PASSWORD EXPIRE ACCOUNT UNLOCK PROFILE DEFAULT;

-- 修改表空間配額
ALTER USER juncky QUOTA 0 ON users;

-- 刪除用戶
DROP USER juncky [CASCADE];

-- 監控視圖
DBA_USERS, DBA_TS_QUOTAS

第 10 章：權限管理

-- 系統權限授予/回收
GRANT CREATE SESSION, CREATE TABLE TO managers;
GRANT CREATE SESSION TO scott WITH ADMIN OPTION;
REVOKE CREATE TABLE FROM karen;

-- SYSDBA/SYSOPER 權限
-- SYSOPER: STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT/BACKUP, RECOVER
-- SYSDBA: 含 SYSOPER + CREATE DATABASE, 完整權限

-- 對象權限授予/回收
GRANT EXECUTE ON DBMS_PIPE TO PUBLIC;
GRANT UPDATE (first_name, salary) ON employee TO karen WITH GRANT OPTION;
REVOKE EXECUTE ON DBMS_PIPE FROM scott [CASCADE CONSTRAINTS];

-- 審計
AUDIT DELETE ON SYS.AUD$ BY ACCESS;
AUDIT USER;
AUDIT SELECT ANY TABLE BY summit BY ACCESS;
AUDIT LOCK ON summit.employee BY ACCESS WHENEVER SUCCESSFUL;

第 11 章：角色管理

-- 創建/修改角色
CREATE ROLE sales_clerk;
CREATE ROLE hr_clerk IDENTIFIED BY bonus;
CREATE ROLE hr_manager IDENTIFIED EXTERNALLY;
ALTER ROLE sales_clerk IDENTIFIED BY commission;
ALTER ROLE hr_manager NOT IDENTIFIED;

-- 授予/回收角色
GRANT sales_clerk TO scott;
GRANT hr_manager TO scott WITH ADMIN OPTION;
REVOKE sales_clerk FROM scott;

-- 默認角色
ALTER USER scott DEFAULT ROLE hr_clerk, sales_clerk;
ALTER USER scott DEFAULT ROLE ALL EXCEPT hr_clerk;

-- 啟用/禁用角色
SET ROLE hr_clerk;
SET ROLE ALL EXCEPT sales_clerk;
SET ROLE NONE;

-- 刪除角色
DROP ROLE hr_manager;

-- 角色信息視圖
DBA_ROLES, DBA_ROLE_PRIVS, ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, SESSION_ROLES

第 12 章：備份與恢復核心命令

-- 關鍵動態視圖
V$SGA, V$INSTANCE, V$PROCESS, V$BGPROCESS, V$DATABASE, V$DATAFILE, V$SGASTAT

-- RMAN 前置參數
DBWR_IO_SLAVES, BACKUP_TAPE_IO_SLAVES, LARGE_POOL_SIZE

-- 並行回滾監控
V$FAST_START_SERVERS, V$FAST_START_TRANSACTIONS

-- 冷備份（無歸檔模式）
SHUTDOWN IMMEDIATE; CP files /backup/; STARTUP;

-- 異地恢復（重命名數據文件）
STARTUP MOUNT;
ALTER DATABASE RENAME FILE '/disk1/../user.dbf' TO '/disk2/../user.dbf';
ALTER DATABASE OPEN;

-- RECOVER 語法
RECOVER DATABASE;
RECOVER DATAFILE '/disk1/data/df2.dbf';
RECOVER TABLESPACE user_data;
RECOVER DATAFILE 2;
ALTER DATABASE RECOVER DATAFILE 2;

-- 自動應用歸檔日誌
SET AUTORECOVERY ON;
RECOVER AUTOMATIC DATAFILE 4;

-- 完全恢復四種方法（按場景選擇）
-- 方法1：掛載狀態恢復單個文件
-- 方法2：打開狀態離線表空間恢復
-- 方法3：掛載狀態離線文件恢復並重命名
-- 方法4：無備份有歸檔日誌重建文件恢復

-- 熱備份（歸檔模式）
ALTER TABLESPACE user_data BEGIN BACKUP;
COPY files /backup/;
ALTER DATABASE DATAFILE '...' END BACKUP;
ALTER SYSTEM SWITCH LOGFILE;

-- 控制文件備份
ALTER DATABASE BACKUP CONTROLFILE TO 'control1.bkp';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- 無歸檔模式恢復
SHUTDOWN ABORT; CP files; STARTUP;

-- 結束備份模式恢復
ALTER DATABASE DATAFILE 2 END BACKUP;

-- 清除重做日誌
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1 UNRECOVERABLE DATAFILE;

-- 重做日誌組恢復
ALTER DATABASE ADD LOGFILE GROUP 3 'c:\oradata\redo03.log' SIZE 1000K;
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE OPEN;