Shulou(Shulou.com)05/31 Report--

This article is about how expdp exports tablespaces. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Let's test it:

Create a tablespace, omitted here. I create several tables and create a partition table

The statement is as follows: the partition uses two table spaces, TEST and USERS

Click (here) to collapse or open

CREATE TABLE scott.obj_part

PARTITION BY RANGE (created)

(

PARTITION p20101231 VALUES LESS THAN (to_date ('20101231') tablespace test

PARTITION p20111231 VALUES LESS THAN (to_date ('20111231') tablespace test

PARTITION p20121231 VALUES LESS THAN (to_date ('20121231') tablespace users

PARTITION p20131231 VALUES LESS THAN (to_date ('20131231') tablespace users

PARTITION p20141231 VALUES LESS THAN (to_date ('20141231') tablespace users

PARTITION p20151231 VALUES LESS THAN (to_date ('20151231') tablespace users

PARTITION p20161231 VALUES LESS THAN (to_date ('20161231') tablespace users

PARTITION p20171231 VALUES LESS THAN (to_date ('20171231') tablespace users

)

As select * from dba_objects where 1: 0

Insert data:

Click (here) to collapse or open

SQL > insert into scott.obj_part select * from dba_objects

86383 rows created.

SQL > commit

Commit complete.

SQL > conn test/test

Connected.

SQL > insert into scott.obj_part select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,to_date ('2017-5-12 copyright copyright MMMMmurdd`), LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME from dba_objects

86383 rows created.

SQL > commit

Commit complete.

SQL > create index scott.objpart_id on scott.obj_part (object_id) tablespace test

Index created.

SQL > commit

Commit complete.

Perform export

Click (here) to collapse or open

[oracle@mystandby dump] $expdp\'/ as sysdba\ 'directory=sh_dmp dumpfile=exp_tablespace01.dmp logfile=exptablesp01.log tablespaces=test

Export: Release 11.2.0.4.0-Production on Thu Aug 10 20:40:29 2017

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, OLAP, Data Mining and Real Application Testing options

Starting "SYS". "SYS_EXPORT_TABLESPACE_01": "/ * AS SYSDBA" directory=sh_dmp dumpfile=exp_tablespace01.dmp logfile=exptablesp01.log tablespaces=test

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 36.12 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . Exported "SCOTT". "OBJ_PART": "P20131231" 8.358 MB 86200 rows

. . Exported "SCOTT". "OBJ_PART": "P20171231" 8.394 MB 86566 rows-Note that this table space is USERS table space

. . Exported "TEST". "TEST" 8.435 MB 87003 rows

. . Exported "TEST". "Tunable C" 3.035 MB 84748 rows

. . Exported "TEST". "T_ADV" 873.0 KB 100000 rows

. . Exported "TEST". "Thump" 83.70 KB 2870 rows

. . Exported "SCOTT". "OBJ_PART": "P20101231" 0 KB 0 rows

. . Exported "SCOTT". "OBJ_PART": "P20111231" 0 KB 0 rows

. . Exported "SCOTT". "OBJ_PART": "P20121231" 0 KB 0 rows

. . Exported "SCOTT". "OBJ_PART": "P20141231" 0 KB 0 rows

. . Exported "SCOTT". "OBJ_PART": "P20151231" 0 KB 0 rows

. . Exported "SCOTT". "OBJ_PART": "P20161231" 0 KB 0 rows

Master table "SYS". "SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

*

Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:

/ backup/dump/exp_tablespace01.dmp

Job "SYS". "SYS_EXPORT_TABLESPACE_01" successfully completed at Thu Aug 10 20:40:42 2017 elapsed 0 00:00:12

That is, when you export a tablespace using expdp, not only all the data in that tablespace is exported, but also the associated related object data is exported.

An official explanation is posted below:

Click (here) to collapse or open

TABLESPACES

Default: There is no default

Purpose

Specifies a list of tablespace names to be exported in tablespace mode.

Syntax and Description

TABLESPACES=tablespace_name [,...]

In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, then its dependent objects are also unloaded. Both object metadata and data are unloaded. If any part of a table resides in the specified set, then that table and all of its dependent objects are exported. Privileged users get all tables. Unprivileged users get only the tables in their own schemas

-general meaning

In tablespace schema, only the tables contained in the specified tablespace are exported. If you export a table, its dependents are also exported. Both object metadata and data will be exported. If any part of the table is saved in the specified tablespace, the table and all its dependent objects are exported. Privileged users get all tables, and unprivileged users can only get tables within their own permissions.

Filtering can restrict what is exported using this mode (see "Filtering During Export Operations")

Restrictions

The length of the tablespace name list specified for the TABLESPACES parameter is limited to a maximum of 4 MB, unless you are using the NETWORK_LINK to an Oracle Database release 10.2.0.3 or earlier or to a read-only database. In such cases, the limit is 4 KB.

Example

The following is an example of using the TABLESPACES parameter. The example assumes that tablespaces tbs_4, tbs_5, and tbs_6 already exist.

> expdp hr DIRECTORY=dpump_dir1 DUMPFILE=tbs.dmp

TABLESPACES=tbs_4, tbs_5, tbs_6

This results in a tablespace export in which tables (and their dependent objects) from the specified tablespaces (tbs_4, tbs_5, and tbs_6) will be unloaded.

Thank you for reading! This is the end of the article on "how to derive tablespaces from expdp". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.