Re: Need help importing / exporting a schema

Thank you so much for a straight forward answer to my questions.

Your answers are just what I was looking for. I do have one follow up question
if you will be so kind. You said:

>
> 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.

Once I drop the user, I'd like the command needed to immediately re-create the
user (with a blank database). This way I can just run my SQL script to re-create the tables, etc.

Thanks again for a straight answer.

Blake McBride

On 4-Jun-2007, Frank van Bortel <frank.van.bortel_at_gmail.com> wrote:

>
> On 3 jun, 06:01, "Blake McBride" <b..._at_mcbride.name> 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:
> >
> > CREATE TABLE abc (
> > ...
> > ...
> > )
> > INSERT INTO abc .....
> >
> > and so on.
>
> If that file would be called "dump.txt", simply start an SQL session
> and use
> @dump.txt
>
> >
> > 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 - 07:55:03 CDT