Re: Creating schema in oracle10g express edition

dops wrote:
> uhm i'm sorry but i'm accustomed with sql server concepts of database
> and schema concept is new for me.. maybe.. becouse from 1 year i work
> with a db/400 (link db2) on as/400 mainframe..
> well, i i understand, in oracle don't exist the concept of more
> database.. the database is unique, but exists the concept of schema..
> when i've tell you this:
>
> "how i can create a new schema associated with exiting user?"
>
> you reply me this:
>
> "> If you want new tables associated with an existing user ( like HR )
> you
> > don't need to do anything ( if the user has the authority to create
> > objects and use space )."
>
> i don't want a new table, i want a new database (or schema)! a new
> database with associated tables and other objects..

The create user command creates new schemas IF that user is then granted permissions such as to create tables/etc.

> why i've to create a new user for a new schema (or database) ??!
> for example.. today i want create a new application, associated with
> this application i must create more tables etc.. tomorrow i want create
> another application totally different from previous.. why i've to
> create the tables of this new application in the same position of first
> application? it's concettually bad.. and logically confuse..

It works fine in oracle and there's nothing conceptually bad about it it's just the way it works in oracle.

For "today" create user schema1 ... etc then grant permissions, etc.

For "tomorrow" create user schema2 ... etc then grant permissions, create your tables, etc.

If you want to copy in all the tables etc that belong to schema1 into schema2 then you can use export/import to do this.

> i simple want two container (database or schema as u want call) that ar
> distinct, but in the same time i want connect to this two database with
> one user..

You don't normally create multiple databases in oracle. That's not the way it works.

You can have one connection to an oracle database then query from multiple schemas. That works just fine.

> i'm sorry, but i no understand this concept..
> in db2 for example exist the concept of "libraries", very like to the
> database concept of sql server.. every library have more tables and
> other objects.. but the database is only one.. and do not have to
> create a new use for a new library.. :(

I gave the best answers I could to your previous questions.

I think it's time for you to read the oracle concepts manual.

>
>
>
>
>
>
>
> hpuxrac ha scritto:
>
> > dops wrote:
> > > thanx for your reply.. my english is not very good, maybe this is the
> > > problem :)
> > > reading documentation i understand that oracle call the "database" as
> > > "schema" (it's right?)
> >
> > In oracle usually you only have 1 database. You have multiple schema's
> > inside a database.
> >
> > HR could be in the same database as SCOTT for example. Each of them
> > has their own tables etc ... all within the same database.
> >
> >
> > > well, if it's right, how i can create a new schema associated with
> > > exiting user?
> >
> > If you want new tables associated with an existing user ( like HR ) you
> > don't need to do anything ( if the user has the authority to create
> > objects and use space ).
> >
> > For example you can login to the database as HR and do this command ...
> >
> > create table myjunktable ( col1 varchar2(10), col2 number );
> >
> >
> > i don't see any menu item "create new schema" maybe this
> > > is a limitation of express version of oracle?
> >
> > If you want a new schema such as HR1 you could do this ...
> >
> > create user HR1 identified by somepassword default tablespace
> > sometablespace temporary tablespace temp;
> >
> > grant connect, resource to HR1;
> >
> > > for example (if u know the express version), the express version of
> > > oracle have a default user named "HR", how i can create a new database
> > > (with tables and related objects) associated with this user?
> >
> > You don't want to create more databases, you want 1 database that all
> > the schema owners will share.
> >
> > The concepts manual is recommended reading for you.
> >
> > > But if i want create a database not associated with a specific user, i
> > > can?
> >
> > You want 1 database that has multiple tablespaces ( some for data,
> > maybe some for indexes, some for oracle specific requirements ( SYSTEM,
> > SYSAUX, UNDO ), and some that are shared ( TEMP )).
> >
> > In oracle a database has mutiple tablespaces. Each schema might have
> > it's own tablespaces or might share tablespaces with other schema'.s
> >
> > > Thanx again ;)
> >
> > Cheers
Received on Sun Nov 19 2006 - 17:10:08 CST