Setting the lab environment
Installing Oracle Database Express Edition 11g
In case you'll be using personal laptops during the lab, you are asked to install Oracle Express Edtition 11g Release 2. This database server is all you need for the practical works, other versions including more advanced features not required at this stage (distributed database or multi-processor access).
To download Oracle Express Edtition 11g Release 2 you need at first to create a student account on the Oracle web page.
During installation, you will be prompted for a password, try to memorize it.
Setting up a new account
SQLPLUS /NOLOG -- This command, executed in command line, opens SQL*Plus without initiating any connection to the database server (this behavior is due to the NOLOG option).
To connect to the database as administrator you will use the user SYS (created during server installation) and you will type:
CONN / AS SYSDBA (or
CONN SYS AS SYSDBA)
and you will be prompted for the password (set for you as "sql" on the computers in the lab, subject to change for future labs).
If you prefer, you can type both the user and the password in the connection string as follows:
CONN SYS/sql AS SYSDBA
The SYS account gives you access to the entire database. For example, typing
SELECT TABLE_NAME FROM DBA_TABLES you can see all the tables existing in the database. A common user should not be able to access these tables, unless the database administrator explicitly grants access. Modifying system tables can compromise the proper functioning of the database, therefore each student has to create a new account he/she will use further during the labs. The accounts will be created from the SYS account (you just have connected to), by typing:
CREATE USER STUDENT IDENTIFIED BY STUDENT DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; -- congrats, you have just created a user named STUDENT with password STUDENT (the password is case sensitive, you have tu use upper case letters every time you type it). We insist that on all the computers in the lab the new accounts will have EXACTLY this name and password so that any student could connect from any computer. In case user STUDENT already exists, you get error ORA-01920: user name STUDENT conflicts with another user or role; you have to delete the user first, by typing
DROP USER STUDENT CASCADE;; then, re-execute the coomand that creates the user. The commands for creating and deleting users can be executed only form the SYS account; you cannot create/drop new users from STUDENT account.
In case on your personal laptops you have installed Oracle XE 12c and you want to create a user, use as user name instead of STUDENT the string c##STUDENT; working with Oracle XE 12c is allowed only for the first lab, you have to uninstall it and use Oracle Express Edtition 11g Release 2 in future labs.
Now, let's give the new user some rights:
ALTER USER STUDENT QUOTA 100M ON USERS; -- you just allocated a maximum amount of 100mb space in the database. Later on, when logged in as STUDENT, in case you need to store millions of records in a table, you could receive error ORA-01536: space quota exceeded for tablespace 'USERS'. The solution is simple: delete old objects or introduce less data :) Only the administrator can allocate more storage to the users.
GRANT CONNECT TO STUDENT; -- the user just received the right to connect to the database. You will find at the bottom of this page the command to connect with the STUDENT user.
GRANT CREATE TABLE TO STUDENT; -- the user just got the right to create tables, constraints and indexes.
GRANT CREATE VIEW TO STUDENT; -- the user is now able to create views
GRANT CREATE SEQUENCE TO STUDENT; -- the user is now able to create sequences
GRANT CREATE TRIGGER TO STUDENT; -- the user is now able to create triggers
GRANT CREATE SYNONYM TO STUDENT; -- the user is now able to create synonyms
GRANT CREATE PROCEDURE TO STUDENT; -- the user is now able to create procedures, functions and packages
CONN STUDENT/STUDENT@localhost/XE -- congrats! you have just connected with the new user. Any connection to the database will be made with the command: CONN (sau CONNECT) <user>/<parola>@<db server>/<nume serviciu>. In our case, we connect on the locally installed server registered by default as XE service. If you change the name during server installation you have to use the new given name. Once connected to the database, you can switch to other account only by giving the desired user and its password:
SELECT 1 FROM DUAL; -- you have just executed your first query; you can move on to the first lab or you can have a look at the SQL*Plus guide - the utility you have already used for executing the above commands and which will serve as lab environment for the entire semester.