impdp导⼊表结构和表数据_impdp导⼊表不创建segments 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_objects2)进⾏导出
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=system
笔记本电池无法充电EXCLUDE=SEQUENCE,TRIGGER,REF_CONSTRAINT,CONSTRAINT,PROCDEPOBJ CONTENT=METADATA_ONLY cluster=n parallel=2Processingobject type SCHEMA_EXPORT/USER
Processingobject type SCHEMA_EXPORT/SYSTEM_GRANT
Processingobject type SCHEMA_EXPORT/ROLE_GRANT
Processingobject type SCHEMA_EXPORT/DEFAULT_ROLE
Processingobject type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processingobject type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processingobject type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processingobject type SCHEMA_EXPORT/TABLE/TABLE
Processingobject type SCHEMA_EXPORT/TABLE/PRE_TABLE_ACTION
Processingobject type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processingobject type SCHEMA_EXPORT/TABLE/COMMENT
Processingobject type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processingobject type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processingobject type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processingobject type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processingobject type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processingobject type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processingobject type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processingobject type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processingobject type SCHEMA_EXPORT/VIEW/VIEW
Processingobject type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processingobject type SCHEMA_EXPORT/VIEW/COMMENT
Processingobject type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processingobject type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processingobject type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processingobject type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table"SYS"."SYS_EXPORT_SCHEMA_02" successfully
loaded/unloaded******************************************************************************Dump fileset for
SYS.SYS_EXPORT_SCHEMA_02 is:/home/oracle/tools/system01.dmp
Job"SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Thu Nov 29 14:48:14 2018 elapsed 0 00:00
3)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------------------------------SYSAUX
SYSTEM--⽬标端创建表空间
crate tablesapce SYSTEM datafile'/u01/app/oracle/oradata/XE/system.dbf'size 10m uniform size 128k autoextend on next 30m maxsize 10g;
sqlplusassysdba
create directories abcas '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 intablespace 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 40IN
ORA-39171: Job isexperiencing a resumable wait.
ORA-12953: The request exceeds the maximum allowed database size of 11GB--搜索MOS 提⽰，学习XE类 Express Edition (简化版本，数据库允许最⼤11g)
ORA-12953: The request exceeds the maximum allowed database size of 11 GB (Doc ID 2414879.1)
[Release11.2 to 12.2]
Thisis an Oracle Database XE environment, and Oracle Database XE has a limitation of 11GB 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 fromdba_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-------------------- ------------------
9999TABLE
初中数学总复习dump⽂件⼤⼩182，导⼊后，表⼤⼩9G5月几号是母亲节
oracle@c46bf408bfad:~/dmpfile$ ls
穿越重生洪荒类小说PICCPROD01.dmp picc.log
oracle@c46bf408bfad:~/dmpfile$ du -sm PICCPROD01.dmp182PICCPROD01.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 .5
select count(*) fromPICCPROD.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/1024BLOCKS--------------- -------------------- ---------- ---------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 128 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 144 128
16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 160 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 176 128 16尝试对表move(本次不再次对数据⽂件resize 进⾏分析，如果需要resize减少数据⽂件，需要从数据⽂件max_block_id 进⾏move，随后对数据⽂件进⾏resize)
alter table PICCPROD.T_DIAGNOSIS_TYPE move tablespace LIFEDATA_T_L;*ERROR at line1:
ORA-12953: The request exceeds the maximum allowed database size of 11GB*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_AUTO221312T_POLICY_PRODUCT309120drop 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/1024BLOCKS-------------------- -------------------- -------------------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 34720 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 34736 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 34752 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 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 datafile5resize 5000M;*ERROR at line1:
ORA-03297: file contains used data beyond requested RESIZE value--虽然数据为空，但是表对应的段，分配了存储，占⽤了存储*truncate table
SQL>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/1024BLOCKS-------------------- -------------------- -------------------- ---------- ----------LIFEDATA_T_L T_DIAGNOSIS_TYPE0 34720 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE1 34736 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE2 34752 128 16LIFEDATA_T_L T_DIAGNOSIS_TYPE3 34768 128 16
*数据库参数，延迟段创建
SQL>show parameter defer
NAME 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 operation
ORA-31694: master table "SYS"."SYS_SQL_FILE_FULL_01" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-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 11GB--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(1000BYTE),"CHARGE_FEE" NUMBER(10,2) DEFAULT 0 NOT NULL ENABLE) PCTFREE 10 PCTUSED 40IN
ORA-39171: Job isexperiencing a resumable wait.
ORA-12953: The request exceeds the maximum allowed database size of 11GB
为何impdp，提取ddl语句也报错，因为数据泵导⼊会在数据库内，创建相关对象，占⽤临时存储，数据库都满了，因此即使不导⼊数据，临时创建个对象都是报错的
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论