oracle数据迁移及全库迁移方案

准备工作
sqlplus 管理员登录

select username,sid,serial# from v$session where username=''

–若没有数据直接进行下一步

 alter system kill session

–删除用户

 drop user 用户名 cascade

–删除表空间

DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;

当我们在删除用户的时候,特殊符号无法删除的时候，可以用

drop user "A"

创建表空间
–查看dmp存放位置

select * from dba_directories;

–创建数据表空间

CREATE TABLESPACE 表空间名  
DATAFILE 'D:\APP\ADMIN\ORADATA\ORCL\space.dbf' size 5000M  
         EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

–创建临时表空间

create temporary temp
tempfile 'C:\app\Administrator\oradata\orcl\temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;

创建用户

create user 用户名 identified by 密码

default tablespace  表空间

temporary tablespace 临时表空间; 

创建用户并指定表空间

 CREATE USER 用户名 IDENTIFIED BY "密码" 
DEFAULT TABLESPACE 数据表空间名
TEMPORARY TABLESPACE temp

授权

GRANT CONNECT TO 用户名; 
GRANT RESOURCE TO 用户名;
GRANT SESSION  TO 用户名;

导出数据库

system/123@localhost/orcl DIRECTORY=DATA_PUMP_DIR schemas=用户 dumpfile=A20200414.dmp  

过滤表对象TABLE_A，TABLE_B将所有的表导出

nohup expdp wjz/123456 directory=DATA_PUMP_DIR schemas=wjz  EXCLUDE=TABLE:\"IN\(\'TABLE_A\',\'TABLE_B\'\)\" dumpfile=A_20200414.dmp logfile=expdp_20200414.log   &

–导出单表数据

nohup expdp  wjz/123456@localhost/wjz  directory=DATA_PUMP_DIR  tables=TABLE_A dumpfile=TABLE_A _20191217.dmp  logfile=TABLE_A _20191217.log  &

导出TABLE_A表一半数据，筛选条件是时间

nohup expdp wjz/123456 directory=impdp_dir tables=TABLE_A dumpfile=TABLE_A_20171231235959.dmp logfile=TABLE_A_20171231235959.log query=\"where  time\<=to_date \(\'2017-12-31 23:59:59\',\'yyyy-mm-dd hh24:mi:ss\'\)\" &

导出分区

expdp wjz/123456  DIRECTORY=dump DUMPFILE=partition_201903010000.dmp TABLES=TABLE_A:P201903010000 logfile=exp_P201903010000.log parallel=4 
impdp wjz/123456 DIRECTORY=dump DUMPFILE=partition_201903010000.dmp TABLES=TABLE_A:P201903010000 logfile=imp_P201903010000.log table_exists_action=append parallel=4 ACCESS_METHOD=DIRECT_PATH CLUSTER=N

导入数据库
从A用户导出的dmp导入到B用户中去

impdp system/123456@orcl DIRECTORY=DATA_PUMP_DIR  DUMPFILE=A_20200414.dmp REMAP_SCHEMA=A:B

从A表空间导入B表空间

impdp system/123456@orcl DIRECTORY=DATA_PUMP_DIR  DUMPFILE=A_20200414.dmp REMAP_SCHEMA=A:B  remap_table= A1:B1, A2:B1

创建oracle dbllink

create public database link DBlink
connect to hotspot_system identified by password
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)))
(CONNECT_DATA = (SERVICE_NAME = orcl)))';

select * from A@DBlink;

将远程服务器上的文件复制到本机

scp root@192.168.1.1:/u01/app/oracle/admin/tdr/dpdump/20191021pm.dmp /u01/app/oracle/admin/wjz/dpdump

将本机文件复制到远程服务器上

scp /home/administrator/news.txt root@192.168.1.1:/etc/squid

表空间扩展

导入dmp文件，要创建表空间，但是dmp文件大小为100G，而oracle设置的表空间最大只能为32G
解决办法：
为表空间增加一个数据文件即可

1.创建两个表空间：NNC_DATA01.DBF 和 NNC_INDEX01.DBF ：
create tablespace NNC_DATA01
logging  
datafile 'H:\IDE\oracle\oradata\orcl\NNC_DATA01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 32767m  
extent management local; 
 
 
create tablespace NNC_INDEX01
logging  
datafile 'H:\IDE\oracle\oradata\orcl\NNC_INDEX01.dbf' 
size 50m  
autoextend on  
next 50m maxsize 32767m  
extent management local;   
 
alter tablespace NNC_DATA01  
add datafile 'H:\IDE\oracle\oradata\orcl\NNC_DATA02.dbf' size 50m 
autoextend on next 50m maxsize 32767m;
 
 
2.创建用户nc633jx/a:
Create user nc633jx identified by a default tablespace NNC_DATA01 temporary  tablespace temp;
 
3.授权用户：
Grant connect,dba to nc633jx; 
 
4.还原数据库nc633jx:
impdp nc633jx/a@orcl schemas=nc633jx directory=DATA_PUMP_DIR dumpfile=1009jt1.DMP logfile=1009jt1.log 
 
 
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
 
5.删除表空间：
drop tablespace  NNC_DATA01  including contents and datafiles;
drop tablespace  NNC_INDEX01 including contents and datafiles;
 
 
6.删除数据库用户：
drop user  nc633jx cascade;
 
 
7.删除磁盘上生成的表空间文件：
H:\IDE\oracle\oradata\orcl\NNC_DATA01.dbf
H:\IDE\oracle\oradata\orcl\NNC_INDEX01.dbf