Hi,

I'm a complete Newbie when it comes to Oracle so please excuse my simple
questions. I have installed Oracle 10g Express on my home system and so far
all works well. I can create tables etc, either programatically (VB6) or
using the GUI provided which I think is quite useable. I have a book which I
think is a very good reference (Hands on Oracle 10g Express foe Windows
published by Osbourne), but the only thing it doesn't explain is how to
create a fresh set of database files. Or is this the reason for the Express
edition?

Any advice would be greatly appreciated.

Ade

Ade wrote:
By "a fresh set of database files" what is it you are trying to accomplish?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote

Hi,

Ideally, as with Access previously, I would expect each application I write
to have their own set of files which can be stored away from other
applications. This was so easily done with Access (sorry if I'm swearing. by
the way!!).

With Oracle it appears that there is a central database area underneath the
Oracle directory tree which will contain data for all applications using it.
I can only see a separation between applications by the use of a
username/password combination. Like I said, I'm a complete Newbie to Oracle,
so please excuse me if I'm way off track.

For some applications, I would like to keep data apart.

In the good ole days on VMS (RDB 6 something) it was just a case of;
SQL> create database filename 'mydb';
which created the main database file and a snapshot file.

With Oracle 10g Express, there doesn't appear to be an option available. I
may have missed something though.

Regards,

Ade

True active RDBMS systems, including Oracle, SQL Server, DB2, and
others, are vastly different from single-user file-based database such
as Access. I suggest that the best place to start would be to download
the Concepts Guide from the Oracle documentation set and read through it
at least twice. The Concepts Guide is only 1% or less of the complete
documentation set but it has more information about Oracle packed into
it than any other source I have seen. Just about any answer to any
question about Oracle that you receive from a knowledgeable source will
start with "have you read the Concepts Guide for your version?", so
might as well get started!

Briefly, Oracle has the concepts of database, instance, and schema.
Schemas can roughly be thought of as userids to get started. Typically
each application will store its data in a schema. Tables and fields are
then referenced using either their name in context or their fully
qualified name:
PART.LENGTH <== if the context is known
ERP.PART.LENGTH
PDM.PART.LENGTH <== here we have two tables and fields
with the same name in two different
applications referenced with the fully
qualified names

There are other levels of abstraction for accessing multiple instances,
multiple databases, remote databases, etc.

Steven

Data files in Oracle are physical OS files attached to a database where
a user/schema may store tables and indexes. An application is typically
a schema/user in a database with privileges to store and retrieve data
in the database. An application can also be made up of more then one
schema/user, and it is not uncommon for several application interacting
with each other to co-exist as multiple schemas in one database and/or
multiple schemas in multiple databases. The Oracle RDBMS handles both
environment (quite an understatement).
The relationship between applications and physical file is:
a schema for an application exist in a database, with privileges to
store information in tablespaces which are made up of physical OS
files.
Note that a number of users/schemas (possibly bellonging to the same
application) may exist in the same database, and could have privileges
(quota) on the same or different tablespaces.
Going the other way, an OS file can only belong to one tablespace which
in turn can only belong to one database.
Also, a schema can only exist in one database. Another schema/user with
the same name can exist in another database but these two don't share
data. This is very common when you have a development database
mirroring the production database.

hth

Ade wrote:
MS Access is a toy: Oracle is an industrial strength RDBMS.
Time to learn Oracle concepts and put away the old paradigm.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

In article <ei7aek$kkq$1 (AT) lore (DOT) csc.com>, Ade (AT) nowhere (DOT) com says...

In Access you might want to do this because Access is so flaky it was a
possible part of several fudges. On any industrial-strength database with
decent security and a proper IMP/EXP facility, there is no real reason to
do this.

Read up on tablespaces. It's probably as close a concept in Oracle as
you'll get to what you seem to be talking about. But *why* you'd bother
to set up separate tablespaces for each schema/user (read that as each
"database" in Access terms) just to simulate what you used to do in Access
is the real question. I can't think of a decent reason why you would.

Geoff M

In message <MPG.1fb275c4fe09017b98987c (AT) news (DOT) readfreenews.net>, Geoff
Muldoon <geoff.muldoon (AT) trap (DOT) gmail.com> writes
--
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

In message <ei7aek$kkq$1 (AT) lore (DOT) csc.com>, Ade <Ade (AT) nowhere (DOT) com> writes
tables logically separated, and a new tablespace to keep them physically
separated. Depending on the size of the apps, the latter may not be
necessary. In fact, given that there is an overall size limitation in
XE, it may be counterproductive.
--
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

jim (AT) ponder-stibbons (DOT) com says...

Commonly done doesn't necessarily mean sensibly done. I've seen similar
debates here about the supposed benefits of separating tables and indexes
into separate tablespaces. Advantages of separate tablespaces per app?

Geoff M