Advanced database lab module

• How to create user:

Create user username identified by password;
For example :
Create user amanuel identified by 1234 ;
• How to grant to user:
Grant all privilege to username;
For example:
Grant all privilege to amanuel;
How to expire password:
Alter user amanuel password expire;
• How to lock account:
Alter user username identified by password account lock;
Or Alter user username account lock;
For example:
Alter user amanuel identified by 1234 account lock;
Or Alter user amanuel account lock;
• How to unlock account:
Alter user username identified by password account unlock;
Or Alter user username account unlock;
For example:
Alter user amanuel identified by password 1234 account unlock;
Or Alter user amanuel account unlock;
• How to expire the password and change it:
alter user username identified by password account lock password expire;
Or alter user username account lock password expire;
For example:
alter user amanuel identified by 1234 account lock password expire;
Or alter user amanuel account lock password expire;
When you unlock the account, password is expire
and tells u to insert another password
• How to drop user:
Drop user username;
For example:
Drop user amanuel;
• How to create table:
Create table aman(column_name datatype,……);
For example:
Create table aman(emp_id number(5),emp_name varchar2(30));
SQL> desc aman
• How to Alter table:
Modify column:
Alter table table_name modify(column_name datatype);
For example:
Alter table aman modify(emp_name varchar(20);
Add column:
Alter table table_name add(column_name datatype);
For example:
Alter table aman add(last_name varchar2(20));
• How to rename table name:
Rename table_name to another_table_name;
For example:
Rename aman into amani;
Drop column:
Alter table table_name drop(column_name);
For example:
Alter table aman drop(emp_name);
• Drop table
Drop table table_name;
For example:
Drop table aman;
• How to Insert data into table
Insert into table_name(column1,column2,column3,..)
values(values1,values2,values3,..);
For example:
Insert into aman(emp_id,emp_name,last_name)
values(12,’alemayew’,’kelay’);
• How to insert multiple data into table using single insert
statement:
Insert all into table_name(column1,column2,column3,..)
values(values1,values2,values3,..) into table_name(column1,column2,column3,..)
values(values1,values2,values3,..) into table_name(column1,column2,column3)
values(values1,values2,values3,…)….) select * from dual;
for example:
Insert all into aman(emp_id,emp_name,last_name) values(12,’alemayew’,’kelay’)
into aman(emp_id,emp_name,last_name) values(13,’abebe’ ,’beso‘) into
aman(emp_id,emp_name,last_name) values(14,’chala’,’chube’) select * from dual;
• Insertion by address:
Insert into table_name values(‘&column1’,’&column2’,…);
For example:
Insert into aman values(‘&emp_id’,’&emp_name’,’&last_name’);
• How to delete data from table:
Delete from table_name where condition;
For example:
Delete from aman where emp_id=13;
• How to update data in table:
UPDATE table_name
SET column_name = value [ column_name = value]...
[ WHERE condition ];
For example:
Update aman set emp_name=‘ababa’ where emp_id=12;
• Dual function: without table
For example
Select 1+1 from dual; =2
Select 5*5 from dual; =25
Select abs(-10) from dual; =10
Select sysdate from dual; =current date
• Dual function with table
For example: for example multiple insertion
• To drop primary key
• ALTER TABLE Persons DROP PRIMARY KEY;
• First create session privilege to new user
Syntax
Grant Create session to new_user;
Example
Grant create session to nn;
• To give select,delete,insert,update privilege
for another user
syntax
Grant select |delete |insert | update on
table_name to new_user_name;
Example:
Grant select |delete |insert | update on emp to
nn;
• If u want to give full privilege for user to
access the table you can use this command:
syntax :
Grant all on table_name to new_user;
Example:
Grant all on emp to nn;
• If no longer allow for user to access this table
you can use this command:
Revoke select |delete |insert | update on
table_name from new_user_name;
Example:
Revoke select |delete |insert | update on emp
from nn;
• You can test your setup by enabling AUTOTRACE and
executing a simple query:
SQL> set AUTOTRACE traceonly
SQL> select * from emp, dept
2 where emp.deptno=dept.deptno;

-------------
• SQL> set AUTOTRACE off
 TIMED_STATISTICS
•
The TIMED_STATISTICS parameter specifies whether Oracle should
measure the execution time for various internal operations. Without
this parameter set, there
is much less value to the trace file output. As with other parameters,
you can set
TIMED_STATISTICS either on an instance level (in INIT.ORA) or on a
session level.
• SQL> alter session set timed_statistics=true;
 SQL_TRACE
• The SQL_TRACE facility is used to trace all SQL activity of a
specified database session or instance down to a trace file in
the database server operating system.
• To enable tracing for the current session, you should issue
ALTER SESSION, as
shown here:

SQL> alter session set sql_trace=true;

SQL> alter session set sql_trace=false;
SQL> commit;
 Query optimization
Step One
• Executing this tree directly first creates a very
large file containing the CARTESIAN
PRODUCT of the entire EMPLOYEE,
WORKS_ON, and PROJECT files.
• explain plan for (your first query)
• select * from table (DBMS_XPLAN.DISPLAY);
 ….Continued
Step Two
• Moving SELECT operations down the query tree.
• an improved query tree that first applies the
SELECT operations to reduce the number of
tuples that appear in the CARTESIAN
PRODUCT.
• explain plan for (your first query)
• select * from table (DBMS_XPLAN.DISPLAY);
 Step Three
Applying the more restrictive SELECT operation first.
• A further improvement is achieved by switching the
positions of the EMPLOYEEand PROJECT relations
in the tree,as shown in (c).This uses the information
that Pnumber is a key attribute of the PROJECT
relation, and hence the SELECT operation on the
PROJECT relation will retrieve a single record only.
• explain plan for (your first query)
• select * from table (DBMS_XPLAN.DISPLAY);
 Step Four
Replacing CARTESIAN PRODUCT and SELECT with JOIN operations.

• We can further improve the query tree by

replacing any CARTESIAN PRODUCT
operation that is followed by a join condition
with a JOIN operation
• explain plan for (your first query)
• select * from table (DBMS_XPLAN.DISPLAY);
 • Step Five
Moving PROJECT operations down the query tree.
• Another improvement is to keep only the attributes needed
by subsequent operations in the intermediate relations, by
including PROJECT (π) operations as early as possible in
the query tree, as shown in (e). This reduces the attributes
(columns) of the intermediate relations, whereas the
SELECT operations reduce the number of tuples (records).
• explain plan for (your first query)
• select * from table (DBMS_XPLAN.DISPLAY);
 Locking
• lock table (table name) in share mode;
• Lock table (table name) in exclusive mode;