oracle 10g管理地址,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