Oracle DBA成长日记(3).doc

Oracle DBA成长日记五表5.1 oracle数据类型数据类型名称描述例子Char最多可以以固定长度的格式存储2000个字符。当位数不够时，需要在其右边添加空格来补满Create table test(name char(20)Varchar,varchar2最多可以可变长度格式来存储4000b，varchar2比varchar更适合使用，由于兼容性的原因，仍然大量使用的是varcharCreate table test(name varchar(20)Nchar 国际语言支持的数据类型仅可以在存储由数据库NLS字符集定义的unicode字符集。该数据类型最多可以存储2000b，当位数不够时需要在右边填充空格Create table test (name nchar(20)Nvarchar2NLS的数据类型与varchar2数据类型等价，最多可以存储4000BCreate table test (name nvarchar2(20)Number用于存储零、正数、负数以及浮点数。可以number（p,s）的形式来定义数字的精度和范围Create table test (grade number(5,2)Long Long类型可以存储可变长的字符串，最多2GB。可以使用long类型来存储long类型的文本字符串（建议使用LOB数据类型来代替LONG类型）Create table test(name long)Date 存储日期和时间Create table test(birthday date)Timestamp 使用年月日时分秒来对日期时间进行详细的支持。Timestamp with time zone包含timestamp 中的所有域，还有timezone_hour和timezone_minuteTimestamp with local time zone日期时间校准为会话的时区Interval year to month用于存储一个时间段，由月份和年组成，需要5B来组成。Select interval 01-05 year to month-01-02 year to month from dualInterval day to second 由存储一个时间段，由日和秒组成，需要11B来存储Select interval 100 10:20:42.22day(3) to second(2)-interval 101 10:20:42.22 day(3) to second(2) from dualRaw 存储raw类型的二进制数据，最多可以存储2000B，建议使用BLOB代替它Create table test (raw_column raw(2000)Long raw存储raw类型的二进制数据，最多可以存储2GB数据 ，建议使用BLOB代替Rowid表中的rowid类型的字符串表示Urowid在索引组织中表示逻辑行地址Crete table test（urowid_column urowid）Clob用于存储基于字符的大对象Nclob可以使用数据库国际字符集所定义的字符集来存储仅为unicode类型的基于字符的数据Blob 最多可以存储4GB数据的二进制大对象BFILE存储向数据库外部文件的定位符，外部文件最大为4GBBINARY_FLOAT该类型是一个基于ANSI_IEEE745标准的浮点数据类型（32位双精度浮点数）Binary_double一个基于ANSI_IEEE745标准的双精度浮点数据类型，定义了32位双精度浮点5.2 表类型表示oracle数据库基本的存储对象，可以通过create table来创建，当在数据库内部建表的时候，数据库会将一大片连续的快（数据库中的最小的存储单元）分配到叫做扩展区（extent）的逻辑实体中去。接着按照需要将一个或多个扩展分区分配给表。Oracle中的表的类型如下：关系表，临时表，外部表，索引组织表，对象表5.2.1 关系表关系表是在数据库中常见类型，由一个列的集合组成，在每个列中分配有名称、数据类型以及宽度。表的数据存储在该表的行中，每一行组成一个单独的记录。5.2.2 临时表临时表由create global temporary table 命令所创建，用于存储短期的数据。临时表中的数据仅对装载数据的会话可见，同时其他的一些操作也仅会影响会话中的数据。临时表中的数据可以一直保存到实际事务完成时或者到该会话结束时，这将会在表创建的时候进行配置。5.2.4 外部表外部表可以访问数据库的外部数据。建表的时候，首先需要定义外部数据的格式，然后定义可以使用select语句查询oracle数据库的外部数据源。可以通过使用函数来将外部表连接到其他oracle数据库表上，同时也可以将数据装载到其他oracle数据库的表中。（外部表不需要数据库的内部空间）Oracle9i仅允许读取外部表，不允许读写外部表。Oracle10g允许对外部表进行读写操作。仅能通过create table as select命令来创建和组装外部表，而不能对外部表接着发出insert ，update，delete命令当使用drop table命令删除外部表时，不会删除由该外部表创建的文件。最后需要注意的是，如果外部表即将写入的文件已经存在，则外部表的创建操作会失败。5.2.4 索引组织表索引组织表（IOT）实际上是一个B*索引树，看起来很像oracle数据库的表。使用索引组织使得表中的记录基于该表的主键进行分组。它可以减少用来访问给定数据集合的逻辑读操作的操作总数，为索引组织不需要访问索引表和关系表。Oracle数据库也允许索引组织表创建辅助索引。5.3 约束Oracle允许在表中和多个表之间定义多重约束或完整性约束。这些约束定义了应用于表列中和多个表列之间的特殊规则。约束类型描述Not null该列中不能包含null值Foreign key用于在两个不同的表列之间强制建立父子关系。为了创建外键，外部键的列必须是父表中主键的一部分或者是一个唯一键Primary key用于唯一地标志表中每一行的一列或者多个累的合并。Unique唯一性约束，与主键类似。Check用于强制实施与给定列相关的特定数据的完整性和有效性规则5.4 表的属性属性描述默认值Pctfree为更新已存在的行以确定将需要保留的数据块数量。一旦数据块的使用数量达到或超过了pctfree属性所定义的值，将会从空闲列表中把数据块删除出去10Pctused如果可用的空间降低到pctused属性所定义的值一下，则之前由于达到或超出pctfree阈值而从空闲表中删除的块将会重新添加到表40Initrans定义分配给每一个数据块并发事务项的初始数量。该数量的最大值取决于块的大小。如果有大量并发的操作，则可以对该值进行修改，但数据库将根据需要动态地添加事务项1Maxtrans确定用来更新给定数据块并发事务的最大数量随着块的大小而变化Storage子句的值参数意图Initial定义表中的第一个扩展区的大小Next定义表中下一个扩展区的大小Minextents定义分配给表的最小的扩展区数目Maxextents定义分配给表的最大的扩展区数目Pctincrease定义在每个扩展区操作之后next参数应该增加的百分比Freelists定义分配给对象的空闲表的数目Freelist group定义分配给对象的空闲表组的数目Buffer_pool定义在oracle中分配给对象的缓冲池5.5 并行处理某些操作在oracle中可以并行地处理。建表时，可以通过parallel子句为表的并行化定义默认值。为表空间的默认值创建对象时，给定对象的并行度可以通过继承来得到。另外可以使用SQL代码的提示可以指出并行操作。定义了并行度之后，oracle将并行地访问基于这些设置的表。Oracle执行并行操作时，一个单独的控制器进程会产生多个子进程，并将所执行的任务分配给这些子进程。Oracle允许并行执行DML和DDL操作。5.6 分区Oracle允许对表和索引进行分区，所支持的四种分区方法如下： 范围：基于表中一个或多个列值的范围对表进行分区 Hash：基于表中一个或多个列值进行计算所得到的hash值对表进行分区 列表：基于分区键特定值的列表对表进行分区 组合：以上方法的组合使用。该方法可以创建分区表，并且可以进一步将分区表划分成多个子分区分区可以改善查询性能，并且可以使大表的管理变得容易。5.7 其他相关特征表数据压缩：该特征仅能在oracle9i的release版本中可用保留或再循环缓冲池的分配：若要频繁地访问表，则最好将表放入缓冲池。如果不再频繁地对对象进行读操作，则可以考虑将其分配到再循环缓冲池中，以防止对默认的缓冲区高速缓存造成紊乱。检测增长：可用用于快速更新表中的统计值对象统计值：当创建完表和任何相关的索引之后，需要对这些对象进行分析，使得优化器可以正确地对这些对象进行统计。禁用日志记录：logging，nologging，用来指定表的创建、任何与关联约束相关的索引、分区以及相关的LOB信息是否因该存储在联机重做日志中。5.8 创建表5.8.1 安全性需求安全性要求来自两个方面： DDL安全性需求：涉及到表的实际创建和管理 DML安全性需求：涉及到表创建后的访问1. DDL安全性需求在自己的模式中创建表，需要有create table 的系统权限。若要在登录所使用的模式之外的另一个模式中创建表，则需要有 create any table的权限。为了建表，必须在建表的表空间中有空间限额，或者拥有unlimited tablespace的系统权限。若空间限额不合适，则需要使用alter user 命令进行更正：alter user scott quota 100m on prod_data;若在另一种用户模式中建表，则该用户必须在建表所在色表空间中拥有空间限额，或通过grant命令授权unlimited tablespace的系统权限。为了建外部表，首先需要通过create directory命令定义目录。目录创建完成之后，使用grant命令来给创建和使用外部表的用户授予目录的read和write的权限。必须在外部数据所在的目录中拥有read object 的权限。另外必须拥有create table或create any table的系统权限。2. DML安全性需求DML语句所需的权限例子Select必须拥有表的select权限或者拥有select any table 系统权限Grant select on my_table to my_user;Grant select any table to my_user;Insert 必须拥有表的insert权限或拥有insert any tableGrant insert on my_table to my_user;Grant insert on my_table to my_user;Grant inser any table to my_user;Update必须拥有表的update权限或update any table权限Grant update on my_table to my_user;Grant update(id) on my_table to my_user;Delete拥有delete或delete any table的权限Grant delete on my_table to my_user;Grant delete any table to my_user;All使用grant all的系统权限以拥有给定表的所有权限Grant all on my_table to my_user;3. 使用角色来管理安全性角色可以用于简单的安全管理。角色会分配权力和权限。首先创建角色：Create role developer identified by developer;分配某些权限：Grant insert ,update,delete on my_table to developer;Grant create session to developer;创建一个用户，将开发者的角色分配给该用户：Create user dev_001 identified by dev_001 default tablespace prod_data;给用户授予开发者角色：Grant develop to dev_001;4关于系统权限和对象集权限的报告DBA有时候也不能确定用户拥有的权限，这时候使用一些视图可以帮助DBA确定用户权限。权力和权限有两种基本的形式：系统权限和对象集权限。关于系统权限的报告通过视图DBA_SYS_PRIVS报告系统权限。Select grantee,privilege from dba_sys_privs where grantee=scott;检查某一角色是否给了某用户：select grantee,grantee_role from dba_role_privs where grantee=scott;确定某一角色拥有哪些系统权限：select grantee,privilege from dba_sys_privs where grantee=scott;关于对象级权限的报告对象级权限表示访问特殊对象的能力。通过grant命令授予对象级权限，通过revoke 命令删除对象级权限。Select grantee,owner,table_name,grantor,privilege from dba_tab_privs where grantee=scott;5.8.2 对分配给本地管理表空间的表限定大小Oracle会将storage 子句的多个值结合起来，使得统一的扩展区大小尽可能接近初始的存储请求。5.8.3 使用create table命令的例子1. 五分区表的例子Create table parts( Id number primary key, Name varchar2(30), Bin_code number, Upc number);一个更加复杂的例子Create table parts( Id number not null, Version number not null, Name varchar2(30), Bin_code number not null, Upc number number not null)tablespace parts_tablespacePctfree 20 pctused 60Storage(initial 10m next 10m pctincrease 0);/创建一个名为parts的表，将其分配到parts_tablespace的表空间中，并且对pctfree，pctused和pctincrease参数进行赋值，使得在数据块中保留一部分空间用于更新，并保证数据块增长的一致性。下面是一个更加复杂的例子：Create table parts( Id number, Version number, Name varchar2(30), Active_code varchar2(1) not null constraint ch_parts_active_code_01 check(upper(active_code)=Y or upper(active_code)=N ), Constraint pk_parts primary key(id,version) using index tablespace parts_index storage(initial 1m next 1m)tablespace parts_tablespace pctfree 20- pctused 60 storage(initial 10m next 10m pctincrease 0);Create table parts tablespace parts_tablespace pctfree 20 pctused 60 storage(initial 100m next 100m pctincrease 0) as select * parts_external;使用oracle数据并行查询来配置表Create table parts ( Id number primary key, Name varchar2(30),)tablespace parts_tablespace parallel 4;/并行度为4索引组织表的例子Create table parts( Serial_number number primary key, Date_of_manufacture date, Start_date date)organization index storage(initial 10m next 10m pctincrease 0) pctfree 10 tablespace parts_tablespace pctthreshold 20 overflow tablespace parts_overflow pctfree 20 pctused 60 storage(initial 10m next 10m pctincrease 0);/pctthreshold子句用来定义将分配给行头部的索引块的百分比。将每一行分为头部和尾部，行的头部存储在索引组织表中，行的尾部存储在溢出段中（溢出段中的内容会被立刻覆盖）。数据库计算行的大小，并且将大小超过pctthreshold与数据库块大小的乘积值的行存储在溢出段中。Overflow tablespace子句定义溢出段。溢出段是一个独立分配的段，用于存储所有插入到相关所以组织表中的记录尾部。索引组织表可以使用键压缩，因为索引组织表时一棵B*索引树。使用organization index compress参数进行索引组织表的键压缩。需要注意的是，减压缩有一个约束，就是必须定义一个多列主键。如下：Create table worker_table ( Worker_no number primary key, Worker_name varchar2(30)tablespace parts_tablespace;Create table part_serial_number( Serial_number number, Version number, Worker_code number, Constraint pk_part_serial_number primary key(serial_number,version), Constraint fk_part_serial_worker foreign key(worker_code) refrence worker_table(worker_no)organization index compress storage(initial 10m next 10m Pctincrease 0) pctfree 10 tablespace parts_tablespace pctthreshold 20 overflow tablespace parts_overflow pctfree 20 pctused 60 storage(initial 10m next 10m Pctincrease 0);2. 分区表的例子范围分区表的创建Create table store_sales( Store_id number(6), Dept_id number, Sales_date date, Dept_sales number(10,2), Partition by range(sales_date) ( Partition sales_q1 values lese than(to_date(01_apr_2003,DD-MON-YYYY), Partition sales_02 valuesless than(to_date(01-jul-2003, DD-MON-YYYY),Partition sales_overflow values less than (maxvalue);HASH分区表的创建使用HASH分区表可以使表中的数据得到最佳的分配，有助于在某些高并发性的应用程序中消除块冲突。Create table site_log( Operation_id number, Server_id number, Ts_of_action timestamp, Constraint commend check(commend in(A,M,D)pctfree 5 pctused 70 storage(initial 100m next 100m Freelists 10 freelist group 2)partition by hash(operation_id) partitions 3 store in(site_log_tbs1, site_log_tbs2, site_log_tbs1);列表分区表的创建Create table store_sales( Store_id number(6), Dept_id number, Store_state varchar2(2)partition by list(store_state)( Partition oklahoma values(OK), Partition texas values(TX), Partition Kansas(KS);3. 全局临时表的例子全局临时表可以在标准的表结构中存储临时数据，全局临时表在许多方面都与其他一些表相似，但每个会话仅能使用该会话自己的数据，它还有一些限制： 不能对全局临时表进行分区、簇操作、索引操作，也不能在其中定义任何外部件约束 不能定义全局临时表的物理属性，如表空间或者存储子句 临时表不支持分布事务和并行操作临时表中的数据的保存时间可以通过on commit子句来控制。如果想保存在特定的事务中，则需要使用commit delete rows子句。如果想要数据保存在整个会话中，则需要使用on commit preserve rows 子句Create global temporary table temporary_table( Temporary_id number, Temporary_value number) on commit delete rows;/在事务结束时，删除全局临时表中的数据Create global temporary table temporary_table( Temporary_id number, Temporary_value number) on commit preserve rows;/仅在会话断开之后才删除全局临时表中的数据4. 外部表的例子外部表允许对外部操作系统文件进行读取操作，或者对外部数据库文件进行写入操作。这使得可以更容易将数据从oracle数据库中移进或移出。为了创建外部表，首先需要使用create directory 命令来定义外部表可以访问的目录，然后给创建外部表的用户授予访问这些目录的权限。 Create directory external_directory_scott as c:oracleexternal_tablemydbscott;Grant read on directory external_directory_scott to scott;Connect scott/tiger;Create table scott_import_data( Sale_date date, Store_number number, Dept_no number, Amount number)organization external(type oracle_loader default directory external_directory_scott access parameters(records delimited by newline fields terminated by , missing field values are null(sales_date char date_format date mask mm-dd-yyyy,store_number,dept_no,amount)location (scott_import_file.dat);5.9 修改表Alter table可以完成以下工作： 添加、修改或删除表属性（表的pctfree，pctused、或者storage） 添加、修改或删除列和列的属性 添加、修改或删除约束 添加、修改或删除表分区 添加、修改或删除外部表的位置 添加、修改或删除外部表的设置 将表移动到另外一个表空间中，或者重新建表5.9.1 安全性需求 在自己的模式中可以对表进行修改。为了在另外一个表空间中使用alter table命令修改表，必须在表中拥有alter的权限或者拥有alter any table的系统权限。在最也有一些操作如果要在不是自己模式的另一个模式中执行，则需要拥有create any index的权限。如果要删除或者阶段分区，而又不是这个表的拥有者，则必须拥有drop any table的权限。如果希望添加、修改、移动或者分割分区，则必须在表空间中拥有足够的表空间执行这操作。如果要创建和使用唯一键或者主键约束，则在表中需要有创建索引所需的权限。最后需要注意的是，如果要在自己模式以外的其他模式中启用或者禁用触发器，则需要拥有alter any trigger的系统权限。5.9.2 修改五分区表的例子1. 向表中添加列Alter table parts add(part_location varchar2(20),part_bin varchar2(20);2.修改列Alter table parts modify(part_location varchar2(30),part_bin varchar2(30);3.删除列Alter table parts drop(part_location, part_bin);4.重命名列Alter table parts rename column part_location to part_loc;5.添加主键Alter table parts add constraint pk_parts_part_id primary key (id) using index tablespace parts_index storage(initial 100k next 100k Pctincrease 0);6.添加唯一主键Alter table parts add constraint uk_parts_part_bin unique(part_bin) using index tablespace parts_index storage(initial 100k next 100k Pctincrease 0);7.添加外部键Alter table parts add constraint fk_part_bin foreign key (bin_code) references part_bin;8.添加check约束Alter table parts add (constraint ck_parts_01 check(id0);9.为列定义默认值Alter table parts modify(name default not available);Alter table add (vendor number default 0);10. 删除约束Alter table parts drop constraint fk_part_bin;Alter table parts drop primary key;如果有外键，则当删除主键时需要使用cascade命令：Alter table parts drop primary key cascade；删除唯一键：Alter table parts drop unique(uk_parts_part_bin);Alter table parts drop unique (uk_parts_part_bin) cascade;11.禁用约束禁用主键：alter table parts disable primary key;总之禁用约束的语法规则是alter table 表名 disable 约束类型;在某些情况下，可能更可取的方法时保持约束的有效性而删除相关联的索引：alter table parts disable validate primary key;12.重新启用约束Alter table parts enable constraint fk_part_bin;Alter table parts enable primary key;Alter table parts enable unique (part_bin);13.禁用及重新启用触发器Alter table parts disable all triggers;Alter table parts enable all triggers;14.将列属性修改为NULL或NOT NULLAlter table parts modify(name not null);Alter table parts modify(name null);15.更改表的物理属性Alter table parts pctfree 10m pctused 60;Alter table parts storage(next 1m);16.修改表的并行设置Alter table parts parallel 4;17.重新创建/移动表Alter table parts move tablespace parts_new_tbs pctfree 10 pctused 60;/将一个已经存在的表移动到另一个表空间中去18.添加LOB数据类型的列Alter table parts add(photo blob) lob(photo) store as lob_parts_photo (tablespace parts_lob_tbs);19.修改LOB参数和存储子句alter table parts modify lob(photo)(storage(storage(Freelists 2);alter table parts modify lob(photo)(pctversion 50);20.联机缩小和压缩段表中删除许多行之后，可能需要对这个表进行压缩，并且重新设置表的高阈值。在oracle10g中，可以使用alter table 表名 shrink space cascade;但在此之前必须使用alter table enable row movement命令来启用表的行移动。5.9.3修改分区表当进行分区表维护的时候，需要牢记全局索引中的一些操作对其的一些影响。当执行物理修改或一个分区中的数据的操作时，应该考虑使用update global indexes子句。错误执行该操作会导致全局索引分区不可用，这样就需要在随后重新创建这些索引。1.添加分区或者子分区 向分区中添加分区或子分区：Alter table store_sales add pa