iSelfSchooling.com - Copyright © 1999-2009 iSelfSchooling.com ||  References  |  Job Openings
    Home  | Search more...  |  FREE Online VIDEO Oracle Training 

   Unlimited access!   

    Oracle  Syntax  | Suggestions

Copyright & User Agreement

Email2aFriend  | Homepage us! |  Bookmark

Products/Services

 Vision/Mission

 Community Sharing

 Services

  Products

 Biography

 Contact Us

 FAQ

 Current News

 Website Traffic

 FREE Training

 SQL

 PL/SQL

 Forms 

 Reports

 Other TOOLS

 Fundamentals

 Performance

 OEM

 Application Server

 Grid Control

 Articles

 Prepare for OCP

Oracle SYNTAX

 Oracle Functions

 Oracle Syntax

 Oracle 10g Syntax

  PL/SQL Syntax

UNIX and more...

 UNIX for DBAs

 LINUX for DBAs

 DB using PHP

  A+ Certification

 Basics of JAVA  

 Tips of  SEO

Finance/Jobs

 Financial Aid

 Skilled

 Oracle

 Jobs

  Magazine

More Training

 Q & Answers

 SQL-PL/SQL

 DBA

 Developer

 Important Notes

 Case Studies

 9i New Features

 10g New Features

 10g Qs/As

 Grid Control

 OracleAS # I

 OracleAS # II

  LDAP and OID

  HTTP Server

 Instructor-Led

  Virtual Hosts

 Community Sharing

More to know...

Acknowledgement**

 FREE Legal Forms

 Who is who

 Market Place

 University Directory

 Advisory Articles

 Links...

 

 

More Resources by Google:

Topics:  Hands-On 04 - Creating Other Database Objects in Oracle

 

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.

 

Manuscript

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!

 

 
 
Google
 
Web web site