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:
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.
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:
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:
Good article for further reading: Oracle Data Pump Quick Start
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.
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
