Oracle Resource Web Page

CSC instructors can request Oracle accounts for their classes by sending an email to csc_help@ncsu.edu. Include: course, section number. If you want accounts for your TAs please include their name(s)with their unity IDs.

IMPORTANT: Students are responsible for their own database backups. Also, Oracle accounts are permanently deleted at the end of each semester.

How To's

Logging in to the Oracle Class Server

To login to your Oracle account using our supported environment:

statements that you need to enter are in bold. When the prompt “SQL>” is returned, your Oracle login was successful!

  1. You need an ssh client. We recommend Putty. For Mac users use the Terminal app.
  2. Launch your ssh client and open a secured connection to the remote host: remote.eos.ncsu.edu.
  3. login: <enter your Unity ID>
  4. Password: <enter your Unity password>
  5. eos% add oracle12
  6. eos% sqlplus

    SQL*Plus: Release 12.2.0.1.0 Production on [date & time stamp]
    Copyright (c) 1982, 2016, Oracle. All rights reserved.
  7. Enter username: UnityID@orcl01 (Do not forget to include @orcl01)
  8. Enter password: <enter your oracle password> (this is NOT your Unity Password - contact your instructor or TA about what is your initial password)
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL>

|top|

Changing your Oracle Password

Important: We highly recommend users to change their Oracle passwords asap as follows:
Note: (special characters in passwords are allowed except "spaces" or leading/trailing @ symbol.)

SQL>password
Changing password for username
Old password:
New password:
Retype new password:
Password changed
SQL>

|top|

How to retrieve assigned Roles and Privileges to your Oracle account

Retrieve Session Privileges:

  1. login to your Oracle account
  2. at the SQL> prompt type:

select * from session_privs;

Retrieve Session Roles:

select * from session_roles;

|top|

Granting/Revoking Other Users access to your Objects

The privileges a user can grant include these( On the user's tables, views, and materialized views):

For example, the user JOE gives MOE SELECT access to the NAMES table:

grant select on NAMES to MOE;

MOE can now view all records on NAMES with:

select * from JOE.NAMES;

To revoke access:

revoke select on NAMES from MOE;

|top|

Some technical Specifications about the Oracle Database Server

|top|

Accessing Oracle Database from Eclipse IDE (not longer supported - use info at is)

Requirements and Installations -

  1. Download Eclipse from Eclipse Downloads
  2. Installing the Eclipse Database Developement Plugin
    • For Europa:
      1. Start eclipse
      2. At the top select Help->Software Updates->find and Install
      3. Expand "europa discover Site" and if "Database Developement" is there then select the box beside it, otherwise the plugin is already installed
      4. then click "Select Required" on the right side
      5. then click "next" and accept the terms then click "next" again
      6. finaly select "next" and then "Finish"
      7. when it asks you to install click "Install All" and when this is finished click "yes"
    • For Ganymede:
      1. Start eclipse
      2. At the top select Help->Software Updates
      3. Select the "Availabe Software" tab
      4. Expand the "Ganymede Update Site"
      5. If the "Database Developement" is there select the box beside it and click "Install", otherwise the plugin is already installed
      6. then click "finish and when that is finished click "Yes"
  3. Install the Oracle Database Plug-in
    Go to Oracle Instructions and follow whatever directions you prefer, keeping in mind that the update manager is the easiest way.
  4. RESTART eclipse for these changes to take effect

Using the Tool -

  1. Configuring Oracle Database
    • First to open Database perspective, select windows menu select open perspective and select Data Developement perspective. This opens the Data source explorer (DSE) view.
    • Right click on the Databases node in the DSE and select the New option. In the new connection profile wizard select Oracle Database Connection and select next
    • Select Oracle Database 10g driver default from the drop down list of drivers
    • Replace localhost with the Hostname of the Database server(ora.csc.ncsu.edu)
    • Replace xe with SID of the database service (i.e., with orcl)
    • Username – Name used to login to the oracle user account
    • Password – Oracle user account’s password
    • Selecting test connection has to result in a Ping successful message
    • Select Finish to complete the wizard

  2. Accessing Database workspace

    3. To connect to the database right click on the database name and select the Connect option.
    After the user has connected to the database, the User Schema is displayed on the Data Source Explorer along with all the tables, procedures, views, etc that are created by the user.
    To perform various Database operations right Click on the required folder name and selection the operation that needs to be done.
    To disconnect from the database right click and select Disconnect from database option


|top|

Additional Documentation

|top|


FAQs

1) I inserted a new record into a table with the "insert" sql command, but the table doesn't show that record.

Issue the sql command: commit; after your insert statement.

2) How to change my Oracle password?

SQL>password

3) How to move to Another User with Connect

SQL>connect oracle_acct@orcl/password

This is the most secured way of doing it. Suppressing the password, the system will prompt you to enter a password.

4) How do I display my user tablespace disk quota?

SQL> SELECT * FROM USER_TS_QUOTAS;

5) How do I connect to the Oracle server from a third party client or development tool?

You will need the Oracle client installed on your PC. The client can be downloaded from Oracle's Web site. Currently our Oracle server is running under version 12c Release 12 so make sure you download a client that is compatible with it. Add an entry to your tnsnames.ora file (this file should be on your hard drive under: \oracle\ora12\network\admin) as follows:

ORCL01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora.csc.ncsu.edu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl01.ora.csc.ncsu.edu)
)
)

Refer to your third-party client or programming tool documentation for further connectivity instructions.

|top|