oracle exp导数据发现缺少好多表

总结一下，oracle 11g 用exp导出的时候默认的情况下空表是倒不出来的，如果想要导出  那些空表，需要修改一个参数，但这个参数默认的情况下是对以后建立的空表有效，如果想要把现在的空表导入的话需要一些小的技巧



                                 exp epaysch/epaysch file=
[oracle@qiaobaojg-DB ~]$ imp epaysch/epaysch file=0710funddb.dmp full=y
SQL> SELECT table_name,tablespace_name,owner from dba_tables where table_name='TBLACKLIST';
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
OWNER
------------------------------
TBLACKLIST                     USERS
EPAYSCH
TBLACKLIST                     EPAYDB
YAKEDB

SQL> SELECT table_name,tablespace_name,owner from dba_tables where table_name='TBLACKLIST';
no rows selected
发现用原来的那条导出命令只能把epaysch用户下的表导出来 不能把其他用户下的表导出来
[oracle@db1 ~]$ exp epaysch/epaysch file=071001.dmp full=y;无奈只能加上full=y把全库导出
SQL> select * from v$nls_parameters;


PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_LANGUAGE
AMERICAN


NLS_TERRITORY
AMERICA


NLS_CURRENCY
$




PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA


NLS_NUMERIC_CHARACTERS
.,


NLS_CALENDAR
GREGORIAN




PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_DATE_FORMAT
yyyy-mm-dd:hh24:mi:ss


NLS_DATE_LANGUAGE
AMERICAN


NLS_CHARACTERSET
AL32UTF8




PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_SORT
BINARY


NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY

PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16


NLS_COMP
BINARY


NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
19 rows selected.
SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.


Total System Global Area 1603411968 bytes
Fixed Size                  2228784 bytes
Variable Size             973082064 bytes
Database Buffers          620756992 bytes
Redo Buffers                7344128 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered
SQL> alter system set job_queue_processes=0;
ystem altered.
SQL> alter database open;

Database altered.
SQL> alter database character set ZHS16GBK;
alter database character set ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

Database altered.

SQL> SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');


PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16GBK
NLS_NCHAR_CHARACTERSET
AL16UTF16

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/epaydb.dbf
/u01/app/oracle/oradata/orcl/funddb.dbf

11G中有个新特性，当表无数据时，不分配segment，以节省空间

　　解决方法：

　　1、insert一行，再rollback就产生segment了。

　　该方法是在在空表中插入数据，再删除，则产生segment。导出时则可导出空表。

　　2、设置deferred_segment_creation 参数

show parameter deferred_segment_creation 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
deferred_segment_creation            boolean     TRUE 
SQL> alter system set deferred_segment_creation=false; 

系统已更改。 

SQL> show parameter deferred_segment_creation 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
deferred_segment_creation            boolean     FALSE

　　参数值默认是TRUE，当改为FALSE时，无论是空表还是非空表，都分配segment。

　　需注意的是：该值设置后对以前导入的空表不产生作用，仍不能导出，只能对后面新增的表产生作用。如需导出之前的空表，只能用第一种方法。

搞了我好久，最后查到这个方法。

先查询一下当前用户下的所有空表

select table_name from user_tables where NUM_ROWS=0;

用以下这句查找空表

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

把查询结果导出，执行导出的语句

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'
-----------------------------------------------------------
alter table AQ$_AQ$_MEM_MC_H allocate extent;
alter table AQ$_AQ$_MEM_MC_G allocate extent;
alter table AQ$_AQ$_MEM_MC_I allocate extent;
alter table AQ$_AQ_PROP_TABLE_T allocate extent;
alter table AQ$_AQ_PROP_TABLE_H allocate extent;
alter table AQ$_AQ_PROP_TABLE_G allocate extent;
alter table AQ$_AQ_PROP_TABLE_I allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_T allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_H allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_G allocate extent;
alter table AQ$_KUPC$DATAPUMP_QUETAB_I allocate extent;

'ALTERTABLE'||TABLE_NAME||'ALLOCATEEXTENT;'
-----------------------------------------------------------
alter table AQ$_SYS$SERVICE_METRICS_TAB_T allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_H allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_G allocate extent;
alter table AQ$_SYS$SERVICE_METRICS_TAB_I allocate extent;

然后再执行

exp 用户名/密码@数据库名 file=/home/oracle/exp.dmp log=/home/oracle/exp_smsrun.log

　　成功！

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

转载于:http://blog.itpub.net/29898569/viewspace-1728618/