Oracle11G密码到期设置与数据库导出空表少表问题

密码到期设置

Oracle11G安装后默认密码是180天到期，所以为了以后麻烦建议修改Oracle密码到期设置。
具体修改方法：

[oracle@hzjz ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 29 17:34:09 2021

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT username,PROFILE FROM dba_users;
USERNAME		       PROFILE
------------------------------ ------------------------------
SYS			       	   DEFAULT
SYSTEM			       DEFAUL
OUTLN			       DEFAULT
MGMT_VIEW		       DEFAULT
FLOWS_FILES		       DEFAULT
MDSYS			       DEFAULT
ORDSYS			       DEFAULT
EXFSYS			       DEFAULT

USERNAME		       PROFILE
------------------------------ ------------------------------
DBSNMP			       MONITORING_PROFILE
WMSYS			       DEFAULT
APPQOSSYS		       DEFAULT
APEX_030200		       DEFAULT
OWBSYS_AUDIT		   DEFAULT
ORDDATA 		       DEFAULT
CTXSYS			       DEFAULT
ANONYMOUS		       DEFAULT
SYSMAN			       DEFAULT
XDB			           DEFAULT
ORDPLUGINS		       DEFAULT

USERNAME		       PROFILE
------------------------------ ------------------------------
OWBSYS			       DEFAULT
SI_INFORMTN_SCHEMA	   DEFAULT
OLAPSYS 		       DEFAULT
SCOTT			       DEFAULT
ORACLE_OCM		       DEFAULT
XS$NULL 		       DEFAULT
MDDATA			       DEFAULT
DIP			           DEFAULT
APEX_PUBLIC_USER	   DEFAULT
SPATIAL_CSW_ADMIN_USR  DEFAULT
SPATIAL_WFS_ADMIN_USR  DEFAULT

33 rows selected.

SQL> SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

PROFILE 		       RESOURCE_NAME			RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
DEFAULT 		       PASSWORD_LIFE_TIME		PASSWORD
180


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

SQL> 

注意：
1、修改之后不需要重启动数据库，会立即生效。
2、已经被提示的帐户必须再改一次密码，举例如下：

[oracle@hzjz ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 29 17:39:35 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> alter user sys identified by "sys";

导出空表少表

在使用Oracle11G数据库导出功能时，会出现空表少表情况。
原因是：
oracle 11g 有一个参数：deferred_segment_creation，含义是段延迟创建，默认是true。
具体的意思就是：
你新建了一个表，如果没有向其中插入数据，那么这个表不会立即分配extent，也就是不占数据空间，只有当插入数据后才分配空间，这样可以节省少量的空间。那么，设置为false后，无论是空表还是非空表，都分配extent。
多的不说直接上代码：

[oracle@hzjz ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 29 17:58:41 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> show parameter deferred_segment_creation 

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation	     boolean	 TRUE

SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> 

注意：
1、该值设置后只对后面新增的表产生作用，对之前建立的空表不起作用。
2、针对之前建立的空表使用以下语句实现：
方法一：

[oracle@hzjz ~]$ sqlplus SXTEST/SXTEST

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 29 18:02:31 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> spool /home/oracle/test.log

SQL>select 'alter table '||table_name||' allocate extent;' from user_tables where segment_created='NO';

SQL> spool off;

方法二：

[oracle@hzjz ~]$ sqlplus SXTEST/SXTEST

SQL*Plus: Release 11.2.0.4.0 Production on Sat May 29 18:02:31 2021

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> spool /home/oracle/test.log

SQL>select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;

SQL> spool off;

以上两个方法选择一个执行就行，将执行的结果文件【 /home/oracle/test.log】拷贝出来，整理成SQL只保留类似“alter table FINAL_ITEM allocate extent; ”语句,然后再sqlplus执行，就解决了以上问题！