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