[Oracle] Register MVIEW manually

1. Check if MVIEW is already registered.

 select * from dba_registered_mviews;  

2. Register MVIEW if it doesn't exist in DBA_REGISTERED_MVIEWS

 begin  
 dbms_mview.register_mview ( 'TSTCBF' , 'ADDRESS_EX' , 'TST', 204, DBMS_MVIEW.REG_PRIMARY_KEY_MVIEW,  
 'select Address_Ex_ID Tracking_ID, Unique_Address_FK Address_ID, Last_Mod_Date Last_Mod_Date from ADDRESS_EX_TBL@BAS',  
 DBMS_MVIEW.REG_V8_SNAPSHOT);  
 end;  

As input parameters use package specific constants where necessary, as mentioned in documentation.

DBMS_MVIEW.REG_PRIMARY_KEY_MVIEW

DBMS_MVIEW.REG_V8_SNAPSHOT

More about sys.dbms_mview package here

Oracle data pump export/import

Standard Oracle export/import tools are damn usefull and fast.

To export entire schema as sys user you need only to specify directory object (more about directory) , dump file name, schema name. Generated file is binary, investigate log file to be sure that export was succesful.

for example:

 expdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS  

As far as OFMDEVPH1_MDS_new_2.dmp is binary you can access file content using impdp tool.

 impdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS SQLFILE=user_OFM_MDS.sql INCLUDE=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA  

option SQLFILE tells to generate sql file with all ddls in text format. insted of performing import.
INCLUDE is saying that generated file user_OFM_MDS.sql will be only containing specified categories of objects.
content of generated file:

 -- CONNECT SYS  
 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  
 -- CONNECT SYSTEM  
 CREATE USER "OFMDEVPH1_MDS" IDENTIFIED BY VALUES 'S:E2ED5B8A25;FB42494E4D316374'  
    DEFAULT TABLESPACE "OFMDEVPH1_MDS"  
    TEMPORARY TABLESPACE "OFMDEVPH1_IAS_TEMP";  
 -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT  
 GRANT CREATE TYPE TO "OFMDEVPH1_MDS";  
 GRANT CREATE PROCEDURE TO "OFMDEVPH1_MDS";  
 GRANT CREATE SEQUENCE TO "OFMDEVPH1_MDS";  
 GRANT CREATE TABLE TO "OFMDEVPH1_MDS";  
 -- new object type path: SCHEMA_EXPORT/ROLE_GRANT  
 GRANT "CONNECT" TO "OFMDEVPH1_MDS";  
 -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE  
 ALTER USER "OFMDEVPH1_MDS" DEFAULT ROLE ALL;  
 -- new object type path: SCHEMA_EXPORT/TABLESPACE_QUOTA  
 DECLARE  
  TEMP_COUNT NUMBER;  
  SQLSTR VARCHAR2(200);  
 BEGIN  
  SQLSTR := 'ALTER USER "OFMDEVPH1_MDS" QUOTA UNLIMITED ON "OFMDEVPH1_MDS"';  
  EXECUTE IMMEDIATE SQLSTR;  
 EXCEPTION  
  WHEN OTHERS THEN  
   IF SQLCODE = -30041 THEN  
    SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES  
        WHERE TABLESPACE_NAME = ''OFMDEVPH1_MDS'' AND CONTENTS = ''TEMPORARY''';  
    EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;  
    IF TEMP_COUNT = 1 THEN RETURN;  
    ELSE RAISE;  
    END IF;  
   ELSE  
    RAISE;  
   END IF;  
 END;  
 /  

In this way you can review entire dump file. For me it was just matter of curiosity, how passwords are presented.

for importing dump file use:

 impdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS  

Good article for further reading: Oracle Data Pump Quick Start