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

This article is about how to use the TRANSFORM option to remove tablespaces and storage clauses. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it.

When you use the IMPDP tool to complete the data import, the data import is completed according to the stored parameter information in the dump file. In many cases, we want to import the data according to the default parameters of the imported user, which can be assisted by the TRANSFORM parameter of IMPDP.

TRANSFORM parameter description of 1.IMPDP

Secooler@secDB / expdp$ impdp help=y

…… Omit.

TRANSFORM

Metadata transform. To apply to applicable objects.

Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

…… Omit.

two。 Create a test table T

Sec@11gR2 > create table t (x varchar2 (8))

Table created.

Sec@11gR2 > insert into t values ('secooler')

1 row created.

Sec@11gR2 > commit

Commit complete.

3. Use EXPDP to generate logical backup files for Table T

Secooler@secDB / expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t

Export: Release 11.2.0.1.0-Production on Thu May 13 09:32:44 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

Starting "SEC". "SYS_EXPORT_TABLE_01": sec/* directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . Exported "SEC". "T" 5.007 KB 1 rows

Master table "SEC". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded

*

Dump file set for SEC.SYS_EXPORT_TABLE_01 is:

/ expdp/sec_expdp.dmp

Job "SEC". "SYS_EXPORT_TABLE_01" successfully completed at 09:32:57

4. Generate SQL statements in dump files

1) generate a SQL creation statement using the SQLFILE parameter

Secooler@secDB / expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql

Import: Release 11.2.0.1.0-Production on Thu May 13 09:33:23 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

Master table "SEC". "SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SEC". "SYS_SQL_FILE_FULL_01": sec/* directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SEC". "SYS_SQL_FILE_FULL_01" successfully completed at 09:33:26

2) check the sec_expdp.sql file to get the SQL creation statement

Secooler@secDB / expdp$ cat sec_expdp.sql

-- CONNECT SEC

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 199'

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "SEC".

("X" VARCHAR2 (8 BYTE)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TBS_SEC_D"

It can be seen that this time contains a large number of storage parameters and tablespace parameters.

5. Use TRANSFORM to drop tablespaces and storage clauses

Secooler@secDB / expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n

Import: Release 11.2.0.1.0-Production on Thu May 13 09:34:12 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

Oracle Database Vault and Real Application Testing options

Master table "SEC". "SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SEC". "SYS_SQL_FILE_FULL_01": sec/* directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SEC". "SYS_SQL_FILE_FULL_01" successfully completed at 09:34:14

Look at the generated piercing SQL statement again:

Secooler@secDB / expdp$ cat sec_expdp.sql

-- CONNECT SEC

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 199'

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "SEC".

("X" VARCHAR2 (8 BYTE)

)

The table T creation statement generated at this time is very brief, yes, it is that simple.

6. Summary

Using the TRANSFORM option, you can accomplish the purpose of removing tablespaces and storing clauses, so that we can control the default parameters by target on import.

Our goal: all tasks to be accomplished should be under our control, UNDER CONTROL!

The above is how to use the TRANSFORM option to remove tablespaces and storage clauses. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.