Oracle 10g 对象管理

文章由LinuxBoy分享于2019-03-30 05:03:47热评（86）

Oracle 10g 对象管理

Oracle数据库中，表，索引，存储过程，函数等都是对象。
schema 的名称与用户名相同，但是与用户不是同一回事，如果用户没有任何对象，则schema就不存在。
一、表
ORACLE表分四类：
普通表     --- 一个表对应一个segment
分区表 Partition Table   虚拟表，没有对应的segment
索引组织表 Index Organized Table 简称 IOT   虚拟表
簇表      ---   虚拟表首先建立一个簇，一个簇对应一个segment

1、普通表
1）tablespace部分设置表的logging属性， Yes 表示对表DML操作生成重做条目， No 不生成。
2）Extents部分，设置Initial size
3）Space Usage部分，设置PCTFREE ，默认10 ，表示数据块可用空间低于10%以后，数据块就不允许insert。PCTUSED 表示什么情况下可以被insert，默认40%，表示
   当数据块使用空间低于40%以后，该数据块就可以再被insert。启用ASSM，就不能设置PCTUSED.
2、rowid 是一个伪列，每一条记录都有rowid伪列。
对应ORACLE10g 来说，rowid 格式：OOOOOOFFFBBBBBBRRR
OOOOOO：数据行所在的对象号
FFF   ：数据行所在的相对文件号
BBBBBB：数据行所在的数据块号
RRR   ：数据行所在的数据块中的行号
rowid采用64进制，即：A~Z a~z 0~9 / + 64个字符来表示。
A~Z ：0~25
a~z ：26~51
0~9 ：52~61
/   ：62
+   ：63

例子 rowid = AAAM0hAAEAAAAGnAAA
SQL>select object_id from user_objects where object_name = 'BOOKS'; --对象号
SQL>select dbms_rowid.rowid_relative_fno(rowid) as "File No" from dual; --文件号
SQL>select dbms_rowid.rowid_block_number(rowid) as "File No" from dual; --块号
SQL>select dbms_rowid.rowid_row_number(rowid) as "File No" from dual; --行号

3、管理普通表
1）扩展表：有时需要主动扩展表占用空间，或者将表数据分布到多个文件，将表的I/O分散到多个磁盘上。
SQL>alter table books allocate extent (size 1M datafile '/u01/app/oracel/oradata/ora10g/users02.dbf');
2）重整表 --消除币表的数据块级别的碎片。
稀疏表产生的原因：该表存在很多的insert 和 delete、
在表的segment header里面，记录了一个值，叫高水位标志（High Water Mark 简称HWM）表示当前segment 使用最后一个数据块的位置。
说明：表进行删除数据后，HWM位置不会改变，在对表进行全表扫描时，仍然要扫描到HWM为止。ORACLE10g之前使用move或者导出导入的方式进行重整表来减小WHM，如下：
SQL>alter table books move tablespace example; --把表移到example表空间，如果没有加表空间，就在当前表对应表空间重整，重整后全表索引失效。
ORACLE 10g 可以使用shrink（收缩）对表进行收缩。
3）收缩表
条件：表所在的表空间必须使用 ASSM （自动段空间管理）
      收缩表引起数据行在不同的数据块转移，必须启用 row movement 选项
SQL>alter table t enable row movement;
收缩表语句：
SQL>alter table t shrink space compact; --对表t只进行压缩阶段，不下降HWM
SQL>alter table t shrink space;          --对表t只进行压缩阶段，下降HWM
SQL>alter table t shrink space cascade; --对表t只进行压缩阶段，下降HWM；同时还收缩表t相关的其他segment
说明：可以使用Segment Advisor 帮助哪些segment可以进行收缩。
4）截断表
truncate 命令是一个DDL命令，最后WHM下降到最低。
SQL>truncate table t;
注意：有些时候截断一个巨大的表要花费很长的时间，导致表长时间不能使用。再截断的时候，可以在更新完数据字典以后，不立即释放全部的数据块，但WHM已经下降到最低。
      可以在系统比较空闲分多次释放数据块，每次释放部分空间。命令如下：
SQL>truncate table t1 reuse storage;
SQL>alter table t1 deallocate unused keep 30M; --将表t1没有用的数据块释放，释放到剩余的表所占用的空间为30M为止。
SQL>alter table t1 deallocate unused keep 15M;
SQL>alter table t1 deallocate unused keep 0M;
说明：如果其他用户已经把数据插入t1，则不会删除，只释放没有的数据块。
5）删除表
删除表属于DDL命令，只是更新数据字典的信息，ORACLE不会读取表包含的数据块信息，因此，即使表处于只读表空间里，该表也是可以被删除的。
SQL>drop table t;
SQL>drop table t cascade constraints;
添加cascade constraints 选项，同时删除引用表 t 的外键。
6）修改或删除列
SQL>alter table t rename column to code;
SQL>alter table t drop column code;
SQL>alter table t drop column code cascade constraints; --同时删除引用的外键
注：在删除列的过程中，oracle会消耗undo表空间，如记录很多，会消耗过多的undo表空间。
SQL>alter table t drop column code cascade constraints checkpoint 2000;
checkpoint 2000 表示每2000条记录提交一次，从而释放出undo资源。
注：在删除列的过程中，oracle会锁定表，无法对表进行DML操作，如果数据量很大，则将花费很长时间，在业务高峰期，影响严重。
SQL>alter table t set unused column code;
SQL>select * from user_unusedd_col_tabs;        ----可以查询到失效的列。
可以先从逻辑上使该列失效，在业务低峰期在物理上删除失效的列。
SQL>alter table t drop umused columns;
SQL>alter table t drop umused columns checkpoint 2000;

4、约束 constraints
ORACLE 数据库里面，有以下5中约束：
1）非空 not null ---本质上说，not null 属于 check ： col_name is not null
2）唯一 unique
3）主键 primary key
4）外键 foreign key
5）检查 check

约束的状态：
1）enable和disable :对表进行插入或修改时，对插入或修改后的数据进行检验，判断是否违反约束。
2）validate和novalidate ：是否对表里已经存在的数据进行检验，判断是否违反约束。
上面的组合存在四种状态。
SQL>alter table books enable validate constraint pk_books;
SQL>alter table books enable novalidate constraint pk_books;
SQL>alter table books rename constraint pk_books to pk_books_id;

约束校验的时机
延迟约束 deferred constraint ：约束在提交的时候进行校验。
1）deferrable ：说明约束是否可以被延迟，添加该选项说明可以被延迟。
2）initially deferred 或者 initially immediate：说明约束创建时候，何时校验数据。
initially deferred ：提交校验。设置该选项必须设置了deferrable。
initially immediate：默认值，立即校验。
SQL>alter table sales add constraint chk_sales check(price*qty=value) deferrable initially deferred;

SQL>alter session set constraint=deferred;
当发出该语句之后，说明当前session中，对于发出的所有DML语句所涉及的表，只要这些表上约束定义了deferrable选项，这些约束全部延迟检验。

5、使用分区表，索引组织表，簇表
Oracle 10g 提供五种分区的方法
1）范围分区 Range Partition
create table t2(id number,createdate date)
partition by range(createdate)
(
partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace ts01,
partition p2 values less than (to_date('2002-01-01','yyyy-mm-dd')) tablespace ts02,
partition p3 values less than (to_date('2003-01-01','yyyy-mm-dd')) tablespace ts03,
partition pmax values less than (maxvalue) tablespace ts04
);
2）哈希分区 Hash Partition
create table t3(id number,name varchar2(10))
partition by hash(id)
partitions 4
store in (ts01,ts02.ts03.ts04);
create table t3(id number,varchar2(10))
partition by hash(id)
(
partition p1 tablespace ts01,
partition p2 tablespace ts02,
partition p3 tablespace ts03,
partition p4 tablespace ts04
):
3）列表分区 List Partition
create table t4(id number,name varchar2(10),category varchar2(10))
partition by list(category)
(
partition p1 values ('01','02') tablespace ts01,
partition p2 values ('03','04') tablespace ts02,
partition p3 values ('05','06','07') tablespace ts03,
partition p4 values (default) tablespace ts04,
);
4）范围哈希组合分区 Range-Hash Partition
create table t5(id,number,name varchar2(10),createdate date)
partition by range (createdate)
subpartition by hash (id)
   subpartitions 4 store in (ts01,ts02)
(partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')),
partition p2 values less than (to_date('2002-01-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2003-01-01','yyyy-mm-dd')),
partition pmax values less than (maxvalue)
subpartitions 2 store in (ts03) );
说明：先按createdate进行范围分区，然后再按照id进行hash分区。默认每个分区包含4个hash子分区，这些子分区都分别在ts01和ts02里。
也是就是p1,p2,p3都是如此，但对于pmax来说，修改了默认设置，pmax有两个hash分区，都位于ts03里。
5）范围列表组合分区 Range-List Partition
create table t6(id number,name varchar2(10),category varchar2(10),createdate date)
partition by range(createdate)
   subpartition by list (category)
(partition p1 values less than (to_date('2001-01-01','yyyy-mm-dd')) tablespace ts01
     (subpartition p1_1 values ('01','02'),
      subpartition p1_2 values ('03','04'),
      subpartition p1_3 values (default) tablespace ts02),
   partition p2 values less than (maxvalue) tablespace ts03
     (subpartition p1_1 values ('01','02'),
      subpartition p1_2 values ('03','04'),
      subpartition p1_3 values (default) tablespace ts04)
);

索引组织表 index organized table

推荐文章：

Oracle 10g 对象管理