Skip to Main Content
  • Questions

  • How to identify database export files

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Lal.

Asked: March 26, 2018 - 5:24 am UTC

Last updated: February 14, 2019 - 4:28 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Any tools/mechanisms to identify database export files taken using exp and expdp, so that i can do a scan on the desktops in my organisation to find who all have kept the export files in their desktops.

and Chris said...

Yikes! You want to scan all the computers in your org looking for exports?!

Have you tried asking people?

Anyway, imp and impdp to display the contents of a file. So you can use these to identify which aren't exports by looking for errors from this.

IMP has the show option:

bash-4.1$ echo test > test.txt
bash-4.1$
bash-4.1$ imp file=test.txt show=y full=y userid=chris/chris

Import: Release 11.2.0.4.0 - Production on Mon Mar 26 03:15:00 2018

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

IMP-00009: abnormal end of export file
IMP-00000: Import terminated unsuccessfully


And impdp sqlfile to paste DDL into a file:

bash-4.1$ impdp dumpfile=test.txt directory=tmp sqlfile=test.sql userid=chris/chris

Import: Release 12.1.0.2.0 - Production on Mon Mar 26 03:21:15 2018

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/tmp/test.txt"
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 5


But to use impdp, the "dumpfile" must be accessible from the database server. Which makes this impractical to use for scanning your company's laptops & desktops.

I'm not aware of a better solution.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 26, 2018 - 10:34 am UTC

Thanks for the quick response.

Other options...

A reader, March 26, 2018 - 12:51 pm UTC

Hi : for sure, at the bare minimum in a organized environment, someone would ask for a LOG FILE for each and all dumpfiles, so the tool used, the version, the arguments in use, etc, will stay in the logs, but besides the given options, someone could :

a. if no direct access to the db server exists BUT a local database (even XE, say) exists and a database link is possible, use the NETWORK_LINK option in expdp

or

b. to read the dump file header : the note "How to Gather the Header Information and the Content of an Export Dumpfile ?" (Doc ID 462488.1) give some examples about reading expdp headers, and traditional exp is even simpler, it begins with the string ".EXPORT:Vxx.yy.zz"

Regards,

Chiappa
Chris Saxon
March 26, 2018 - 2:13 pm UTC

1. Yep. Somehow I doubt all the computers in the org have Oracle Database installed though...

2. Thanks for sharing. Simply scanning the header could lead to false positives in the (unlikely) event someone created another file with that start. Should narrow down the list for validation though.

Correct..

J. Laurindo Chiappa, March 26, 2018 - 2:38 pm UTC

Yes, it's very unlikely someone creating a file mimicking Exactly the full file header for Oracle dump files but is possible, yes.... Conversely, of course not all computers containing .DMP files Obligatorily have some Oracle Database installed but it´s common place for dev machines, the kind of machines more probably creating .DMP files...
So yes : all the given Techniques can present false negatives AND/OR require some additional software, the interested person will need to know this and plan accordingly...

Of course, as said imho the Best thing would be a tight-controlled environment, where not all people can generate dump files from the database AND all the generated dump files contains LOG files....

Regards,

Chiappa
Chris Saxon
March 26, 2018 - 2:46 pm UTC

Best thing would be a tight-controlled environment, where not all people can generate dump files from the database

Haha, yes! And stop people moving the exports from the dump location to anywhere else...

But it sounds like that's a bit late for the OP.

damp confuse ans

SALEH, February 13, 2019 - 5:18 pm UTC

Which three statements are true about wrapping? (Choose three.)

A. The PL/SQL wrapper detects and reports only syntax errors. B. The PL/SQL wrapper detects and reports both syntax and semantic errors. C. When wrapping a package or object type, both the body and specification should be wrapped. D. When wrapping a package or object type, only the body should be wrapped, not the specification. E. To change a wrapped object, the original source code needs to be modified and then wrapped again F. To change a wrapped object, the wrapped code can be unwrapped, modified In a text file, and then wrapped again.

Connor McDonald
February 14, 2019 - 4:28 am UTC

And this is related to the original question how exactly?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.