col name for a40

select name,locks,pins

from v$db_object_cache

where locks > 0 and pins > 0 and type='PROCEDURE';

10.21.1.20 和10.24.51.11两个数据库的dblibk已经建好了，主要就impdp就可以了，一共需要导出

drop user ILEARN_TRA       cascade;

drop user ILA_SAP          cascade;

drop user ILEARN_JOB       cascade;

drop user ILEARN           cascade;

drop user ILEARN_BEIYAN    cascade;

drop user APEX_PUBLIC_USER cascade;

srvctl stop database -d ilndb

srvctl start database -d ilndb

SELECT SID,JOB FROM DBA_JOBS_RUNNING;

MACHINE

 PORT

 TERMINAL

 PROGRAM  

select username,sid,SERIAL# from gv$session where username ='ILEARN_TRA';

select username,sid,SERIAL# from v$session where username ='ILEARN_TRA';

USERNAME                              SID    SERIAL#

------------------------------ ---------- ----------

ILEARN_TRA                           2545          5

ALTER SYSTEM KILL SESSION '2545,5';

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='ILEARN_TRA';

这几个用户下数据，正式迁移的时候，10.21.1.20上这几个用户数据要删除，整个过程耗时10:15:38 

这是上次测试的日志 

SYS@ilndb1>  select name from v$database@dblink_datapump; 

NAME

---------

ILNDB 

dblink导库过程： 

1、在客户端数据库创建 dblink 和要保存数据文件的目录：

create public database link dblink_datapump connect to system identified by Clic1234 using '10.24.51.12:1521/ilndb';

select name from v$database@dblink_datapump;

create or replace directory expdp_dir as '/oracle/expdp';

grant read, write on directory expdp_dir to public; 

2、在客户端数据库创建相关表空间和用户

create tablespace ILEARN_DATA DATAFILE '+DATADG' SIZE 32767M AUTOEXTEND off;        //对比生产端，加足表空间容量

create user ILEARN_TRA identified by "Qwer4321" default tablespace ILEARN_DATA;

grant resource,connect to ILEARN_TRA; 

3、在客户端数据库使用IMPDP导入

nohup impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn1.log directory=expdp_dir schemas=ILEARN_TRA EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILEARN_TRA parallel=20 &

select sum(bytes/1024/1024) from dba_segments where owner='ILEARN_TRA';

impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn3.log directory=expdp_dir schemas=ILEARN EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILEARN  parallel=20

select sum(bytes/1024/1024) from dba_segments where owner='ILEARN';

impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn2.log directory=expdp_dir schemas=ILA_SAP EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ILA_SAP parallel=14

--wancheng

impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn4.log directory=expdp_dir schemas=ilearn_beiyan EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_beiyan parallel=14

--wancheng 

impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn4.log directory=expdp_dir schemas=ilearn_job EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_job parallel=14

--wancheng

impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn5.log directory=expdp_dir schemas=APEX_PUBLIC_USER EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_ilearn_apex parallel=14

wancheng

4、在客户端数据库删除 dblink:

drop public database link dblink_datapump;

  祝工作顺利，万事如意~~ 

nohup impdp system/Clic1234 network_link=dblink_datapump logfile=impdp_ilearn_1210.log directory=expdp_dir schemas=ILEARN_TRA,ILA_SAP,ILEARN_JOB,ILEARN,ILEARN_BEIYAN,APEX_PUBLIC_USER  EXCLUDE=statistics cluster=N job_name=SYS_IMPORT_1210 parallel=14 &

ILEARN_TRA,ILA_SAP,ILEARN_JOB,ILEARN,ILEARN_BEIYAN,APEX_PUBLIC_USER

create user VIEW_LMS_WORKBOOKS identified by Clic1234 default tablespace ILEARN_DATA account unlock;

CREATE_AND_VIEW_LMS_WORKBOOKS