October 8, 2012

Extract list of Oracle schema names from dump file

Filed under: Oracle — Tags: , , , — Darrin Maidlow @ 8:00 am

Recently I was tasked with extracting some data from an server and converting it to 2008.   I could not get very much information about the Oracle server other than a sys password and the location of the nightly backup dump files.  The server was serving two applications with very little vendor support – but they were working.   The icing on the cake was that the the Oracle EM web app was not working – so I was also running a little blind.  I didn’t want to break anything so I opted to leave the EM non-functional and I decided to import a recent dump into a fresh scratch Oracle instance.  I would do my export to SQL server from there – zero risk of an oops.

The dumps were full system dumps and were tens of GB in size.  I needed to know the names of the schemas being backed up so I could selectively restore the schemas to my new Oracle instance.   In my messing around I accidentally loaded one of the dumps into a text editor (that was smart enough to deal with large files) and noticed it was mostly text.

Grep the dump

So if you’ve got a small multi-schema dump you can probably the dump directly:

grep ‘CONNECT<space>’ file_name.dmp

Note – the space at the end of the word CONNECT is important.

Chances are however grep’ing a dump that is tens of GB is not the best plan.

Imp to the rescue

No we’re not going to import the dump – but imp will help us create a more grep’able file to process:

imp indexfile=output.txt full=y

When prompted, login to Oracle and specify the dump you want to process.  This will not actually import anything but it will create a much smaller text file that we can grep or even open with notepad.  Again search for “Connect<space>” and you will be presented with a list of schemas in the dump!   One last relevant note.  If you grep the file and cannot find “Connect” – then the dump file in question only contains a single Oracle schema.

Technorati Tags:

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress

Switch to our mobile site