1、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 字符集。该数据类型最多
2、可以存储 2000b，当位数不够时需要在右边填充空格Create table test (name nchar(20)Nvarchar2 NLS 的数据类型与 varchar2 数据类型等价，最多可以存储 4000BCreate table test (name nvarchar2(20)Number 用于存储零、正数、负数以及浮点数。可以 number（ p,s）的形式来定义数字的精度和范围Create table test (grade number(5,2)Long Long 类型可以存储可变长的字符串，最多 2GB。可以使用 long 类型来存储long 类型的文本字符串（建议使用LO
3、B 数据类型来代替 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 in
4、terval 01-05 year to month-01-02 year to month from dualInterval day to 由存储一个时间段，由日和秒组成， Select interval 100 10:20second 需要 11B 来存储 :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)Lon
5、g raw 存储 raw 类型的二进制数据，最多可以存储 2GB 数据 ，建议使用 BLOB代替Rowid 表中的 rowid 类型的字符串表示Urowid 在索引组织中表示逻辑行地址 Crete table test（urowid_column urowid）Clob 用于存储基于字符的大对象Nclob 可以使用数据库国际字符集所定义的字符集来存储仅为 unicode 类型的基于字符的数据Blob 最多可以存储 4GB 数据的二进制大对象BFILE 存储向数据库外部文件的定位符，外部文件最大为 4GBBINARY_FLOAT 该类型是一个基于 ANSI_IEEE745 标准的浮点数据类型（3
6、2 位双精度浮点数）Binary_double 一个基于 ANSI_IEEE745 标准的双精度浮点数据类型，定义了 32 位双精度浮点5.2 表类型表示 oracle 数据库基本的存储对象，可以通过 create table 来创建，当在数据库内部建表的时候，数据库会将一大片连续的快（数据库中的最小的存储单元）分配到叫做扩展区（extent ）的逻辑实体中去。接着按照需要将一个或多个扩展分区分配给表。Oracle 中的表的类型如下：关系表，临时表，外部表，索引组织表，对象表5.2.1 关系表关系表是在数据库中常见类型，由一个列的集合组成，在每个列中分配有名称、数据类型以及宽度。表的数据存储在
7、该表的行中，每一行组成一个单独的记录。5.2.2 临时表临时表由 create global temporary table 命令所创建，用于存储短期的数据。临时表中的数据仅对装载数据的会话可见，同时其他的一些操作也仅会影响会话中的数据。临时表中的数据可以一直保存到实际事务完成时或者到该会话结束时，这将会在表创建的时候进行配置。5.2.4 外部表外部表可以访问数据库的外部数据。建表的时候，首先需要定义外部数据的格式，然后定义可以使用 select 语句查询 oracle 数据库的外部数据源。可以通过使用函数来将外部表连接到其他 oracle 数据库表上，同时也可以将数据装载到其他 oracle
8、 数据库的表中。（外部表不需要数据库的内部空间）Oracle9i 仅允许读取外部表，不允许读写外部表。Oracle10g 允许对外部表进行读写操作。仅能通过 create table as select 命令来创建和组装外部表，而不能对外部表接着发出 insert ，update，delete 命令当使用 drop table 命令删除外部表时，不会删除由该外部表创建的文件。最后需要注意的是，如果外部表即将写入的文件已经存在，则外部表的创建操作会失败。5.2.4 索引组织表索引组织表（IOT ）实际上是一个 B*索引树，看起来很像 oracle 数据库的表。使用索引组织使得表中的记录基于该表的
9、主键进行分组。它可以减少用来访问给定数据集合的逻辑读操作的操作总数，为索引组织不需要访问索引表和关系表。Oracle 数据库也允许索引组织表创建辅助索引。5.3 约束Oracle 允许在表中和多个表之间定义多重约束或完整性约束。这些约束定义了应用于表列中和多个表列之间的特殊规则。约束类型 描述Not null 该列中不能包含 null 值Foreign key 用于在两个不同的表列之间强制建立父子关系。为了创建外键，外部键的列必须是父表中主键的一部分或者是一个唯一键Primary key 用于唯一地标志表中每一行的一列或者多个累的合并。Unique 唯一性约束，与主键类似。Check 用于强制
10、实施与给定列相关的特定数据的完整性和有效性规则5.4 表的属性属性 描述 默认值Pctfree 为更新已存在的行以确定将需要保留的数据块数量。一旦数据块的使用数量达到或超过了 pctfree 属性所定义的值，将会从空闲列表中把数据块删除出去10Pctused 如果可用的空间降低到 pctused 属性所定义的值一下，则之前由于达到或超出 pctfree 阈值而从空闲表中删除的块将会重新添加到表40Initrans 定义分配给每一个数据块并发事务项的初始数量。该数量的最大值取决于块的大小。如果有大量并发的操作，则可以对该值1进行修改，但数据库将根据需要动态地添加事务项Maxtrans 确定用来更
11、新给定数据块并发事务的最大数量 随着块的大小而变化Storage 子句的值参数 意图Initial 定义表中的第一个扩展区的大小Next 定义表中下一个扩展区的大小Minextents 定义分配给表的最小的扩展区数目Maxextents 定义分配给表的最大的扩展区数目Pctincrease 定义在每个扩展区操作之后 next 参数应该增加的百分比Freelists 定义分配给对象的空闲表的数目Freelist group 定义分配给对象的空闲表组的数目Buffer_pool 定义在 oracle 中分配给对象的缓冲池5.5 并行处理某些操作在 oracle 中可以并行地处理。建表时，可以通过
12、parallel 子句为表的并行化定义默认值。为表空间的默认值创建对象时，给定对象的并行度可以通过继承来得到。另外可以使用 SQL 代码的提示可以指出并行操作。定义了并行度之后，oracle 将并行地访问基于这些设置的表。Oracle 执行并行操作时，一个单独的控制器进程会产生多个子进程，并将所执行的任务分配给这些子进程。Oracle 允许并行执行 DML 和 DDL 操作。5.6 分区Oracle 允许对表和索引进行分区，所支持的四种分区方法如下： 范围：基于表中一个或多个列值的范围对表进行分区 Hash：基于表中一个或多个列值进行计算所得到的 hash 值对表进行分区 列表：基于分区键特定
13、值的列表对表进行分区 组合：以上方法的组合使用。该方法可以创建分区表，并且可以进一步将分区表划分成多个子分区分区可以改善查询性能，并且可以使大表的管理变得容易。5.7 其他相关特征表数据压缩：该特征仅能在 oracle9i 的 release 版本中可用保留或再循环缓冲池的分配：若要频繁地访问表，则最好将表放入缓冲池。如果不再频繁地对对象进行读操作，则可以考虑将其分配到再循环缓冲池中，以防止对默认的缓冲区高速缓存造成紊乱。检测增长：可用用于快速更新表中的统计值对象统计值：当创建完表和任何相关的索引之后，需要对这些对象进行分析，使得优化器可以正确地对这些对象进行统计。禁用日志记录：logging
14、，nologging，用来指定表的创建、任何与关联约束相关的索引、分区以及相关的 LOB 信息是否因该存储在联机重做日志中。5.8 创建表5.8.1 安全性需求安全性要求来自两个方面： DDL 安全性需求：涉及到表的实际创建和管理 DML 安全性需求：涉及到表创建后的访问1. DDL 安全性需求在自己的模式中创建表，需要有 create table 的系统权限。若要在登录所使用的模式之外的另一个模式中创建表，则需要有 create any table 的权限。为了建表，必须在建表的表空间中有空间限额，或者拥有 unlimited tablespace 的系统权限。若空间限额不合适，则需要使用
15、alter user 命令进行更正：alter user scott quota 100m on prod_data;若在另一种用户模式中建表，则该用户必须在建表所在色表空间中拥有空间限额，或通过 grant 命令授权 unlimited tablespace 的系统权限。为了建外部表，首先需要通过 create directory 命令定义目录。目录创建完成之后，使用 grant 命令来给创建和使用外部表的用户授予目录的 read 和 write 的权限。必须在外部数据所在的目录中拥有 read object 的权限。另外必须拥有 create table 或 create any tabl
16、e 的系统权限。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_use
17、r;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.
18、使用角色来管理安全性角色可以用于简单的安全管理。角色会分配权力和权限。首先创建角色：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 t
19、o 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 g
20、rantee,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
21、 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 60St
22、orage(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_
23、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_tablesp
24、ace 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_manufa
25、cture 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 子句用来定义将分配给行头部的索引块的百分比。将每一行分为头部和尾部，行的头部存储
26、在索引组织表中，行的尾部存储在溢出段中（溢出段中的内容会被立刻覆盖）。数据库计算行的大小，并且将大小超过 pctthreshold 与数据库块大小的乘积值的行存储在溢出段中。Overflow tablespace 子句定义溢出段。溢出段是一个独立分配的段，用于存储所有插入到相关所以组织表中的记录尾部。索引组织表可以使用键压缩，因为索引组织表时一棵 B*索引树。使用 organization index compress 参数进行索引组织表的键压缩。需要注意的是，减压缩有一个约束，就是必须定义一个多列主键。如下：Create table worker_table (Worker_no numbe
27、r 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
28、(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),
29、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 分区表可
30、以使表中的数据得到最佳的分配，有助于在某些高并发性的应用程序中消除块冲突。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(sit
31、e_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. 全局临时表的例子全局临时表可以在标准的表结构中存储临时数据，全局临时表在许多方面都与其他一些表相似，但每个会话仅
32、能使用该会话自己的数据，它还有一些限制： 不能对全局临时表进行分区、簇操作、索引操作，也不能在其中定义任何外部件约束 不能定义全局临时表的物理属性，如表空间或者存储子句 临时表不支持分布事务和并行操作临时表中的数据的保存时间可以通过 on commit 子句来控制。如果想保存在特定的事务中，则需要使用 commit delete rows 子句。如果想要数据保存在整个会话中，则需要使用 on commit preserve rows 子句Create global temporary table temporary_table(Temporary_id number,Temporary_val
33、ue 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 命令来定
34、义外部表可以访问的目录，然后给创建外部表的用户授予访问这些目录的权限。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 ex
35、ternal(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 可以
36、完成以下工作： 添加、修改或删除表属性（表的 pctfree，pctused 、或者 storage） 添加、修改或删除列和列的属性 添加、修改或删除约束 添加、修改或删除表分区 添加、修改或删除外部表的位置 添加、修改或删除外部表的设置 将表移动到另外一个表空间中，或者重新建表5.9.1 安全性需求在自己的模式中可以对表进行修改。为了在另外一个表空间中使用 alter table 命令修改表，必须在表中拥有 alter 的权限或者拥有 alter any table 的系统权限。在最也有一些操作如果要在不是自己模式的另一个模式中执行，则需要拥有 create any index 的权限。如果要删除或者阶段分区，而又不是这个表的拥有者，则必须拥有 drop any table的权限。如果希望添加、修改、移动或者分割分区，则必须在表空间中拥有足够的表空间执行这操作。
