Oracle Resource Web Page
CSC instructors can request Oracle accounts for their classes by sending an email to firstname.lastname@example.org. 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.
- Logging in to your Oracle class account
- Changing your Oracle password
- How to retrieve assigned Roles and Privileges to your Oracle account
- Setting your account for Autotrace
- Granting/Revoking Other Users access to your Objects
- Exporting/Importing your Oracle data
- Technical Specifications about the Oracle 10g DBMS
- Accessing Oracle Database from Eclipse IDE (unsupported)
- Additional Oracle Documentation
- Resource Page for DBAs
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!
- You need an ssh client. We recommend Putty. For Mac users use the Terminal app.
- Launch your ssh client and open a secured connection to the remote host: remote.eos.ncsu.edu.
- login: <enter your Unity ID>
- Password: <enter your Unity password>
- eos% add oracle11
- eos% sqlplus
SQL*Plus: Release 184.108.40.206.0 Production on [date & time stamp]
Copyright (c) 1982, 2004, Oracle. All rights reserved.
- Enter username: UnityID@orcl (Do not forget to include @orcl)
- Enter password: <enter your oracle password> (this is NOT your Unity Password - contact your instructor or TA about what is your initial password)
Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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.)
Changing password for username
Retype new password:
Retrieve Session Privileges:
- login to your Oracle account
- at the SQL> prompt type:
select * from session_privs;
Retrieve Session Roles:
select * from session_roles;
- ssh to remote.eos.ncsu.edu
- Sqlplus to your oracle account
- execute the utlxplan.sql script as follows:
For more information about Autotrace check Oracle's User's Guide.
The privileges a user can grant include these( On the user's tables, views, and materialized views):
- UPDATE (all or specific columns)
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;
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:
- ssh to remote.eos.ncsu.edu
- add oracle
- Execute the exp utility by typing the command: /afs/bp/dist/oracle9i/OraHome1/bin/exp and press <enter>
- user-name: UnityID@orcl
- Password: <enter your Oracle password>
- 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.
- ORACLE HOME PATH: /afs/eos.ncsu.edu/dist/oracle11/app/product/11.2.0/client_1
- Hostname: orca.csc.ncsu.edu
- DB Name: ORCL
- SID: ORCL01
- SERVICE NAME: ORCL
Requirements and Installations -
- Download Eclipse from Eclipse Downloads
- Installing the Eclipse Database Developement Plugin
- For Europa:
- Start eclipse
- At the top select Help->Software Updates->find and Install
- Expand "europa discover Site" and if "Database Developement" is there then select the box beside it, otherwise the plugin is already installed
- then click "Select Required" on the right side
- then click "next" and accept the terms then click "next" again
- finaly select "next" and then "Finish"
- when it asks you to install click "Install All" and when this is finished click "yes"
- For Ganymede:
- Start eclipse
- At the top select Help->Software Updates
- Select the "Availabe Software" tab
- Expand the "Ganymede Update Site"
- If the "Database Developement" is there select the box beside it and click "Install", otherwise the plugin is already installed
- then click "finish and when that is finished click "Yes"
Go to Oracle Instructions and follow whatever directions you prefer, keeping in mind that the update manager is the easiest way.
Using the Tool -
- 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
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
- Oracle 11g Release 2 Documentation Library
- Oracle11g JDBC Developer's Guide and Reference:
- metalink.oracle.com (can be setup to email you security updates)
- www.orakle.com (Full or Oracle recources and tools)
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?
3) How to move to Another User with Connect
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 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:
(ADDRESS = (PROTOCOL = TCP)(HOST = orca.csc.ncsu.edu)(PORT = 1521))
(SERVICE_NAME = orcl.csc.ncsu.edu)
Refer to your third-party client or programming tool documentation for further connectivity instructions.