Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help importing / exporting a schema

Re: Need help importing / exporting a schema

From: Frank van Bortel <>
Date: Mon, 04 Jun 2007 08:31:03 -0000
Message-ID: <>

On 3 jun, 06:01, "Blake McBride" <> wrote:
> Greetings,
> I have an application which runs under Postgres & MSSQL and I have a
> new client who wants to run it under Oracle 10g. I have no experience
> with Oracle but all I need is some basic abilities in order to port
> the app. The things I need to do are as follows:
> 1. Import a database schema and data from a text file containing SQL
> statements. The exact format of the SQL isn't important because I
> can change my program to output whatever syntax. So basically I
> am trying to import from a text file which looks like:
> ...
> ...
> )
> INSERT INTO abc .....
> and so on.

If that file would be called "dump.txt", simply start an SQL session and use

> I would also create primary keys, indexes, foreign keys, etc.

Done from the same "dump.txt", I presume? The @dump.txt will take care of that - it allows for DDL (Data Definition Language) as well as
DML (Data Manipulation Language)
> 2. I need to be able to export the Oracle schema and/or data in a
> similar format as my import. Again, the exact (Oracle specific)
> format is not important but is must be a text file with SQL
> statements.

No standard tool - Oracle uses exp.exe, but the export is in an internal format.

> 3. I'd like to be able to easily drop all the data, tables, indexes,
> foreign keys, etc. with a few simple commands in order to be able
> to easily clear it all out and start over again. In other DB
> systems I usually drop the whole database and recreate it with two
> simple commands or clicks in the GUI.

A "Database" is not the same in Oracle - in Oracle speak, you drop a schema. As a schema is connected to a user, "drop user abc cascade;" will do the trick, as long as you are logged on with a priveleged account,
such as system.

> In order to avoid the cost while testing my app locally I am
> attempting to avoid a purchase and just use Oracle's express edition
> (XE). Of course the client has an enterprise license or some such. I
> fear my difficulties may be associated with the limitations of the XE
> version. If so, what is the minimum version I can buy to get the type
> of facilities I am looking for?

XE only has size limitiations, not functional (some options may not be available, though) Received on Mon Jun 04 2007 - 03:31:03 CDT

Original text of this message