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

Editor to share with you how impdp automatically create user prerequisites and application scenarios, I believe most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's learn about it!

The impdp command automatically creates a user if the user exists when importing data, because the dmp file exported by expdp contains script information for creating the user (including password, default tablespace, temporary tablespace, etc.).

There is a prerequisite for impdp to automatically create users, that is, the default and temporary tablespaces of users need to be created first. If the default or temporary tablespaces do not exist, automatic user creation will fail, resulting in the failure of data import.

Here is an experiment to describe the prerequisites and application scenarios for impdp to automatically create users.

[@ more@]

one。 Create tablespaces and users

SQL > create tablespace aidu datafile'/ oradata/gridctl/aidu01.dbf' size 128m extent management local segment space management auto logging

Tablespace created.

SQL > create temporary tablespace temp2 tempfile'/ oradata/gridctl/temp021.dbf' size 128m extent management local

Tablespace created.

SQL > create user aidu profile default identified by "aidutest" default tablespace aidu temporary tablespace temp2 account unlock

User created.

SQL > grant resource,connect to aidu

Grant succeeded.

SQL > conn aidu/aidutest

Connected.

SQL > create table test (id number (10) not null,name varchar2 (20))

Table created.

SQL > insert into test values (1)

1 row created.

SQL > insert into test select id+1,name from test

1 row created.

SQL > insert into test select id+2,name from test

2 rows created.

SQL > select * from test

ID NAME

--

1 first

2 first

3 first

4 first

SQL > commit

Commit complete.

two。 Create DIRECTORY to export user's data

# # establishing directory for expdp,impdp

SQL > create directory impdp as'/ oradata/gridctl'

SQL > grant read,write on directory impdp to aidu

[oracle@primarydb ~] $expdp system/* schemas=aidu directory=impdp dumpfile=aidu2.dmp

Export: Release 10.2.0.4.0-64bit Production on Friday, 11 February, 2011 8:36:18

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM". "SYS_EXPORT_SCHEMA_01": system/* schemas=aidu directory=impdp dumpfile=aidu.dmp

Estimate in progress using BLOCKS method...

.

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . Exported "AIDU". "TEST" 5.304 KB 4 rows

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

*

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/ oradata/aidu.dmp

Job "SYSTEM". "SYS_EXPORT_SCHEMA_01" successfully completed at 08:36:36

[oracle@primarydb oradata] $ls-lt aidu*

-rw-r- 1 oracle oinstall 155648 Feb 11 08:36 aidu.dmp

three。 Delete users, delete user tablespaces and temporary tablespaces

SQL > drop user aidu cascade

User dropped.

SQL > drop tablespace aidu including contents

Tablespace dropped.

SQL > drop tablespace temp2 including contents

Tablespace dropped.

SQL > exit

four。 Import user data and test whether users can be created automatically

[oracle@primarydb oradata] $impdp system/* directory=impdp dumpfile=aidu.dmp

.

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

Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/* directory=impdp dumpfile=aidu2.dmp

Processing object type SCHEMA_EXPORT/USER

ORA-39083: Object type USER failed to create with error:

ORA-00959: tablespace 'AIDU' does not exist

Failing sql is:

CREATE USER "AIDU" IDENTIFIED BY VALUES 'FBF36F881A20141D' DEFAULT TABLESPACE "AIDU" TEMPORARY TABLESPACE "TEMP2"

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

ORA-39083: Object type SYSTEM_GRANT failed to create with error:

ORA-01917: user or role 'AIDU' does not exist

Failing sql is:

GRANT UNLIMITED TABLESPACE TO "AIDU"

.

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39083: Object type TABLE failed to create with error:

ORA-01918: user 'AIDU' does not exist

Failing sql is:

CREATE TABLE "AIDU". "TEST" ("ID" NUMBER (10p0) NOT NULL ENABLE, "NAME" VARCHAR2 (20) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "AIDU"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Job "SYSTEM". "SYS_IMPORT_FULL_01" completed with 7 error (s) at 09:26:51

You can see that when the user was created automatically, the user creation failed because the user tablespace and temporary tablespace did not exist, and an error was reported as follows:

Failing sql is:

CREATE USER "AIDU" IDENTIFIED BY VALUES 'FBF36F881A20141D' DEFAULT TABLESPACE "AIDU" TEMPORARY TABLESPACE "TEMP2"

The author has tried to establish only user tablespace aidu, but not to establish temporary tablespace, tried to import user data, but failed to create users automatically. So both user and temporary tablespaces need to exist before import.

five。 Create user and temporary tablespaces to prepare for data import

SQL > create tablespace aidu datafile'/ oradata/gridctl/aidu01.dbf' size 128m reuse extent management local segment space management auto logging

Tablespace created.

SQL > create temporary tablespace temp2 tempfile'/ oradata/gridctl/temp021.dbf' size reuse 128m extent management local

Tablespace created.

six。 Use impdp to import user data and automatically create users successfully.

[oracle@primarydb oradata] $impdp system/aidu2010 directory=impdp dumpfile=aidu.dmp

.

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

Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/* directory=impdp dumpfile=aidu.dmp

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/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . Imported "AIDU". "TEST" 5.304 KB 4 rows

Job "SYSTEM". "SYS_IMPORT_FULL_01" successfully completed at 08:49:42

[oracle@primarydb oradata] $sqlplus aidu/aidutest

SQL > select * from test

ID NAME

--

1 first

2 first

3 first

4 first

Summary:

Impdp can automatically create users, but there is a prerequisite: users' default and temporary tablespaces must be created (exist) first.

Use impdp to automatically create user application scenarios:

1. The database data is migrated or upgraded to another database environment, and there are many users in the database. DBA can not know the password of each user, but can only find the user's default table space and temporary table space.

2.DBA cannot reset users' passwords. Resetting passwords will lead to many old applications that need to be configured.

3. The IP,PORT of the new database is the same as the old one, and the application system does not need to be modified.

Creating users through impdp can retain their password information, and the application system does not need to be configured.

Attach SQL statements that query the user's default and temporary tablespaces:

SQL > select username,default_tablespace,temporary_tablespace from dba_users

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-

AIDU AIDU TEMP2

OUTLN SYSTEM TEMPTS1

SYS SYSTEM TEMPTS1

SYSTEM SYSTEM TEMPTS1

DBSNMP SYSAUX TEMPTS1

MGMT_VIEW MGMT_TABLESPACE TEMPTS1

SYSMAN MGMT_TABLESPACE TEMPTS1

WMSYS SYSAUX TEMPTS1

TSMSYS USERS TEMPTS1

DIP USERS TEMPTS1

ORACLE_OCM USERS TEMPTS1

The above is all the content of the article "how impdp automatically creates user prerequisites and application scenarios". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to 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.