Cluster creation in oracle 11g at college

In cluster creation first we create a cluster, index on that add tables for the cluster and see the cluster finally delete it

SQL> connect ram/r1234;             Connected.

SQL> create cluster emp_dept(deptno number(2));        Cluster created.

SQL> create index ramu on cluster emp_dept;                   Index created.

SQL> create table dept(dno number(2),name varchar2(10),loc varchar2(10)) cluster emp_dept(dno);

Table created.

SQL> create table empc(eno number(5),name varchar2(10),sal number(10,2),dno number(2)) cluster emp_dept(dno);

Table created.

SQL> select *from user_clusters;

CLUSTER_NAME                   TABLESPACE_NAME                  PCT_FREE

------------------------------ ------------------------------ ----------

  PCT_USED   KEY_SIZE  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT

---------- ---------- ---------- ---------- -------------- -----------

MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS

----------- ----------- ------------ ---------- ---------------

AVG_BLOCKS_PER_KEY CLUST FUNCTION          HASHKEYS DEGREE     INSTANCES  CACHE

------------------ ----- --------------- ---------- ---------- ---------- -----

BUFFER_ SINGL DEPENDEN

------- ----- --------

EMP_DEPT                       SYSTEM                                 10

        40                     2        255          65536

          1  2147483645                       1               1

CLUSTER_NAME                   TABLESPACE_NAME                  PCT_FREE

------------------------------ ------------------------------ ----------

  PCT_USED   KEY_SIZE  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT

---------- ---------- ---------- ---------- -------------- -----------

MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS

----------- ----------- ------------ ---------- ---------------

AVG_BLOCKS_PER_KEY CLUST FUNCTION          HASHKEYS DEGREE     INSTANCES  CACHE

------------------ ----- --------------- ---------- ---------- ---------- -----

BUFFER_ SINGL DEPENDEN

------- ----- --------

                   INDEX                          0          1          1     N

DEFAULT     N DISABLED

SQL> drop cluster emp_dept;                    ERROR at line 1:ORA-00951: cluster not empty

SQL> drop cluster emp_dept including tables cascade constraints;            Cluster dropped.

Concurrency control in oracle using locks

old   1: insert into e values(&empno,'&ename')

SQL> select *from e;

     EMPNO ENAME

---------- ----------

      7369 smith

      7499 allen

      7251 ward

SQL> select *from e where empno=7369 for update;

     EMPNO ENAME

---------- ----------

      7369 smith

SQL> update e set ename='ramu' where empno=7369;

1 row updated.

SQL> select *from e;

     EMPNO ENAME

---------- ----------

      7369 ramu

      7499 allen

      7251 ward

SQL> select *from e where empno=7369 or empno=7499 for update;

     EMPNO ENAME

---------- ----------

      7369 ramu

      7499 allen

SQL> lock table e in share mode;

Table(s) Locked.

SQL> update e set ename='dayina' where empno=7369;

1 row updated.

SQL> lock table e in share update mode;

Table(s) Locked.

SQL> lock table e in exclusive mode;

Table(s) Locked.