Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

The transform parameter of impdp removes tablespaces and storage clauses to solve the problem of incorrect import times or insufficient space.

2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

You can take a look at the official website, the address of the official website:

Http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300

Transform can be used in such scenarios:

To import a table in SLKTESTNEW mode to SH_SMCVDMS_SLK_OWSLK mode in another library.

The actual size of the object in this mode:

SQL > select sum (bytes) / 1024 plus 1024 from dba_segments where owner=upper ('SLKTESTNEW')

SUM (BYTES) / 1024 Compact 1024

-

11747.25-very large, block under all hwm will be calculated

Export statement:

Expdp system/1111sys directory=dmpdata SCHEMAS=SLKTESTNEW dumpfile=owslk20110630.dmp logfile=owslk20110630.log

Dmp size after exporting the data:

$du-m OWSLK20110630.DMP

80.63 OWSLK20110630.DMP-small because expdp only exports valid block

If you do not add transform and the imported tablespace is less than 11747.25 calculated earlier, an error will be reported:

ORA-01659: unable to allocate MINEXTENTS beyond 14 in tablespace SH_SMCVDMS_SLK_OWSLK_DATA01 .

There is no need to add 11G space for 80m of data. Use the transform option of impdp to implement the import.

The transform option of impdp determines whether to remove the various storage parameters of the ddl statement

Purpose

Enables you to alter object creation DDL for specific objects, as well as for all applicable objects being loaded.

Metadata transform to apply to applicable objects. Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE,OID, and PCTSPACE

Syntax and Description

TRANSFORM = transform_name:value [: object_type]

For example: transform=storage:n:table, if the storage parameter of ddl is removed, it is only valid for table. If there is no table clause, it is valid for all objects, such as index.

After the sql_file option is added, it only simulates the import process and forms a sql script, which is not really imported. Through the script, you can see if the storage option exists and whether the segment_attributes exists.

Using transform can reduce the storage requirements for imported data. For example, if the source data needs 30G tablespace, but only 300m after the data is exported, you do not need to allocate 30G when importing. Because transform works, storage is removed.

In particular, it is pointed out that there is a new parameter above 11.2.0.2, SEGMENT_CREATION, and if it is set to n, the ddl statement does not contain the SEGMENT CREATION IMMEDIATE field.

Transform=storage:n

Transform=oid:n

Transform=segment_attributes:n

TRANSFORM=SEGMENT_CREATION:n

For example:

No transform specified. Import the resulting dll.

CREATE TABLE "SH_SMCVDMS_SLK_OWSLK". "DE_DEMESSAGEIDMAP"

"DEMESSAGEID" CHAR (40) NOT NULL ENABLE

..

"UPDATE_BY" CHAR (10)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING

STORAGE (INITIAL 2952790016 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645-this is not necessary to initialize such a large space first. Import will be terminated if there is not enough space.

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE "SH_SMCVDMS_SLK_OWSLK_DATA01"

Joined transform=storage:n

CREATE TABLE "SH_SMCVDMS_SLK_OWSLK". "DE_DEMESSAGEIDMAP"

"DEMESSAGEID" CHAR (40) NOT NULL ENABLE

..

"UPDATE_BY" CHAR (10)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING

TABLESPACE "SH_SMCVDMS_SLK_OWSLK_DATA01";-remove the storage section so that you don't need so much space, according to the database default initial 64k.

Joined transform=segment_attributes:n

CREATE TABLE "SH_SMCVDMS_SLK_OWSLK". "DE_DEMESSAGEIDMAP"

"DEMESSAGEID" CHAR (40) NOT NULL ENABLE

..

"UPDATE_BY" CHAR (10)

);-this is very clean. Create table uses the default storage parameters of the database, and the tablespace uses the user default tablespace.

-simulate import to form a sql script:

Impdp system/1111directory=dmp transform=storage:n REMAP_SCHEMA= "SLKTESTNEW:sh_smcvdms_slk_owslk" remap_tablespace= "SH_DMS_SLK_OWSGM_DATA01:sh_smcvdms_slk_owslk_data01" remap_tablespace= "SH_DMS_SLK_OWSGM_LOB01:SH_SMCVDMS_SLK_OWSLK_LOB01" dumpfile=OWSLK20110630.DMP logfile=owslk20110702_storage_n.log sqlfile=owslk0702_storage_n.sql

-Real Import:

Impdp system/1111 directory=dmp transform=storage:n REMAP_SCHEMA= "SLKTESTNEW:sh_smcvdms_slk_owslk" remap_tablespace= "SH_DMS_SLK_OWSGM_DATA01:sh_smcvdms_slk_owslk_data01" remap_tablespace= "SH_DMS_SLK_OWSGM_LOB01:SH_SMCVDMS_SLK_OWSLK_LOB01" dumpfile=OWSLK20110630.DMP logfile=owslk20110702_storage_n.log table_exists_action=replace

= end=

Impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql transform=segment_attributes:n

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 276

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report