Oracle 数据库操作命令

常用SQL命令

数据查询命令

命令类型	示例命令	说明
基本查询	`SELECT * FROM employees;`	查询表中所有数据
条件查询	`SELECT * FROM employees WHERE department_id = 10;`	根据条件查询数据
排序查询	`SELECT * FROM employees ORDER BY salary DESC;`	按薪资降序排序
分组查询	`SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;`	按部门分组统计人数
连接查询	`SELECT e.employee_id, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;`	连接两个表查询
子查询	`SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);`	使用子查询过滤数据
分页查询	`SELECT * FROM (SELECT rownum rn, t.* FROM employees t WHERE rownum <= 20) WHERE rn > 10;`	查询第11-20条记录

数据操作命令

命令类型	示例命令	说明
插入数据	`INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) VALUES (1000, 'Smith', 'smith@example.com', SYSDATE, 'IT_PROG');`	插入单条数据
批量插入	`INSERT ALL INTO employees VALUES (1001, 'Jones', 'jones@example.com', SYSDATE, 'IT_PROG') INTO employees VALUES (1002, 'Brown', 'brown@example.com', SYSDATE, 'IT_PROG') SELECT * FROM dual;`	批量插入多条数据
更新数据	`UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;`	更新指定员工薪资
删除数据	`DELETE FROM employees WHERE employee_id = 1000;`	删除指定员工数据
截断表	`TRUNCATE TABLE temporary_table;`	清空表数据，保留表结构
合并数据	`MERGE INTO employees e USING (SELECT 100 employee_id, 'NewName' last_name FROM dual) s ON (e.employee_id = s.employee_id) WHEN MATCHED THEN UPDATE SET e.last_name = s.last_name WHEN NOT MATCHED THEN INSERT (employee_id, last_name) VALUES (s.employee_id, s.last_name);`	根据匹配条件更新或插入数据

数据库管理命令

数据库连接与断开

命令类型	示例命令	说明
连接数据库	`sqlplus sys/password@orcl as sysdba`	以sysdba身份连接到orcl数据库
断开连接	`DISCONNECT;` 或 `EXIT;`	断开当前数据库连接
切换用户	`CONNECT scott/tiger@orcl;`	切换到scott用户
显示当前连接	`SHOW USER;`	显示当前登录用户

数据库状态查看

命令类型	示例命令	说明
查看实例状态	`SELECT instance_name, status FROM v$instance;`	查看数据库实例状态
查看数据库状态	`SELECT name, open_mode, database_role FROM v$database;`	查看数据库名称、打开模式和角色
查看表空间状态	`SELECT tablespace_name, status FROM dba_tablespaces;`	查看所有表空间状态
查看数据文件	`SELECT file_name, tablespace_name, bytes/1024/1024 MB FROM dba_data_files;`	查看所有数据文件信息
查看临时文件	`SELECT file_name, tablespace_name, bytes/1024/1024 MB FROM dba_temp_files;`	查看所有临时文件信息
查看控制文件	`SELECT name FROM v$controlfile;`	查看控制文件路径
查看日志文件	`SELECT member FROM v$logfile;`	查看在线日志文件路径

数据库参数管理

命令类型	示例命令	说明
查看所有参数	`SHOW PARAMETERS;`	查看所有初始化参数
查看指定参数	`SHOW PARAMETER sga_max_size;`	查看SGA最大大小参数
修改参数（静态）	`ALTER SYSTEM SET sga_max_size = 4G SCOPE=SPFILE;`	修改静态参数，需要重启数据库
修改参数（动态）	`ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;`	修改动态参数，立即生效且永久保存
查看SPFILE位置	`SHOW PARAMETER spfile;`	查看服务器参数文件位置
创建SPFILE	`CREATE SPFILE FROM PFILE;`	从文本参数文件创建服务器参数文件
创建PFILE	`CREATE PFILE FROM SPFILE;`	从服务器参数文件创建文本参数文件

用户和权限管理命令

用户管理

命令类型	示例命令	说明
创建用户	`CREATE USER test_user IDENTIFIED BY test_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 100M ON users;`	创建测试用户并分配表空间
修改用户密码	`ALTER USER test_user IDENTIFIED BY new_password;`	修改用户密码
锁定用户	`ALTER USER test_user ACCOUNT LOCK;`	锁定用户账号
解锁用户	`ALTER USER test_user ACCOUNT UNLOCK;`	解锁用户账号
删除用户	`DROP USER test_user CASCADE;`	删除用户及其所有对象
查看用户列表	`SELECT username FROM dba_users;`	查看所有数据库用户
查看当前用户对象	`SELECT object_name, object_type FROM user_objects;`	查看当前用户所有对象

权限管理

命令类型	示例命令	说明
授予系统权限	`GRANT CREATE SESSION, CREATE TABLE TO test_user;`	授予用户创建会话和表的权限
授予对象权限	`GRANT SELECT, INSERT, UPDATE ON hr.employees TO test_user;`	授予用户对hr.employees表的操作权限
授予角色	`GRANT CONNECT, RESOURCE TO test_user;`	授予用户CONNECT和RESOURCE角色
回收系统权限	`REVOKE CREATE TABLE FROM test_user;`	回收用户创建表的权限
回收对象权限	`REVOKE INSERT, UPDATE ON hr.employees FROM test_user;`	回收用户对表的插入和更新权限
查看用户系统权限	`SELECT * FROM dba_sys_privs WHERE grantee = 'TEST_USER';`	查看用户拥有的系统权限
查看用户对象权限	`SELECT * FROM dba_tab_privs WHERE grantee = 'TEST_USER';`	查看用户拥有的对象权限
查看用户角色	`SELECT * FROM dba_role_privs WHERE grantee = 'TEST_USER';`	查看用户拥有的角色

角色管理

命令类型	示例命令	说明
创建角色	`CREATE ROLE test_role;`	创建测试角色
授予角色权限	`GRANT CREATE SESSION, SELECT ANY TABLE TO test_role;`	授予角色权限
授予角色给用户	`GRANT test_role TO test_user;`	将角色授予用户
回收角色权限	`REVOKE SELECT ANY TABLE FROM test_role;`	回收角色的权限
删除角色	`DROP ROLE test_role;`	删除角色
查看角色权限	`SELECT * FROM role_sys_privs WHERE role = 'TEST_ROLE';`	查看角色拥有的系统权限

表和索引操作命令

表操作命令

命令类型	示例命令	说明
创建表	`CREATE TABLE employees (employee_id NUMBER(6) PRIMARY KEY, first_name VARCHAR2(20), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25) NOT NULL UNIQUE, phone_number VARCHAR2(20), hire_date DATE DEFAULT SYSDATE NOT NULL, job_id VARCHAR2(10) NOT NULL, salary NUMBER(8,2), commission_pct NUMBER(2,2), manager_id NUMBER(6), department_id NUMBER(4));`	创建员工表
修改表	`ALTER TABLE employees ADD (middle_name VARCHAR2(20));`	向表中添加列
修改列	`ALTER TABLE employees MODIFY (salary NUMBER(10,2));`	修改列的数据类型
删除列	`ALTER TABLE employees DROP COLUMN middle_name;`	从表中删除列
重命名表	`ALTER TABLE old_table_name RENAME TO new_table_name;`	重命名表
删除表	`DROP TABLE temp_table;`	删除表
查看表结构	`DESCRIBE employees;`	查看表的结构信息
查看表空间中的表	`SELECT table_name FROM dba_tables WHERE tablespace_name = 'USERS';`	查看指定表空间中的所有表

索引操作命令

命令类型	示例命令	说明
创建B树索引	`CREATE INDEX emp_last_name_idx ON employees(last_name);`	创建B树索引
创建唯一索引	`CREATE UNIQUE INDEX emp_email_idx ON employees(email);`	创建唯一索引
创建复合索引	`CREATE INDEX emp_dept_job_idx ON employees(department_id, job_id);`	创建复合索引
创建位图索引	`CREATE BITMAP INDEX emp_gender_idx ON employees(gender);`	创建位图索引（适合低基数列）
创建函数索引	`CREATE INDEX emp_hire_year_idx ON employees(EXTRACT(YEAR FROM hire_date));`	创建函数索引
修改索引	`ALTER INDEX emp_last_name_idx REBUILD;`	重建索引
重命名索引	`ALTER INDEX emp_last_name_idx RENAME TO emp_ln_idx;`	重命名索引
删除索引	`DROP INDEX emp_ln_idx;`	删除索引
查看索引信息	`SELECT index_name, table_name, index_type FROM user_indexes WHERE table_name = 'EMPLOYEES';`	查看表的索引信息
查看索引列	`SELECT index_name, column_name FROM user_ind_columns WHERE table_name = 'EMPLOYEES';`	查看索引包含的列

表空间和存储管理命令

表空间管理

命令类型	示例命令	说明
创建表空间	`CREATE TABLESPACE users DATAFILE 'C:\app\oracle\oradata\ORCL\users01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;`	创建用户表空间
创建临时表空间	`CREATE TEMPORARY TABLESPACE temp TEMPFILE 'C:\app\oracle\oradata\ORCL\temp01.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 200M;`	创建临时表空间
创建UNDO表空间	`CREATE UNDO TABLESPACE undo_data DATAFILE 'C:\app\oracle\oradata\ORCL\undo01.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;`	创建UNDO表空间
修改表空间	`ALTER TABLESPACE users ADD DATAFILE 'C:\app\oracle\oradata\ORCL\users02.dbf' SIZE 50M;`	向表空间添加数据文件
扩展数据文件	`ALTER DATABASE DATAFILE 'C:\app\oracle\oradata\ORCL\users01.dbf' RESIZE 200M;`	扩展数据文件大小
修改表空间状态	`ALTER TABLESPACE users OFFLINE;`	将表空间设置为离线状态
	`ALTER TABLESPACE users ONLINE;`	将表空间设置为在线状态
删除表空间	`DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;`	删除表空间及其数据文件
查看表空间使用情况	SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb, SUM(bytes - free_bytes)/1024/1024 used_mb, SUM(free_bytes)/1024/1024 free_mb, ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) used_pct FROM (SELECT tablespace_name, bytes, CASE WHEN bytes - NVL(free_bytes, 0) > 0 THEN bytes - NVL(free_bytes, 0) ELSE 0 END free_bytes FROM (SELECT tablespace_name, bytes FROM dba_data_files UNION ALL SELECT tablespace_name, bytes FROM dba_temp_files) a LEFT JOIN (SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) b ON a.tablespace_name = b.tablespace_name) GROUP BY tablespace_name;	查看表空间使用情况

数据导入导出命令

数据泵导入导出

命令类型	示例命令	说明
导出全库	`expdp system/password@orcl FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_backup.dmp LOGFILE=full_backup.log;`	导出整个数据库
导出模式	`expdp hr/hr@orcl SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp LOGFILE=hr_schema.log;`	导出指定模式
导出表	`expdp hr/hr@orcl TABLES=employees,departments DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp LOGFILE=hr_tables.log;`	导出指定表
导出表空间	`expdp system/password@orcl TABLESPACES=users,tools DIRECTORY=dpump_dir DUMPFILE=tablespaces.dmp LOGFILE=tablespaces.log;`	导出指定表空间
导入全库	`impdp system/password@orcl FULL=Y DIRECTORY=dpump_dir DUMPFILE=full_backup.dmp LOGFILE=full_restore.log;`	导入整个数据库
导入模式	`impdp hr/hr@orcl SCHEMAS=hr DIRECTORY=dpump_dir DUMPFILE=hr_schema.dmp LOGFILE=hr_restore.log;`	导入指定模式
导入表	`impdp hr/hr@orcl TABLES=employees,departments DIRECTORY=dpump_dir DUMPFILE=hr_tables.dmp LOGFILE=hr_tables_restore.log;`	导入指定表
导入表空间	`impdp system/password@orcl TABLESPACES=users,tools DIRECTORY=dpump_dir DUMPFILE=tablespaces.dmp LOGFILE=tablespaces_restore.log;`	导入指定表空间

传统导入导出

命令类型	示例命令	说明
导出全库	`exp system/password@orcl FULL=Y FILE=full_backup.dmp LOG=full_backup.log;`	使用传统导出工具导出全库
导出用户	`exp hr/hr@orcl OWNER=hr FILE=hr_backup.dmp LOG=hr_backup.log;`	导出指定用户
导出表	`exp hr/hr@orcl TABLES=employees,departments FILE=hr_tables.dmp LOG=hr_tables.log;`	导出指定表
导入全库	`imp system/password@orcl FULL=Y FILE=full_backup.dmp LOG=full_restore.log;`	使用传统导入工具导入全库
导入用户	`imp hr/hr@orcl FROMUSER=hr TOUSER=hr FILE=hr_backup.dmp LOG=hr_restore.log;`	导入指定用户
导入表	`imp hr/hr@orcl TABLES=employees,departments FILE=hr_tables.dmp LOG=hr_tables_restore.log;`	导入指定表

数据库维护命令

统计信息收集

命令类型	示例命令	说明
收集表统计信息	`EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);`	收集表及其索引的统计信息
收集模式统计信息	`EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR', CASCADE => TRUE);`	收集模式下所有对象的统计信息
收集数据库统计信息	`EXEC DBMS_STATS.GATHER_DATABASE_STATS(CASCADE => TRUE);`	收集数据库所有对象的统计信息
收集固定对象统计信息	`EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();`	收集固定对象（如动态性能视图）的统计信息
查看表统计信息	`SELECT table_name, num_rows, last_analyzed FROM dba_tables WHERE table_name = 'EMPLOYEES';`	查看表的统计信息

碎片整理命令

命令类型	示例命令	说明
表碎片整理	`ALTER TABLE employees MOVE;`	移动表以消除碎片
索引重建	`ALTER INDEX emp_last_name_idx REBUILD;`	重建索引以消除碎片
在线重定义表	`EXEC DBMS_REDEFINITION.START_REDEF_TABLE('HR', 'EMPLOYEES', 'EMPLOYEES_TMP');`	开始在线重定义表
	`EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('HR', 'EMPLOYEES', 'EMPLOYEES_TMP');`	完成在线重定义表

数据库验证命令

命令类型	示例命令	说明
检查数据文件	`ALTER DATABASE CHECK DATAFILE 1;`	检查指定数据文件的完整性
检查表空间	`ALTER TABLESPACE users CHECK DATAFILES;`	检查表空间中所有数据文件的完整性
检查对象	`ANALYZE TABLE employees VALIDATE STRUCTURE;`	检查表的结构完整性
	`ANALYZE INDEX emp_last_name_idx VALIDATE STRUCTURE;`	检查索引的结构完整性
检查逻辑块	`DBV FILE='C:\app\oracle\oradata\ORCL\users01.dbf' LOGFILE='dbv_users01.log';`	使用DBV工具检查数据文件的逻辑块完整性
检查物理块	`RMAN> BACKUP VALIDATE DATABASE;`	使用RMAN检查数据库的物理块完整性

事务和锁管理命令

事务控制命令

命令类型	示例命令	说明
提交事务	`COMMIT;`	提交当前事务
回滚事务	`ROLLBACK;`	回滚当前事务
保存点	`SAVEPOINT update_salary;`	创建事务保存点
回滚到保存点	`ROLLBACK TO update_salary;`	回滚到指定保存点
设置事务隔离级别	`SET TRANSACTION ISOLATION LEVEL READ COMMITTED;`	设置事务隔离级别
只读事务	`SET TRANSACTION READ ONLY;`	设置事务为只读模式
查看事务信息	`SELECT * FROM v$transaction;`	查看当前活动事务

锁管理命令

命令类型	示例命令	说明
查看锁信息	`SELECT * FROM v$lock;`	查看当前锁信息
查看等待锁的会话	`SELECT * FROM v$session_wait WHERE event LIKE '%enqueue%';`	查看等待锁的会话
查看锁持有者和等待者	`SELECT l.session_id blocker, s.serial#, l.locked_mode, l.oracle_username, l.os_user_name, s.machine, w.session_id waiter FROM v$locked_object l, v$session s, v$session w WHERE l.session_id = s.sid AND l.blocking_others = 'Blocking' AND w.wait_event = 'enqueue' AND w.row_wait_obj# = l.object_id;`	查看锁的持有者和等待者
杀死会话	`ALTER SYSTEM KILL SESSION '123,456';`	杀死指定会话（session_id, serial#）
强制杀死会话	`ALTER SYSTEM DISCONNECT SESSION '123,456' IMMEDIATE;`	立即断开指定会话

常见问题（FAQ）

Q1: 如何快速查看当前数据库的连接数？

A: 可以使用以下SQL查询查看当前数据库的连接数：

sql

SELECT COUNT(*) FROM v$session;

或者查看活跃连接数：

sql

SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE';

Q2: 如何查看表空间的剩余空间？

A: 可以使用以下SQL查询查看表空间的剩余空间：

sql

SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024, 2) total_mb, 
       ROUND(SUM(bytes - free_bytes)/1024/1024, 2) used_mb, 
       ROUND(SUM(free_bytes)/1024/1024, 2) free_mb, 
       ROUND((SUM(bytes - free_bytes)/SUM(bytes))*100, 2) used_pct 
FROM (
    SELECT tablespace_name, bytes, 
           CASE WHEN bytes - NVL(free_bytes, 0) > 0 THEN bytes - NVL(free_bytes, 0) ELSE 0 END free_bytes 
    FROM (
        SELECT tablespace_name, bytes FROM dba_data_files 
        UNION ALL 
        SELECT tablespace_name, bytes FROM dba_temp_files
    ) a 
    LEFT JOIN (
        SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name
    ) b ON a.tablespace_name = b.tablespace_name
) 
GROUP BY tablespace_name;

Q3: 如何查看长时间运行的SQL语句？

A: 可以使用以下SQL查询查看长时间运行的SQL语句：

sql

SELECT s.sid, s.serial#, s.username, s.machine, s.program, 
       ROUND(elapsed_seconds/60, 2) minutes_running, 
       sql_text 
FROM v$session_longops l, v$sql s 
WHERE l.sql_id = s.sql_id 
AND elapsed_seconds > 60 
ORDER BY elapsed_seconds DESC;

Q4: 如何备份和恢复单个表？

A: 可以使用数据泵工具备份和恢复单个表：

备份单个表：

bash

expdp hr/hr@orcl TABLES=employees DIRECTORY=dpump_dir DUMPFILE=employees.dmp LOGFILE=employees_exp.log;

恢复单个表：

bash

impdp hr/hr@orcl TABLES=employees DIRECTORY=dpump_dir DUMPFILE=employees.dmp LOGFILE=employees_imp.log;

Q5: 如何查看数据库的版本信息？

A: 可以使用以下命令查看数据库的版本信息：

sql

SELECT * FROM v$version;

或者使用简化命令：

sql

SELECT banner FROM v$version WHERE rownum = 1;

最佳实践

使用绑定变量：在编写SQL语句时，尽量使用绑定变量而不是直接拼接字符串，以提高性能和安全性。
定期收集统计信息：定期收集数据库对象的统计信息，确保优化器生成高效的执行计划。
合理使用索引：根据查询模式合理创建和使用索引，避免过度索引导致的性能问题。
监控锁和等待事件：定期监控数据库中的锁和等待事件，及时发现和解决性能瓶颈。
使用事务管理：合理使用事务控制，确保数据的完整性和一致性。
定期备份：制定合理的备份策略，定期备份数据库，确保数据安全。
使用自动化脚本：将常用的数据库操作命令编写为自动化脚本，提高工作效率。
遵循命名规范：为数据库对象（表、索引、视图等）制定并遵循统一的命名规范，提高代码的可读性和可维护性。
定期进行数据库健康检查：定期检查数据库的性能、空间使用情况和安全状况，及时发现和解决问题。
记录重要操作：对重要的数据库操作进行记录，包括操作内容、时间和操作人员，便于追踪和审计。

Oracle 数据库操作命令 ​

常用SQL命令 ​

数据查询命令 ​

数据操作命令 ​

数据库管理命令 ​

数据库连接与断开 ​

数据库状态查看 ​

数据库参数管理 ​

用户和权限管理命令 ​

用户管理 ​

权限管理 ​

角色管理 ​

表和索引操作命令 ​

表操作命令 ​

索引操作命令 ​

表空间和存储管理命令 ​

表空间管理 ​

数据导入导出命令 ​

数据泵导入导出 ​

传统导入导出 ​

数据库维护命令 ​

统计信息收集 ​

碎片整理命令 ​

数据库验证命令 ​

事务和锁管理命令 ​

事务控制命令 ​

锁管理命令 ​

常见问题（FAQ） ​

Q1: 如何快速查看当前数据库的连接数？ ​

Q2: 如何查看表空间的剩余空间？ ​

Q3: 如何查看长时间运行的SQL语句？ ​

Q4: 如何备份和恢复单个表？ ​

Q5: 如何查看数据库的版本信息？ ​

最佳实践 ​

Oracle 数据库操作命令

常用SQL命令

数据查询命令

数据操作命令

数据库管理命令

数据库连接与断开

数据库状态查看

数据库参数管理

用户和权限管理命令

用户管理

权限管理

角色管理

表和索引操作命令

表操作命令

索引操作命令

表空间和存储管理命令

表空间管理

数据导入导出命令

数据泵导入导出

传统导入导出

数据库维护命令

统计信息收集

碎片整理命令

数据库验证命令

事务和锁管理命令

事务控制命令

锁管理命令

常见问题（FAQ）

Q1: 如何快速查看当前数据库的连接数？

Q2: 如何查看表空间的剩余空间？

Q3: 如何查看长时间运行的SQL语句？

Q4: 如何备份和恢复单个表？

Q5: 如何查看数据库的版本信息？

最佳实践