数据库使用数据泵迁移遇到LOB字段
impdp system/Clic1234 attach=SYS_IMPORT_ILEARN_TRA
desc ILEARN_TRA.NOTIFI_TACTIC
desc ILEARN_TRA.MSG_MAIL
desc ILEARN_TRA.NT_HISTORY
desc ILEARN_TRA.ATTEMPT
desc ILEARN_TRA.NOTIFI_TACTIC
desc ILEARN_TRA.USER_INIT_EXAM
while true
do
ora tsfree
sleep 600
done;
create table t_lob as select distinct(table_name) from dba_lobs@dblink_datapump where owner ='ILEARN_TRA';
select distinct(a.table_name) from dba_lobs a,t_lob b where a.table_name not in (select table_name from t_lob) and a.owner='ILEARN_TRA';
ATTEMPT
USER_INIT_EXAM
select BYTES/1024/1024,b.table_name,a.segment_name from dba_segments a,dba_lobs b where a.segment_name = b.segment_name and b.table_name in ('ATTEMPT','USER_INIT_EXAM') order by BYTES;
select sum(a.BYTES/1024/1024) as s_size from dba_segments a,dba_lobs b where a.segment_name = b.segment_name and b.owner ='ILEARN_TRA' and b.table_name='USER_INIT_EXAM';
SUBCATEGORY_PK
select BYTES/1024/1024 from dba_segments where segment_name='SUBCATEGORY_PK';
select round(count(distinct(CONSTRAINT_NAME))/512*100,2)||'%' as AA from all_constraints where OWNER='ILEARN_TRA' and CONSTRAINT_TYPE='R';
select count(distinct(CONSTRAINT_NAME)) from all_constraints where OWNER='ILEARN_TRA' and CONSTRAINT_TYPE='R';
select CONSTRAINT_TYPE from all_constraints where CONSTRAINT_NAME='ILA_ORDER_PART_XREF_ORDER_FK';
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME
--create table lob_persent as
insert into lob_persent
SELECT round(sum(c.BYTES / 1024 / 1024) / sum(a.BYTES / 1024 / 1024) * 100, 2) || '%' AS PERCENT,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime
FROM dba_segments@dblink_datapump a,
dba_lobs@dblink_datapump b,
dba_segments c,
dba_lobs d
WHERE c.segment_name = d.segment_name
AND d.OWNER = 'ILEARN_TRA'
AND d.table_name = 'USER_INIT_EXAM'
AND a.segment_name = b.segment_name
AND b.OWNER = 'ILEARN_TRA'
AND b.table_name = 'USER_INIT_EXAM';
commit;
select * from lob_persent;
create or replace procedure test as
begin
SELECT round(sum(c.BYTES / 1024 / 1024) / sum(a.BYTES / 1024 / 1024) * 100, 2) || '%' AS PERCENT,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime
FROM dba_segments@dblink_datapump a,dba_lobs@dblink_datapump b,dba_segments c,dba_lobs d
WHERE c.segment_name = d.segment_name
AND d.OWNER = 'ILEARN_TRA'
AND d.table_name = 'USER_INIT_EXAM'
AND a.segment_name = b.segment_name
AND b.OWNER = 'ILEARN_TRA'
AND b.table_name = 'USER_INIT_EXAM';
end;
/
declare
job1 number;
begin
dbms_job.submit(job1,'test;',sysdate,'sysdate+1/144');
end;
/
select sum(BYTES/1024/1024) from dba_segments a,dba_lobs b where a.segment_name = b.segment_name and b.owner ='ILEARN_TRA';
集电港
BYTES/1024/1024 TABLE_NAME SEGMENT_NAME
--------------- -------------------- ------------------------------
.0625 ATTEMPT SYS_LOB0000053178C00039$$
.0625 ATTEMPT SYS_LOB0000065763C00039$$
6 USER_INIT_EXAM SYS_LOB0000054426C00018$$
27 USER_INIT_EXAM SYS_LOB0000067328C00018$$
45 USER_INIT_EXAM SYS_LOB0000067328C00028$$
244 USER_INIT_EXAM SYS_LOB0000067328C00027$$
244 USER_INIT_EXAM SYS_LOB0000067328C00029$$
7481 USER_INIT_EXAM SYS_LOB0000054426C00017$$
67883 USER_INIT_EXAM SYS_LOB0000067328C00017$$
卡园
BYTES/1024/1024 TABLE_NAME SEGMENT_NAME
--------------- ------------------------------ ---------------------------------------------------------------------------------
.0625 ATTEMPT SYS_LOB0000126246C00039$$
.0625 ATTEMPT SYS_LOB0000128357C00039$$
.25 USER_INIT_EXAM SYS_LOB0000128579C00018$$
.5625 USER_INIT_EXAM SYS_LOB0000126445C00018$$
2 USER_INIT_EXAM SYS_LOB0000128579C00028$$
8 USER_INIT_EXAM SYS_LOB0000128579C00027$$
80 USER_INIT_EXAM SYS_LOB0000128579C00029$$
6859 USER_INIT_EXAM SYS_LOB0000126445C00017$$
11980 USER_INIT_EXAM SYS_LOB0000128579C00017$$
75930
75393.75
24732
1分钟256M
源：
CREATE OR REPLACE VIEW USER_INIT_EXAM_LOB
(size)
AS select sum(a.BYTES/1024/1024) from dba_segments a,dba_lobs b where a.segment_name = b.segment_name and b.owner ='ILEARN_TRA' and b.table_name='USER_INIT_EXAM';
exec DBMS_STATS.GATHER_schema_STATS ('ILEARN_TRA',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
exec DBMS_STATS.GATHER_schema_STATS ('ILA_SAP',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
exec DBMS_STATS.GATHER_schema_STATS ('ILEARN_JOB',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
exec DBMS_STATS.GATHER_schema_STATS ('ILEARN',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
exec DBMS_STATS.GATHER_schema_STATS ('ILEARN_BEIYAN',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
exec DBMS_STATS.GATHER_schema_STATS ('APEX_PUBLIC_USER',estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for all columns size auto',degree => 15,cascade => true);
ILEARN_TRA
ILA_SAP
ILEARN_JOB
ILEARN
ILEARN_BEIYAN
APEX_PUBLIC_USER
alter system set "_gc_policy_minimum"=1000000 sid='ilndb1' scope=both;
alter system set "_gc_affinity_ratio"=1000000 sid='ilndb1' scope=both;
alter system set "_gc_policy_minimum"=1000000 sid='ilndb2' scope=both;
alter system set "_gc_affinity_ratio"=1000000 sid='ilndb2' scope=both;
alter system set "_gc_policy_minimum"=1000000 sid='ilndb3' scope=both;
alter system set "_gc_affinity_ratio"=1000000 sid='ilndb3' scope=both;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATADG/ILNDB/CONTROLFILE/snapcf_ilndb1.f';
CONFIGURE SNAPSHOT CONTROLFILE NAME clear
+DATADG/ILNDB/CONTROLFILE/snapcf_ilndb1.f
