Unloading an Oracle Schema to Script Files for Source Control

thatjeffsmith SQL Developer 1 Comment

Tell Others About This Story:

The question:

We have big database with a lot of stuff and I want to use version control (Git) to manage changes. There are a lot of articles how to do it step by step but one piece is missing for me. Is there standard or recommended way for file structure of whole database (data excluded) and how it can be obtained from existing database?

So I’m not sure about standard or recommended, but we do have a few tools for you to take advantage of.

I realized I had ONLY talked about the Cart previously – which is good for taking a subset or handful of objects, and loading to scripts. But this person wants the entire schema.

So let’s look at the Database Export Wizard, under the Tools menu.

Step 1: Your Connection

The easiest way to walk this is to login AS the schema you want to unload. I’m going with HR.

Then proceed to the tools menu.

That’s what we’re looking for.

Step 2: The Most Important Part, the Options

Two things to consider here – what types of objects do you want, and how do you want the output generated.

I’ve highlighted 3 critical areas.

  1. Show Schema – do you want to see CREATE TABLE HR.TABLE1 or do you want to see CREATE TABLE TABLE1?
  2. Export Data – you probably do NOT want the data included as INSERT statements – unless these are lookup tables
  3. Save As – I have mine set to SEPARATE DIRECTORIES, so I will get a directory for each schema object type

Adjust according to your needs.

I could click ‘Finish’ here – as I am EXPORTING EVERYTHING in my schema. But if you don’t want everything, you could go to the next screen to use the Object Selector to say exactly what you want to export.

And GO!

The Results

Ta-da!

Let’s look at two files.

First a sample, generated file.

If you don’t like how the DDL is shaped, mind your options when running the wizard.

And there’s also a ‘master’ file in the root output directory.

You could run this script to kick off all of the generated files from our Wizard session.

Don’t Like GUIs?

You could do this in SQLcl with some JavaScript, courtesy @krisrice.

Have a go with this.

Related Posts

Tell Others About This Story:

Comments 1

  1. Thanks.
    We use this extensively. However, We need to modify further to make it re-runnable I.e. at the start add a check to see if table exists and drop (based on various conditions e.g. is table empty) .
    Also, we have other parameters like “ENABLE ROW MOVEMENT” compression etc.
    Also, as we use continuous deployment we also have a diff mechanism to generate alter scripts. We used schema comparator and are reviewing the same or this use case.
    All in all a life saver feature.

    Thanks,
    Vikram R.

Leave a Reply

Your email address will not be published. Required fields are marked *