以下是 Oracle 数据库的常用指令，涵盖连接管理、模式对象操作、数据查询与管理、用户权限及备份恢复等方面。

1. 连接与会话管理

sql

-- 连接到 Oracle 数据库
sqlplus username/password@hostname:port/SID

-- 以 SYSDBA 身份连接
sqlplus / AS SYSDBA

-- 查看当前会话信息
SHOW USER;        -- 显示当前用户
SELECT * FROM V$SESSION;  -- 查看会话详情

-- 断开连接
EXIT;

2. 表空间管理

sql

-- 创建表空间
CREATE TABLESPACE tablespace_name
DATAFILE '/path/to/datafile.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

-- 删除表空间
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

-- 查看表空间信息
SELECT * FROM DBA_TABLESPACES;

3. 模式对象操作

sql

-- 创建表
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    hire_date DATE,
    salary NUMBER(10,2)
);

-- 创建序列
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

-- 创建视图
CREATE VIEW emp_view AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 5000;

-- 创建索引
CREATE INDEX idx_emp_name ON employees (emp_name);

-- 查看对象
SELECT * FROM ALL_TABLES WHERE OWNER = 'your_username';

4. 数据操作

sql

-- 插入数据
INSERT INTO employees (emp_id, emp_name, hire_date, salary)
VALUES (emp_seq.NEXTVAL, 'John Doe', SYSDATE, 6000);

-- 查询数据
SELECT * FROM employees;
SELECT emp_name, salary FROM employees WHERE salary > 5000;

-- 更新数据
UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';

-- 删除数据
DELETE FROM employees WHERE emp_id = 1001;

5. 高级查询

sql

-- 排序与分页（ROWNUM）
SELECT * FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;

-- 聚合函数
SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees;

-- 分组查询
SELECT department, AVG(salary) FROM employees GROUP BY department;

-- 连接查询
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

6. 用户与权限管理

sql

-- 创建用户
CREATE USER new_user IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA 100M ON users;

-- 授予权限
GRANT CREATE SESSION, CREATE TABLE TO new_user;
GRANT SELECT, INSERT ON employees TO new_user;

-- 授予角色
GRANT CONNECT, RESOURCE TO new_user;

-- 撤销权限
REVOKE INSERT ON employees FROM new_user;

-- 删除用户
DROP USER new_user CASCADE;

7. 备份与恢复

bash

-- 使用 RMAN 备份（命令行）
rman target /
BACKUP DATABASE;

-- 使用 EXPDP 导出数据（命令行）
expdp username/password directory=DATA_PUMP_DIR dumpfile=expdp.dmp tables=employees;

-- 使用 IMPDP 导入数据（命令行）
impdp username/password directory=DATA_PUMP_DIR dumpfile=expdp.dmp;

8. 系统视图与监控

sql

-- 查看数据库参数
SHOW PARAMETERS;
SELECT * FROM V$PARAMETER;

-- 查看表空间使用情况
SELECT tablespace_name, SUM(bytes)/1024/1024 AS MB_USED
FROM dba_data_files
GROUP BY tablespace_name;

-- 查看会话与锁
SELECT * FROM V$SESSION WHERE username IS NOT NULL;
SELECT * FROM V$LOCK;

9. PL/SQL 基础

plsql

-- 匿名块
DECLARE
    v_salary NUMBER(10,2);
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = 1001;
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

-- 创建存储过程
CREATE OR REPLACE PROCEDURE increase_salary(p_emp_id NUMBER, p_percent NUMBER)
IS
BEGIN
    UPDATE employees SET salary = salary * (1 + p_percent/100)
    WHERE emp_id = p_emp_id;
    COMMIT;
END;
/

10. 其他常用命令

sql

-- 提交/回滚事务
COMMIT;
ROLLBACK;

-- 查看约束
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES';

-- 查看表结构
DESCRIBE employees;