Oracle同一SID复刻完整业务用户（Schema）实操笔记（含表/数据/存储过程/函数全量复制）

一、前言 & 业务场景

本次实操针对Oracle 11g 单实例 生产环境，核心需求：

现有数据库实例：SID = ora11
源业务用户（Schema）：test
源用户表空间：永久 USERS、临时 TEMP
目标需求：新建用户 test_123，完全复用原表空间，全量复刻 test 所有表结构、业务数据、存储过程、函数、触发器、序列，实现两套完全隔离的业务环境

核心特性：同一实例、复用物理表空间、逻辑对象完全隔离、零新建数据文件，适合测试、灰度、并行业务环境快速搭建。

二、核心概念扫盲（必懂，避坑关键）

1. Oracle 与 MySQL 核心对应关系

MySQL create database ≈ Oracle 创建用户（Schema）
MySQL 多库共用进程 ≈ Oracle 多Schema 共用同一个 SID 实例
Schema = 用户名：一个用户对应一套独立的业务对象（表、过程、函数）

2. 关键账号区分

sysdba：数据库最高权限，本机 sqlplus / as sysdba 免密登录，用于建用户、赋权、系统运维
system：数据库内置管理员账号，专门用于数据泵 expdp/impdp 导入导出（普通业务用户权限不足）
test/test_123：普通业务Schema用户，仅管理自身业务对象

3. 复用表空间说明

本次操作不新建表空间，新用户test_123 直接复用原 USERS（永久）+TEMP（临时）表空间：

优点：操作简单、无需新建dbf文件、快速落地
缺点：物理磁盘资源共用，仅适合测试/过渡环境，生产长期并行建议独立表空间
隔离性：物理共用、逻辑完全独立，两套表、存储过程、数据互不干扰

三、完整实操步骤（可直接照搬执行）

执行环境区分：服务器Oracle系统用户（终端）、sysdba管理员（SQL命令行）

步骤1：登录管理员账号，核对源用户环境

服务器终端执行，登录最高权限管理员：

sqlplus / as sysdba

执行SQL，确认原test用户表空间信息：

-- 核对源用户表空间配置
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE 
FROM DBA_USERS 
WHERE USERNAME='TEST';

-- 确认公共表空间正常可用
SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('USERS','TEMP');

步骤2：创建数据泵目录（已存在可跳过）

sysdba状态下执行，创建导出导入文件存放目录：

-- 创建数据泵逻辑目录
CREATE DIRECTORY dump_dir AS '/u01/app/oracle/dump';
-- 授权system账号读写权限
GRANT READ,WRITE ON DIRECTORY dump_dir TO SYSTEM;
-- 校验目录创建成功
SELECT DIRECTORY_NAME,DIRECTORY_PATH FROM DBA_DIRECTORIES;

退出sqlplus，服务器终端创建物理目录并授权：

exit
mkdir -p /u01/app/oracle/dump
chown oracle:oinstall /u01/app/oracle/dump
chmod 775 /u01/app/oracle/dump

步骤3：新建业务用户 test_123（复用原表空间）

重新登录sysdba，执行创建用户、赋权语句：

sqlplus / as sysdba

-- 新建用户，完全复用test的表空间
CREATE USER test_123 IDENTIFIED BY Hydx@123456
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;

-- 分配标准业务权限
GRANT CONNECT,RESOURCE TO test_123;
-- 授予完整权限（支持存储过程执行、任务创建，测试/生产通用）
GRANT DBA TO test_123;

-- 校验新用户配置是否正确
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE 
FROM DBA_USERS 
WHERE USERNAME='TEST_123';

校验标准：DEFAULT_TABLESPACE=USERS、TEMPORARY_TABLESPACE=TEMP，与源用户一致。

步骤4：全量导出源用户 test 所有数据

服务器Oracle终端执行，替换为你自己的system密码：

expdp system/你的system密码 \
schemas=test \
directory=dump_dir \
dumpfile=test_full.dmp \
logfile=exp_test.log \
reuse_dumpfiles=y

执行说明：

导出内容：表结构、数据、存储过程、函数、触发器、序列、索引全量对象
reuse_dumpfiles=y：重复执行自动覆盖旧文件
执行完毕查看 exp_test.log，确保无报错

步骤5：导入全量数据到新用户 test_123

核心语句，实现Schema一键迁移，自动适配新用户、复用原表空间：

impdp system/你的system密码 \
directory=dump_dir \
dumpfile=test_full.dmp \
logfile=imp_test_123.log \
remap_schema=test:test_123

核心参数解释：remap_schema=test:test_123 将原test所有对象，批量映射迁移到test_123用户下。

步骤6：完整性校验（必做）

1. 登录新用户自查

sqlplus test_123/Hydx@123456

-- 查看所有业务表
SELECT TABLE_NAME FROM USER_TABLES;

-- 核对业务数据（与源表对比行数）
SELECT COUNT(*) FROM TB_USER;

-- 查看所有复刻的存储过程、函数、触发器
SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS 
WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE','TRIGGER','SEQUENCE');

2. 管理员对比新旧用户数据一致性

sqlplus / as sysdba

-- 对比源用户数据
SELECT COUNT(*) FROM test.tb_user;
-- 对比新用户数据
SELECT COUNT(*) FROM test_123.tb_user;

四、项目连接配置

数据库连接串完全不变，仅修改登录用户名密码：

连接地址：192.168.0.1:ora11
原业务账号：test
新业务账号：test_123 / 密码：Hydx@123456

五、核心特性 & 避坑总结

1. 隔离特性

test.tb_user 和 test_123.tb_user 是两张完全独立的表
双方存储过程、函数互不影响，修改、删除、新增完全隔离
登录新用户后，无需加Schema前缀，直接执行业务SQL即可

2. 关键避坑点

sqlplus / as sysdba 是本机免密超级管理员，仅用于建用户、赋权、系统操作，远程工具无法使用
expdp/impdp 必须用system账号，普通业务用户权限不足，无法完整复刻存储过程、系统对象
复用USERS表空间仅适合测试环境，生产长期并行业务建议单独创建专属表空间
存储过程无硬编码Schema前缀，复刻后自动适配新用户，无需修改代码

六、补充：忘记system密码重置方法

sysdba权限下直接重置，无需旧密码：

ALTER USER system IDENTIFIED BY Sys@123456;