-- 数据库管理
-- 创建用户
CREATE USER username IDENTIFIED BY password;
-- 修改用户密码
ALTER USER username IDENTIFIED BY new_password;
-- 授予权限
GRANT CONNECT, RESOURCE TO username;
GRANT CREATE SESSION TO username;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table TO username;
-- 撤销权限
REVOKE SELECT, INSERT ON schema.table FROM username;
-- 查看用户权限
SELECT * FROM dba_sys_privs WHERE grantee = 'USERNAME';
SELECT * FROM dba_role_privs WHERE grantee = 'USERNAME';
-- 删除用户
DROP USER username CASCADE;
-- 表空间管理
-- 创建表空间
CREATE TABLESPACE tablespace_name
DATAFILE '/path/to/datafile.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
-- 修改表空间大小
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 200M;
-- 添加数据文件到表空间
ALTER TABLESPACE tablespace_name
ADD DATAFILE '/path/to/new_datafile.dbf' SIZE 50M;
-- 查看表空间信息
SELECT tablespace_name, status, contents FROM dba_tablespaces;
SELECT file_name, bytes/1024/1024 MB FROM dba_data_files;
-- 删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
-- 表操作
-- 创建表
CREATE TABLE table_name (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
salary NUMBER(10,2),
hire_date DATE DEFAULT SYSDATE,
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 创建表并指定表空间
CREATE TABLE table_name (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
) TABLESPACE tablespace_name;
-- 查看表结构
DESC table_name;
-- 查看表创建语句
SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') FROM dual;
-- 修改表结构
ALTER TABLE table_name ADD (email VARCHAR2(100));
ALTER TABLE table_name MODIFY (name VARCHAR2(100));
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name DROP COLUMN column_name;
-- 重命名表
RENAME old_table_name TO new_table_name;
-- 删除表
DROP TABLE table_name CASCADE CONSTRAINTS;
-- 清空表数据
TRUNCATE TABLE table_name;
-- 索引操作
-- 创建索引
CREATE INDEX idx_name ON table_name(column_name);
CREATE UNIQUE INDEX uq_idx_name ON table_name(column_name);
-- 创建复合索引
CREATE INDEX idx_composite ON table_name(col1, col2);
-- 创建函数索引
CREATE INDEX idx_func ON table_name(UPPER(column_name));
-- 查看索引
SELECT index_name, table_name FROM user_indexes;
SELECT * FROM user_ind_columns WHERE table_name = 'TABLE_NAME';
-- 删除索引
DROP INDEX idx_name;
-- 数据操作
-- 插入数据
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
INSERT INTO table_name SELECT * FROM other_table;
-- 批量插入
INSERT ALL
INTO table_name VALUES (val1, val2)
INTO table_name VALUES (val3, val4)
SELECT * FROM dual;
-- 更新数据
UPDATE table_name SET col1 = val1 WHERE condition;
-- 删除数据
DELETE FROM table_name WHERE condition;
-- 合并数据（存在则更新,不存在则插入）
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (s.id, s.name);
-- 查询数据
SELECT * FROM table_name;
SELECT col1, col2 FROM table_name WHERE condition;
-- 排序
SELECT * FROM table_name ORDER BY col1 ASC, col2 DESC;
-- 分组
SELECT dept_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;
-- 分页查询（Oracle 12c及以上）
SELECT * FROM table_name
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
-- 连接查询
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
-- 子查询
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 联合查询
SELECT col1 FROM table1 UNION SELECT col1 FROM table2;
SELECT col1 FROM table1 UNION ALL SELECT col1 FROM table2;
-- 序列操作
-- 创建序列
CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
-- 使用序列
INSERT INTO table_name (id, name) VALUES (seq_name.NEXTVAL, 'Name');
-- 修改序列
ALTER SEQUENCE seq_name INCREMENT BY 10;
-- 查看序列
SELECT seq_name.CURRVAL FROM dual;
SELECT seq_name.NEXTVAL FROM dual;
-- 删除序列
DROP SEQUENCE seq_name;
-- 视图操作
-- 创建视图
CREATE VIEW view_name AS
SELECT col1, col2 FROM table_name WHERE condition;
-- 创建或替换视图
CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2 FROM table_name WHERE condition;
-- 查看视图
SELECT * FROM user_views WHERE view_name = 'VIEW_NAME';
-- 查看视图定义
SELECT text FROM user_views WHERE view_name = 'VIEW_NAME';
-- 删除视图
DROP VIEW view_name;
-- 存储过程和函数
-- 创建存储过程
CREATE OR REPLACE PROCEDURE proc_name (
param1 IN NUMBER,
param2 OUT VARCHAR2
) AS
BEGIN
-- 过程逻辑
SELECT name INTO param2 FROM employees WHERE id = param1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
param2 := 'Not Found';
END;
/
-- 调用存储过程
DECLARE
v_name VARCHAR2(100);
BEGIN
proc_name(100, v_name);
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/
-- 创建函数
CREATE OR REPLACE FUNCTION func_name (
param1 IN NUMBER
) RETURN NUMBER AS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = param1;
RETURN v_count;
END;
/
-- 调用函数
SELECT func_name(10) FROM dual;
-- 查看存储过程/函数源码
SELECT text FROM user_source WHERE name = 'PROC_NAME' AND type = 'PROCEDURE';
-- 删除存储过程
DROP PROCEDURE proc_name;
-- 删除函数
DROP FUNCTION func_name;
-- 触发器
-- 创建触发器
CREATE OR REPLACE TRIGGER trig_name
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
:NEW.salary := 0;
END IF;
IF INSERTING THEN
:NEW.created_date := SYSDATE;
END IF;
IF UPDATING THEN
:NEW.updated_date := SYSDATE;
END IF;
END;
/
-- 查看触发器
SELECT trigger_name, table_name FROM user_triggers;
-- 删除触发器
DROP TRIGGER trig_name;
-- 事务控制
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 设置保存点
SAVEPOINT savepoint_name;
-- 回滚到保存点
ROLLBACK TO savepoint_name;
-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 实用命令
-- 查看当前用户
SHOW USER;
-- 查看当前会话参数
SHOW PARAMETERS;
-- 查看表空间使用情况
SELECT tablespace_name,
round(SUM(bytes)/1024/1024) total_mb,
round(SUM(bytes)/1024/1024 - SUM(NVL(free_bytes,0))/1024/1024) used_mb,
round(SUM(NVL(free_bytes,0))/1024/1024) free_mb
FROM (
SELECT tablespace_name, bytes, NULL free_bytes
FROM dba_data_files
UNION ALL
SELECT tablespace_name, NULL bytes, bytes free_bytes
FROM dba_free_space
)
GROUP BY tablespace_name;
-- 查看锁信息
SELECT l.session_id, l.oracle_username, l.os_user_name,
o.object_name, o.object_type, l.process, l.locked_mode
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id;
-- 终止会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- 导出查询结果到文件
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF
SPOOL /path/to/output.html
SELECT * FROM employees;
SPOOL OFF
-- 性能优化
-- 执行计划分析
EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 自动跟踪SQL
ALTER SESSION SET SQL_TRACE = TRUE;
-- 执行SQL
ALTER SESSION SET SQL_TRACE = FALSE;
-- 查看SQL执行统计
SELECT * FROM v$sqlarea WHERE sql_text LIKE '%SELECT * FROM employees%';
-- 查看表统计信息
SELECT table_name, num_rows, blocks, avg_row_len
FROM user_tables
WHERE table_name = 'EMPLOYEES';
-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
-- 查看索引统计信息
SELECT index_name, leaf_blocks, distinct_keys
FROM user_indexes
WHERE table_name = 'EMPLOYEES';
-- 重建索引
ALTER INDEX idx_name REBUILD;
-- 备份与恢复
-- 使用数据泵导出(expdp)
-- expdp username/password@dbname schemas=SCHEMA_NAME
dumpfile=export.dmp logfile=export.log
-- 使用数据泵导入(impdp)
-- impdp username/password@dbname schemas=SCHEMA_NAME
dumpfile=export.dmp logfile=import.log
-- 使用RMAN备份(命令行)
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
-- 使用RMAN恢复(命令行)
-- RMAN> RESTORE DATABASE;
-- RMAN> RECOVER DATABASE;