Re: Need help importing / exporting a schema

On Jun 4, 2:55 pm, "Blake McBride" <b..._at_mcbride.name> wrote:
> 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.bor..._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)- Hide quoted text -
>
> - Show quoted text -

Please do not top post.
Oracle can have multiple schema's in a database. Consequently you can't associate a database with an user. So you can drop the user (and implictly drop all objects in that schema), but if you recreate the user the schema will be empty.

The code to retrieve the current definitions is something along the lines of

set long 10000000 heading off verify off pages 0 feedback off set lines 132
column ddl format a132 word_wrap
define unam='&1'

• make sure every statement is followed by a semicolon -- execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

spool &unam._defs.sql
-- get the create user statement
select dbms_metadata.get_ddl('USER',upper('&unam')) ddl from dual; -- get his profile
select dbms_metadata.get_ddl('PROFILE', profile) ddl from dba_users where username=upper('&unam');
-- get his roles
select dbms_metadata.get_granted_ddl('ROLE_GRANT',upper('&unam')) ddl from dual;
-- get his enabled roles
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE',upper('&unam')) ddl from dual;
-- get his system privileges

select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper('&unam')) ddl from dual;

spool off

Similar things can be done for tables and indexes. And NO, I won't give away the complete script for free.

--
Sybrand Bakker
Senior Oracle DBA

Fact: 99.9 percent of all questions on any Oracle forum have already
been asked and answered over and over again. This is because most
posters think they are the first to run into a problem and
consequently don't search Google.
They are wrong: they are not unique, they are with *too many*