Oracle12c之隐含参数_disable_file_resize_logging

新建了一个测试库向里面数据泵导入部分测试数据，这次导入的时候只导入约束include=constraint

导入过程中alert日志一直在打印：
2018-12-19T00:01:03.045147+08:00
Resize operation completed for file# 2, old size 15486976K, new size 15589376K
Resize operation completed for file# 2, old size 15589376K, new size 15691776K
2018-12-19T00:06:01.772874+08:00
Resize operation completed for file# 2, old size 15691776K, new size 15794176K
2018-12-19T00:08:47.945876+08:00
Resize operation completed for file# 2, old size 15794176K, new size 15896576K
2018-12-19T00:08:48.439287+08:00
Resize operation completed for file# 2, old size 15896576K, new size 15998976K
Resize operation completed for file# 2, old size 15998976K, new size 16101376K
2018-12-19T00:08:50.278101+08:00
Resize operation completed for file# 2, old size 16101376K, new size 16203776K
2018-12-19T00:08:51.652302+08:00
Resize operation completed for file# 2, old size 16203776K, new size 16306176K
2018-12-19T00:08:52.895049+08:00
Resize operation completed for file# 2, old size 16306176K, new size 16408576K
Resize operation completed for file# 2, old size 16408576K, new size 16510976K
2018-12-19T00:08:54.021458+08:00
Resize operation completed for file# 2, old size 16510976K, new size 16613376K
2018-12-19T00:09:30.045528+08:00
Resize operation completed for file# 2, old size 16613376K, new size 16715776K
2018-12-19T00:09:30.288598+08:00
Resize operation completed for file# 2, old size 16715776K, new size 16818176K
Resize operation completed for file# 2, old size 16818176K, new size 16920576K
Resize operation completed for file# 2, old size 16920576K, new size 17022976K
2018-12-19T00:09:31.843378+08:00
Resize operation completed for file# 2, old size 17022976K, new size 17125376K
Resize operation completed for file# 2, old size 17125376K, new size 17227776K
2018-12-19T00:09:34.043831+08:00
Resize operation completed for file# 2, old size 17227776K, new size 17330176K
Resize operation completed for file# 2, old size 17330176K, new size 17432576K
2018-12-19T00:09:35.935287+08:00
Resize operation completed for file# 2, old size 17432576K, new size 17534976K
2018-12-19T00:10:15.350903+08:00
Resize operation completed for file# 2, old size 17534976K, new size 17637376K
2018-12-19T00:10:15.744100+08:00
Resize operation completed for file# 2, old size 17637376K, new size 17739776K
Resize operation completed for file# 2, old size 17739776K, new size 17842176K
Resize operation completed for file# 2, old size 17842176K, new size 17944576K
2018-12-19T00:10:17.325759+08:00
Resize operation completed for file# 2, old size 17944576K, new size 18046976K
Resize operation completed for file# 2, old size 18046976K, new size 18149376K
2018-12-19T00:10:18.578974+08:00
Resize operation completed for file# 2, old size 18149376K, new size 18251776K
Resize operation completed for file# 2, old size 18251776K, new size 18354176K
2018-12-19T00:10:20.990145+08:00
Resize operation completed for file# 2, old size 18354176K, new size 18456576K


这种打印行为是隐含参数_disable_file_resize_logging控制的。

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 20 19:04:55 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> col name for a30
SQL> col value for a20
SQL> col description for a70
SQL> set line 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%disable_file_resize_logging%')
  7     order by name;

NAME                           VALUE                DESCRIPTION
------------------------------ -------------------- ----------------------------------------------------------------------
_disable_file_resize_logging   FALSE                disable file resize logging to alert log

SQL> col KSPPINM for a30
SQL>  select
  2        x.ksppinm ,
  3         decode(bitand(ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')   ISSES_MODIFIABLE  ,
  4         decode(bitand(ksppiflg / 65536, 3)   ,
  5                1,
  6                'IMMEDIATE',
  7                2,
  8                'DEFERRED',
  9                3,
 10                'IMMEDIATE',
 11                'FALSE')  ISSYS_MODIFIABLE
 12    from x$ksppi x, x$ksppcv y
 13   where  x.indx = y.indx
 14  and
 15  x.ksppinm like'%disable_file_resize_logging%';

KSPPINM                        ISSES_MODIFIABL ISSYS_MODIFIABLE
------------------------------ --------------- ---------------------------
_disable_file_resize_logging   FALSE           IMMEDIATE

修改该参数为true就可以了，动态生效。
SQL> alter system set "_disable_file_resize_logging"=true;

System altered.

除此之外，还可以得到一些其他结论：
file#=2的数据文件是索引表空间的数据文件，之前导过索引，到了导约束的时候出了异常退出了，本次我只导入constraint约束。也就是说，导入索引的时候不会建主键和unique约束的索引，在导入constraint约束的时候，会把索引和约束一起导入，而不是所有的索引都在index那里就导入了，这也可以解释为什么导入constraint速度其实是不快的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2285850/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2285850/