Skip to main content

Office of Information Technology (OIT)

UT Arlington
OIT: Office of Information Technology

helpdesk@uta.edu ·  Work Order · 817-272-2208 · System Status

We are your IT partner!

Using Oracle on Omega

Description:

Oracle accounts are available to everyone with a current Omega account. You can request an Oracle account by emailing Help Desk at helpdesk@uta.edu. Additionally, instructors may request Oracle accounts for all students in a particular session.

The following documentation is divided into 2 sections:
Section 1 - For database beginners, or those taking an SQL course for the first time.
Section 2 - For advanced users.


1.1 Getting Started

Logging in

The primary way in which you will interact with Oracle is through SQL*Plus, an interactive SQL-based front-end. Log intoSQL*Plus by typing:

>sqlplus

You will then be prompted for your Oracle username, which is the same as your NetID username. When you are prompted for a password, enter your default NetID password.

After logging in successfully, you will see the prompt:

SQL>.


1.2 Entering a SQL command in SQL*Plus

You can enter a single-line or multiple-line SQL statement. SQL statement is not case-sensitive. To end a SQL statment and execute, you can do it in one of the two ways:

  • end with a semicolon ;
  • end with a slash / on a line by itself

1.3 Changing your passwords

To change your Oracle password. In sqlplus, type:

SQL> ALTER USER username IDENTIFIED BY newpassword;

Replace username with your NetID.

Replace newpassword with the new password that want to create.


1.4 Running SQL Command Files

The START command retrieves a SQL command file and runs the command(s) it contains. Use START to run a command file containing SQL commands and/or SQL*Plus commands. Follow the word START with the name of the file:

SQL> START file_name

If the file has the extension .sql, you need not add the period and the extension sql to the filename.


1.5 Getting out of SQL*Plus

Type

SQL> EXIT


1.6 Capture output from SQL*Plus to a file

In SQLPLUS, enter the command

SPOOL output.lst

to begin capture output and the command

SPOOL OFF

to end the spooling. Any commands issued inbetween the SPOOL commands are saved in 'output.lst'.

SQL> SPOOL output.lst
SQL> SELECT * FROM mytable;
SQL> SPOOL OFF
SQL> EXIT
omega> cat output.lst


1.7 List tables owned by you

Enter the following SQL statement to list schema objects (including tables, views and sequences) owned by you

SQL> SELECT * FROM cat;


1.8 List all columns of a table

Enter the following SQL statement to list all columns of table 'staff':

SQL> DESCRIBE staff;


1.9 List Database Objects owned by you

Enter the following SQL statement to list database objects owned by you:

SQL> SELECT * FROM user_objects;

To list only useful information, enter the following SQL statement:

SQL> SELECT object_name,timestamp, object_type FROM user_objects;


1.10 Compilation of a Pro*C/C++ program

Information about Oracle Precompilers can be found in the Oracle9i Database Documentation Library (see below) under the Pro*C/C++ Precompiler Programmer's Guide.


1.11 Oracle9i Documentation Library

Oracle9i Database Documentation Library is available at http://omega.uta.edu/oracle.


2.1 Check your system privileges

To check for system privileges granted on your current session, enter the following SQL command:

SQL> SELECT * from SESSION_PRIVS;


2.2 Create public synonyms

You must have CREATE PUBLIC SYNONYM privilege to create a public synonym. This privilege is only granted to database course instructors, please contact the OIT Helpdesk for support.

To create a public sysnonym in Scott's schema:

SQL> CREATE PUBLIC SYNONYMstaff FOR scott.staff;


2.3 Using SQL*Loader

The Unix command to invoke SQL*Loader is 'sqlldr'. To understand the data loading concept, the structure of control file and the usage of 'sqlldr' command, click the system online documentation hyperlink on Omega home page and select Oracle9i Server library and Oracle9i Utilities section for information.

At Unix system prompt, enter the command 'sqlldr'to see a list of keywords accepted by the sqlldr command.

Available To:

  • Vendors
  • Departments and Offices at UT Arlington
  • Event-Based
  • Faculty (currently appointed) and Staff
  • Retirees
  • Visitors and Guests
  • Students Admitted to UT Arlington

System requirements: None, Linux, Solaris