1）资料获取数据泵INCLUDE and EXCLUDE对象类型视图Export/Import DataPump Parameters INCLUDE and EXCLUDE - How to Load and Unload Specific Objects (Doc ID 341733.1)­­~database_export_objects  /schema_export_objects  /table_export_objects  2)进行导出SQL> create directory dump as '/home/oracle/tools';expdp \'/ as sysdba\' directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=system EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel--导出元数据，加了并行，但实质只导出一个dump文件,说明导出元数据只能串行导出--附上导出日志，如果愿意，后缀的导出类型，均可过滤，保留表、索引即可，最小化原则处理需求Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" directory=dump dumpfile=system%u.dmp logfile=system.log SCHEMAS=systemEXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel=2 Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/SYNONYM/SYNONYMProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPECProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPECProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/VIEW/COMMENTProcessing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODYProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTIONProcessing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMAMaster table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:/home/oracle/tools/system01.dmpJob "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov 29 14:48:14 2018 elapsed 0 00:003）scp 省略4） 导入操作--docker 命令进行SQL*Plus docker run -d --name "oradb" -v /home/dmuser/my_oracle_data:/u01/app/oracle IP:10082/sath89/oracle-xe-11g--正常环境，登陆服务器端，即可使用impdp工具，本次场景为docker 有些特殊--创建表空间--源端用户所在表空间SQL> select tablespace_name from dba_segments where owner='SYSTEM' group by tablespace_name;TABLESPACE_NAME------------------------------SYSAUXSYSTEM--目标端创建表空间crate tablesapce SYSTEM datafile '/u01/app/oracle/oradata/XE/system.dbf' size  10m uniform size 128k autoextend on next 30m maxsize 10g; sqlplus as sysdbacreate directories abc as 'xxx';--导入 impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc.log REMAP_TABLESPACE=LIFELOB_DATA:LIFEDATA_T_L,LIFEINDEX_T_L:LIFEDATA_T_L --导入报错  ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM--对system表空间进行扩容--再次导入报错 CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE, "NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2(1000 BYTE), "CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INORA-39171: Job is experiencing a resumable wait.ORA-12953: The request exceeds the maximum allowed database size of 11 GB --搜索MOS 提示，学习XE类 Express Edition (简化版本，数据库允许最大11g)ORA-12953: The request exceeds the maximum allowed database size of 11 GB (Doc ID 2414879.1)    [Release 11.2 to 12.2]This is an Oracle Database XE environment, and Oracle Database XE has a limitation of 11 GB of user data 疑问？测试库怎么有11g这么大？ SQL> select round( sum(bytes)/1024/1024) m from dba_segments where owner not in('PICCPROD');         M----------      1708--导入用户后 >11g出发XE峰值，报错SQL> select sum(bytes)/1024/1024 from dba_segments;SUM(BYTES)/1024/1024--------------------          11706.8125--什么类型的对象占用空间SQL> select sum(bytes/1024/1024) ,segment_type from dba_segments where owner='PICCPROD' group by owner,segment_type;SUM(BYTES/1024/1024) SEGMENT_TYPE-------------------- ------------------                9999 TABLE？dump文件大小182，导入后，表大小9G                oracle@c46bf408bfad:~/dmpfile$ lsPICCPROD01.dmp  picc.logoracle@c46bf408bfad:~/dmpfile$ du -sm PICCPROD01.dmp 182     PICCPROD01.dmpfile查询表数据：select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='PICCPROD' and segment_type='TABLE' and rownum=1;SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024------------------------------ ------------------ ---------------T_DIAGNOSIS_TYPE               TABLE                           .5select count(*) from PICCPROD.T_DIAGNOSIS_TYPE;  COUNT(*)----------         0查询表的区的分布SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';TABLESPACE_NAME SEGMENT_NAME          EXTENT_ID   BLOCK_ID BYTES/1024     BLOCKS--------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L    T_DIAGNOSIS_TYPE              0        128        128         16LIFEDATA_T_L    T_DIAGNOSIS_TYPE              1        144        128         16LIFEDATA_T_L    T_DIAGNOSIS_TYPE              2        160        128         16LIFEDATA_T_L    T_DIAGNOSIS_TYPE              3        176        128         16尝试对表move（本次不再次对数据文件resize 进行分析，如果需要resize减少数据文件，需要从数据文件max_block_id 进行move，随后对数据文件进行resize）alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;                    *ERROR at line 1:ORA-12953: The request exceeds the maximum allowed database size of 11 GB*Move时，需要存在一倍对象空闲空间，此时空闲空间不足无法Move**释放空间，找出max最大对象select segment_name,sum(blocks) from dba_extents where owner='PICCPROD' group by segment_name order by 2;SEGMENT_NAME        SUM(BLOCKS)-------------------------------------------T_POLICY_AUTO        221312T_POLICY_PRODUCT     309120drop table PICCPROD.T_POLICY_AUTO purge;drop table PICCPROD.T_POLICY_PRODUCT purge;alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';TABLESPACE_NAME      SEGMENT_NAME          EXTENT_ID   BLOCK_ID BYTES/1024     BLOCKS-------------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L         T_DIAGNOSIS_TYPE              0      34720        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              1      34736        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              2      34752        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              3      34768        128         16*尝试对数据文件进行resize*select file_id,bytes/1024/1024 from dba_data_files where tablespace_name='LIFEDATA_T_L';   FILE_ID BYTES/1024/1024---------- ---------------         5           10000alter database datafile 5 resize 5000M;*ERROR at line 1:ORA-03297: file contains used data beyond requested RESIZE value--虽然数据为空，但是表对应的段，分配了存储，占用了存储*truncate tableSQL> truncate table PICCPROD.T_DIAGNOSIS_TYPE;Table truncated.SQL> select tablespace_name,segment_name,EXTENT_ID,BLOCK_ID,BYTES/1024,BLOCKS from dba_extents where owner='PICCPROD' and segment_name='T_DIAGNOSIS_TYPE';TABLESPACE_NAME      SEGMENT_NAME          EXTENT_ID   BLOCK_ID BYTES/1024     BLOCKS-------------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L         T_DIAGNOSIS_TYPE              0      34720        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              1      34736        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              2      34752        128         16LIFEDATA_T_L         T_DIAGNOSIS_TYPE              3      34768        128         16*数据库参数，延迟段创建SQL> show parameter deferNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------deferred_segment_creation            boolean     TRUE--参数并未关闭--false，但是之前的导入还是创建表，因此延迟段创建参数，对于数据泵导入的对象是不起作用的*查询建表语句--导入 impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc%U.log sqlfile=sql01.sql ORA-39002: invalid operationORA-31694: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unloadORA-02354: error in exporting/importing dataORA-39776: fatal Direct Path API error loading table "SYS"."SYS_SQL_FILE_FULL_01"ORA-12953: The request exceeds the maximum allowed database size of 11 GB-- CREATE TABLE "PICCPROD"."T_POLICY_CHECK_ITEM" ("ITEM_ID" NUMBER(10,0) NOT NULL ENABLE,"CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "CHECK_NUM" NUMBER(4,0) NOT NULL ENABLE, "NORMAL" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE, "NOTES" VARCHAR2(1000 BYTE), "CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INORA-39171: Job is experiencing a resumable wait.ORA-12953: The request exceeds the maximum allowed database size of 11 GB为何impdp，提取ddl语句也报错，因为数据泵导入会在数据库内，创建相关对象，占用临时存储，数据库都满了，因此即使不导入数据，临时创建个对象都是报错的

impdp \'/ as sysdba\' dumpfile=PICCPROD%U.dmp directory=dump logfile=picc.log REMAP_TABLESPACE=LIFELOB_DATA:LIFEDATA_T_L,LIFEINDEX_T_L:LIFEDATA_T_LTRANSFORM = SEGMENT_ATTRIBUTES:N

TRANSFORM = SEGMENT_ATTRIBUTES:N 参数作用何在，有什么特性实验测试：  1.创建一个测试表a, 创建主键，创建一个索引2.对对象进行元数据导出3.实验参数，进行导入4.查询及验证表及索引是否存在5.查询表及索引的段是否分配6.impdp sqlfile 查询与表 and  索引 get ddl 差异

1.创建一个测试表a, 创建主键，创建一个索引SQL> create table yang.ceshi as select * from scott.emp;SQL> alter table yang.ceshi add constraint pk_id primary key(empno);SQL> create index yang.id2 on  yang.ceshi(deptno);          2.对对象进行元数据导出expdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang.log SCHEMAS=yang include=TABLE:"IN('CESHI')" CONTENT=METADATA_ONLY3.实验参数，进行导入impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott4.查询及验证表及索引是否存在select object_name,object_type,status from user_objects where created>sysdate-1OBJECT_NAME OBJECT_TYPE STATUS-------------------- ------------------- -------ID2 INDEX VALIDPK_ID INDEX VALIDCESHI TABLE VALID5.查询表及索引的段是否分配SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1);no rows selected 6.impdp sqlfile 查询与表 and  索引 get ddl 差异[oracle@t2 tools]$ impdp \'/ as sysdba\' directory=dump dumpfile=yang%u.dmp logfile=yang1.log TRANSFORM = SEGMENT_ATTRIBUTES:N REMAP_SCHEMA=yang:scott sqlfile=test_table.sql--查询创建语法，进行说明：  第一部分 alter session level trace文件第二部分，创建表语法，没有了storage语法第三部分，创建索引，对索引取消并行，在真实导入数据过程中，parallel并行能帮助快速创建索引，创建索引后数据泵自动noparallel第四部分，导入完毕后，对数据泵临时创建对象进行清理[oracle@t2 tools]$ more test_table.sql -- CONNECT SYS ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';-- new object type path: SCHEMA_EXPORT/TABLE/TABLECREATE TABLE "SCOTT"."CESHI" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10 BYTE), "JOB" VARCHAR2(9 BYTE), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) ;-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX-- CONNECT SCOTTCREATE UNIQUE INDEX "SCOTT"."PK_ID" ON "SCOTT"."CESHI" ("EMPNO") ;ALTER INDEX "SCOTT"."PK_ID" NOPARALLEL;CREATE INDEX "SCOTT"."ID2" ON "SCOTT"."CESHI" ("DEPTNO") ;ALTER INDEX "SCOTT"."ID2" NOPARALLEL;-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT-- CONNECT SYSALTER TABLE "SCOTT"."CESHI" ADD CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")USING INDEX "SCOTT"."PK_ID" ENABLE;-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSDECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'PK_ID'; i_o := 'SCOTT';EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,14,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:17:22',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END; /DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGINDELETE FROM "SYS"."IMPDP_STATS"; i_n := 'ID2'; i_o := 'SCOTT'; EXECUTE IMMEDIATE stmt USING 2,I_N,NV,NV,I_O,14,1,3,1,1,1,0,14,NV,NV,TO_DATE('2018-11-29 15:18:08',df),NV;DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"'); DELETE FROM "SYS"."IMPDP_STATS"; END; /---查询表的ddl语法select dbms_metadata.get_ddl('TABLE','CESHI','SCOTT') ddl_text from dualDDL_TEXT--------------------------------------------------------------------------------CREATE TABLE "SCOTT"."CESHI"( "EMPNO" NUMBER(4,0),"ENAME" VARCHAR2(10),"JOB" VARCHAR2(9),"MGR" NUMBER(4,0),"HIREDATE" DATE,"SAL" NUMBER(7,2),"COMM" NUMBER(7,2),"DEPTNO" NUMBER(2,0),CONSTRAINT "PK_ID" PRIMARY KEY ("EMPNO")USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICSTABLESPACE "USERS" ENABLE) SEGMENT CREATION DEFERREDPCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGINGTABLESPACE "USERS"--表不存在storage属性后insert 数据进行测试SQL> insert into scott.ceshi select * from scott.emp;--查询表及索引的extent分配，发现表其实并没有extent storage属性，是怎么分配的呢？SQL> select SEGMENT_NAME,SEGMENT_TYPE,BLOCKS,EXTENTS,INITIAL_EXTENT/1024 INITIAL_EXTENT_K,NEXT_EXTENT/1024 NEXT_EXTENT_K from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1);SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS INITIAL_EXTENT_K NEXT_EXTENT_K--------------- ------------------ ---------- ---------- ---------------- -------------             CESHI                        TABLE                8           1                          64                                1024              PK_ID                         INDEX               8           1                          64                                 1024                ID2                             INDEX              8           1                           64                                 1024--查询表空间属性SQL> select tablespace_name from user_segments where segment_name in(select object_name from user_objects where created>sysdate-1) group by tablespace_name;TABLESPACE_NAME------------------------------USERSSQL> select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT/1024 INITIAL_EXTENT_K,NEXT_EXTENT/1024 NEXT_EXTENT_K,SEGMENT_SPACE_MANAGEMENT,ALLOCATION_TYPE,EXTENT_MANAGEMENT from dba_tablespaces where tablespace_name='USERS';TABLESPACE BLOCK_SIZE INITIAL_EXTENT_K NEXT_EXTENT_K SEGMEN ALLOCATIO EXTENT_MAN---------- ---------- ---------------- ------------- ------ --------- ----------USERS 8192 64 AUTO SYSTEM LOCAL  --表空间本地管理表空间，系统自动扩展分配， 8K,1M,8M,64M--可见，对于表来说，如果未配置storage属性，则表使用表空间的默认storage属性分配extents 大小