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

"Steffen Siebert" <news_at_SteffenSiebert.de> wrote in message news:u3c960w6v.fsf_at_SteffenSiebert.de...
> >>>>> "HF" == Hans Forbrich <hforbric_at_yahoo.net> writes:
>
> HF> Hopefully you are starting to understand that RDBMS
> HF> terminology is overloaded and things like Database do not mean
> HF> the same thing in Oracle vs SQLServer.
>
> and
>
> HF> A DATABASE is the complete collection of **files** - logs,
> HF> control files, tablespace files, and so on, as well as the
> HF> system dictionary.
>
> This seems not very different to SQLServer. If I create a new
> database, several files are created (for data, transaction log etc.)

It is in fact quite different. You get the equivalent of master,msdb and tempdb as well. You set aside the memory that you require and so on. it is pretty similar in fact to installing a new instance of sql server on the same machine. (Not identical but similar).

> But I'm still not convinced that a schema is all we want/need. Are all
> tables in one schema stored in files separate from tables of other
> schemas so I can transfer my schema "xy" only via a bunch of files
> from one oracle server to another?

If you choose to yes. Each user should get specifically assigned a default tablespace - keep these different and the tables will be physically as well as logically separated.

> Can I create backups of schemas?

you can create 'logical' backups of schemas using the export utility. A true backup is database wide.(and restores a bunch of system stuff as well)

> HF> And yes - what you want CAN be done easily in Java!
>
> Creating schemas? With your sql statements it seems easy. Creating
> databases? I still have no clue.

I suspect that you *can* do it, there is after all a CREATE DATABASE statement and a set of steps to be followed including the os stuff. You really, really,really don't want to do it, and if you go to an oracle shop will most probably not be *allowed* to do it - certainly I'd question the competence of those who allowed it to be done like this. It will also take several hours to complete in all likelihood.

You might find it helpful to think of it like this

1. DATABASE complete set of files that comprise the physical storage of the data and metadata in the RDBMS. Managed by One or more instances.
2. INSTANCE set of os processes and memory structures that manage a database.
3. SCHEMA owner of a set of database objects and stored application code.
4. TABLESPACE logical storage area for database objects.

you ought to be thinking in terms of 3, and from the sounds of it requiring that each SCHEMA has its own dedicated tablespace or tablespaces for data storage. You probably also ought to be creating an application role so that each time your schema owner gets the same privileges.

Something like (to be run as a sql script by a dba account)

CREATE ROLE APP_OWNER;
GRANT
CREATE SESSION,
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE
etc
TO APP_OWNER; CREATE TABLESPACE <SCHEMA>_DATA ...;

CREATE USER <SCHEMA> IDENTIFIED BY <password> DEFAULT TABLESPACE <SCHEMA>_DATA
temporary tablespace temp
quota unlimited on <SCHEMA>_DATA;

grant APP_OWNER TO <SCHEMA>;

CONNECT <SCHEMA>/<PASSWORD>
do your stuff...

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK