Using Oracle on Omega
Oracle accounts are available to everyone with a current Omega account. You can request an Oracle account by emailing Help Desk at firstname.lastname@example.org. 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
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:
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 the password provided to you by the Help Desk. If you need to request a password for a MySQL account please email the Help Desk at email@example.com.
After logging in successfully, you will see the prompt:
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
1.6 Capture output from SQL*Plus to a file
In SQLPLUS, enter the command
to begin capture output and the command
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
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.
- Departments and Offices at UT Arlington
- Faculty (currently appointed) and Staff
- Visitors and Guests
- Students Admitted to UT Arlington
System requirements: None, Linux, Solaris