Oracle 表结构管理

表其实是数据的‘容器’。oracle有几种类型的表：

• 普通表（ordinary table)又叫堆组织表。
• 聚簇表（clustered table）
• 分区表（partition table）
• 外部表（external table）
• 临时表 （temporary table）
• 索引组织表（index-Organized table IOT）

聚簇是表的一种特殊结构，一个聚簇有多个表组成，几个表共享相同的数据块。一个聚簇有一个或者多个公共的列，多个表共享这些列（聚簇关键字 Cluster Key）。

Oracle  把多个表的数据物理的存储在一起，以加速表的连接（join），这是聚簇的特点。

只有在创建聚簇后，才能在聚簇中创建表，在往聚簇表中插入数据之前必须在聚簇上创建聚簇索引。

在应用中满足下面的条件时才适合使用聚簇：

• 建立聚簇表的目的是为了查询而不是为了修改，繁修改的表不适合创建成聚簇表；
• 查询时，常常对聚簇中的多个表进行连接

创建聚簇表的步骤

• 1，创建聚簇。
• 2，把多个表加入到聚簇中（创建聚簇表）

select tablespace_name  from dba_tablespaces;
create cluster tb_cluster (postcode int) tablespace userdb；
SQL> create table stu (
  2  id int primary key,
  3  name varchar2(20) not null,
  4  postcode int)
  5  cluster tb_cluster (postcode);
SQL>  create table address_info (
  2   postcode int primary key,
  3   name varchar2(30),
  4  detail varchar2(30))
  5  cluster tb_cluster (postcode);

表已创建。

创建索引组织表（index-Organized Table)

create table stu(
name varchar2(20) PRIMARY KEY,
id NUMBER,
detail  VARCHAR2(100))
ORGAINZATION INDEX
TABLESPACE users
PCTTHRESHOLD 30
INCLUDING detail
OVERFLOW TABLESPACE myspace;

索引组织表中一定要有主键。ORGAINZATION INDEX 是指定创建的表示索引组织表，pctthreshold 是指定溢出比例，如果超过溢出比例的限制，则溢出部分讲被存储到溢出区中。

including 指定列名，表示从这个列以后的所有列将存储在溢出区中。overflow tablespace 指定溢出表空间。

创建外部表

先创建本地目录F:\temt\data F:\temt\bad F:\temt\log.

首先用sys身份创建目录对象，授权给用户 item

SQL> CREATE OR REPLACE DIRECTORY dat_dir AS 'f:\temtb\data';

目录已创建。

已用时间:  00: 00: 00.09
SQL> CREATE OR REPLACE DIRECTORY log_dir AS 'F:\temtb\log';

目录已创建。

已用时间:  00: 00: 00.04
SQL> CREATE OR REPLACE DIRECTORY bad_dir AS 'F:\temtb\bad';

目录已创建。

已用时间:  00: 00: 00.07
SQL> GRANT READ ON DIRECTORY dat_dir to item;

授权成功。

已用时间:  00: 00: 00.12
SQL> GRANT READ,WRITE ON DIRECTORY log_dir TO item;

授权成功。

已用时间:  00: 00: 00.01
SQL> GRANT READ,WRITE ON DIRECTORY bad_dir TO item;

授权成功。

连接item用户：

conn item

create table fitness_member
(id integer,
name VARCHAR2(14),
city VARCHAR2(30),
age int)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER    //指定访问的驱动是ORACLE_LOADER
DEFAULT  DIRECTORY dat_dir // 指定数据的默认存放路径
ACCESS PARAMETERS
(
  records delimited by newline   //一行为一条记录
  badfile bad_dir:'empxt%a_%p.bad'  //指定坏文件的名字及路径
  logfile log_dir:'empxt%a_%p.log'
fields terminated by ','  //指定字段之间是以逗号进行分割的
missing field VALUES are NULL
(id,name,city,age)
)
LOCATION ('temstu.txt')    //指定存放数据的文件的名字
   )
PARALLEL
REJECT LIMIT UNLIMITED;  //表示允许无限制的行发生错误

 

SQL> select * from fitness_member;

        ID NAME           CITY                                  AGE
---------- -------------- ------------------------------ ----------
     50016 xiao1          shanghai                               22
     30021 xiao2          shanghai                               22
     30032 xiaoW           beijing                               23
     20033 xiaoZ          guangzhou                              24

已选择4行。

注释：一般情况下 %a 数据库的ID；%p 进程ID；

创建临时表

临时表用于 临时存放中间数据.

• 创建事务型临时表

create global temporary table gtt2
(name varchar2(10),
id number,
birthday date)
on commit delete rows;

• 创建会话型临时表

create global temporary table gtt2
  (name varchar2(10),
   id number,
   birthday date)
   on  commit preserve  rows;

修改表的定义

添加length列 alter table t add ( length number(8,3));

重命名列 alter table t rename column length to new_length;

改变列的属性 alter table t modify (new_length  not null | number (4,1) |encrypt using ‘3DES168’ |DECRYPT)…..非空、改变列的宽带、加密、解密

为 表手工分配一个新的分区  alter table t allocate extent (size 50K);

删除列 alter table t drop (length,address);

表管理技巧

将表移动到一个新的段 Segment

alter table t move

storage (

initial 20K

minextents 2

…

)

将表移动到其他表空间

alter table  t  move tablespace myspace2;

锁住表

lock table t

in exclusive mode

nowait; //行级锁 

锁住指定行

select * from t where name =’**’ for update;

 

让一个列自动增长：

Oracle没有提供一种便捷的方法实现表中某列的自动增长，但是我们可以使用一种变通的方式实现表中某列的自动增长。

create table myorder
  2  (order_NO NUMBER,
  3  order_name VARCHAR2(20),
  4  order_desc VARCHAR2(20));
 create SEQUENCE seq_order_NO
  2   START WITH 1
  3     MAXVALUE 999999999999999999999999
  4     MINVALUE 1
  5     NOCYCLE
  6     CACHE 100;
 

create or replace  TRIGGER trigger_myorder
  2  BEFORE INSERT ON myorder
  3  FOR EACH ROW
  4  BEGIN SELECT seq_order_NO.NEXTVAL INTO :NEW.order_NO from dual;
  5  END;
  6  /
创建触发器，在插入数据之前每一行都出发，使order_NO自动自动增加，.NEXTVAL 表示取序列（seq_order_NO）的下一个值

SQL> INSERT INTO myorder

2 (order_name,order_desc)
3 VALUES
4 ('光盘采购','采购公司需要的光盘');

SQL> insert into myorder
2 (order_name,order_desc)
3 VALUES
4 ('大米','要采购来自美国的大米');

已创建 1 行。

SQL> select * from myorder;

ORDER_NO ORDER_NAME ORDER_DESC
---------- -------------------- --------------------
1 光盘采购 采购公司需要的光盘
2 大米 要采购来自美国的大米

 可以看到 order_NO一列的值在自动增长。