create tablespace tsa
datafile '/oradata/beijing/tsa01.dbf' size 10m;
create tablespace tsb
datafile '/oradata/beijing/tsb01.dbf' size 10m;
create tablespace tsc
datafile '/oradata/beijing/tsc01.dbf' size 10m;
create tablespace tsd
datafile '/oradata/beijing/tsd01.dbf' size 10m;

10:50:38 SQL> CREATE TABLE sales_range
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount  NUMBER(10),
sales_date    DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_apr2011 VALUES LESS THAN(TO_DATE('04/01/2011','MM/DD/YYYY')) tablespace tsa,
PARTITION sales_jun2011 VALUES LESS THAN(TO_DATE('07/01/2011','MM/DD/YYYY')) tablespace tsb,
PARTITION sales_oct2011 VALUES LESS THAN(TO_DATE('10/01/2011','MM/DD/YYYY'))  tablespace tsc,
PARTITION sales_jan2012 VALUES LESS THAN(TO_DATE('01/01/2012','MM/DD/YYYY'))  tablespace tsd
);
Table created.
10:55:19 SQL> insert into  sales_range  values ( 1001,'tom',1000,to_date('2011-02-01', 'yyyy-mm-dd'));
1 row created.
10:55:34 SQL> insert into  sales_range  values ( 1002,'jerry',1000, to_date('2011-05-01', 'yyyy-mm-dd'));
1 row created.
10:55:43 SQL> insert into  sales_range  values ( 1003,'rose',1000, to_date('2011-08-01', 'yyyy-mm-dd'));
1 row created.
10:55:55 SQL> insert into  sales_range  values ( 1004,'john',1000, to_date('2011-01-01', 'yyyy-mm-dd'));
1 row created.
10:56:04 SQL> select * from sales_range partition (sales_apr2011);
10:56:09 SQL> insert into  sales_range  values ( 1005,'john',1000,'2011-11-01');
1 row created.
10:56:34 SQL> select * from sales_range partition (sales_jun2011);
10:56:36 SQL> select * from sales_range partition (sales_jun2011);
10:56:40 SQL> select * from sales_range partition (sales_oct2011);
10:56:47 SQL> select * from sales_range partition (sales_jan2012);
10:56:52 SQL> select * from sales_range ;
SALESMAN_ID SALESMAN_NAME                  SALES_AMOUNT SALES_DATE
----------- ------------------------------ ------------ -------------------
1001 tom                                    1000 2011-02-01 00:00:00
1004 john                                   1000 2011-01-01 00:00:00
1002 jerry                                  1000 2011-05-01 00:00:00
1003 rose                                   1000 2011-08-01 00:00:00
1005 john                                   1000 2011-11-01 00:00:00
10:57:08 SQL>

09:31:25 SQL> create cluster dept_emp_clu (deptno number(3))
pctfree 20 pctused 60
size 500 tablespace users;
Cluster created.

09:33:56 SQL> create table department(
id number(3) primary key,
dname varchar2(14) ,loc varchar2(13))
cluster dept_emp_clu(id);
Table created.
09:35:24 SQL> create table employee(
eno number(4) primary key ,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
dept_id number(3) references department
) cluster dept_emp_clu(dept_id);
Table created.

09:38:31 SQL> create index dept_emp_idx on cluster dept_emp_clu tablespace users;
Index created.
08:38:08 SQL> desc user_clusters;
08:38:34 SQL> select CLUSTER_NAME,TABLESPACE_NAME,CLUSTER_TYPE from user_clusters;
CLUSTER_NAME                   TABLESPACE_NAME                CLUST
------------------------------ ------------------------------ -----
DEPT_EMP_CLU
USERS                          INDEX

22:27:03 SQL> select e.eno,e.ename,e.sal,d.id,d.dname,d.loc from employee e,department d where e.dept_id=d.id;
ENO ENAME             SAL         ID DNAME                LOC
---------- ---------- ---------- ---------- -------------------- -------------
7782 CLARK            2450         10 ACCOUNTING           NEW YORK
7839 KING             5000         10 ACCOUNTING           NEW YORK
7934 MILLER           1300         10 ACCOUNTING           NEW YORK
7369 SMITH             800         20 RESEARCH             DALLAS
7566 JONES            2975         20 RESEARCH             DALLAS
7788 SCOTT            3000         20 RESEARCH             DALLAS
7876 ADAMS            1100         20 RESEARCH             DALLAS
7902 FORD             3000         20 RESEARCH             DALLAS
7499 ALLEN            1600         30 SALES                CHICAGO
7521 WARD             1250         30 SALES                CHICAGO
7654 MARTIN           1250         30 SALES                CHICAGO
7698 BLAKE            2850         30 SALES                CHICAGO
7844 TURNER           1500         30 SALES                CHICAGO
7900 JAMES             950         30 SALES                CHICAGO
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1419571889
--------------------------------------------
| Id  | Operation             | Name       |
--------------------------------------------
|   0 | SELECT STATEMENT      |            |
|   1 |  NESTED LOOPS         |            |
|   2 |   TABLE ACCESS FULL   | DEPARTMENT |
|   3 |   TABLE ACCESS CLUSTER| EMPLOYEE   |
--------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
17  consistent gets
0  physical reads
0  redo size
1042  bytes sent via SQL*Net to client
384  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
14  rows processed
22:27:09 SQL> select e.empno,e.ename,e.sal,d.dname,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
EMPNO ENAME             SAL DNAME          DNAME          LOC
---------- ---------- ---------- -------------- -------------- -------------
7369 SMITH             800 RESEARCH       RESEARCH       DALLAS
7499 ALLEN            1600 SALES          SALES          CHICAGO
7521 WARD             1250 SALES          SALES          CHICAGO
7566 JONES            2975 RESEARCH       RESEARCH       DALLAS
7654 MARTIN           1250 SALES          SALES          CHICAGO
7698 BLAKE            2850 SALES          SALES          CHICAGO
7782 CLARK            2450 ACCOUNTING     ACCOUNTING     NEW YORK
7788 SCOTT            3000 RESEARCH       RESEARCH       DALLAS
7839 KING             5000 ACCOUNTING     ACCOUNTING     NEW YORK
7844 TURNER           1500 SALES          SALES          CHICAGO
7876 ADAMS            1100 RESEARCH       RESEARCH       DALLAS
7900 JAMES             950 SALES          SALES          CHICAGO
7902 FORD             3000 RESEARCH       RESEARCH       DALLAS
7934 MILLER           1300 ACCOUNTING     ACCOUNTING     NEW YORK
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   518 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |    14 |   518 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   238 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
139  recursive calls
0  db block gets
59  consistent gets
3  physical reads
0  redo size
1257  bytes sent via SQL*Net to client
384  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
7  sorts (memory)
0  sorts (disk)
14  rows processed
22:27:43 SQL>

15:17:17 SQL> select dbms_rowid.rowid_block_number(rowid) from emp where rownum =1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
30
Elapsed: 00:00:00.12
15:17:26 SQL> select dbms_rowid.rowid_block_number(rowid) from dept where rownum =1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
14
Elapsed: 00:00:00.00

15:17:32 SQL> select dbms_rowid.rowid_block_number(rowid) from employee where rownum =1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
78
Elapsed: 00:00:00.01
15:17:39 SQL> select dbms_rowid.rowid_block_number(rowid) from department where rownum =1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
78

extended rowid：
object id （6）
relative file id（3）
block id （6）
row id（3）

22:31:27 SQL> select rowid ,ename,sal from emp;
ROWID              ENAME             SAL
------------------ ---------- ----------
AAACYCAAEAAAAAfAAA SMITH             800
AAACYCAAEAAAAAfAAB ALLEN            1600
AAACYCAAEAAAAAfAAC WARD             1250
AAACYCAAEAAAAAfAAD JONES            2975
AAACYCAAEAAAAAfAAE MARTIN           1250
AAACYCAAEAAAAAfAAF BLAKE            2850
AAACYCAAEAAAAAfAAG CLARK            2450
AAACYCAAEAAAAAfAAH SCOTT            3000
AAACYCAAEAAAAAfAAI KING             5000
AAACYCAAEAAAAAfAAJ TURNER           1500
AAACYCAAEAAAAAfAAK ADAMS            1100
AAACYCAAEAAAAAfAAL JAMES             950
AAACYCAAEAAAAAfAAM FORD             3000
AAACYCAAEAAAAAfAAN MILLER           1300
14 rows selected.
22:31:31 SQL> select rowid,deptno,dname from dept;
ROWID                  DEPTNO DNAME
------------------ ---------- --------------
AAACYAAAEAAAAAPAAA         10 ACCOUNTING
AAACYAAAEAAAAAPAAB         20 RESEARCH
AAACYAAAEAAAAAPAAC         30 SALES
AAACYAAAEAAAAAPAAD         40 OPERATIONS
22:31:42 SQL> select rowid,id,dname from department;
ROWID                      ID DNAME
------------------ ---------- --------------------
AAACZNAAEAAAABWAAA         10 ACCOUNTING
AAACZNAAEAAAABWAAB         20 RESEARCH
AAACZNAAEAAAABWAAC         30 SALES
AAACZNAAEAAAABWAAD         40 OPERATIONS
22:31:58 SQL> select rowid ,eno,ename,sal from employee;
ROWID                     ENO ENAME             SAL
------------------ ---------- ---------- ----------
AAACZNAAEAAAABWAAA       7369 SMITH             800
AAACZNAAEAAAABWAAB       7499 ALLEN            1600
AAACZNAAEAAAABWAAC       7521 WARD             1250
AAACZNAAEAAAABWAAD       7566 JONES            2975
AAACZNAAEAAAABWAAE       7654 MARTIN           1250
AAACZNAAEAAAABWAAF       7698 BLAKE            2850
AAACZNAAEAAAABWAAG       7782 CLARK            2450
AAACZNAAEAAAABWAAH       7788 SCOTT            3000
AAACZNAAEAAAABWAAI       7839 KING             5000
AAACZNAAEAAAABWAAJ       7844 TURNER           1500
AAACZNAAEAAAABWAAK       7876 ADAMS            1100
AAACZNAAEAAAABWAAL       7900 JAMES             950
AAACZNAAEAAAABWAAM       7902 FORD             3000
AAACZNAAEAAAABWAAN       7934 MILLER           1300
14 rows selected.
DECLARE
v_rowid_type  NUMBER;
v_OBJECT_NUMBER  NUMBER;
v_RELATIVE_FNO   NUMBER;
v_BLOCK_NUMBERE_FNO   NUMBER;
v_ROW_NUMBER   NUMBER;
BEGIN
DBMS_ROWID.rowid_info(rowid_in=>'&num',
rowid_type  =>v_rowid_type,
object_number  =>v_OBJECT_NUMBER,
relative_fno  =>v_RELATIVE_FNO,
block_number  =>v_BLOCK_NUMBERE_FNO,
ROW_NUMBER =>v_ROW_NUMBER);
DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type));
DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER));
DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO));
DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO));
DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER));
END;
/
Enter value for num:
old   7:     DBMS_ROWID.rowid_info(rowid_in=>'&num',
new   7:     DBMS_ROWID.rowid_info(rowid_in=>'',
PL/SQL procedure successfully completed.
22:36:48 SQL> set serverout on
22:36:53 SQL> /
Enter value for num: AAACZNAAEAAAABWAAA
old   7:     DBMS_ROWID.rowid_info(rowid_in=>'&num',
new   7:     DBMS_ROWID.rowid_info(rowid_in=>'AAACZNAAEAAAABWAAA',
ROWID_TYPE:1
OBJECT_NUMBER:9805
RELATIVE_FNO:4
BLOCK_NUMBER:86
ROW_NUMBER:0
PL/SQL procedure successfully completed
DECLARE
v_rowid_type  NUMBER;
v_OBJECT_NUMBER  NUMBER;
v_RELATIVE_FNO   NUMBER;
v_BLOCK_NUMBERE_FNO   NUMBER;
DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO));
v_ROW_NUMBER   NUMBER;
BEGIN
DBMS_ROWID.rowid_info(rowid_in=>'&num',
rowid_type  =>v_rowid_type,
object_number  =>v_OBJECT_NUMBER,
relative_fno  =>v_RELATIVE_FNO,
block_number  =>v_BLOCK_NUMBERE_FNO,
ROW_NUMBER =>v_ROW_NUMBER);
DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type));
DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER));
DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO));
DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO));
DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER));
END;
22:38:10  19   /
Enter value for num: AAACZNAAEAAAABWAAM
old   7:     DBMS_ROWID.rowid_info(rowid_in=>'&num',

new   7:     DBMS_ROWID.rowid_info(rowid_in=>'AAACZNAAEAAAABWAAM',

ROWID_TYPE:1

OBJECT_NUMBER:9805

RELATIVE_FNO:4

BLOCK_NUMBER:86

ROW_NUMBER:12

PL/SQL procedure successfully completed.

在建立cluster的表，通过rowid，可以看到不同的表的记录放在了相同的block 上

11:29:43 SQL>                                                                                                                     

  1  DECLARE  v_rowid_type  NUMBER;

  2  v_OBJECT_NUMBER  NUMBER;

  3  v_RELATIVE_FNO   NUMBER;

  4  v_BLOCK_NUMBERE_FNO   NUMBER;

  5  v_ROW_NUMBER   NUMBER;

  6  BEGIN

  7  DBMS_ROWID.rowid_info(rowid_in=>'&num',

  8  rowid_type  =>v_rowid_type,

  9  object_number  =>v_OBJECT_NUMBER,

 10  relative_fno  =>v_RELATIVE_FNO,

 11  block_number  =>v_BLOCK_NUMBERE_FNO,

 12  ROW_NUMBER =>v_ROW_NUMBER);
13  DBMS_OUTPUT.put_line('ROWID_TYPE:' ||TO_CHAR(v_rowid_type));
14  DBMS_OUTPUT.put_line('OBJECT_NUMBER:' ||TO_CHAR(v_OBJECT_NUMBER));
15  DBMS_OUTPUT.put_line('RELATIVE_FNO:' ||TO_CHAR(v_RELATIVE_FNO));
16  DBMS_OUTPUT.put_line('BLOCK_NUMBER:' ||TO_CHAR(v_BLOCK_NUMBERE_FNO));
17  DBMS_OUTPUT.put_line('ROW_NUMBER:' ||TO_CHAR(v_ROW_NUMBER));
18* END;
/
Enter value for num: AAAMfMAAEAAAAAgAAB
old   7: DBMS_ROWID.rowid_info(rowid_in=>'&num',
new   7: DBMS_ROWID.rowid_info(rowid_in=>'AAAMfMAAEAAAAAgAAB',
ROWID_TYPE:1
OBJECT_NUMBER:51148
RELATIVE_FNO:4
BLOCK_NUMBER:32
ROW_NUMBER:1
PL/SQL procedure successfully completed.
11:31:54 SQL>select object_name,object_id,object_type ,status from user_objects where object_name='EMP';
OBJECT_NAME                     OBJECT_ID OBJECT_TYPE         STATUS
------------------------------ ---------- ------------------- ----------
EMP                                 51148 TABLE               VALID
11:32:56 SQL> COL SEGMENT_NAME FOR A30
11:33:02 SQL>select segment_name,tablespace_name,file_id,block_id from dba_extents where segment_name='EMP';
SEGMENT_NAME                   TABLESPACE_NAME                   FILE_ID   BLOCK_ID
------------------------------ ------------------------------ ---------- ----------
EMP                            USERS                                   4         25
11:33:55 SQL> select segment_name,tablespace_name,file_id,block_id,EXTENT_ID,BYTES/1024 from dba_extents
11:34:25   2    where segment_name='EMP';
SEGMENT_NAME                   TABLESPACE_NAME                   FILE_ID   BLOCK_ID  EXTENT_ID BYTES/1024
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
EMP                            USERS                                   4         25          0         64
11:34:27 SQL

--转自