Oracle11g温习-第十二章：tables

2013年4月27日星期六

10:44

1、表的功能

存储、管理数据的基本单元(二维表：由行和列组成)

2、表的类型

1)普通表：【heap table(堆表) ：数据存储时，无序的，对它的访问采用全表扫描】。

2)分区表：【 (>2G) 对大表进行优化 (Range Partitioning，List PartitioningHash Partitioning，Composite Partitioning)】

——按range 建立分区表

SYS @ prod > 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 p1 values less than(TO_DATE('04/01/2011','MM/DD/YYYY')) tablespace lx01,

partition p2 values less than(TO_DATE('07/01/2011','MM/DD/YYYY')) tablespace lx02,

partition p3 values less than(TO_DATE('10/01/2011','MM/DD/YYYY')) tablespace lx03,

partition p4 values less than(TO_DATE('01/01/2012','MM/DD/YYYY')) tablespace lx04)

enable row movement

注意修改当前会话日期时间的格式

SYS @ prod > insert into sales_range values ( 1001,'tom',1000,'2011-02-01');

1 row created.

SYS @ prod > insert into sales_range values ( 1002,'jerry',1000,'2011-05-01');

1 row created.

SYS @ prod > insert into sales_range values ( 1003,'rose',1000,'2011-08-01');

1 row created.

SYS @ prod > insert into sales_range values ( 1004,'john',1000,'2011-01-01');

1 row created.

SYS @ prod > insert into sales_range values ( 1005,'john',1000,'2011-11-01');

1 row created.

SYS @ prod > select * from sales_range partition(p1);

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

SYS @ prod > select * from sales_range partition(p2);

SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE

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

1002 jerry 1000 2011-05-01 00:00:00

SYS @ prod > select * from sales_range partition(p3);

SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE

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

1003 rose 1000 2011-08-01 00:00:00

SYS @ PROD > select * from sales_range partition(p4);

SALESMAN_ID SALESMAN_NAME SALES_AMOUNT SALES_DATE

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

1005 john 1000 2011-11-01 00:00:00

SYS @ PROD > 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

SCOTT @ prod > desc user_tab_partitions;

SCOTT @ prod > select table_name,partition_name,subpartition_count,tablespace_name from user_tab_partitions;

3)索引组织表(IOT)(如果表经常以主键为索引查询，可建立索引组织表，加快表的访问速度)

——建立索引组织表

SCOTT @ prod > create table sales_info(

onstraid number(6) primary key, ——指定主键约束

customer_name varchar2(30),sales_amount number(10,2),

sales_date date,remark varchar2(2000))

organization index tablespace cuug ——指定organization index 选项

pctthreshold 20 including remark 【——pctthreshold，用于指定数据块中为键列和部分非键列数据所预留空间的百分比】

overflow tablespace lx02

SYS @ prod > select OWNER,TABLE_NAME,IOT_NAME,IOT_TYPE,STATUS from dba_tables

where IOT_NAME='SALES_INFO';

OWNER TABLE_NAME IOT_NAME I OT_TYPE STATUS

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

SYS SYS_IOT_OVER_9774 SALES_INFO IOT_OVERFLOW VALID

【定义索引表时，主键约束和ORGANIZATION index 选项必须指定。

PCTTHRESHOLD :用于指定数据块中为键列和部分非键列数据所预留空间的百分比；如果数据块剩余空间低于PCTTHRESHOLD 设置，Oracle会将其他数据存放到溢出段。

INCLUDING column ：用于指定数据被存放到溢出段的 起始列。

OVERFLOW TABLESPACE：用于指定溢出段所在的表空间。】

4)簇：cluster (多表链接查询)【 先建立簇，然后建立簇表，最后建立索引 】

1)建立簇

SCOTT @ prod >create cluster dept_emp_clu(deptno number(3)) pctfree 20 pctused 60 size 500 tablespace lx01;

Cluster created. size后面不需要加单位

2)建立簇表

SCOTT @ prod >create table department( id number(3) primary key,dname varchar(14),loc varchar2(13))

cluster dept_emp_clu(id);

Table created.

SCOTT @ prod > 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.

3)建立索引

SCOTT @ prod > create index dept_emp_idx on cluster dept_emp_clu tablespace lx02; ——在簇上建立索引，并将索引和簇放在不同的表空间

Index created.

cluster访问和普通表连接查询访问对比

SYS @ PROD > insert into department select * from scott.dept;

4 rows created.

SYS @ PROD > insert into employee select * from scott.emp;

14 rows created.

SYS @ PROD > set autotrace on

SYS @ PROD > select e.eno,e.ename,e.sal,d.id,d.dname,d.loc from employee e,department d

2 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

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

0 recursive calls 递归调用 0

0 db block gets

31 consistent gets 一致性读取 31

0 physical reads 物理读 0

0 redo size

1009 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

SYS @ PROD > select e.empno,e.ename,e.sal,d.dname,d.dname,d.loc from emp e,dept d

2 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 |

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

| 0 | SELECT STATEMENT | |

| 1 | NESTED LOOPS | |

| 2 | TABLE ACCESS FULL | EMP | 全表扫描 emp

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 然后通过索引访问表dept

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 访问唯一索引

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

Predicate Information (identified by operation id):

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

4 - access("E"."DEPTNO"="D"."DEPTNO")

Note

-----

- rule based optimizer used (consider using cbo)

Statistics

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

41 recursive calls 递归调用 41

0 db block gets

30 consistent gets 一致性读取 30

1 physical reads 物理读 1

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

2 sorts (memory) 内存排序 2

0 sorts (disk)

14 rows processed

3、 rowid ：行号(伪列)精确的定义记录的物理位置

extended rowid：

object id 对象号(6)，

relative file id 文件号(3)，

block id 块号(6)，

row id 行号(3)

分析rowid

——普通表和簇表的rowid ，对比

SQL> select rowid,ename,sal from scott.emp;

ROWID ENAME SAL

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

AAACYNAAEAAAAAeAAA SMITH 800

AAACYNAAEAAAAAeAAB ALLEN 1600

AAACYNAAEAAAAAeAAC WARD 1250

AAACYNAAEAAAAAeAAD JONES 2975

AAACYNAAEAAAAAeAAE MARTIN 1250

AAACYNAAEAAAAAeAAF BLAKE 2850

AAACYNAAEAAAAAeAAG CLARK 2450s

AAACYNAAEAAAAAeAAH SCOTT 6000

AAACYNAAEAAAAAeAAI KING 5000

AAACYNAAEAAAAAeAAJ TURNER 1500

AAACYNAAEAAAAAeAAK ADAMS 1100

AAACYNAAEAAAAAeAAL JAMES 950

AAACYNAAEAAAAAeAAM FORD 3000

AAACYNAAEAAAAAeAAN MILLER 1300

SQL> select rowid,deptno,dname from scott.dept;

ROWID DEPTNO DNAME

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

AAACYLAAEAAAAAOAAA 10 ACCOUNTING

AAACYLAAEAAAAAOAAB 20 RESEARCH

AAACYLAAEAAAAAOAAC 30 SALES

AAACYLAAEAAAAAOAAD 40 OPERATIONS

SQL> select rowid,ename,sal from employee;

ROWID ENAME SAL

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

AAACaEAAGAAAAEZAAA SMITH 800

AAACaEAAGAAAAEZAAB ALLEN 1600

AAACaEAAGAAAAEZAAC WARD 1250

AAACaEAAGAAAAEZAAD JONES 2975

AAACaEAAGAAAAEZAAE MARTIN 1250

AAACaEAAGAAAAEZAAF BLAKE 2850

AAACaEAAGAAAAEZAAG CLARK 2450

AAACaEAAGAAAAEZAAH SCOTT 6000

AAACaEAAGAAAAEZAAI KING 5000

AAACaEAAGAAAAEZAAJ TURNER 1500

AAACaEAAGAAAAEZAAK ADAMS 1100

AAACaEAAGAAAAEZAAL JAMES 950

AAACaEAAGAAAAEZAAM FORD 3000

AAACaEAAGAAAAEZAAN MILLER 1300

SQL> select rowid,id,dname from department;

ROWID ID DNAME

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

AAACaEAAGAAAAEZAAA 10 ACCOUNTING

AAACaEAAGAAAAEZAAB 20 RESEARCH

AAACaEAAGAAAAEZAAC 30 SALES

AAACaEAAGAAAAEZAAD 40 OPERATIONS

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

将rowid转换成十进制形式

SYS @ PROD > set serverout on

SYS @ PROD >

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: AAACYNAAEAAAAAeAAH

old 6: DBMS_ROWID.rowid_info(rowid_in=>'&num',

new 6: DBMS_ROWID.rowid_info(rowid_in=>'AAACYNAAEAAAAAeAAH',

ROWID_TYPE:1

OBJECT_NUMBER:9741

RELATIVE_FNO:4

BLOCK_NUMBER:30

ROW_NUMBER:7

PL/SQL procedure successfully completed.

SYS @ PROD > select object_name,object_id,object_type,status from user_objects

2 where object_name='EMP';

OBJECT_NAME OBJECT_ID OBJECT_TYPE STATUS

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

EMP 9741 TABLE VALID

SYS @ PROD > conn /as sysdba

SYS @ PROD > COL SEGMENT_NAME FOR A30

SYS @ PROD > select dbms_rowid.rowid_block_number(rowid) from emp where rownum < 2 ;

SYS @ PROD > select dbms_rowid.rowid_object(rowid) from emp where rownum < 2 ;

SYS @ PROD > select segment_name,tablespace_name,file_id,block_id,EXTENT_ID,BYTES/1024 from dba_extents where segment_name='EMP';

SEGMENT_NAME TABLESPACE_NAME FILE_ID BLOCK_ID EXTENT_ID BYTES/1024

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

EMP USERS 4 25 0 64

SYS @ PROD > select segment_name,segment_type,tablespace_name,file_id,block_id,blocks,releative_fno from user_segments where segment_name=’EMP’;

4、临时表(global temporary table)：用于电子商务的网上购物

临时表用于存放会话或事务的私有数据。建立临时表后，其结构会一直存在，但其数据只在当前事务内或当前会话内有效。

1.临时表的基础知识

临时表只在Oracle 8i 以及以上产品中支持。ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，但是临时表的结构以及元数据还存储在用户的数据字典中。

Oracle的临时表创建之后基本不占用表空间，临时表并非存放在用户的表空间中，而是存放在 Schema 所指定的临时表空间中。如果你没有指定临时表(包括临时表的索引)存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中(TEMP)。

可以对临时表创建索引，视图，触发器，可以用export和import工具导入导出表的定义，但是不能导出数据。表的定义对所有的会话可见。建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.

尽管对临时表的DML操作速度比较快，但同样也是要产生 Redo Log ，只是同样的DML语句，比对 PERMANENT 的DML 产生的Redo Log 少。

临时表的不足之处：

1.不支持lob对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。

2.不支持主外键关系

特性和性能(与普通表和视图的比较)

1.临时表只在当前连接内有效

2.临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用

3.数据处理比较复杂的时候时表快,反之视图快点

4.在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';

临时表的应用：

对于一个电子商务类网站，不同消费者在网站上购物，就是一个独立的 SESSION，选购商品放进购物车中，最后将购物车中的商品进行结算。也就是说，必须在整个SESSION期间保存购物车中的信息。同时，还存在有些消费者，往往最终结账时放弃购买商品。如果，直接将消费者选购信息存放在最终表(PERMANENT)中，必然对最终表造成非常大的压力。因此，对于这种案例，就可以采用创建临时表(ON COMMIT PRESERVE ROWS)的方法来解决。数据只在 SESSION 期间有效，对于结算成功的有效数据，转移到最终表中后，ORACLE自动TRUNCATE 临时数据；对于放弃结算的数据，ORACLE 同样自动进行 TRUNCATE ，而无须编码控制，并且最终表只处理有效订单，减轻了频繁的DML的压力。

Temp Table 的另一个应用，就是存放数据分析的中间数据。

1)基于事务，在事务提交时，自动删除记录

SCOTT @ prod > create global temporary table temp_01(id int) on commit delete rows;

Table created. ——在事务提交时删除记录

SYS @ PROD > insert into temp_01 values(1);

SYS @ PROD > insert into temp_01 values(2);

SYS @ PROD > select * from temp_01;

ID

----------

1

2

SYS @ PROD > commit;——提交

Commit complete.

SYS @ PROD > select * from temp_01;

no rows selected ——记录已经删除了

2)基于会话，当用户退出session 时，自动删除记录

SYS @ PROD > create global temporary table

temp_02(id int) on commit preserve rows;

Table created.

SYS @ PROD > insert into temp_02 values(1);

1 row created.

SYS @ PROD > insert into temp_02 values(2);

1 row created.

SYS @ PROD > commit;

Commit complete.

SYS @ PROD > select * from temp_02; ——提交还能看到记录

ID

----------

1

2

SYS @ PROD > conn /as sysdba ——会话结束后，记录删除

Connected.

SYS @ PROD > select * from temp_02;

no rows selected

5、data block 数据块

pctfree：预留空间的百分比，这个数值要得当

过大，浪费块空间

过小：update 产生行迁移，insert 产生行链接，降低了记录的访问速度，影响性能。

6、表的空间(extent)管理：

当建立表的时候，建立相应的段，然后自动分配相应的extent(1个或者多个)，亦可以手工提前分配extent(用于需大量插入数据的表)

——给表分配空间(allocate extent)

SYS @ PROD > analyze table emp compute statistics; ——该语句用于分析表

Table analyzed.

SYS @ PROD > select segment_name,extent_id,bytes/1024,blocks from user_extents where segment_name='EMP';

SEGMENT EXTENT_ID BYTES/1024 BLOCKS

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

EMP 0 64 8

SCOTT @ prod >alter table emp allocate extent(size 1m datafile '/u01/app/oracle/oradata/prod/users01.dbf');

Table altered. ——分配空间给表

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> select segment_name,extent_id,bytes/1024,blocks from user_extents where segment_name='EMP';

SEGMENT_NAME EXTENT_ID BYTES/1024 BLOCKS

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

EMP 0 64 8

EMP 1 64 8

EMP 2 64 8

EMP 3 64 8

EMP 4 64 8

EMP 5 64 8

EMP 6 64 8

EMP 7 64 8

EMP 8 64 8

EMP 9 64 8

EMP 10 64 8

EMP 11 64 8

EMP 12 64 8

EMP 13 64 8

EMP 14 64 8

EMP 15 64 8

EMP 16 1024 128 查看系统为表分配的数据扩展

17 rows selected.

——回收未使用的空间(deallocate unused)

SQL> alter table emp deallocate unused;

Table altered.

SQL> analyze table emp compute statistics;

Table analyzed.

SQL> select segment_name,extent_id,bytes/1024,blocks from user_extents where segment_name='EMP';

SEGMENT_NAME EXTENT_ID BYTES/1024 BLOCKS

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

EMP 0 64 8 回收空间以后查询系统为表分配的数据扩展

——表的数据块的移动(move)

SYS @ PROD > select count(*) from emp1;

COUNT(*)

----------

229376

SYS @ PROD > analyze table emp1 compute statistics;

Table analyzed.

SYS @ PROD > select table_name,num_rows,blocks,empty_blocks,tablespace_name from user_tables

where table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME

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

EMP1 0 1381 27 USERS

SYS @ PROD > delete from emp1;

229376 rows deleted.

SYS @ PROD > analyze table test compute statistics;

Table analyzed.

SYS @ PROD > select table_name,num_rows,blocks,empty_blocks,tablespace_name from user_tables

where table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME

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

EMP1 0 1381 27 USERS

——delete没有释放资源，已使用的blocks数没变

SYS @ PROD > alter table emp1 move;

Table altered.

SYS @ PROD > analyze table emp1 compute statistics;

Table analyzed.

SYS @ PROD > select table_name,num_rows,blocks,empty_blocks,tablespace_name from user_tables

where table_name='EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME

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

EMP1 0 0 8 USERS

——通过move，释放未使用的块

——通过shrink 移动(需要重建索引)

SYS @ PROD > analyze table emp1 compute statistics;

Table analyzed.

SYS @ PROD > select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables

where table_name='EMP1';

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

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

EMP1 USERS 458752 2700 116

SYS @ PROD > delete from emp1;

458752 rows deleted.

SYS @ PROD > analyze table emp1 compute statistics;

Table analyzed.

SYS @ PROD > select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables

where table_name='EMP1';

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

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

EMP1 USERS 0 2700 116

SYS @ PROD > alter table emp1 enable row movement; ——允许行迁移

Table altered.

SYS @ PROD > alter table emp1 shrink space; —— 收缩空间

Table altered.

SYS @ PROD > analyze table emp1 compute statistics;

Table analyzed.

SYS @ PROD > select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables

where table_name='EMP1';

TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

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

EMP1 USERS 0 1 7

外部表管理：

1) sql loader 导入

2) 通过external table管理(只读)

管理外部表

外部表是表结构被存放在数据字典，而表数据被存放在OS文件的表。通过使用外部表，不仅可以在数据库中查询OS文件的数据，还可以使用INSERT方式将OS文件数据装载到数据库中，从而实现SQL*Loader所提供的功能。建立外部表后，可以查询外部表的数据，在外部表上执行连接查询，或对外部表的数据进行排序。需要注意，外部表上不能执行DML修改，也不能在外部表上建立索引。

建立外部表

建立外部表时使用create table 命令完成的，但建立外部表时必须指定 organization external 选项 .与建立普通表不同,使用create table 建立外部表包括两部分内容: 一部分描述列的数据类型,另一部分描述os文件与表列的对应关系. 下面以访问os文件emp.dat 的数据为例,说明建立和使用外部表的方法,假定emp.dat包括以下数据:

——准备工作：

[oracle@solaris10 ~]$mkdir /export/home/oracle/dat

[oracle@solaris10 ~]$cd /export/home/oracle/dat

[oracle@solaris10 dat]$vi empxt1.dat

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus

361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper

362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr

363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda

[oracle@solaris10 dat]$vi empxt2.dat

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel

402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega

403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins

404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

——建立对应的目录：

SYS @ PROD > conn /as sysdba

Connected.

SYS @ PROD > create or replace directory admin_dat_dir 该目录存放数据文件

as '/export/home/oracle/dat';

Directory created.

SYS @ PROD > create or replace directory admin_log_dir 该目录存放导入操作产生的日志

as '/export/home/oracle/log';

Directory created.

SYS @ PROD > create or replace directory admin_bad_dir 该目录存放导入失败的那些数据

as '/export/home/oracle/bad';

Directory created.

SYS @ PROD > !

[oracle@solaris10 ~]$mkdir /export/home/oracle/{log,bad}

[oracle@solaris10 ~]$ls

1 Documents core dat local.login shell

1.sql afiedt.buf cr_anny_db.sql hell.txt local.profile x86

Desktop bad cr_dict.sql local.cshrc log

——授权scott 可以访问所建立的目录

SYS @ PROD > grant read on directory admin_dat_dir to scott;

Grant succeeded.

SYS @ PROD > grant write on directory admin_log_dir to scott;

Grant succeeded.

SYS @ PROD > grant write on directory admin_bad_dir to scott;

Grant succeeded.

——建立外部表

SYS @ PROD > conn scott/tiger

Connected.

SYS @ PROD >

CREATE TABLE admin_ext_employees

(employee_id NUMBER(4),

first_name VARCHAR2(20),

last_name VARCHAR2(25),

job_id VARCHAR2(10),

manager_id NUMBER(4),

hire_date DATE,

salary NUMBER(8,2),

commission_pct NUMBER(2,2),

department_id NUMBER(4),

email VARCHAR2(25)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY admin_dat_dir

ACCESS PARAMETERS

(

records delimited by newline

badfile admin_bad_dir:'empxt%a_%p.bad'

logfile admin_log_dir:'empxt%a_%p.log'

fields terminated by ','

missing field values are null

( employee_id, first_name, last_name, job_id, manager_id,

hire_date char date_format date mask "dd-mon-yyyy",

salary, commission_pct, department_id, email

)

)

LOCATION ('empxt1.dat', 'empxt2.dat')

)

PARALLEL

REJECT LIMIT UNLIMITED;

Table created.

SYS @ PROD > select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

BONUS TABLE

SALGRADE TABLE

TEST TABLE

ADMIN_EXT_EMPLOYEES TABLE

6 rows selected.

——查询外部表记录

SYS @ PROD > select * from ADMIN_EXT_EMPLOYEES;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

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

360 Jane Janus ST_CLERK 121 2001-05-17 00:00:00 3000 0 50 jjanus

361 Mark Jasper SA_REP 145 2001-05-17 00:00:00 8000 .1 80 mjasper

362 Brenda Starr AD_ASST 200 2001-05-17 00:00:00 5500 0 10 bstarr

363 Alex Alda AC_MGR 145 2001-05-17 00:00:00 9000 .15 80 aalda

401 Jesse Cromwell HR_REP 203 2001-05-17 00:00:00 7000 0 40 jcromwel

402 Abby Applegate IT_PROG 103 2001-05-17 00:00:00 9000 .2 60 aapplega

403 Carol Cousins AD_VP 100 2001-05-17 00:00:00 27000 .3 90 ccousins

404 John Richardson AC_ACCOUNT 205 2001-05-17 00:00:00 5000 0 110 jrichard

10 rows selected.

只能读，不能做dml

SYS @ PROD > delete from ADMIN_EXT_EMPLOYEES;

delete from ADMIN_EXT_EMPLOYEES

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

——把外部表数据插入到oracle 表里

SYS @ PROD > create table employees as select * from admin_ext_employees where 1=2;

Table created.

SYS @ PROD > insert into employees select * from admin_ext_employees;

10 rows created.

SYS @ PROD > select * from employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL

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

401 Jesse Cromwell HR_REP 203 2001-05-17 00:00:00 7000 0 40 jcromwel

402 Abby Applegate IT_PROG 103 2001-05-17 00:00:00 9000 .2 60 aapplega

403 Carol Cousins AD_VP 100 2001-05-17 00:00:00 27000 .3 90 ccousins

404 John Richardson AC_ACCOUNT 205 2001-05-17 00:00:00 5000 0 110 jrichard

360 Jane Janus ST_CLERK 121 2001-05-17 00:00:00 3000 0 50 jjanus

361 Mark Jasper SA_REP 145 2001-05-17 00:00:00 8000 .1 80 mjasper

362 Brenda Starr AD_ASST 200 2001-05-17 00:00:00 5500 0 10 bstarr

363 Alex Alda AC_MGR 145 2001-05-17 00:00:00 9000 .15 80 aalda

posted @ 2017-09-15 11:29 Oracle-fans 阅读(258) 评论(0) 编辑收藏举报

刷新页面返回顶部