Re: Newbie : My schema is gone whenever SQLPLUS restarts

What do you mean, you 'lose all the objects'. How did you check this? The password of your user PRACTICE04 is 'PASSWORD'. Creating objects do not need a commit. DDL is commited implicitly. User and schema are two aspects of the same thing: user refers to being able to log on and do things, schema refers to the possibility of owning objects.
A user is not owned be another user; PRACTICE04 is not different from SYS other than the privileges that are granted to it.

Jaap.

On Sat, 16 Feb 2002 17:24:31 +1100, "Leon" <litomail_at_yahoo.com> wrote:

>Thank you for your time reading my message.
>First of all, may I ask what's the different between Schema and User in
>Oracle? They are so subtle.
>
>Second, I used the Bobrowski book to start on Oracle8i. I examine the code
>from the book, here's the snippet of the SQL code:
>
>... //code omitted
>
>DEFINE schema = PRACTICE04
>DEFINE default_tablespace = "USERS"
>DEFINE temp_tablespace = "TEMP"
>
>...
>
>--Part1
>
>CONNECT system/&system_password;
>
>...
>
>ACCEPT system_password PROMPT 'Please enter the password for the SYSTEM
>account'
>
>...
>
>DROP USER &schema CASCADE;
>
>...
>
>WHENEVER SQLERROR EXIT;
>
>...
>
>--Part2
>
>CREATE USER &schema
> IDENTIFIED BY password
> DEFAULT TABLESPACE &&default_tablespace
> TEMPORARY TABLESPACE &&temp_tablespace
> QUOTA 200K ON &&default_tablespace;
>
>--Part3
>
>GRANT CONNECT, RESOURCE TO &schema;
>
>CONNECT &schema/password;
>
>-- Part4
>
>CREATE TABLE ITEMS
> (O_ID NUMBER(38)
> ,ID NUMBER(38)
> ,P_ID NUMBER(38) NOT NULL
> ,QUANTITY NUMBER(38) DEFAULT 1 NOT NULL
> );
>
>...
>COMMIT;
>
>
>The code is executed using SYSTEM account (passwd is default: manager).
>
>The problem is:
>
> a.. Everytime I exit SQLPLUS, I loose all the objects (tables)
> b.. In the new session of SQLPLUS, I cannot log in as PRACTICE04
>
>I don't understand why, since the facts:
>
> a.. In part 4, all tables are created and committed, this should keep the
>object in the db permanently
> b.. In part 2 and 3, a user (schema .. whatever) called PRACTICE04 with
>passwd "manager" is created. And when I use the 'connect PRACTICE04/manager'
>in a SQLPLUS session, it tells me the password is invalid.
>And, ... in part 1 I connect using SYSTEM account and then create the
>so-called PRACTICE04 schema. Is that mean SYSTEM account is the owner of
>PRACTICE04 and only SYSTEM account can access PRACTICE04? Why often DBA can
>create a user that can be access by the user directly without through the
>login of the creator(DBA)?
>I want to thank you if you could help clarify this problem, since I almost
>finish the book but still don't understand this phenomenon.
>
>
>
Received on Sat Feb 16 2002 - 11:38:11 CST