Oracle 更改架构和架构对象名称

SQL> create user demo identified by demo;

User created.

SQL>
SQL> grant resource, connect to demo;

Grant succeeded.

SQL> alter user demo quota 100m on users;

User altered.

SQL>
SQL> grant read, write on directory TEMP to demo;

Grant succeeded.

SQL>
SQL> connect demo/demo
Connected.

SQL>
SQL> create table demo.demo_t1 ( x int );

Table created.

SQL> create table demo.demo_t2 ( x int );

Table created.

SQL> create procedure demo.demo_proc1 is begin null; end;
  2  /

Procedure created.

SQL>
SQL> create or replace trigger demo.demo_t1_trg
  2  before insert or update on demo.demo_t1
  3  begin
  4    null;
  5  end;
  6  /

Trigger created.

SQL>
SQL>
SQL> create or replace trigger demo.demo_t2_trg
  2  before insert or update on demo_t2
  3  begin
  4    null;
  5  end;
  6  /

Trigger created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\hamcdc>
C:\Users\hamcdc>expdp userid=demo/demo directory=TEMP dumpfile=my_schema

Export: Release 12.2.0.1.0 - Production on Wed Jul 5 09:57:17 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DEMO"."SYS_EXPORT_SCHEMA_01":  userid=demo/******** directory=TEMP dumpfile=my_schema
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
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/TRIGGER
. . exported "DEMO"."DEMO_T1"                                0 KB       0 rows
. . exported "DEMO"."DEMO_T2"                                0 KB       0 rows
Master table "DEMO"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEMO.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\MY_SCHEMA.DMP
Job "DEMO"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Jul 5 09:57:45 2017 elapsed 0 00:00:27



SQL> drop user vr cascade;

User dropped.

SQL>
SQL> create user vr identified by vr;

User created.

SQL>
SQL> grant resource, connect to vr;

Grant succeeded.

SQL> alter user vr quota 100m on users;

User altered.

SQL>
SQL> grant read, write on directory TEMP to vr;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
C:\Users\hamcdc>
C:\Users\hamcdc>impdp userid=vr/vr directory=TEMP dumpfile=my_schema remap_table=demo_t1:vr_t1 remap_table=demo_t2:vr_t2 remap_schema=demo:vr

Import: Release 12.2.0.1.0 - Production on Wed Jul 5 10:06:06 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "VR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "VR"."SYS_IMPORT_FULL_01":  userid=vr/******** directory=TEMP dumpfile=my_schema remap_table=demo_t1:vr_t1 remap_table=demo_t2:vr_t2 remap_schema=demo:vr
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 "VR"."VR_T1"                                    0 KB       0 rows
. . imported "VR"."VR_T2"                                    0 KB       0 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39083: Object type TRIGGER:"VR"."DEMO_T1_TRG" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
CREATE EDITIONABLE TRIGGER "VR"."DEMO_T1_TRG"
before insert or update on demo.demo_t1
begin
  null;
end;

ORA-39083: Object type TRIGGER:"VR"."DEMO_T2_TRG" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
CREATE EDITIONABLE TRIGGER "VR"."DEMO_T2_TRG"
before insert or update on demo_t2
begin
  null;
end;

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "VR"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Wed Jul 5 10:06:08 2017 elapsed 0 00:00:01

SQL> conn vr/vr
Connected.

SQL> select object_name from obj;

OBJECT_NAME
--------------------------------------
DEMO_PROC1
VR_T1
VR_T2