How to Find out / Identify the list of Schema / Users Available Inside a expdp data pump .dmp dumpfile


This is also another interesting question. Suppose if you get a dump from the source database and you are not aware of the users/schemas present in the dump. If you wanted to see the list of schemas/users present in the dump file, you can use this method to find out the same.
Step 1. Import the dump with sqlfile option.
This will create a file with all sql script inside the dump. The parameter value for the sqlfile would be the generated sql file.

Step 2: grep for the “CREATE USER” in the generated sqlfile. This will list out the Users/schemas present in the dump.

See Example Here
$ impdp directory=exp_dirdumpfile=exp_testuser1_all_tab.dmp logfile=imp_exp_testuser1_tab sqlfile=dumpScript.sql

Import: Release 11.2.0.3.0 - Production on Fri Apr 26 08:29:06 2013

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

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01": /******** AS SYSDBA directory=exp_dirdumpfile=exp_testuser1_all_tab.dmplogfile=imp_exp_user1_tab sqlfile=tables.sql
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 08:29:12

$ grep "CREATE USER" dumpScript.sql
CREATE USER "TESTUSER" IDENTIFIED BY VALUES 'S:270D559F9B97C21F1FEDF2B2D552AB105EA50F78507CD6EAC6AD63969E5E;BBE7786A584F9103'

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google