Re: Newbie : My schema is gone whenever SQLPLUS restarts

Hi!

Leon <litomail_at_yahoo.com> schrieb in im Newsbeitrag: a4ktl3$1eb$1_at_spacebar.ucc.usyd.edu.au...
> 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:
>
> ... file://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;

Where is the Variable password set?
So the password for the new account is not 'manager', which is the value of the variable system_password, but the value of the variable password.if it is not set, it is null.

Alex

>
> --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 Thu Feb 21 2002 - 08:28:06 CST