Oracle Tips

Logging in to Oracle

Oracle usernames and passwords will be allocated to each group (i.e. pair of students) via the Fire system. This information will be sent manually, so there could a a delay of a day or two before you get the account information, so please request your Oracle account in good time.

There is a web-based interface to Oracle: iSQL*Plus

You can also use Oracle via the command-line program SQL*Plus.

UNIX_PROMPT> sqlplus

    
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 31 10:53:06 2008
    
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
   
Enter user-name: username@db.student.chalmers.se
Enter password: 
    
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
    
SQL> exit;
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
UNIX_PROMPT>

Changing Your Password

You can change your Oracle password by executing the command:

    ALTER USER <userName> IDENTIFIED BY <newPassword>;

where <userName> is your Oracle username, and <newPassword> is the password you would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.

Note that SQL is completely case-insensitive. You can use upper case or lower case for keywords like ALTER. Even your password is case insensitive. We tend to use upper case keywords and not other things.

Executing Commands From a File

To execute commands stored in a file, type

    @<file.sql>

(where <file.sql> is your file) at the command prompt. All commands stored in <file.sql> will be executed by sqlplus.

Getting Information About Your Schema

The system keeps information about your schema in certain system tables. The most important for now is USER_TABLES. You can recall the names of your tables by issuing the query:

    SELECT TABLE_NAME
    FROM USER_TABLES;

More information about your tables is available from USER_TABLES. To see all the attributes of USER_TABLES, try:

    SELECT *
    FROM USER_TABLES;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

    DESCRIBE <tableName>;

to learn about the attributes of relation <tableName>.

Deleting Everything

The following script can be run in SQL*Plus to delete all of your tables, views and triggers:

begin
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;
end;
/
begin
for c in (select * from user_objects) loop
execute immediate ('drop '||c.object_type||' '||c.object_name);
end loop;
end;