Oracle Resource Web Page

About The Oracle Database Server

The CSC department uses the Oracle relational database management system as the primary DBMS system for "Class purposes".

Note: Oracle database is site licensed for campus. It is available in Windows, Solaris and LInux versions. For more information visit Administrative Computing Services Web site

Database accounts can be created for each student on the department's Oracle10g Release 2 server. The students' Oracle10g account names are each student's unity ID with passwords being the new 9-digit student IDs including the leading zeroes. Note: Student Oracle accounts are permanently purged at the end of each semester.

If you are an instructor and would like to request Oracle accounts for your class, send an email to help@csc.ncsu.edu including course, section number, and TA name(s) with their unity IDs. If you also need a class server, please visit the Virtual Computing Lab Web site. To request additional student workspace go here.

Important: We highly recommend users to change their Oracle passwords asap as follows:

SQL> ALTER USER your_oracle_account_name IDENTIFIED BY "newpassword";

Accounts are limited to 5MB. Students registering after the deadline need to coordinate with the instructor or TA to get their Oracle10g accounts created.

Some Oracle Database Server Tech Facts:

|top|

Logging in to the Oracle Class Server

Oracle10g has been installed on the Linux server, ora.csc.ncsu.edu and the sqlplus client on an AFS locker.

To test your Oracle10g account
1) To test your Oracle10g account you must first get access to SQL*Plus, and then successfully login. Following below is a sample session showing the login sequence. The statements that you need to enter are in bold. When the prompt “SQL>” is returned, your Oracle10g login was successful! If you are trying to access your Oracle Account from home, you must have an ssh client to connect to remote-linux.eos.ncsu.edu. We recommend Putty.

ssh remote-linux.eos.ncsu.edu

login: <enter your Unity login> (Do not enter any trailing suffix/modifier)

Password: <enter your Unity password>

eos% add oracle10g

eos% sqlplus


SQL*Plus: Release 10.1.0.3.0 - Production on [date & time stamp]

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

Enter Your Unity login@orcl

Enter password: <enter your oracle password>

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

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|

 

Setting up your Oracle account for Autotrace

  1. ssh to remote-linux.eos.ncsu.edu
  2. Sqlplus to your oracle account
  3. execute the sql script /afs/bp.ncsu.edu/dist/oracle10g/OraHome1/rdbms/admin/utlxplan.sql as follows:

    @/afs/bp.ncsu.edu/dist/oracle10g/OraHome1/rdbms/admin/utlxplan.sql

For more information about Autotrace check Oracle's Autotrace OTN Web page


|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|

Exporting/importing your Oracle data

The easiest way to export data is by invoking the "exp" utility and then let it prompt you what you want to export. To run the export utility:

  1. ssh to remote-linux.eos.ncsu.edu
  2. add oracle
  3. Execute the exp utility by typing the command: /afs/bp/dist/oracle9i/OraHome1/bin/exp and press <enter>
  4. user-name: oracle_username@orcl
  5. Password: <enter your Oracle password>
  6. answer the prompted questions. When asked for the output file, make sure to write the output to a location where you have write permissions such as your own home unity or eos directory.

Note: The import utility, "imp" works similarly as the exp utility.

|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> ALTER USER your_oracle_account_name IDENTIFIED BY newpassword;

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) Does the Oracle server support XML data types?

Yes, for a simple exercise please check here

6) 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 10g Release 1, 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\ora90\network\admin) as follows:

ORCL_ORA.CSC.NCSU.EDU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora.csc.ncsu.edu)(PORT = 1523))
)
(CONNECT_DATA = (SID = orcl)(SERVER = DEDICATED))
)

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

|top|

Accessing Oracle Database from Eclipse IDE

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(orca.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

go to top