Newbie : My schema is gone whenever SQLPLUS restarts

From: Leon <litomail_at_yahoo.com>
Date: Sat, 16 Feb 2002 17:25:10 +1100
Message-ID: <a4ktom$1gl$1_at_spacebar.ucc.usyd.edu.au>

[Quoted] [Quoted] 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 - 07:25:10 CET