CODEWITHSUNDEEP
oracle-databaseimpdp
wiegehtdasblos
wiegehtdasblos

Reputation: 11

How to analyze oracle dump file

I need to analyze a large Oracle DMP file. So far, I have no experience with Oracle.

I know that the database contains information about certain people, for example a person with the name Smith. I don't know how the database is structured (which table contains which information, are there triggers, ...).

As long as I don't know which tables I have to search, the best way I have found to work with the database files is to use grep. This way, I can at least verify that the database really does contain the name "Smith". Ultimately, I would like to have an SQL dump that can be viewed, filtered and understood in a text editor.

The DMP file was created with

expdp system / [PW] directory = [expdp_dir] dumpfile = [dumpfile.dmp] full = yes logfile = [logfile.log] reuse_dumpfiles = y

I know that the name Smith occurs often in the Database. Running grep -ai smith dumpfile.dmp returns many hits.

To analyze the database further I installed oracle-database and sqldeveloper-20.2.0.175.1842-x64. I imported the DMP file with

impdp USERID = system / [PW] FULL = y FILE = [dumpfile.dmp]

The folder C:\app\[user]\oradata\orcl now contains the files SYSAUX01.DBF and SYSTEM01.DBF, among others. I suspect that these are the database files.

The command grep -ai smith * .DBF does not return any hits. Either the files SYSAUX01.DBF and SYSTEM01.DBF are not the databases or something did not work on the import.

Using the SQL developer, I log in with the following data:

User: system Password: [PW] (= PW from the expdp command) SDI: orcl

In SQL developer, I do not find Smith. SQL developer displays many tables, most of which seem to be empty and none of which I understand. I suspect that these tables are not the tables I am looking for. Perhaps I need to log in a different way (different user, different SDI?).

I tried to export the database to an SQL dump file, trying out various options that SQL developer provides, but the result does not contain the string "Smith".

Something is not right:

  1. Import is faulty
  2. wrong SDI
  3. Export is faulty
  4. anything else

What might have gone wrong along the way?

Upvotes: 0

Views: 4703

Answers (1)

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

You have a lot misconceptions in your question.

  • Oracle Datapump is a database utility designed for exporting and importing. But the content, either is DDL commands ( as create table, create index ) or data from the tables, is stored as binary, so you can't check the contents of those files. There are options to extract the DDL commands from the dumpfile and put it into a script.
  • The datafiles you are mentioned are part of the database itself, they have nothing to do with datapump. Do not touch those files
  • I don't know what you mean by "Smith" , if you mean an schema, after importing make a select over dba_users looking for the field username = 'SMITH'
  • If you mean looking for "Smith" as part of any of those tables, you will have to look in any single table of the database ( except the ones of schemas belonging to Oracle ) and for each field that is a string
  • SDI does not mean anything. I guess you meant SID or Oracle System ID, an unique identifier to identify a database in a specific environment

There is nothing wrong. The problem I believe is that you don't exactly know what you are looking for.

Check this

A user/schema with name SMITH

SQL> SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'SMITH' ;

A table which name contains the word SMITH ( unlikely )

SQL> SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLE_NAME LIKE '%SMITH%' ;

Upvotes: 2

Related Questions