SQL

Sometime you may want to open multiple SQLPLUS sessions. It is very normal that you may not know that what session you are in. To avoid this confusion, you can use the SQLPROMPT option to stay away from such problem.

In this hands-on, you need to open three sessions. Each session will be a different user. For example, in the first session login to "sqlplus" as "oracle" password "learning"

Set the sql prompt to "oracle"

Go to the second session and login to "sqlplus" as "system" password "manager"

Set the sql prompt to "system"

Go back to the oracle session.

When you drop a table all its dependencies such as its indexes, constraints, and triggers will be deleted. All the views, procedures, packages, and functions that reference to that table will be in an invalid status.

Drop the employee table.

If you want to copy a table, you can use the CREATE TABLE AS statement. It copies all records. Notice that the new table will not inherit any constraints, indexes, and triggers from original table. Only records will be copied not dependencies.

Copy column of employe name, job, commission and department number of the �EMP� table to the employee table.

AS SELECT ename, job, comm, deptno

When you create a table, you would like to know what its columns� name and their attributes are. The DESC command will display such information. Notice that you will not find its indexes information, and constraints� name.

Describe the employee table.

Once you create a table, you may want to delete, resize, or add more columns to it. The ALTER TABLE ADD statement will add a column or add a constraint to a table.

Add a salary column to the employee table.

Use "add" clause to add a column.

Describe the employee table.

The "salary" column was added.

The MODIFY option in the ALTER TABLE statement, can change a column�s datatype and its length. Note that if you decrease a column�s length, you may truncate some of the data in the column. Therefore, Oracle will not allow you to reduce the length of a column unless it is null. So in order to reduce a column�s size, just copy that column to an alternative table, and then null the column and change the size. Then put the column values back. If the column has constraint, you may have to disable the constraint.

Change the "ename" column size from 10 to 15.

Use the "modify" clause to modify the column table.

Describe the employee table to see the changes.

The RENAME command will change the table�s name. Remember that when you change the table name, all the status of those views, procedures, functions, and packages that were referencing to the table will be changed to an INVALID mode. You should modify them (views, procedures, functions, and packages) in order to force them to refer to the new table; and once they have been compiled or used, their status will be changed to a VALID mode.

Rename the "employee" table to the "oracle_employee" table.

Query the oracle_employee table to make sure that its data was not changed.

You use view to present rows and columns of a table in the way you want. You may use it for security reason. For example, you may eliminate some rows and columns that are very sensitive information. These changes are transparent to a user. There are two types of views. They are Simple View and Complex View. A Simple View is a view that was created only from one single table. In the contrast a Complex View is a view that it has two or more tables joining together. You may also insert, update, and delete a record using simple views. Do not try to insert, update, or delete a record from a complex view.

Create a view to display only the employees name of the �Accounting� department.

You may use a view for a security reason.

This is an example of a simple view.

Query against the new view to see the output.

As we mentioned before, when you rename a table, all the status of its associated views, procedures, functions, and packages become in an invalid mode. One way to change the status from invalid to valid is to use the �ALTER VIEW� statement with the COMPILE option.

The view should be compiled if its status is invalid.

Compile the view.

A view can be dropped if it is not needed. When you drop a view, all the tables used in the view will be untouched. But all the views that were created using the dropped view, their status will change to an INVALID mode.

Drop the employee_name view.

When you use the WHERE clause in your SQL statement, Oracle�s optimizer will check to see whether you have an index on that column or not. If not, then it will scan the whole table. If your table is a long table which normally it is. It may take a very long time to retrieve that query. Creating an index on a column that you normally have it in the WHERE clause, will eliminate to scan the table. Creating an index will increase a performance of a query. Keep this in mind, just making all columns index because that will increase a query�s performance, it is not a good practice. When you create an index on a column, any insert, update, and delete has to do one more task to maintain the index table. That may cause some performance problem.

There are different types of indexes:

1- B-Tree index,

2- Bitmap index, and

B-Tree index

When you create an index table by default will be the B-Tree index, unless you specify otherwise. The B-Tree index is the traditional index. It stores your data in a treelike fashion. It has the root node, which is an entry point for your search. It contains pointers to other nodes. The pointers in the root node point to another level in the index, which we call them branch nodes. The branch nodes also contain pointers to other nodes to the next level of node in the index. The highest level of the index is called the leaf nodes. Each leaf node is linked to its right and left leaf nodes. You create a B-Tree index on a column, if that column has a very high cardinality. The more unique value in a column represents a higher cardinality. For example: assuming that I have a table that contains 1000 records. If one of its columns (column c1) has 995 unique values against 1000 values, then that column has a high cardinality. In the contrast if one of its columns (columns c2) has 25 unique values against all 1000 values, then that column has a low cardinality.

Bitmap Index

You create a bitmap index against a column when you have a low cardinality. Think of a Bitmap Index as a matrix. Its columns correspond to all unique values in the column. Notice that the higher cardinality brings more columns in your index table. That is the reason; why you should use a Bitmap Index when your column has a very low cardinality.

Now, create a B-Tree index on the employee name column on the employee table.

An index table can be reorganized if it has fragmentation. A table may have fragmentation when you deleted lots of records or your table contains lots of migration or chained records.

Assuming that your employee_ename_ind_01 index table, it has an index fragmentation. Reorganize the index table.

An index table can be dropped, if it is not needed. When you drop an index table, its table will not be dropped.

Drop the employee_ename index table.

Go to the "system/manager" session.

No one can login to Oracle unless they have userid and password. They should also have been granted to �CREATE SESSION.� Otherwise, they will not able to login to SQLPLUS. They should have some system privileges in order to do their task such as CREATE TABLE, CREATE PROCEDURE, ALTER TABLE, etc. Make sure always to assign a default tablespace and a temporary tablespace to a user. Make sure that they are not able to write in the SYSTEM tablespace. Later in this book, you will learn how to stop users to create an object in the SYSTEM tablespace.

Create a user with username �newuser� and password "newpass."

Make its default tablespace as "oracle_data."

The RESOURCE and CONNECT roles contains some system privileges that allows users to login, create table, alter table, etc. For example: The CONNECT role has the CREATE SESSION system privilege. A user without the CREATE SESSION system privilege can not even login to SQLPLUS.

Notice that the user can not connect or create any objects unless the privileges are granted to it. Grant the resource and connect roles to newuser.

Go to the third session and connect as newuser password newpass.

SQL> sqlplus newuser/newpass

Set the sql prompt to newuser. The SQLPROMPT option is a good command to show a user that what session he/she is in.

Change the password often. It is a good practice. You as a user can only change your password by using the ALTER USER statement. You can not do any other altering such as changing your default tablespace, etc. The only user that can change your default tablespace is the one has already been granted the ALTER ANY USER system privilege.

Change the newuser password to "mypass".

Users should be granted access by other users to query their tables.

Query the oracle_employee table.

No access granted.

Go to the �system/manager� session

You create public synonym so that the users don�t need to type schema name to a table when they query the table. Creating a public synonym does not mean that oracle users can access to that table or object. Still the owner of the object has to grant access to a user on its table.

Create a public synonym. The public synonym must be unique to an object. The public synonym �employees� is exit.

It comes a time that you may have to drop a public synonym. Dropping a public synonym, it doesn�t drop its dependencies.

Drop the public synonym.

Then create it again.

Now, all the users that can access to oracle�s employee table should be able to access it through the public synonym.

Go to the "oracle" session

You can grant an specific object privilege to user or all the privileges.

For example:

GRANT SELECT gives only a query access on a table.

GRANT UPDATE gives only a write access on a table.

GRANT DELELTE gives only a delete access on a table.

GRANT INSERT gives only an insert access on a table and more�

GRANT ALL gives all the access on a table to a user.

Grant select and update on the employee table to the newuser. The newuser can not delete or insert any record.

Go to the "newuser� session

Query the employee table using its public synonym.

Sometime when you access to an object, you may have to type a lot of words. For example; I can access to a table like this:

SELECT * FROM [email protected];

Notice that typing [email protected] is not very practical every time. So, you may create your own private synonym.

SQL> CREATE SYNONYM emp

FOR [email protected];

From now on, you can access to the table by using emp. For example:

SQL> SELECT * FROM emp;

When you create a private synonym, it will be used only by you. No one can use that synonym.

Create a private synonym emp_table for the oracle employee table.

Query the table using the �emp_table� private synonym.

A user can drop its private synonym if is not needed.

Drop the emp_table private synonym.

Go to the "oracle" session and revoke the privileges from "newuser."

It comes a time that you may have to revoke some object or system privileges from a user.

Notice that an object privilege may have been granted to a user by using WITH GRANT OPTION. If that is the case then when you revoke an object privilege from a user, all the object privileges that were granted by that user to some other Oracle users will be revoked too.

In the contrast with the WITH GRANT OPTION, if a system privilege was granted WITH ADMIN OPTION. When you revoke it, all the system privileges that were granted by that user to some other Oracle users will not be revoked.

Revoke the update and select privileges on the employee table from newuser.

Back to the "newuser" session.

Query the employee table.

No access.

Q: Copy the �EMP� table to another table and name the new table "employee." In the new employee table use the employee name, job, commission and department number.

Q: Add a salary column to the employee table.

Q: Modify the "ename" column size from varchar10 to varchar15.

Q: Rename the "employee" table to the "oracle_employee" table.

Q: Create a view to display the employee names of the �Accounting� department only.

Q: Why do you use the view?

Q: How do you compile the view?

Q: How do you delete the view?

Q: Create an index on the employee table on the ename column only and name it employee_indx.

Q: Reorganize the �employee_indx� index table.

Q: Drop the employee_ename index table.

Q: Create a user with username �newuser� and password "newpass." Its default tablespace should be the "oracle_data" tablespace.

Q: Grant the resource and connect roles to newuser.

Q: Change the newuser password to "mypass".

Q: Can the above new user access to any other user tables?

Q: What is a public synonym?

Q: What is the syntax to create a public synonym?

Q: What is the difference between public and private synonym?

Q: Create and drop a private synonym.

Q: Revoke an object privilege on a table from a user.