Table and Constraint Modification

    Adding and Modifying Columns,

    Dropping Tables,

    Renaming Objects, Viewing Dictionary.

Views

    Data Dictionary Views,

    Creating Simple Views,

    Dropping Views.

Indexes

    Manual and Automatic Indexes,

    Uses for Indexes, Creating Indexes,

    Removing Indexes.

User Access Control

    creating user, Granting System Privileges,

    Granting and Revoking Object Privileges,

    Changing Passwords, Alter user, Using Synonyms.

You need to open three sessions.  Each session will be a different user. 

In the first session login to "sqlplus" as "iself” password “schooling"

>>        sqlplus iself/schooling

Set the sql prompt to “iself.”

>>        set sqlprompt ‘iself > ’

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

>>        sqlplus system/manager

Set the sql prompt to “system.”

>>        set sqlprompt ‘system > ’

Go back to the iself session.

Drop the employee table.

>>        drop table employee;

Copy column of employe name, job, commission and department number of the “E-M-P” table to the employee table.

>>        create table employee

                  as select ename, job, comm, deptno from emp;

Describe the employee table.

>>     desc employee

Add a salary column to the employee table.

Use "add" clause to add a column.

>>     alter table employee

    add (salary number(8,2));

Describe the employee table.

>>     desc employee

The "salary" column was added.

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

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

>>     alter table employee

    modify (ename varchar2(15));

Describe the employee table to see the changes.

>>     desc employee

Rename the "employee" table to the "iself_employee" table.

>>      rename employee to iself_employee;

Query the iself_employee table.

>>      select * from iself_employee;

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.

>>      create view employee_name

    as select ename

    from iself_employee

    where deptno = 10;

Query against the new view.

>>      select * from employee_name;

The view should be compiled if its status is invalid.

Compile the view.

>>     alter view employee_name compile;

A view can be dropped if it is not needed.

Drop the view

>>     drop view employee_name;

Create an index on the employee name column on the employee table.

>>      create index employee_ename_ind_01

    on iself_employee (ename);

An index table can be reorganized if it has fragmentation.

Assuming you have index fragmentation, reorganize the index table.

>>     alter index employee_ename_ind_01 rebuild;

An index table can be dropped, if it is not needed.

Drop the employee_ename index table.

>>     drop index employee_lname_ind_01;

Go to the "system/manager" session.

Create a user with username “newuser” and password "newpass."

Make its default tablespace as "iself_data."

>>        create user newuser identified by newpass

    default tablespace iself_data;

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.

>>     grant resource, connect to newuser;

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

===

>>     sqlplus newuser/newpass

set the sql prompt to newuser.

          set sqlprompt ‘newuser > ‘

Change the newuser password to "mypass".

>>     alter user newuser identified by mypass;

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

Query the iself_employee table.

>>      select * from iself.iself_employee;

No access granted.

Go to the “system/manager” session

Create a public synonym.

The public synonym must be unique to an object.

The public synonym employees is exit.

>>    create public synonym employees for iself.iself_employee;

Drop the public synonym.

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 select and update on the employee table to the newuser.

The newuser can not delete or insert any record.

>>     grant select, update on employee to newuser;

Go to the "newuser” session

Query the employee table using its public synonym.

>>      select * from employee;

Create a private synonym emp_table for the iself employee table.

>>      create synonym emp_table for iself.iself_employee;

Query the table using the “emp_table” private synonym.

>>      select * from emp_table;

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

Drop the private synonym.

>>      drop synonym emp_table;

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

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

>>      revoke update, select on employee from newuser;

Back to the "newuser" session.

Query the employee table.

>>      select * from employee;

No access.

Now, you should practice this over and over, until you become a master at it.

Good Luck!