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

This article introduces the analysis of 12c data punp clone users, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Sometimes there is a need for a production environment to move the user and its objects and data to another database for testing and to rename the user. At this time, the remap_schema parameters in the data pump can be used to deal with.

The data pump catalog has been created: dp_dir

Data pump user has been created: dp/dp permission is dba

The test user is hr

1. Export everything from schema to HR

[oracle@snow ~] $expdp dp/dp directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hr

Export: Release 12.1.0.1.0-Production on Mon Feb 9 15:21:47 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "DP". "SYS_EXPORT_SCHEMA_01": dp/* directory=dp_dir dumpfile=hr.dmp logfile=hr.log schemas=hr

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

. . Exported "HR". "COUNTRIES" 6.437 KB 25 rows

. . Exported "HR". "DEPARTMENTS" 7.101 KB 27 rows

. . Exported "HR". "EMPLOYEES" 17.06KB 107rows

. . Exported "HR". "JOBS" 7.085 KB 19 rows

. . Exported "HR". "JOB_HISTORY" 7.171 KB 10 rows

. . Exported "HR". "LOCATIONS" 8.414 KB 23 rows

. . Exported "HR". "REGIONS" 5.523 KB 4 rows

Master table "DP". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

*

Dump file set for DP.SYS_EXPORT_SCHEMA_01 is:

/ home/oracle/hr.dmp

Job "DP". "SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 9 15:22:17 2015 elapsed 0 00:00:30

Import everything whose schema is HR and rename it hr_new. If there is no hr_new user in the database at this time, the data pump is created automatically.

[oracle@snow ~] $impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new

Import: Release 12.1.0.1.0-Production on Mon Feb 9 15:23:13 2015

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0-64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Master table "DP". "SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DP". "SYS_IMPORT_FULL_01": dp/* directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "HR_NEW". "COUNTRIES" 6.437 KB 25 rows

. . Imported "HR_NEW". "DEPARTMENTS" 7.101 KB 27 rows

. . Imported "HR_NEW". "EMPLOYEES" 17.06KB 107rows

. . Imported "HR_NEW". "JOBS" 7.085 KB 19 rows

. . Imported "HR_NEW". "JOB_HISTORY" 7.171 KB 10 rows

. . Imported "HR_NEW". "LOCATIONS" 8.414 KB 23 rows

. . Imported "HR_NEW". "REGIONS" 5.523 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

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

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Job "DP". "SYS_IMPORT_FULL_01" successfully completed at Mon Feb 9 15:23:27 2015 elapsed 0 00:00:12

If you only need metadata and do not need data, you can use the content=metadata_only parameter to implement

[oracle@snow ~] $impdp dp/dp directory=dp_dir dumpfile=hr.dmp remap_schema=hr:hr_new_2 content=metadata_only

Verify the cloning results

[oracle@snow ~] $sqlplus / as sysdba

SYS@ora12c > select username from dba_users where username like 'HR%'

USERNAME

HR

HR_NEW

HR_NEW_2

Log in to a user who uses the newly cloned hr_new_2 and contains only metadata with the same password as the cloned user is also hr

SYS@ora12c > conn hr_new_2/hr

Connected.

HR_NEW_2@ora12c > col tname for A20

HR_NEW_2@ora12c > select * from tab

TNAME TABTYPE CLUSTERID

EMP_DETAILS_VIEW VIEW

REGIONS TABLE

LOCATIONS TABLE

DEPARTMENTS TABLE

JOBS TABLE

EMPLOYEES TABLE

JOB_HISTORY TABLE

COUNTRIES TABLE

8 rows selected.

The tables have been created, but there is no data in the table, as we expected!

HR_NEW_2@ora12c > select count (*) from employees

COUNT (*)

-

0

This is the end of the analysis of 12c data punp cloning users. I hope the above content can be helpful to you and 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.