Re: Newbie : My schema is gone whenever SQLPLUS restarts

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.
>
>

Please read your code *carefully*.
In part 1, the user practice04 is *dropped* in part 2, the user practice04 is created with the password *password* So connect PRACTICE04/manager won't work as expected.

A schema simply is an user with tables, indexes etc. There is a create schema statement (which I have never used) to create all tables etc. in one go.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Feb 16 2002 - 01:44:38 CST