iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 05

"Not everything that can be counted counts, and not everything that counts can be counted." - Albert Einstein (1879-1955)

 

Read first then play the video: 

   SQL-VIDEO -Creating Other Oracle Database Objects

  

Creating more Database Objects

SET SQLPROMPT

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 "iself” password “schooling"

MS-DOS> SQLPLUS iself/schooling

Set the sql prompt to “iself.”

SQL> SET SQLPROMPT ‘iself > ’

 

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

MS-DOS> SQLPLUS system/manager

Set the sql prompt to “system.”

SQL> SET SQLPROMPT ‘system > ’

 

Go back to the iself session.

 

DROP TABLE

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.

SQL> DROP TABLE employee;

 

CREATE TABLE AS …

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.

 

SQL> CREATE TABLE employee

              AS SELECT ename, job, comm, deptno

                       FROM emp;

 

DESCRIBE command

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.

SQL> DESC employee

 

ALTER TABLE ADD …

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.

SQL> ALTER TABLE employee

               ADD (salary NUMBER(8,2));

 

Describe the employee table.

SQL> DESC employee

The "salary" column was added.

 

ALTER TABLE MODIFY …

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.

SQL> ALTER TABLE employee

              MODIFY (ename VARCHAR2(15));

 

Describe the employee table to see the changes.

SQL> desc employee

 

RENAME command

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 "iself_employee" table.

SQL> RENAME employee TO iself_employee;

 

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

SQL> SELECT * FROM iself_employee;

 

CREATE VIEW AS …

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.

SQL> CREATE VIEW employee_name

              AS SELECT ename

                       FROM iself_employee

                       WHERE deptno = 10;

 

Query against the new view to see the output.

SQL> SELECT * FROM employee_name;

 

ALTER VIEW …

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.

SQL> ALTER VIEW employee_name COMPILE;

 

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

SQL> DROP VIEW employee_name;

 

CREATE INDEX command

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.

SQL> CREATE INDEX employee_ename_ind_01

              ON iself_employee (ename);

 

ALTER INDEX REBUILD

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.

SQL> ALTER INDEX employee_ename_ind_01 REBUILD;

 

DROP INDEX …

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.

SQL> DROP INDEX employee_lname_ind_01;

Go to the "system/manager" session.

 

CREATE USER …

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 "iself_data."

SQL> CREATE USER newuser IDENTIFIED BY by newpass

               DEFAULT TABLESPACE iself_data;

 

GRANT RESOURCE, CONNECT statement

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.

SQL> GRANT resource, connect 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.

SQL> SET SQLPROMPT ‘newuser > ‘

 

ALTER USER …

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

SQL> ALTER USER newuser IDENTIFIED BY mypass;

 

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

Query the iself_employee table.

SQL> SELECT * FROM iself.iself_employee;

No access granted.

Go to the “system/manager” session

 

CREATE PUBLIC SYNONYM …

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.

SQL> CREATE PUBLIC SYNONYM employees FOR iself.iself_employee;

 

DROP PUBLIC SYNONYM …

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.

SQL> DROP PUBLIC SYNONYM employees;

Then create it again.

 

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

Go to the "iself" session

 

GRANT command

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.

SQL> GRANT SELECT, UPDATE ON employee TO newuser;

 

Go to the "newuser” session

Query the employee table using its public synonym.

SQL> SELECT * FROM employee;

 

Private 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 go2mydatabase_in_AL@accounting.employees;

 

Notice that typing go2mydatabase_in_AL@accounting.employees is not very practical every time. So, you may create your own private synonym.

SQL> CREATE SYNONYM emp

FOR go2mydatabase_in_AL@accounting.employees;

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 iself employee table.

SQL> CREATE SYNONYM emp_table FOR iself.iself_employee;

 

Query the table using the “emp_table” private synonym.

SQL> SELECT * FROM emp_table;

 

DROP SYNOMYM …

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

Drop the emp_table private synonym.

SQL> DROP SYNONYM emp_table;

 

Go to the "iself" 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.

SQL> REVOKE UPDATE, SELECT ON employee FROM newuser;

 

Back to the "newuser" session.

Query the employee table.

SQL> SELECT * FROM employee;

No access.

 

 

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." - Albert Einstein (1879-1955)

 

Questions:

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 "iself_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 "iself_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.