Oracle数据库的对象及其管理

1、 管理表空间和数据文件 存储结构和关系 管理表 管理索引 维护数据完整性Oracle数据库的对象及其管理主要内容 说明数据库的逻辑结构 创建表空间 使用不同方法改变表空间大小 改变表空间的状态和存储设置 重定位表空间 准备必要的表空间概述数据库数据库控制文件控制文件重做日志重做日志文件文件数据文件数据文件数据库数据库逻辑的逻辑的物理的物理的表空间表空间数据文件数据文件O/S 块块Oracle 块块段段范围范围SYSTEM表空间和非SYSTEM表空间 SYSTEM表空间包含: 数据字典信息 SYSTEM回滚段 非SYSTEM表空间包含: 回滚段 临时段 应用数据 应用索引创建表空间CREATE

2、TABLESPACE app_dataDATAFILE DISK4/app01.dbf SIZE 100M, DISK5/app02.dbf SIZE 100MMINIMUM EXTENT 500KDEFAULT STORAGE (INITIAL 500K NEXT 500K MAXEXTENTS 500 PCTINCREASE 0);存储参数 以下参数影响段的存储分配: INITIAL NEXT MAXEXTENTS MINEXTENTS PCTINCREASE临时表空间 排序操作使用 不能包含永久对象CREATE TABLESPACE sortDATAFILE DISK2/sort01.d

3、bf SIZE 50M MINIMUM EXTENT 1MDEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 500 PCTINCREASE 0) TEMPORARY;为表空间增加数据文件ALTER TABLESPACE app_data ADD DATAFILE DISK5/app03.dbf SIZE 200M;app03.dbf 2Mapp02.dbf1Mapp01.dbf1M数据文件的自动扩展ALTER TABLESPACE app_data ADD DATAFILE DISK6/app04.dbf SIZE 200MAUTOEXTEND ON

4、 NEXT 10MMAXSIZE 500M;app04.dbf 2Mapp03.dbf2Mapp01.dbf1Mapp02.dbf1M 1M ALTER DATABASE DATAFILE ALTER DATABASE DATAFILE DISK5/app02.dbfDISK5/app02.dbf RESIZE 200M; RESIZE 200M;app02.dbf1M手工改变数据文件的大小手工改变数据文件的大小app01.dbf1M改变存储设置ALTER TABLESPACE app_data MINIMUM EXTENT 2M; ALTER TABLESPACE app_dataDEFAU

5、LT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);脱机状态 脱机的表空间不能进行数据的存取操作 SYSTEM表空间和带有活动回滚段的表空间 均不能脱机ALTER TABLESPACE app_data OFFLINE;移动数据文件:ALTER TABLESPACE 表空间APP_DATA必须脱机 目标数据文件必须存在ALTER TABLESPACE app_data RENAME DATAFILE DISK4/app01.dbf TO DISK5/app01.dbf;移动数据文件: ALTER DATABASE 数据库必须已经装配 目标数据文件必须存

6、在ALTER DATABASE RENAME FILE DISK1/system01.dbf TO DISK2/system01.dbf;表空间的只读状态ALTER TABLESPACE app_data READ ONLY; 设置表空间为只读 表空间必须联机 没有活动的事务才可以 表空间不能包含活动的回滚段 表空间当前一定不能与联机备份有关删除表空间DROP TABLESPACE app_data INCLUDING CONTENTS;获得表空间有关信息DBA_TABLESPACES TABLESPACE_NAME NEXT_EXTENT MAX_EXTENTS PCT_INCREASE M

7、IN_EXTLEN STATUS CONTENTS获得数据文件有关信息 FILE_NAME TABLESPACE_NAME BYTES AUTOEXTENSIBLE MAXBYTES INCREMENT_BY 管理表空间和数据文件 存储结构和关系 管理表 管理索引 维护数据完整性Oracle数据库的对象及其管理主要内容 列出不同种类的段及其它们的使用由段控制范围的使用 说明对象的块空间利用参数的使用 从数据字典获得存储结构的有关信息 根据分裂程度和生存范围确定段的位置数据库数据库逻辑的逻辑的物理的物理的表空间表空间数据文件数据文件O/S 块块Oracle 块块段段范围范围数据库存储层次数据库存

8、储层次段的种类段的种类段的种类存储子句的优先级Oracle缺省缺省表空间表空间段段范围的分配和去配 段在以下情况分配+ 创建 + 扩展+ 修改 段在以下情况去配 删除 修改 清除 自动调整大小(仅回滚段)使用的和空闲的范围空闲空间合并ALTER TABLESPACE data01 COALESCE;数据库块: 回顾 I/O的最小单位的最小单位 由一个或多个由一个或多个O/S块组成块组成 由参数由参数DB_BLOCK_SIZE设置设置 在数据库创建时设置在数据库创建时设置 数据库块内容块空间利用参数块空间的使用1234数据字典视图表空间表空间DBA_TABLESPACES段段DBA_SEGMEN

9、TS数据文件数据文件DBA_DATA_FILES空闲的范围空闲的范围DBA_FREE_SPACE使用的范围使用的范围DBA_EXTENTS查询段的有关信息DBA_SEGMENTS一般信息一般信息 OWNERSEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME 大小大小 EXTENTS BLOCKS 存储设置存储设置 INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE获得使用的范围信息DBA_EXTENTS标识标识 OWNER SEGMENT_NAME EXTENT_ID 位置和大小位置和大

10、小 TABLESPACE_NAME RELATIVE_FNO FILE_ID BLOCK_ID BLOCKS 检查空闲的范围信息DBA_FREE_SPACE位置和大小位置和大小 TABLESPACE_NAME RELATIVE_FNO FILE_ID BLOCK_ID BLOCKS 管理表空间和数据文件 存储结构和关系 管理表 管理索引 维护数据完整性Oracle数据库的对象及其管理主要内容 区分不同种类的Oracle数据类型 使用适当的存储设置创建表 控制表所使用的空间 分析表检查其完整性及其迁移情况 从数据字典检索有关表的信息 不同格式ROWID之间的相互转换 存储用户数据行结构 Orac

11、le 数据类型数据类型数据类型内置的内置的用户定义的用户定义的标量标量关系关系集合集合ROWID数据类型OOOOOOBBBBBBFFFRRR数据对象号数据对象号相对文件号相对文件号行号行号块号块号 行的唯一标识符 用作行定位受限的ROWID 可以在一个段内确定行 需要较少空间BBBBBBBBFFFFRRRR块号块号行号行号文件号文件号.集合 集合是包含对象的对象 VARRAY是有次序的元素集合，其中包括计数和界限 嵌套的表是带有TABLE数据类型列的表创建表CREATE TABLE employees(empno NUMBER(4), last_name VARCHAR2(30)deptno

12、NUMBER(2)PCTFREE 20 PCTUSED 50STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE data01;创建表: 指导原则 使用较小的标准范围大小以减少 表空间的碎片 对于频繁使用并且较小的表可以使用CACHE子句PCTFREE和PCTUSED的设置 计算PCTFREE(平均行大小平均行大小 初始行大小初始行大小) * 100平均行大小平均行大小 计算PCTUSED 平均行大小平均行大小 * 100100 PCTFREE 可用数据空间可用数据空间行迁移和链接拷贝一个存在的表CREATE

13、TABLE new_empSTORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)NOLOGGINGTABLESPACE data01ASSELECT * FROM scott.employees;改变存储参数和块利用参数ALTER TABLE scott.employeesPCTFREE 30PCTUSED 50STORAGE(NEXT 500KMINEXTENTS 2MAXEXTENTS 100);手工分配范围ALTER TABLE scott.employeesALLOCATE EXTENT(SIZE 500KDATAFILE

14、DISK3/DATA01.DBF);高水位得到高水位: DBMS_SPACE.UNUSED_SPACE 高水位高水位LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_IDTOTAL_BLOCKSUNUSED_BLOCKS未使用空间的去配ALTER TABLE scott.employeesDEALLOCATE UNUSED;清除表TRUNCATE TABLE scott.employees;删除表DROP TABLE scott.departmentsCASCADE CONSTRAINTS;分析表结构 Oracle服务器核实每一个数据块的完整性

15、使用CASCADE选项可以确认与表有关的全部索引，并且执行表和索引之间的交叉引用ANALYZE TABLE scott.employeesVALIDATE STRUCTURE;检测行迁移 Oracle服务器根据样本数据收集统计信息并更新数据字典 检查CHAIN_CNT ANALYZE TABLE scott.employeesESTIMATE STATISTICS;SELECT chain_cntFROM DBA_TABLESWHERE table_name=EMPLOYEES AND owner=SCOTT;检索表信息DBA_OBJECTSOWNEROBJECT_NAMEOBJECT_IDD

16、ATA_OBJECT_IDCREATEDDBA_SEGMENTSOWNERSEGMENT_NAMETABLESPACE_NAMEHEADER_FILEHEADER_BLOCKDBA_TABLESOWNERTABLE_NAMEPCT_FREEPCT_USEDINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASECACHEBLOCKSEMPTY_BLOCKSCHAIN_CNTDBA_EXTENTSOWNERSEGMENT_NAMEEXTENT_IDFILE_IDBLOCK_IDBLOCKS检索范围信息DBMS_ROWID包函数名称函数

17、名称ROWID_CREATEROWID_OBJECTROWID_RELATIVE_FNOROWID_BLOCK_NUMBERROWID_ROW_NUMBERROWID_TO_ABSOLUTE_FNOROWID_TO_EXTENDEDROWID_TO_RESTRICTED说明说明根据各个独立部分根据各个独立部分 建立一个建立一个ROWID为一个为一个ROWID返回对象标识符返回对象标识符为一个为一个ROWID返回相对文件号返回相对文件号为一个为一个ROWID返回块号返回块号为一个为一个ROWID返回行号返回行号为一个为一个ROWID返回绝对文件号返回绝对文件号将一个将一个ROWID从受限的转换成

18、扩展的从受限的转换成扩展的将一个将一个ROWID从扩展的转换成受限的从扩展的转换成受限的通常使用的函数通常使用的函数: 管理表空间和数据文件 存储结构和关系 管理表 管理索引 维护数据完整性Oracle数据库的对象及其管理主要内容 列出各种不同类型的索引及其用途 建立B-树和位图索引 索引重组 删除索引 从数据字典获得有关索引信息索引分类逻辑的 单列的或组合列的 唯一的或非唯一的物理的 分区的或非分区的 B-树或位图 正常或倒序键 (仅B-树)B-树索引索引条目头索引条目头键列长度键列长度键列值键列值ROWID根根分支分支叶叶索引条目索引条目KEY ROWIDEMPNO (BLOCK# ROW

19、# FILE#)- -1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001.颠倒键索引EMPNO ENAME JOB .- - -7499 ALLEN SALESMAN7369 SMITH CLERK7521 WARD SALESMAN .7566 JONES MANAGER7654 MARTIN SALESMAN7698 BL

20、AKE MANAGER7782 CLARK MANAGER.位图索引表索引块 10块 11块 12文件 3B-树与位图索引的比较B-树树适合于高基数的列适合于高基数的列键值的更新相对昂贵键值的更新相对昂贵对使用或谓词的查询语句无效对使用或谓词的查询语句无效适用于适用于OLTP位图位图适合于低基数的列适合于低基数的列 键值的更新非常昂贵键值的更新非常昂贵对使用或谓词的查询语句有效对使用或谓词的查询语句有效适用于适用于 DSS 建立正常B-树索引CREATE INDEX scott.emp_lname_idxON scott.employees(last_name)PCTFREE 30STORAG

21、E(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx01;创建索引: 指导原则 权衡查询和DML的需要 放置在单独的表空间中 使用相同的范围大小: 5个块的倍数或表空间 MINIMUM EXTENT的大小 大的索引可以考虑使用NOLOGGING选项 如果新键值都接近当前范围则PCTFREE设置要高建立倒序键索引CREATE UNIQUE INDEX scott.ord_ord_no_idxON scott.ord(ord_no) REVERSEPCTFREE 30STORAGE(INITIAL 200K NEXT

22、 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx01;建立位图索引CREATE BITMAP INDEX scott.ord_region_id_idxON scott.ord(region_id)PCTFREE 30STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE indx01;改变索引的存储参数ALTER INDEX scott.emp_lname_idxSTORAGE(NEXT 400KMAXEXTENTS 100);索引空间的分配与去配ALTER IN

23、DEX scott.ord_region_id_idxALLOCATE EXTENT (SIZE 200KDATAFILE DISK6/indx01.dbf);ALTER INDEX scott.ord_ord_no_idxDEALLOCATE UNUSED;重建索引 ALTER INDEX scott.ord_region_id_idx REBUILDTABLESPACE indx02;使用该命令可以: 将一个索引移到另一个不同的表空间 通过去除已经删除的条目改善空间的利用 颠倒键索引与正常B-树索引之间的变换检查索引的有效性ANALYZE INDEX scott.ord_region_id

24、_idx VALIDATE STRUCTURE;INDEX_STATS删除索引 在大批数据装入之前先删除索引，然后再重在大批数据装入之前先删除索引，然后再重建建 删除不经常使用的索引，并在需要时再建立删除不经常使用的索引，并在需要时再建立 删除并重建无效的索引删除并重建无效的索引DROP INDEX scott.dept_dname_idx;获得有关索引信息DBA_INDEXESOWNERINDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAMEUNIQUENESSTABLESPACE_NAMELOGGINGSTATUSDBA_IND_COLUMNSINDEX_OWNE

25、R INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITIONCOLUMN_LENGTH 管理表空间和数据文件 存储结构和关系 管理表 管理索引 维护数据完整性Oracle数据库的对象及其管理主要内容 实现数据完整性约束和触发器 维护完整性约束和触发器 从数据字典获得约束和触发器的有关信息数据完整性表表约束的类型说明说明指定一个列不能包含空值指定一个列不能包含空值标明一个列或列的组合是唯一的标明一个列或列的组合是唯一的标明一个列或列的组合是表的主键标明一个列或列的组合是表的主键标明一个列或列的组合是引用完整标明一个列或列的组合是引

26、用完整性中的外部键性中的外部键指定表的每一行必须满足的条件指定表的每一行必须满足的条件约束约束NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK延期的约束 DML语句检查非延期的约束检查非延期的约束COMMIT检查延期的约束检查延期的约束主/唯一键强制外部键考虑至至Drop parent tableTruncate parent tableDrop tablespace containing parent tableAvoid locks on child table while performing DML on parent tablePerform DML

27、 on child table执行执行Cascade constraintsDisable/drop foreign keyUse CASCADE CONSTRAINTS clauseCreate index on foreign keyEnsure tablespace containing parent key index online数据库触发器表触发器DML操作触发器类型触发器类型 INSERT或或UPDATE或或DELETE BEFORE或或AFTER ROW或或STATEMENT创建表时定义约束 CREATE TABLE scott.employees(empno NUMBER(4

28、) CONSTRAINT emp_pk PRIMARY KEYDEFERRABLE USING INDEX STORAGE(INITIAL 100K NEXT 100K) TABLESPACE indx01, last_name VARCHAR2(30) CONSTRAINT emp_ln_nn NOT NULL,deptno NUMBER(2)TABLESPACE data01;定义约束的指导方针 主键和唯一性约束: 将索引放置在另外的表空间中 如果批量装入非常频繁,使用非唯一索引 自引用外部键: 初始装入后再定义或启用外部键 延期约束检查禁用约束 在批量装入时禁用，尤其是自引用外部键的情况

29、 禁用主键之前，首先禁用引用外部键 唯一索引被删除，但保留非唯一索引ALTER TABLE scott.departmentsDISABLE CONSTRAINT dept_pk CASCADE;启用约束 不锁表不锁表 主主/唯一键必须使用非唯唯一键必须使用非唯一索引一索引ALTER TABLE scott.departmentsENABLE NOVALIDATE CONSTRAINT dept_pk;启用约束 锁表锁表 可以使用唯一或非唯一可以使用唯一或非唯一索引索引 需要正确的表数据需要正确的表数据ALTER TABLE scott.employeesENABLE VALIDATE CONSTRA