第十二章： 表（2）
4、临时表：用于电子商务的网上购物
1）基于事务，在事务提交时，自动删除记录
11:42:30 SQL> create global temporary table
11:42:37 2 tmp_01 (id int) on commit delete rows;
Table created.
11:42:52 SQL> insert into tmp_01 values (1);
1 row created.
11:43:07 SQL> insert into tmp_01 values (2);
1 row created.
11:43:09 SQL> select * from tmp_01;
ID
----------
1
2
11:43:14 SQL> commit;
Commit complete.
11:43:17 SQL> select * from tmp_01;
no rows selected
11:43:20 SQL>
2）基于会话，当用户退出session 时，自动删除记录
11:43:20 SQL> create global temporary table
11:43:38 2 tmp_02 (id int) on commit preserve rows;
Table created.
11:44:26 SQL> insert into tmp_02 values (1);
1 row created.
11:44:40 SQL> insert into tmp_02 values (2);
1 row created.
11:44:41 SQL> commit;
Commit complete.
11:44:43 SQL> select * from tmp_02;
ID
----------
1
2
11:44:51 SQL> conn scott/tiger
Connected.
11:44:57 SQL>
11:44:57 SQL> select * from tmp_02;
no rows selected
11:44:59 SQL>
5、datablock ：
pctfree ，过大，浪费块空间
过小：update 产生行迁移，insert 产生行链接，降低了记录的访问速度，影响性能。
6、表的空间（extent）管理：
当建立表的时候，建立想的段，然后自动分配相应的extent（1个或者多个），亦可以手工提前分配extent（用于需大量插入数据的表）
11:55:05 SQL> analyze table emp compute statistics;
Table analyzed.
11:55:37 SQL> select segment_name,extent_id,bytes/1024,blocks from user_extents
11:55:55 2 where segment_name='EMP';
SEGMENT_NAME EXTENT_ID BYTES/1024 BLOCKS
------------------------------ ---------- ---------- ----------
EMP 0 64 8
11:57:01 SQL> alter table emp allocate extent (size 1m datafile '/u01/app/oracle/oradata/prod/users01.dbf');
Table altered.
11:57:57 SQL> analyze table emp compute statistics;
Table analyzed.
11:58:06 SQL> select segment_name,extent_id,bytes/1024,blocks from user_extents
11:58:10 2 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.
11:58:13 SQL>
----------------回收未使用的空间
11:58:13 SQL> alter table emp deallocate unused;
Table altered.
12:00:30 SQL> analyze table emp compute statistics;
Table analyzed.
12:00:34 SQL> select segment_name,extent_id,bytes/1024,blocks from user_extents
12:00:38 2 where segment_name='EMP';
SEGMENT_NAME EXTENT_ID BYTES/1024 BLOCKS
------------------------------ ---------- ---------- ----------
EMP 0 64 8
12:00:43 SQL>
----------------表的数据块的移动
12:19:13 SQL> select count(*) from test;
COUNT(*)
----------
65536
12:19:24 SQL> analyze table test compute statistics;
Table analyzed.
12:19:36 SQL>
12:20:12 SQL> select table_name,NUM_ROWS ,blocks,empty_blocks,tablespace_name from user_tables
12:20:56 2 where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME
------------------------------ ---------- ---------- ------------ ------------------------------
TEST 65536 106 6 USER01
12:21:01 SQL> delete from test;
65536 rows deleted.
12:21:23 SQL> analyze table test compute statistics;
Table analyzed.
12:21:33 SQL> select table_name,NUM_ROWS ,blocks,empty_blocks,tablespace_name from user_tables
12:21:37 2 where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME
------------------------------ ---------- ---------- ------------ ------------------------------
TEST 0 106 6 USER01
12:21:40 SQL> alter table test move;
Table altered.
12:21:55 SQL> analyze table test compute statistics;
Table analyzed.
12:21:58 SQL> select table_name,NUM_ROWS ,blocks,empty_blocks,tablespace_name from user_tables
12:22:01 2 where table_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS TABLESPACE_NAME
------------------------------ ---------- ---------- ------------ ------------------------------
TEST 0 0 8 USER01
12:22:04 SQL>
---------------通过shrink 移动（需要重建索引）
12:24:44 SQL> analyze table test compute statistics;
Table analyzed.
12:25:26 SQL> select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables
12:25:32 2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
TEST USER01 6144 13 3
12:25:33 SQL> DELETE from test;
6144 rows deleted.
12:25:45 SQL> analyze table test compute statistics;
Table analyzed.
12:25:49 SQL> select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables
12:25:52 2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
TEST USER01 0 13 3
12:26:48 SQL> alter table test enable row movement;
Table altered.
12:27:09 SQL> alter table test shrink space;
Table altered.
12:27:27 SQL> analyze table test compute statistics;
Table analyzed.
12:27:36 SQL> select table_name,tablespace_name,num_rows,blocks,empty_blocks from user_tables
12:27:40 2 where table_name='TEST';
TABLE_NAME TABLESPACE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ---------- ------------
TEST USER01 0 1 7
12:27:43 SQL>
外部表管理：1)sql loader 导入 2） 通过external table管理（只读）
[oracle@work ~]$ mkdir /home/oracle/dat
[oracle@work ~]$ cd dat
[oracle@work 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@work 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
建立对应的目录：
12:37:11 SQL> CREATE OR REPLACE DIRECTORY admin_dat_dir
12:37:17 2 as '/home/oracle/dat';
Directory created.
12:37:35 SQL> CREATE OR REPLACE DIRECTORY admin_log_dir
12:37:50 2 as '/home/oracle/log';
Directory created.
12:37:56 SQL> CREATE OR REPLACE DIRECTORY admin_bad_dir
12:38:06 2 as '/home/oracle/bad';
Directory created.
12:38:14 SQL> !
[oracle@work ~]$ mkdir /home/oracle/{dat,log,bad}
[oracle@work ~]$ ls
bad dat Desktop empxt1.dat empxt2.dat log soft
[oracle@work ~]$
授权scott 可以访问所建立的目录
12:39:41 SQL> GRANT READ ON DIRECTORY admin_dat_dir TO scott;
Grant succeeded.
12:39:52 SQL> GRANT write ON DIRECTORY admin_log_dir TO scott;
Grant succeeded.
12:39:55 SQL> GRANT write ON DIRECTORY admin_bad_dir TO scott;
Grant succeeded.
12:39:58 SQL>
建立外部表
12:39:58 SQL> conn scott/tiger
Connected.
12:40:36 SQL>
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
12:40:37 32 REJECT LIMIT UNLIMITED;
Table created.
12:40:38 SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
TEST TABLE
ADMIN_EXT_EMPLOYEES TABLE
6 rows selected.
12:40:42 SQL>
查询外部表记录
12:42:58 SQL> 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 17-MAY-01 3000 0
50 jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1
80 mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0
10 bstarr
363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15
80 aalda
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT
----------- -------------------- ------------------------- ---------- ---------- --------- ---------- --------------
DEPARTMENT_ID EMAIL
------------- -------------------------
401 Jesse Cromwell HR_REP 203 17-MAY-01 7000 0
40 jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2
60 aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3
90 ccousins
404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0
110 jrichard
10 rows selected.
只能读，不能做dml
12:44:48 SQL> delete from ADMIN_EXT_EMPLOYEES;
delete from ADMIN_EXT_EMPLOYEES
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
把外部表数据插入到oracle 表里
12:45:25 SQL> 12:45:25 SQL> create table employees as select * from admin_ext_employees where 1=2;
Table created.
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
12:45:55 3 SELECT * FROM admin_ext_employees;
10 rows created.
12:45:56 SQL> 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 17-MAY-01 7000 0
40 jcromwel
402 Abby Applegate IT_PROG 103 17-MAY-01 9000 .2
60 aapplega
403 Carol Cousins AD_VP 100 17-MAY-01 27000 .3
90 ccousins
404 John Richardson AC_ACCOUNT 205 17-MAY-01 5000 0
110 jrichard
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID MANAGER_ID HIRE_DATE SALARY COMMISSION_PCT
----------- -------------------- ------------------------- ---------- ---------- --------- ---------- --------------
DEPARTMENT_ID EMAIL
------------- -------------------------
360 Jane Janus ST_CLERK 121 17-MAY-01 3000 0
50 jjanus
361 Mark Jasper SA_REP 145 17-MAY-01 8000 .1
80 mjasper
362 Brenda Starr AD_ASST 200 17-MAY-01 5500 0
10 bstarr
363 Alex Alda AC_MGR 145 17-MAY-01 9000 .15
80 aalda
10 rows selected.
12:46:01 SQL>
