使用exp导出导入，需要注意的问题。

问题：由于段延迟分配。导致新创建的表未分配段。与参数deferred_segment_creation有关。如果是空表，使用exp和imp会导致导不出来表结构。

使用exp加参数rows=n导出元数据，记录一个问题
导入后，往表里插入数据，发现占用了很大的空间，经过排查是initial extent的问题。
使用rows=n,导出元数据，然后在导入到其它库。initial extent会改变(新建立的表默认为64k)

怎样才处理该问题？
ALTER TABLE <username>.<table_name> MOVE tablespace <tablespace_name> STORAGE(INITIAL 64k NEXT 1m);
ALTER INDEX <username>.<index_name> REBUILD online tablespace <tablespace_name> STORAGE(INITIAL 64k NEXT 1m);

测试：
1.查询表的定义，initial默认为64k
SQL> select dbms_metadata.get_ddl('TABLE','T1','ERWA') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1','ERWA')
--------------------------------------------------------------------------------

CREATE TABLE "ERWA"."T1"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(20),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXT
ENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL>
2.使用rows=n导出
[oracle@lxtrac05 bk]$ exp system/oracle file=test.dmp tables=erwa.t1 rows=n
Export: Release 11.2.0.4.0 - Production on Thu Feb 22 11:14:16 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
Current user changed to ERWA
. . exporting table T1
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@lxtrac05 bk]$
3.导入
[oracle@lxtrac05 bk]$ imp system/oracle file=test.dmp tables=t1 fromuser=erwa touser=srw
Import: Release 11.2.0.4.0 - Production on Thu Feb 22 11:15:27 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing ERWA's objects into SRW
Import terminated successfully without warnings.
[oracle@lxtrac05 bk]$
4.再次查询表的定义结构。发现initial extent变了。
SQL> select dbms_metadata.get_ddl('TABLE','T1','SRW') from dual;
DBMS_METADATA.GET_DDL('TABLE','T1','SRW')
--------------------------------------------------------------------------------
CREATE TABLE "SRW"."T1"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(20),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE( INITIAL 710934528 NEXT 1048576 MINEXTENTS 1
FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL>

 

转载于:https://www.cnblogs.com/erwadba/p/8458634.html