Re: Newbie : My schema is gone whenever SQLPLUS restarts

Get a different book. What you have posted is horrifying to me.

Daniel Morgan

Leon 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 Mon Feb 18 2002 - 18:33:27 CET