Prepared for MIS 6326 by Dr. Sumit Sarkar 6
Logging on to ORACLE 8i using Telnet
This handout will show you how you to use SQL Plus in ORACLE 8.0 for HW # 6. The instructions provided assume that you will be using a Windows-based (Windows NT/98/95) machine to remotely log on to the ORACLE server.
1. Logon to the Linux server called “” via telnet.
To do that, click the “Start” button in the left bottom of your windows NT/98/95 and select the “Run…” menu item. Type “telnet munin.utdallas.edu” in the pop-up window and click the “OK” button.
2. You will see a Telnet pop-up window. Enter the User ID “mis6326” and password “hw6s00” to log on to the Linux server (the User ID and password are the same for all students). Note that all alphabetic characters must be in lower case.
3. Once you are logged on to the server, you will see the prompt “[mis6326@munin mis6326]$” as shown above. You can now invoke SQL Plus by typing the command “sqlplus” and pressing Enter.
5. ORACLE will prompt you to enter your User ID and password for the Oracle database. Your user ID is the same one that you used for your individual FTP account. Your Oracle password has an additional character ‘m’ that precedes the password used for your FTP account. For example, if your FTP account password is “5021234”, your new password for the ORACLE database will be “m5021234”.
Once you have successfully logged on, the SQL *Plus 8.1 screen will default to a SQL> prompt as shown above. From this prompt, you will be able to run the necessary SQL statements to complete your assignment.
6. To exit SQL Plus, type the command exit and press Enter. To end your telnet session, type the command logout and press Enter.
Note: If the log on procedure does not work, please verify that you have followed the instructions exactly as indicated. For instance, the id and password must be exactly as specified (computers can be real picky sometimes!).
Once you have logged on, you can change your password at the SQL> prompt, by typing the following command
ALTER USER USERNAME IDENTIFIED BY NEWPASSWORD;
In this command, the USERNAME is your current User ID. The NEWPASSWORD is the password you want to use in future.
This command gets executed once you hit the Enter key. Once the command is executed, you should get a message saying User Altered. This means that your password has been changed successfully.
USING SQL in ORACLE’s Editor: A Two Page Primer
The Editor in ORACLE is NOT user friendly! It is essentially a line editor. You will need to be careful in typing your commands. Furthermore, you will be working in a unix environment, which is also not user-friendly (for instance, the Backspace key is not recognized, and the Delete key serves as a backspace). However, you can copy and paste individual lines (part of your queries) that are similar to previous ones you have used. Remember to use the copy and paste operations from the Edit menu (Ctrl+C and Ctrl+V are not supported by unix). Also, you can break up a query over several lines. The query is executed once you hit the Enter key. Note that in ORACLE, each SQL query must have a semi-colon at the end. When you hit the Enter key without a semi-colon, ORACLE assumes that you have not finished entering the query. In that case, it will create a new line. Some examples are shown below. The system response is italicized for your benefit.
I. CREATING A TABLE:
SQL> create table inventory (
2 partno integer,
3 description char(15),
4 qonhand integer);
Table created.
In the above query, lines 2, 3, and 4 resulted from Enter commands. The Enter command after the semi-colon resulted in the query being executed. The message “Table created.” indicates that the query ran successfully. If it does not (typically due to syntax errors), some other message will appear.
II. INSERTING DATA IN A TABLE:
You will need to run an INSERT query to insert each row of data. For example, to insert data into the table created above you would run a query such as the following:
SQL> insert into inventory (partno, description, qonhand)
2 values (207,'gear',75);
To insert another row of data in the above table, you must run a fresh INSERT statement. Instead of retyping the whole query, you can copy and paste part of the query that will not change (e.g., line 1 and part of line 2).
SQL> insert into inventory (partno, description, qonhand)
2 values
Then, you will only have to type the data that is being inserted into the table. For e.g., the data to be inserted could be (209,'cam',50).
III. RETRIEVING DATA FROM A TABLE:
Once data has been inserted into the table you can run a simple query to display all of the information in that table.
Example 1:
SQL> select * from inventory;
PARTNO DESCRIPTION QONHAND
------
207 gear 75
209 cam 50
221 bolt 650
222 bolt 1250
231 nut 700
232 nut 1100
241 washer 6000
285 wheel 350
290 hozel 155
295 belt 85
9 rows selected.
Example 2:
SQL> select suppliers.name from suppliers;
NAME
------
Your name
defecto parts
vesuvius, inc.
atlantis co.
titanic parts
eagle hardware
sky parts
knight ltd
8 rows selected.
IV. DISPLAYING THE LIST OF TABLES YOU HAVE CREATED:
SQL> select table_name from user_tables;
This will display the list of all tables you have created in your account. Another way to do this is to use the following query:
SQL> select * from tab;
V. MAKING CHANGES PERMANENT:
The way ORACLE is set up, changes made to data in a table are made permanent when you exit the current session, i.e., after you exit SQL Plus. Until that time, all changes are made to temporary copies of your tables (typically in main memory). If you wish to make the changes permanent after running a query without waiting until the end of the session, follow the steps shown.
Enter the following command at the start of your session (once you have obtained the SQL prompt).
SQL> SET AUTOCOMMIT ON;
After each data modification statement (e.g., an INSERT, UPDATE, or DELETE statement), or at periodic intervals, enter the following command.
SQL> COMMIT;
This will record the changes made up to that point on the stored copies of your tables.
VI. ENDING THE CURRENT SESSION:
To end the current session in SQL Plus, type Exit at the SQL prompt and press Enter. To end your telnet session, type the command logout and press Enter.
AUTHORIZATION in ORACLE
System Privileges: DBA provides to Users to access the Database
Object Privileges: DBA/Users Provide to Other Users to Work with Objects (Tables, Views, Procedures, Sequences, etc.)
Object Privilege / Table / View / SequenceAlter / Y / Y
Index / Y
Delete / Y /
Y
Insert / Y / YSelect / Y / Y / Y
Update / Y / Y
References / Y
1. GRANT
GRANT SELECT ON S TO User-name;
GRANT SELECT, UPDATE ON S TO User-name;
GRANT SELECT, UPDATE, INSERT ON S TO User-name;
GRANT SELECT, UPDATE (STATUS, CITY) ON S TO User-name;
Also : GRANT ALL ON S TO User-name;
Note that this enables the grantee to SELECT, UPDATE, INSERT, etc.
2. WITH GRANT OPTION
GRANT SELECT, UPDATE ON S TO User-name
WITH GRANT OPTION;
3. REVOKE
REVOKE SELECT ON S FROM User-name;
REVOKE SELECT, UPDATE ON S FROM User-name;