Re: How to create/drop a database from Java (via JDBC)?

From: Hans Forbrich <hforbric_at_yahoo.net>
Date: Fri, 20 Feb 2004 11:29:33 GMT
Message-ID: <xcmZb.27671$n17.21812@clgrps13>

Steffen Siebert wrote:

>>>>>>"JK" == Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net> writes:

>
> Yes. Someone else installs the the rdbms binaries and my program can
> create or drop the whole database (all tables etc.)
<snip>
>

Hopefully you are starting to understand that RDBMS terminology is overloaded and things like Database do not mean the same thing in Oracle vs SQLServer.

> Is it possible with one oracle database to create to schemas under
> different names where the table names are identical?

Absolutely. Fundemental concept.

>
> And if it's really sufficient to create a schema only, how do I do
> this? And how to drop a schema completely?

CONNECT dba/dba_pwd

CREATE USER xyz ...
ALTER USER xyz grant appropriate RESOURCE capability [to be able to use tablespace]
CREATE USER qrs ...
ALTER USER qrs {grant appropriate RESOURCE capability}

CONNECT xyz/xyz_pwd
CREATE TABLE abc ( ... ) ...
GRANT SELECT ON abc TO me;
INSERT {5 rows} into abc;

CONNECT qrs/qrs_pwd
CREATE TABLE abc ( ... ) ...
GRANT SELECT ON abc TO me;
INSERT {10 rows} into abc;

CONNECT me/my_pwd
SELECT count(*) FROM xyz.abc;
 >> 5 rows
SELECT count(*) form qrs.abc;
 >> 10 rows

(and look up CREATE SYNONYMS if you don't want to qualify tables using xyz.abc and qrs.abc)

CONNECT dba/dba_pwd
DROP USER xyz CASCADE;
DROP USER qrs CASCADE;

All this and MUCH more is possible in a single Oracle database - and has been available since Oracle version 5 (or earlier - memory about v4 is vague).

Relevant documentation at http://docs.oracle.com (go into the appropriate version, VIEW documentation, LIST ALL BOOKS and look in the SQL Reference Manual

>
> And finally, what is created by the oracle sql statement "create
> database", a database or a schema?
>

A DATABASE is the complete collection of **files** - logs, control files, tablespace files, and so on, as well as the system dictionary.

You REALLY need to spend some time reading the ORACLE DATABASE 'CONCEPTS' manual available at http://docs.oracle.com

And yes - what you want CAN be done easily in Java!

/Hans Received on Fri Feb 20 2004 - 05:29:33 CST