ORACLE 常用恢复操作

╔══════════════════════════════════╗
║ - 数据库启停 · 表空间管理
║ - 用户权限 · 数据泵导入导出
║ - 日常维护命令速查手册
╚══════════════════════════════════╝

登录 SYS 用户

sqlplus sys/aa123789 as sysdba;

一、Linux 启动 Oracle

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

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

启动数据库实例：

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

二、Linux 关闭 Oracle

关闭数据库实例：

SQL> shutdown
SQL> quit

关闭监听：

[oracle@localhost ~]$ lsnrctl stop

三、会话管理

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

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

四、用户管理

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

五、表空间管理

查询表空间使用情况

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;

查询表空间名称及位置

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

查询锁表情况

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;

新建表空间

-- 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;

六、创建用户与授权

创建用户

-- 普通用户
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;

修改密码

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

赋予权限

grant dba, resource to tien;
grant dba, resource to c#tien;
grant dba, resource, unlimited tablespace to c##tien;

七、EXPDP/IMPDP 数据泵

创建逻辑目录

-- 管理员登录
[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;

示例：

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

查看目录是否创建成功

select * from dba_directories;

EXPDP 导出

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

导出场景

# 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 导入

步骤一：创建表空间

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

步骤二：创建用户并授权

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) 导入用户
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;

直接导入示例

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