Oracle11g温习-第十二章:tables

摘要:
2013年4月27日星期六10:441、表的功能存储、管理数据的基本单元2、表的类型1)普通表:。PCTTHRESHOLD:用于指定数据块中为键列和

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

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

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.临时表的基础知识

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

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

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

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

临时表的不足之处:

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

2.不支持主外键关系

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

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

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

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

4.在仅仅查询数据的时候建议用游标:opencursorfor'sqlclause';

临时表的应用:

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

TempTable的另一个应用,就是存放数据分析的中间数据。

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、datablock 数据块

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

免责声明:文章转载自《Oracle11g温习-第十二章:tables》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇在WINDOWS中安装使用GSL(MinGW64+Sublime Text3 &amp;amp; Visual Studio)ubuntu 8.04开启3D桌面下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

Oracle报 ORA-00054资源正忙的解决办法

来源于:http://www.cnblogs.com/loveLearning/p/3625544.html oracle之报错:ORA-00054: 资源正忙,要求指定 NOWAIT 问题如下: SQL> conn scott/tiger@vm_databaseConnected to Oracle Database 11g Enterprise...

vue,element列表大数据卡顿问题,vue列表渲染慢,element表格渲染慢,表格渲染慢(卡),表格全选卡,使用umy-ui

https://u-leo.github.io/umy-ui/docs/index.html https://github.com/u-leo/umy-ui ### umy-ui 一套为开发者准备的基于 Vue 2.0 的桌面端组件库,完美解决表格万级数据渲染卡顿,编辑表格卡顿问题 > umy-ui叫(U米-ui)或者叫悠米-ui > um...

php mysql jquery ajax 查询数据库三级联动

1、php 页面打开直接展示第一个select option中的数据 2、当第一个下拉列表选中的内容发生改变的时候,查询数据库填充第二个下拉列表 3、当第二个下拉列表选中的内容发生改变时,查询数据库填充第三个下拉列表 注意点: 1、查询出来的数据,如果绑定到select上 2、select cochange事件 3、ajax 请求,提交到某个php 页面,...

date

在linux环境中,不管是编程还是其他维护,时间是必不可少的,也经常会用到时间的运算,熟练运用date命令来表示自己想要表示的时间,肯定可以给自己的工作带来诸多方便。 1.命令格式: date[参数]...[+格式] 2.命令功能: date可以用来显示或设定系统的日期与时间。 3.命令参数: 必要参数: %H小时(以00-23来表示)。 %I小时(以01...

DVWA(三):SQL injection 全等级SQL注入

(本文不定期更新) 一、所需环境: 1.DVWA 2.web环境 phpstudy/wamp 3.burp suite 二、SQL注入产生的原因:   程序员在编写代码的时候,没有对用户输入数据的合法性进行判断,使应用程序存在安全隐患 用户可以提交一段数据库查询代码,根据程序返回的结果,获得某些他想得知的数据或进行数据库操作。 三、关于SQL注入需要注意...

IDEA创建SpringBoot项目整合JPA,连接Oracle数据库,使用Swagger进行测试

一、信息 IDEA  2019.1 jdk   1.8 Oracle  11.2.0.1.0 二、创建Spring Boot项目 1、选择JDK 2、根据你的公司名填写Group名,Artifact名不能包含大写,IDEA会报告含有非法字符,这一点挺奇怪的 3、选择依赖,这里我们选择Spring Data JPA和Spring Web,点击next 4...