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 1 server. The students' Oracle10g account names are each student's unity ID with passwords being the new 9-digit student IDs (financial IDs not the SSNs) 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, orca.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! Note, you must "ssh" into ssh.ncsu.edu. If you are trying to access your Oracle Account from home, you must have an ssh client to connect to ssh.ncsu.edu. For more information about ssh please check here.

ssh remote-linux.eos.ncsu.edu

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

Password: <enter your Unity password>

unity% add oracle10g

unity% sqlplus


SQL*Plus: Release 10.1.0.3.0 - Production on Thu Oct 11 15:35:26 2007

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

Enter user-name: Your Unity login@orcl.world

Enter password: <enter your oracle password>

Connected to:
Oracle Database 10g Release 10.1.0.4.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 ssh.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. The exp utility can be found under "/afs/bp.ncsu.edu/dist/oracle9i/.install/sun4x_58/OraHome1/bin". To run the export utility:

  1. ssh to ssh.ncsu.edu
  2. add oracle
  3. change directory to afs/bp.ncsu.edu/dist/oracle9i/.install/sun4x_58/OraHome1/bin as follows:
    cd afs/bp.ncsu.edu/dist/oracle9i/.install/sun4x_58/OraHome1/bin
  4. type exp and press <enter>
  5. user-name: oracle_username@orcl.world
  6. Password: <enter your Oracle password>
  7. 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_ORCA.CSC.NCSU.EDU =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = orca.csc.ncsu.edu)(PORT = 1521))
)
(CONNECT_DATA = (SID = orcl)(SERVER = DEDICATED))
)

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

|top|

Additional Documentation
go to top