Re: Creating schema in oracle10g express edition

"dops" <r.solofria_at_gmail.com> schreef in bericht news:1163978784.558260.106010_at_e3g2000cwe.googlegroups.com...
>i read the manual ;) but i want a confirm of what i read :)
> ok, well.. u confirm what i now tell u:
> oracle have the concept of one database, where i can create a user (and
> than a schema) as container of my database objects (tables, stored
> procedure etc..).
> a schema is simple like a "database" in sql server and a "library" in
> db2 but to create a schema i've to create a user (and i'm sorry but its
> a really strange concept form me :( ).
> it's all right?
>
> hpuxrac ha scritto:
>
>> 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
>

Dops:
just consider a schema as a user with the objects he owns... So a schema is just a user who is owner of some objects.

Shakespeare
(what's in a schema?) Received on Wed Nov 22 2006 - 16:08:40 CST