oracle 12c 隐含参数,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速度其实是不快的。