oracle本地环境搭建-公共库私有库
UAT-C2环境
一、本地数据库导入目标数据库数据的前期准备
1、win + r 输入cmd 打开命令提示符窗口
2、进入Oracle数据库
sqlplus 回车 登录有dba权限的用户
3、删除c2comapp用户（这一步非必须 前提时你确认这个用户不存在）：
drop user c2comapp cascade;
例：
4、创建用户：
create user c2comapp identified by c2comapp default tablespace users temporary tablespace temp;
例：
5、给用户授权：
grant connect,resource,create view,create table,create sequence,create synonym,create database link to c2comapp;
例：
6、修改用户的配额限制：
alter user c2comapp quota unlimited on com_data;
例：
alter user c2comapp quota unlimited on com_idx;
例：
7、授权：
grant read,write on directory data_pump_dir to c2comapp;
例：
8、用户c2comapp创建完毕
将例中的AA替换为要创建的用户名即可
9、重复3~7创建用户
公共库用户：c2comapp/c2omapp
私有库用户：（用户名密码）
c2glmsapp/c2glmsapp、c4glmsapp/c4glmsapp、c6glmsapp/c6glmsapp、 c8glmsapp/c8glmsapp
–删除“用户名”用户
drop user 用户名cascade;
–创建用户 为用户分配表空间
create user 用户名 identified by 密码 default tablespace users temporary tablespace temp;
–给用户授权
grant connect,resource,create view,create table,create sequence,create synonym,create database link to 用户名;
–修改用户的配额限制
alter user 用户名quota unlimited on com_data;
alter user 用户名quota unlimited on com_idx;
–授权
grant read,write on directory data_pump_dir to 用户名;
注：若是表空间不存在就创建
create tablespace 表空间 datafile ‘D:\Oracle\data\表空间.DBF’ size 1024m autoextend on next 50M maxsize unlimited;
若是表空间不足扩表空间
ALTER TABLESPACE 表空间 ADD DATAFILE 'D:\Oracle\data\表空间.DBF’size 1024M;
二、本地数据库导入目标数据库数据
1、Xshell连接远程服务器：
2、导出目标数据库的dmp文件
–导出数据泵
expdp 账号/密码@服务器地址:网段/实例 schemas=导出用户 dumpfile=导出数据库文件名.dmp DIRECTORY=数据泵目录名称 logfile=导出日志名.log compression=ALL REUSE_DUMPFILES=Y version=11.2.0.4.0 exclude=statistics
3、打开本地Oracle数据库连接工具dbvis ——连接之前创建的本地用作公共库的数据库
执行 select * from all_directories; 获取本地Oracle数据库的数据泵
4、将目标数据库生成的dmp文件拉取到本地数据泵目录下
5、将目标数据库dmp文件导入到本地对应数据库中
① 进入本地数据泵目录下
② 在地址栏输入cmd回车
③ 执行导入dmp文件语句
–导入数据泵(断开vpn)
Impdp 账号/密码 DIRECTORY=本地数据泵目录 dumpfile=导出数据文件名.dmp logfile=导出日志文件名.log REMAP_SCHEMA=导出的dmp文件属于那个用户:要导入到那个用户下 remap_tablespace=导出用户表空间:导入用户表空间,导出用户表空间:导入用户表空间 (EXCLUDE=VIEW,SYNONYM 跳过视图和同义词 如果导入时卡在视图同义词可以添加)
④ 导入之后 查询结果
执行查询语句： select * from user_tables;
⑤ dmp文件导入成功
6、重复步骤2~5导出、导入目标私有库
目标数据库名：账号/密码@服务器地址:网段/实例
UAT-C2私有库: c2glmsapp/c2glmsapp@服务器地址:网段/实例
UAT-C4私有库: c4glmsapp/c4glmsapp@服务器地址:网段/实例
UAT-C6私有库: c6glmsapp/c6glmsapp@服务器地址:网段/实例
UAT-C8私有库: c8glmsapp/c8glmsapp@服务器地址:网段/实例
–导出数据泵
expdp 账号/密码@服务器地址:网段/实例 schemas=导出用户 dumpfile=导出数据库文件名.dmp DIRECTORY=数据泵目录名称 logfile=导出日志名.log compression=ALL REUSE_DUMPFILES=Y version=11.2.0.4.0 exclude=statistics
–导入数据泵(断开vpn)
Impdp 账号/密码 DIRECTORY=本地数据泵目录 dumpfile=导出数据文件名.dmp logfile=导出日志文件名.log REMAP_SCHEMA=导出的dmp文件属于那个用户:要导入到那个用户下 remap_tablespace=导出用户表空间:导入用户表空间,导出用户表空间:导入用户表空间 (EXCLUDE=VIEW,SYNONYM 跳过视图和同义词 如果导入时卡在视图同义词可以添加)
三、数据导入之后数据库之间的操作
1、修改用户的dblink（dblink无法修改只能删除重建）
数据库用户c2comapp/c2comapp（总账系统-UATC2-本地公共库）
和 数据库用户c2glmsapp/c2glmsapp（总账-UAT-C2-本地私有库）
和 数据库用户c4glmsapp/c4glmsapp（总账-UAT-C4-本地私有库）
和 数据库用户c6glmsapp/c6glmsapp（总账-UAT-C6-本地私有库）
和 数据库用户c8glmsapp/c8glmsapp（总账-UAT-C8-本地私有库）
–查询用户下的dblink：select * from user_db_links;
select ‘drop database link ‘|| OBJECT_NAME ||’;’ from user_objects t where t.object_type=‘DATABASE LINK’ ;
修改dblink（将地址修改为本地地址）
create database link C2GLMSAPP connect to C2GLMSAPP identified by “c2glmsapp” USING ‘localhost:1521/xe’;
create database link C4GLMSAPP connect to C4GLMSAPP identified by “c4glmsapp” USING ‘localhost:1521/xe’;
create database link C6GLMSAPP connect to C6GLMSAPP identified by “c6glmsapp” USING ‘localhost:1521/xe’;
create database link C8GLMSAPP connect to C8GLMSAPP identified by “c8glmsapp” USING ‘localhost:1521/xe’;
数据库用户c2glmsapp/c2glmsapp（总账-UAT-C2-本地私有库）
删除操作相同
创建dblink：create database link C2COMAPP connect to C2COMAPP identified by “c2comapp” USING ‘localhost:1521/xe’;
数据库用户c4glmsapp/c4glmsapp（总账-UAT-C4-本地私有库）
删除操作相同
创建dblink：create database link C2COMAPP connect to C2COMAPP identified by “c2comapp” USING ‘localhost:1521/xe’;
数据库用户c6glmsapp/c6glmsapp（总账-UAT-C6-本地私有库）
删除操作相同
创建dblink：create database link C2COMAPP connect to C2COMAPP identified by “c2comapp” USING ‘localhost:1521/xe’;
数据库用户c8glmsapp/c8glmsapp（总账-UAT-C8-本地私有库）
删除操作相同
创建dblink：create database link C2COMAPP connect to C2COMAPP identified by “c2comapp” USING ‘localhost:1521/xe’;
2、将公共库用户下的表和私有库用户下的表分别授查询权限给对方
①将公共库表的查询权限授予私有库用户
在c2comapp/c2comapp（总账系统-UATC2-本地公共库）执行
select ’ grant select on ‘||table_name ||’ to c2glmsapp;’ from user_tables;
（在公共库中将查询权限授予用户c2glmsapp/c2glmsapp（总账-UAT-C2-本地私有库））
粘贴到当前用户下执行
同样将查询权限授予
c4glmsapp/c4glmsapp（总账-UAT-C4-本地私有库）
select ’ grant select on ‘||table_name ||’ to c4glmsapp;’ from user_tables;
c6glmsapp/c6glmsapp（总账-UAT-C6-本地私有库）
select ’ grant select on ‘||table_name ||’ to c6glmsapp;’ from user_tables;
c8glmsapp/c8glmsapp（总账-UAT-C8-本地私有库）
select ’ grant select on ‘||table_name ||’ to c8glmsapp;’ from user_tables;
②私有库创建公共库表格的同义词
在c2comapp/c2comapp（总账系统-UATC2-本地公共库）下执行
select ‘create synonym ’ || table_name ||’ for c2comapp.’||TABLE_NAME ||’;’ from user_tables;
粘贴到c2glmsapp/c2glmsapp（总账-UAT-C2-本地私有库）下执行
粘贴到c4glmsapp/42glmsapp（总账-UAT-C4-本地私有库）下执行
图：略
粘贴到c6glmsapp/c6glmsapp（总账-UAT-C6-本地私有库）下执行
图：略
粘贴到c8glmsapp/c8glmsapp（总账-UAT-C8-本地私有库）下执行
图：略
注意：如果执行创建同义词报错如下则删除再建
删除操作
3、公共库创建私有库视图
注：③~⑤中要带逗号
分6步：wps word 均可
①.在私有库下执行查询创建视图语句;
②.替换 ^p -> ,
③.替换 REPLACETOAS, - > AS
④.替换 REPLACETOUNIONALL, -> UNION ALL
⑤.替换 REPLACETONEXTLINE, -> ;^p
⑥.将替换后的结果在公共库下执行
私有库执行 将结果集复制到 word wps 替换
select
case
when b.id=0 and a.COLUMN_ID = b.min_column_id then ‘CREATE OR REPLACE VIEW ‘||a.table_name||’ ( ‘|| a.COLUMN_NAME
when b.id=0 and a.COLUMN_ID = b.max_column_id then a.COLUMN_NAME||’ ’ ||’) REPLACETOAS’
when b.id=0 and a.COLUMN_ID <> b.max_column_id and a.COLUMN_ID <> b.min_column_id then a.COLUMN_NAME
when b.id=1 and a.COLUMN_ID = b.min_column_id then ’ SELECT ‘||’"’|| a.COLUMN_NAME|| ‘"’
when b.id=1 and a.COLUMN_ID = b.max_column_id then ‘"’||a.COLUMN_NAME||’"’||’ FROM ‘||a.table_name ||’@C2GLMSAPP’||‘REPLACETOUNIONALL’
when b.id=1 and a.COLUMN_ID <> b.max_column_id and a.COLUMN_ID <> b.min_column_id then ‘"’||a.COLUMN_NAME||’"’
when b.id=2 and a.COLUMN_ID = b.min_column_id then ’ SELECT ‘||’"’|| a.COLUMN_NAME|| ‘"’
when b.id=2 and a.COLUMN_ID = b.max_column_id then ‘"’||a.COLUMN_NAME||’"’||’ FROM ‘||a.table_name ||’@C4GLMSAPP’||‘REPLACETOUNIONALL’
when b.id=2 and a.COLUMN_ID <> b.max_column_id and a.COLUMN_ID <> b.min_column_id then ‘"’||a.COLUMN_NAME||’"’
when b.id=3 and a.COLUMN_ID = b.min_column_id then ’ SELECT ‘||’"’|| a.COLUMN_NAME|| ‘"’
when b.id=3 and a.COLUMN_ID = b.max_column_id then ‘"’||a.COLUMN_NAME||’"’||’ FROM ‘||a.table_name ||’@C6GLMSAPP’||‘REPLACETOUNIONALL’
when b.id=3 and a.COLUMN_ID <> b.max_column_id and a.COLUMN_ID <> b.min_column_id then ‘"’||a.COLUMN_NAME||’"’
when b.id=4 and a.COLUMN_ID = b.min_column_id then ’ SELECT ‘||’"’|| a.COLUMN_NAME|| ‘"’
when b.id=4 and a.COLUMN_ID = b.max_column_id then ‘"’||a.COLUMN_NAME||’"’||’ FROM ‘||a.table_name ||’@C8GLMSAPP’||‘REPLACETONEXTLINE’
when b.id=4 and a.COLUMN_ID <> b.max_column_id and a.COLUMN_ID <> b.min_column_id then ‘"’||a.COLUMN_NAME||’"’
else null end
from user_tab_columns a left join
( select ‘0’ as id,max(COLUMN_ID) as max_column_id ,min(COLUMN_ID) as min_column_id ,table_name from user_tab_columns where table_name in (select table_name from user_tables) and table_name not like ‘AMS%’ group by table_name union all
select ‘1’ as id,max(COLUMN_ID) as max_column_id ,min(COLUMN_ID) as min_column_id ,table_name from user_tab_columns where table_name in (select table_name from user_tables) and table_name not like ‘AMS%’ group by table_name union all
select ‘2’ as id,max(COLUMN_ID) as max_column_id ,min(COLUMN_ID) as min_column_id ,table_name from user_tab_columns where table_name in (select table_name from user_tables) and table_name not like ‘AMS%’ group by table_name union all
select ‘3’ as id,max(COLUMN_ID) as max_column_id ,min(COLUMN_ID) as min_column_id ,table_name from user_tab_columns where table_name in (select table_name from user_tables) and table_name not like ‘AMS%’ group by table_name union all
select ‘4’ as id,max(COLUMN_ID) as max_column_id ,min(COLUMN_ID) as min_column_id ,table_name from user_tab_columns where table_name in (select table_name from user_tables) and table_name not like ‘AMS%’ group by table_name
) b on a.table_name=b.table_name
where a.table_name in (select table_name from user_tables ) and a.table_name not like ‘AMS%’ and a.table_name not in (select table_name from user_tables where substr(table_name,length(table_name))>=‘0’ and substr(table_name,length(table_name))<=‘9’) and a.data_type not in (‘CLOB’,‘BLOB’)
order by a.TABLE_NAME||b.id,a.table_name ,a.COLUMN_ID;
替换完成 复制到公共库执行
(1)将上述查询语句复制到c2glmsapp/c2glmsapp（总账-UAT-C2-本地私有库）下执行 并复制 结果集
(2)将复制的结果集粘贴在新建文档中wps word 均可
①替换^p -> ,
②替换 REPLACETOAS, - > AS
③替换 REPLACETOUNIONALL, -> UNION ALL
④替换 REPLACETONEXTLINE, -> ;^p
(3)将修改好的结果集复制到c2comapp/c2comapp（总账系统-UATC2-本地公共库）执行
(4)重复(1)~(3)步 修改好的结果集粘贴到公共库下执行
c4glmsapp/c4glmsapp（总账-UAT-C4-本地私有库）
c6glmsapp/c6glmsapp（总账-UAT-C6-本地私有库）
c8glmsapp/c8glmsapp（总账-UAT-C8-本地私有库）
