Oracle数据库的对象及其管理.ppt

管理表空间和数据文件存储结构和关系管理表管理索引维护数据完整性,Oracle数据库的对象及其管理,主要内容,说明数据库的逻辑结构 创建表空间使用不同方法改变表空间大小 改变表空间的状态和存储设置重定位表空间 准备必要的表空间,概述,数据库,控制文件,重做日志文件,数据库,逻辑的,物理的,表空间,数据文件,O/S 块,Oracle 块,段,范围,数据库结构,SYSTEM表空间和非SYSTEM表空间,SYSTEM表空间包含:数据字典信息SYSTEM回滚段,非SYSTEM表空间包含:回滚段临时段应用数据应用索引,创建表空间,CREATE 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.dbf 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;,表空间APP_DATA,app02.dbf1M,app01.dbf1M,例子,数据文件的自动扩展,ALTER TABLESPACE app_data ADD DATAFILE DISK6/app04.dbf SIZE 200MAUTOEXTEND ON NEXT 10MMAXSIZE 500M;,表空间APP_ DATA,app04.dbf 2M,app03.dbf2M,app01.dbf1M,app02.dbf1M,例子,ALTER DATABASE DATAFILE DISK5/app02.dbf RESIZE 200M;,表空间APP_DATA,app02.dbf1M,手工改变数据文件的大小,app01.dbf1M,例子,改变存储设置,例子,ALTER TABLESPACE app_data MINIMUM EXTENT 2M;ALTER TABLESPACE app_dataDEFAULT 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,数据库必须已经装配 目标数据文件必须存在,ALTER DATABASE RENAME FILE DISK1/system01.dbf TO DISK2/system01.dbf;,例子,表空间的只读状态,ALTER TABLESPACE app_data READ ONLY;,表空间APP_DATA只允许进行读操作,例子,设置表空间为只读,表空间必须联机 没有活动的事务才可以 表空间不能包含活动的回滚段 表空间当前一定不能与联机备份有关,删除表空间,DROP TABLESPACE app_data INCLUDING CONTENTS;,以下语句删除APP_DATA表空间及其全部内容。,例子,获得表空间有关信息,DBA_TABLESPACES TABLESPACE_NAMENEXT_EXTENTMAX_EXTENTSPCT_INCREASEMIN_EXTLENSTATUSCONTENTS,获得数据文件有关信息,DBA_DATA_FILESFILE_NAMETABLESPACE_NAMEBYTESAUTOEXTENSIBLE MAXBYTESINCREMENT_BY,管理表空间和数据文件存储结构和关系管理表管理索引维护数据完整性,Oracle数据库的对象及其管理,主要内容,列出不同种类的段及其它们的使用由段控制范围的使用说明对象的块空间利用参数的使用从数据字典获得存储结构的有关信息根据分裂程度和生存范围确定段的位置,数据库,逻辑的,物理的,表空间,数据文件,O/S 块,Oracle 块,段,范围,数据库存储层次,段的种类,表,表分区,索引,段的种类,索引分区,回滚段,临时段,段的种类,LOB索引,LOB段,引导程序段,嵌套的表,存储子句的优先级,Oracle缺省,表空间,段,范围的分配和去配,段在以下情况分配创建 扩展修改段在以下情况去配删除修改清除自动调整大小(仅回滚段),使用的和空闲的范围,数据文件,空闲的范围,使用的范围,文件头,空闲空间合并,ALTER TABLESPACE data01 COALESCE;,数据库块:回顾,I/O的最小单位由一个或多个O/S块组成由参数DB_BLOCK_SIZE设置在数据库创建时设置,数据库块内容,头,空闲空间,数据,块空间利用参数,INITRANSMAXTRANS,PCTFREEPCTUSED,块空间的使用,插入,插入,插入,插入,1,2,3,4,PCTFREE=20PCTUSED=40,80%,80%,40%,数据字典视图,表空间DBA_TABLESPACES,段DBA_SEGMENTS,数据文件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标识OWNERSEGMENT_NAMEEXTENT_ID 位置和大小TABLESPACE_NAMERELATIVE_FNOFILE_IDBLOCK_IDBLOCKS,检查空闲的范围信息,DBA_FREE_SPACE位置和大小TABLESPACE_NAME RELATIVE_FNO FILE_ID BLOCK_ID BLOCKS,管理表空间和数据文件存储结构和关系管理表管理索引维护数据完整性,Oracle数据库的对象及其管理,主要内容,区分不同种类的Oracle数据类型使用适当的存储设置创建表控制表所使用的空间分析表检查其完整性及其迁移情况从数据字典检索有关表的信息不同格式ROWID之间的相互转换,存储用户数据,规则的表,分区的表,行结构,数据库块,行头,列长度,列值,Oracle 数据类型,CHAR(N),NCHAR(N)VARCHAR2(N),NVARCHAR2(N)NUMBER(P,S)DATERAW(N)BLOB,CLOB,NCLOB,BFILELONG,LONG RAWROWID,VARRAYTABLE,REF,数据类型,内置的,用户定义的,标量,关系,集合,ROWID数据类型,OOOOOO,BBBBBB,FFF,RRR,数据对象号,相对文件号,行号,块号,ROWID 格式,行的唯一标识符 用作行定位,受限的ROWID,可以在一个段内确定行需要较少空间,BBBBBBBB,FFFF,RRRR,块号,行号,文件号,.,.,集合,集合是包含对象的对象VARRAY是有次序的元素集合，其中包括计数和界限嵌套的表是带有TABLE数据类型列的表,创建表,CREATE TABLE employees(empno NUMBER(4),last_name VARCHAR2(30)deptno NUMBER(2)PCTFREE 20 PCTUSED 50STORAGE(INITIAL 200K NEXT 200KPCTINCREASE 0 MAXEXTENTS 50)TABLESPACE data01;,创建表:指导原则,使用较小的标准范围大小以减少 表空间的碎片 对于频繁使用并且较小的表可以使用CACHE子句,PCTFREE和PCTUSED的设置,计算PCTFREE,(平均行大小-初始行大小)*100平均行大小,计算PCTUSED,平均行大小*100100-PCTFREE-可用数据空间,行迁移和链接,更新之前,更新之后,拷贝一个存在的表,CREATE 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 DISK3/DATA01.DBF);,Free space after delete,Unused block,高水位,范围 ID 01234,Used block,高水位,插入之后,删除之后,得到高水位:DBMS_SPACE.UNUSED_SPACE,范围 ID 01234,高水位LAST_USED_EXTENT_FILE_ID,LAST_USED_EXTENT_BLOCK_ID,TOTAL_BLOCKS,UNUSED_BLOCKS,删除之后的空闲空间,未使用的块,高水位,去配之前,使用的块,未使用空间的去配,ALTER TABLE scott.employeesDEALLOCATE UNUSED;,去配之后,清除表,TRUNCATE TABLE scott.employees;,范围 ID 01,高水位,空闲空间,删除表,DROP TABLE scott.departmentsCASCADE CONSTRAINTS;,分析表结构,Oracle服务器核实每一个数据块的完整性使用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_IDDATA_OBJECT_IDCREATED,DBA_SEGMENTSOWNERSEGMENT_NAMETABLESPACE_NAMEHEADER_FILEHEADER_BLOCK,DBA_TABLESOWNERTABLE_NAMEPCT_FREEPCT_USEDINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASECACHEBLOCKSEMPTY_BLOCKSCHAIN_CNT,DBA_EXTENTSOWNERSEGMENT_NAMEEXTENT_IDFILE_IDBLOCK_IDBLOCKS,检索范围信息,DBMS_ROWID包,函数名称ROWID_CREATEROWID_OBJECTROWID_RELATIVE_FNOROWID_BLOCK_NUMBERROWID_ROW_NUMBERROWID_TO_ABSOLUTE_FNOROWID_TO_EXTENDEDROWID_TO_RESTRICTED,说明根据各个独立部分 建立一个ROWID为一个ROWID返回对象标识符为一个ROWID返回相对文件号为一个ROWID返回块号为一个ROWID返回行号为一个ROWID返回绝对文件号将一个ROWID从受限的转换成扩展的将一个ROWID从扩展的转换成受限的,通常使用的函数:,管理表空间和数据文件存储结构和关系管理表管理索引维护数据完整性,Oracle数据库的对象及其管理,主要内容,列出各种不同类型的索引及其用途建立B-树和位图索引索引重组删除索引从数据字典获得有关索引信息,索引分类,逻辑的单列的或组合列的唯一的或非唯一的物理的分区的或非分区的 B-树或位图正常或倒序键(仅B-树),B-树索引,索引条目头,键列长度,键列值,ROWID,根,分支,叶,索引条目,KEY ROWIDEMPNO(BLOCK#ROW#FILE#)-1257 0000000F.0002.00012877 0000000F.0006.00014567 0000000F.0004.00016657 0000000F.0003.00018967 0000000F.0005.00019637 0000000F.0001.00019947 0000000F.0000.0001.,颠倒键索引,EMP的索引(EMPNO),EMP表,EMPNO ENAME JOB.-7499 ALLEN SALESMAN7369 SMITH CLERK7521 WARD SALESMAN.7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER.,位图索引,表,索引,块 10,块 11,块 12,文件 3,B-树与位图索引的比较,B-树适合于高基数的列键值的更新相对昂贵对使用或谓词的查询语句无效适用于OLTP,位图适合于低基数的列 键值的更新非常昂贵对使用或谓词的查询语句有效适用于 DSS,建立正常B-树索引,CREATE INDEX scott.emp_lname_idxON scott.employees(last_name)PCTFREE 30STORAGE(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 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 INDEX 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_idx VALIDATE STRUCTURE;,INDEX_STATS,删除索引,在大批数据装入之前先删除索引，然后再重建删除不经常使用的索引，并在需要时再建立删除并重建无效的索引,DROP INDEX scott.dept_dname_idx;,获得有关索引信息,DBA_INDEXESOWNERINDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAMEUNIQUENESSTABLESPACE_NAMELOGGINGSTATUS,DBA_IND_COLUMNSINDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME COLUMN_POSITIONCOLUMN_LENGTH,管理表空间和数据文件存储结构和关系管理表管理索引维护数据完整性,Oracle数据库的对象及其管理,主要内容,实现数据完整性约束和触发器维护完整性约束和触发器从数据字典获得约束和触发器的有关信息,数据完整性,应用程序,完整性约束,数据库触发器,表,数据,约束的类型,说明指定一个列不能包含空值标明一个列或列的组合是唯一的标明一个列或列的组合是表的主键标明一个列或列的组合是引用完整性中的外部键指定表的每一行必须满足的条件,约束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 on child table,执行Cascade constraintsDisable/drop foreign keyUse CASCADE CONSTRAINTS clauseCreate index on foreign keyEnsure tablespace containing parent key index online,数据库触发器,表,触发器,DML操作,触发器类型INSERT或UPDATE或DELETEBEFORE或AFTERROW或STATEMENT,创建表时定义约束,CREATE TABLE scott.employees(empno NUMBER(4)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;,定义约束的指导方针,主键和唯一性约束:将索引放置在另外的表空间中如果批量装入非常频繁,使用非唯一索引自引用外部键:初始装入后再定义或启用外部键延期约束检查,禁用约束,在批量装入时禁用，尤其是自引用外部键的情况禁用主键之前，首先禁用引用外部键唯一索引被删除，但保留非唯一索引,ALTER TABLE scott.departmentsDISABLE CONSTRAINT dept_pk CASCADE;,启用约束,启用 NOVALIDATE,不锁表 主/唯一键必须使用非唯一索引,ALTER TABLE scott.departmentsENABLE NOVALIDATE CONSTRAINT dept_pk;,启用约束,启用 VALIDATE,锁表可以使用唯一或非唯一索引需要正确的表数据,ALTER TABLE scott.employeesENABLE VALIDATE CONSTRAINT emp_dept_fk;,使用EXCEPTIONS表,1.建立EXCEPTIONS表(utlexcpt.sql)2.执行带EXCEPTIONS子句的ALTER TABLE 3.在EXCEPTIONS上使用子查询定位不正确数据的行4.纠正错误5.再次执行ALTER TABLE启用约束,禁用和启用触发器,使用ALTER TRIGGER禁用或启用一个触发器使用ALTER TABLE禁用或启用全部触发器,ALTER TRIGGER scott.emp_conv_lnDISABLE;,ALTER TABLE scott.employeesENABLE ALL TRIGGERS;,删除约束,使用此命令删除约束:使用此命令删除表及其任何引用的外部键:,ALTER TABLE scott.employeesDROP CONSTRAINT emp_ln_uk;,DROP TABLE departmentsCASCADE CONSTRAINTS;,删除触发器,DROP TRIGGER scott.audit_dept;,获得约束的有关信息,DBA_CONSTRAINTSOWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAMEDELETE_RULE STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD LAST_CHANGE,DBA_CONS_COLUMNSOWNER CONSTRAINT_NAMETABLE_NAME COLUMN_NAME POSITION,获得触发器的有关信息,DBA_TRIGGERS OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNER TABLE_NAME STATUS DESCRIPTION TRIGGER_BODY,DBA_TRIGGER_COLSTRIGGER_OWNERTRIGGER_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME,DBA_OBJECTSOWNEROBJECT_NAME OBJECT_TYPE STATUS,