집 >데이터 베이스 >MySQL 튜토리얼 >Oracle 10g 对象管理
ORACLE数据库中,表,索引,存储过程,函数等都是对象。schema 的名称与用户名相同,但是与用户不是同一回事,如果用户没有任何对
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