Oracle 11g用impdp还原数据库

来源：这里教程网时间：2026-03-03 18:05:25 作者：

Oracle 11g用impdp还原数据库:

oracle用户下：
export ORACLE_SID=WMSTEST
sqlplus / as sysdba
create or replace directory scprd as '/data/oracle';
grant read,write on directory scprd to system;

CREATE TABLESPACE WMS_USER DATAFILE
'/data/oracle/oradata/WMS_USER.ora' SIZE 1450M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
'/data/oracle/oradata/WMS_USER01.ora' SIZE 32767M AUTOEXTEND OFF,
'/data/oracle/oradata/WMS_USER02.ora' SIZE 32767M AUTOEXTEND OFF,
'/data/oracle/oradata/WMS_USER03.ora' SIZE 32767M AUTOEXTEND OFF,
'/data/oracle/oradata/WMS_USER04.ora' SIZE 32767M AUTOEXTEND OFF,
'/data/oracle/oradata/WMS_USER05.ora' SIZE 32767M AUTOEXTEND OFF,
'/data/oracle/oradata/WMS_USER06.ora' SIZE 12767M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create temporary tablespace WMS_USER_TEMP tempfile '/data/oracle/oradata/WMSTEST/WMS_USER_TEMP.ora' size 1024m autoextend on NEXT 100M MAXSIZE UNLIMITED;

create user WMS_USER identified by WWW default tablespace WMS_USER temporary tablespace WMS_USER_TEMP;
grant create session,create table,create view,create sequence,unlimited tablespace to WMS_USER;
grant resource,connect to WMS_USER;
grant dba to WMS_USER;

[oracle@e4hg3iwmyzZ oracle]$ impdp system/www DIRECTORY=SCPRD dumpfile=tablespace_WMSPROD.dmp schemas=WMS_USER logfile=WMS_USER_imp.log CONTENT=ALL PARALLEL=4 TABLE_EXISTS_ACTION=TRUNCATE exclude=statistics
Import: Release 11.2.0.4.0 - Production on Wed Nov 9 17:08:47 2022
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
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** DIRECTORY=SCPRD dumpfile=tablespace_WMSPROD.dmp schemas=WMS_USER logfile=WMS_USER_imp.log CONTENT=ALL PARALLEL=4 TABLE_EXISTS_ACTION=TRUNCATE exclude=statistics
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"WMS_USER" already exists
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "WMS_USER"."DOC_ORDER_DETAILS"              5.029 GB 17651032 rows
...
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"WMS_USER"."SPASNUDF_PYDRK" created with compilation warnings
...
ORA-39082: Object type ALTER_PROCEDURE:"WMS_USER"."SPSOUDF_YJDF" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"WMS_USER"."V_EXPRESS_ERROR_SF" created with compilation warnings
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-01435: user does not exist
Failing sql is:
CREATE MATERIALIZED VIEW "WMS_USER"."SEC_SYS_USER" ("USER_ID", "USER_NAME", "PASSWORD", "ACTIVE_FLAG", "SALT", "ROLE_ID", "ORG_ID", "PRICEACCESS", "USER_LEVEL", "USER_TITLE", "USER_TEL", "USER_ZIP", "USER_ADDRESS1", "USER_ADDRESS2", "USER_ADDRESS3", "USER_ADDRESS4", "USER_EMAIL", "ADDTIME", "ADDWHO", "EDITTIME", "EDITWHO", "GLOBALDATEFORMAT", "CARDNO") USING ("SEC_SYS_USER", (10, 'WMSPROD', 1
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 269 error(s) at Wed Nov 9 18:05:52 2022 elapsed 0 00:57:04

完成。