目录
ORACLE 常用恢复操作
  1. 登录 SYS 用户
  2. 一、Linux 启动 Oracle
  3. 二、Linux 关闭 Oracle
  4. 三、会话管理
  5. 四、用户管理
  6. 五、表空间管理
    1. 查询表空间使用情况
    2. 查询表空间名称及位置
    3. 查询锁表情况
    4. 新建表空间
  7. 六、创建用户与授权
    1. 创建用户
    2. 修改密码
    3. 赋予权限
  8. 七、EXPDP/IMPDP 数据泵
    1. 创建逻辑目录
    2. 查看目录是否创建成功
    3. EXPDP 导出
    4. IMPDP 导入

ORACLE 常用恢复操作

tan
  2026-06-26 13:49 2026-06-26 13:49 创建   2026-09-20 19:52:23 2026-09-20 19:52:23 更新    
  • 数据库启停 · 表空间管理
  • 用户权限 · 数据泵导入导出
  • 日常维护命令速查手册

登录 SYS 用户

1
sqlplus sys/aa123789 as sysdba;

一、Linux 启动 Oracle

启动监听，查看监听状态：

1
2
[oracle@localhost ~]$ lsnrctl start
[oracle@localhost ~]$ lsnrctl status

启动数据库实例：

1
2
3
[oracle@localhost ~]$ sqlplus /nolog
SQL> conn /as sysdba
SQL> startup

二、Linux 关闭 Oracle

关闭数据库实例：

1
2
SQL> shutdown
SQL> quit

关闭监听：

1
[oracle@localhost ~]$ lsnrctl stop

三、会话管理

1
2
3
4
5
-- 查询活跃会话
select * from v$session where username = 'NXECR' and status = 'ACTIVE';

-- 杀掉会话
alter system kill session '1150, 9';

四、用户管理

1
2
-- 删除用户（级联）
drop user c##tien cascade;

五、表空间管理

查询表空间使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
  df.tablespace_name TABLESPACE,
  df.total_space TOTAL_SPACE,
  fs.free_space FREE_SPACE,
  df.total_space_mb TOTAL_SPACE_MB,
  (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
  fs.free_space_mb FREE_SPACE_MB,
  ROUND(((df.total_space - fs.free_space) / df.total_space) * 100, 2) PCT_USED
FROM
  (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
          ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
   FROM dba_data_files
   GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
          ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
   FROM dba_free_space
   GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY ((df.total_space - fs.free_space) / df.total_space) desc;

查询表空间名称及位置

1
2
select file_name, tablespace_name, bytes/1024/1024, maxbytes/1024/1024, autoextensible
from dba_data_files;

查询锁表情况

1
2
3
4
5
6
7
8
9
10
select
  b.owner TABLEOWNER,
  b.object_name TABLENAME,
  c.OSUSER LOCKBY,
  c.USERNAME LOGINID,
  c.sid SID,
  c.SERIAL# SERIAL
from v$locked_object a, dba_objects b, v$session c
where b.object_id = a.object_id
  and a.SESSION_ID = c.sid;

新建表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Windows
create tablespace TIEN
  datafile 'C:\APP\MICROENGINEER\ORADATA\ORCL\TIAN01.DBF'
  size 5M autoextend on;

-- Linux 路径示例
create tablespace TIEN
  datafile '/orcl/app/oracle/oradata/orcl/tien01.dbf'
  size 5M autoextend on;

create tablespace TIEN
  datafile '/usr/local/oracle/oradata/orcl/tien01.dbf'
  size 5M autoextend on;

create tablespace TIEN
  datafile '/data/tien/tien02.dbf'
  size 5M autoextend on;

create tablespace TAN
  datafile '/usr/local/oracle/oradata/orcl/tan01.dbf'
  size 5M autoextend on;

create tablespace TAN
  datafile '/orcl/app/oracle/oradata/ORCL/datafile/tan01.dbf'
  size 5M autoextend on;

六、创建用户与授权

创建用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 普通用户
create user tien identified by tien
  default tablespace TIEN;

-- CDB 模式用户
create user c#tien identified by xxxxxx
  default tablespace TIEN;

-- 带临时表空间的用户
create user ALS_CREDIT
  default tablespace ALS
  temporary tablespace ALS_TEMP
  profile DEFAULT
  identified by alscredit;

修改密码

1
2
alter user c##tien identified by c#tienxxxx;
alter user c##tieninfo identified by c#tieninfoxxxx;

赋予权限

1
2
3
grant dba, resource to tien;
grant dba, resource to c#tien;
grant dba, resource, unlimited tablespace to c##tien;

七、EXPDP/IMPDP 数据泵

创建逻辑目录

1
2
3
4
5
6
7
8
9
10
-- 管理员登录
[oracle@localhost ~]$ sqlplus /nolog
SQL> conn /as sysdba

-- 创建目录
create directory mydata as '/orcl/app/oracle/mydata';

-- 授权
grant read, write on directory mydata to c##tien;
grant dba, resource, unlimited tablespace to c##tien;

示例：

1
2
3
create directory tiendata as '/data/tienbak';
grant read, write on directory tiendata to c##tien;
grant dba, resource, unlimited tablespace to c##tien;

查看目录是否创建成功

1
select * from dba_directories;

EXPDP 导出

1
expdp c##tien/c#tien0918@tien schemas=c##tien dumpfile=2021tien.dmp directory=tiendata logfile=2021tien.log;

导出场景

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 1) 导出用户及其对象
expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

# 2) 导出指定表
expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir logfile=expdp.log;

# 3) 按查询条件导出
expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=emp query='where deptno=20' logfile=expdp.log;

# 4) 按表空间导出
expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=temp,example logfile=expdp.log;

# 5) 导出整个数据库
expdp scott/123@127.0.0.1/orcl directory=dump_dir dumpfile=ly.dmp full=y logfile=expdp.log;

# 导出指定用户
expdp JCPT/123@127.0.0.1/orcl directory=mydata schemas=jcpt dumpfile=ly.dmp logfile=expdp.log;

IMPDP 导入

步骤一：创建表空间

1
create tablespace data_test datafile 'e:\oracle\oradata\test\test.dbf' size 2000M;

步骤二：创建用户并授权

1
2
3
4
5
6
7
create user study identified by study default tablespace data_test;

-- 给用户逻辑目录读写权限
grant read, write on directory mydata to study;

-- 给用户表空间权限
grant dba, resource, unlimited tablespace to study;

步骤三：执行导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 1) 导入用户
impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott logfile=impdp.log;

# 2) 导入表（含 remap）
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp
  tables=scott.dept,scott.emp remap_schema=scott:system
  logfile=impdp.log table_exists_action=replace;

# 3) 导入表空间
impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example logfile=impdp.log;

# 4) 导入整个数据库
impdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y logfile=impdp.log;

# 5) 用户 remap 导入（常用）
impdp lyxt/lyxt123@127.0.0.1/orcl directory=mydata dumpfile=LY.DMP
  remap_schema=jcpt:lyxt logfile=ims20171122.log table_exists_action=replace;

# 6) 追加数据
impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=system table_exists_action=append logfile=impdp.log;

直接导入示例

1
impdp c##tien/tien@orcl directory=mydata dumpfile=tien20210207.dmp schemas=c##tien logfile=impdp.log;