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