数据泵导出导入物化视图（ORA

[oracle@OCPLHR ~]$ impdp system/lhr dumpfile=mview3.dmp sqlfile=c.txt

Import: Release 11.2.0.3.0 - Production on Wed May 30 11:42:48 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

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

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

Starting "SYSTEM"."SYS_SQL_FILE_FULL_01":  system/******** dumpfile=mview3.dmp sqlfile=c.txt

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

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

Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW

Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG

Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 11:42:53

[oracle@OCPLHR ~]$ cd /u01/app/oracle/admin/OCPLHR2/dpdump

[oracle@OCPLHR dpdump]$ cat c.txt

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: SCHEMA_EXPORT/USER

CREATE USER "LHR" IDENTIFIED BY VALUES 'S:B8183DC121F881C2FA1B308FC6F7ED3ED020707C59062FB3EC22F461E886;157AE4BCFD41976D'

      DEFAULT TABLESPACE "USERS"

      TEMPORARY TABLESPACE "TEMP";

-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT

GRANT CREATE MATERIALIZED VIEW TO "LHR";

GRANT UNLIMITED TABLESPACE TO "LHR";

-- new object type path: SCHEMA_EXPORT/ROLE_GRANT

GRANT "DBA" TO "LHR";

-- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE

ALTER USER "LHR" DEFAULT ROLE ALL;

-- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT LHR

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'OCPLHR1', inst_scn=>'1380545');

COMMIT;

END;

/

-- new object type path: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYSTEM

。。。。。。。。。。。。。。

CREATE TABLE "LHR"."TEST_MV"

   (    "OBJECT_ID" NUMBER NOT NULL ENABLE,

        "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

。。。。。。。。。。。。。。

CREATE TABLE "LHR"."TEST_MV_LHR"

   (    "OBJECT_ID" NUMBER NOT NULL ENABLE,

        "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS" ;

。。。。。。。。。。

-- new object type path: SCHEMA_EXPORT/TABLE/COMMENT

COMMENT ON TABLE "LHR"."MLOG$_TEST_MV"  IS 'snapshot log for master table LHR.TEST_MV';

COMMENT ON TABLE "LHR"."RUPD$_TEST_MV"  IS 'temporary updatable snapshot log';

COMMENT ON MATERIALIZED VIEW "LHR"."TEST_MV_LHR"  IS 'snapshot table for snapshot LHR.TEST_MV_LHR';

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX

-- CONNECT LHR

CREATE INDEX "LHR"."I_TU" ON "LHR"."TEST_UI" ("TABLE_NAME")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING

  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "EXAMPLE" PARALLEL 1 ;

  ALTER INDEX "LHR"."I_TU" NOPARALLEL;

  ALTER INDEX "LHR"."I_TU"  UNUSABLE;

-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

-- CONNECT SYSTEM

ALTER TABLE "LHR"."TEST_MV" ADD PRIMARY KEY ("OBJECT_ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE;

ALTER TABLE "LHR"."TEST_MV_LHR" ADD PRIMARY KEY ("OBJECT_ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE;

-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

-- new object type path: SCHEMA_EXPORT/MATERIALIZED_VIEW

CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 11:25:11', 0, 76926, '1950-01-01 12:00:00', '', 0, 1378477, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 62, 0, 0, 1378477, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV";

ALTER MATERIALIZED VIEW "LHR"."TEST_MV_LHR" COMPILE;

-- new object type path: SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG

-- CONNECT SYSTEM

CREATE MATERIALIZED VIEW LOG ON "LHR"."TEST_MV" WITH PRIMARY KEY USING ("MLOG$_TEST_MV", (10, 'OCPLHR1', 270434, '2018-05-30 11:25:11', '2018-05-30 11:25:11', '2018-05-30 10:56:34', '4000-01-01 00:00:00', '4000-01-01 00:00:00', '4000-01-01 00:00:00', 1, "OBJECT_ID", '2018-05-30 10:56:34', 2, 1, 62, '2018-05-30 11:25:11', 1378477, ("RUPD$_TEST_MV")));

[oracle@OCPLHR dpdump]$ ss