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 usernameIDENTIFIED BY newpassword;
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