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 delay of a day or two before you get the account information, so please request your Oracle account in good time.

You can use Oracle via:

• the command-line program SQL*Plus.
• the graphical tool SQL Developer

Running SQL*Plus at Chalmers

UNIX_PROMPT> sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 21 15:14:28 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: username@db.student.chalmers.se
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing 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 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;
/