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