oracle迁移

#导出scott的数据，排除 table_a table_b
expdp system/password schemas=scott directory=datadir dumpfile=scott_%U.dmp PARALLEL=2  CLUSTER=no logfile=scott.log  exclude=table:\"IN\(\'table_a\'\,\'table_b\'\)\" 

#查看导出文件所在的目录
select * from dba_directories;


#删除用户
drop user scott cascade;
#如果删除失败，查看用户的使用情况
select * from v$session  t where t.USERNAME='scott';


#查看表空间
select * from dba_tablespaces;
#删除表空间
drop tablespace TS_scott_user_DATA including contents and datafile;

#查看表空间对应的文件
select * from dba_data_files t;

#创建表空间
create tablespace TS_scott_user_DATA
 logging 
 datafile '/data/oracle/oradata/transdb/ts_scott_user_data1' 
 size 8192m 
 autoextend on next 256m 
 maxsize unlimited 
 extent management local 
 uniform segment space management auto;
 
#创建用户
create user scott identified by oracle default tablespace TS_scott_user_DATA temporary tablespace TEMP;
#授权
grant connect, resource, unlimited tablespace, debug any procedure, debug connect session  to scott;  
grant create view to scott;
grant create synonym to scott;


#查看datadir目录，将dmp文件放到目录下
select * from dba_directories;

#导入数据
impdp system/password schemas=scott directory=datadir dumpfile=scott_%U.dmp PARALLEL=2 CLUSTER=no logfile=scott.log TABLE_EXISTS_ACTION=replace