Oracle DBA成长日记3.docx
《Oracle DBA成长日记3.docx》由会员分享，可在线阅读，更多相关《Oracle DBA成长日记3.docx（22页珍藏版）》请在冰豆网上搜索。
OracleDBA成长日记3
OracleDBA成长日记
五．表
5.1oracle数据类型
数据类型名称
描述
例子
Char
最多可以以固定长度的格式存储2000个字符。
当位数不够时，需要在其右边添加空格来补满
Createtabletest（namechar（20））
Varchar,varchar2
最多可以可变长度格式来存储4000b，varchar2比varchar更适合使用，由于兼容性的原因，仍然大量使用的是varchar
Createtabletest（namevarchar（20））
Nchar
国际语言支持的数据类型仅可以在存储由数据库NLS字符集定义的unicode字符集。
该数据类型最多可以存储2000b，当位数不够时需要在右边填充空格
Createtabletest（namenchar（20））
Nvarchar2
NLS的数据类型与varchar2数据类型等价，最多可以存储4000B
Createtabletest（namenvarchar2（20））
Number
用于存储零、正数、负数以及浮点数。
可以number（p,s）的形式来定义数字的精度和范围
Createtabletest（gradenumber（5,2））
Long
Long类型可以存储可变长的字符串，最多2GB。
可以使用long类型来存储long类型的文本字符串（建议使用LOB数据类型来代替LONG类型）
Createtabletest（namelong）
Date
存储日期和时间
Createtabletest（birthdaydate）
Timestamp
使用年月日时分秒来对日期时间进行详细的支持。
Timestampwithtimezone
包含timestamp中的所有域，还有timezone_hour和timezone_minute
Timestampwithlocaltimezone
日期时间校准为会话的时区
Intervalyeartomonth
用于存储一个时间段，由月份和年组成，需要5B来组成。
Selectinterval’01-05’yeartomonth-’01-02’yeartomonthfromdual
Intervaldaytosecond
由存储一个时间段，由日和秒组成，需要11B来存储
Selectinterval‘10010:
20
:
42.22’day（3）tosecond
（2）-interval‘10110:
20:
42.22’day（3）tosecond
（2）fromdual
Raw
存储raw类型的二进制数据，最多可以存储2000B，建议使用BLOB代替它
Createtabletest（raw
_columnraw（2000））
Longraw
存储raw类型的二进制数据，最多可以存储2GB数据，建议使用BLOB代替
Rowid
表中的rowid类型的字符串表示
Urowid
在索引组织中表示逻辑行地址
Cretetabletest（urowid_columnurowid）
Clob
用于存储基于字符的大对象
Nclob
可以使用数据库国际字符集所定义的字符集来存储仅为unicode类型的基于字符的数据
Blob
最多可以存储4GB数据的二进制大对象
BFILE
存储向数据库外部文件的定位符，外部文件最大为4GB
BINARY_FLOAT
该类型是一个基于ANSI_IEEE745标准的浮点数据类型（32位双精度浮点数）
Binary_double
一个基于ANSI_IEEE745标准的双精度浮点数据类型，定义了32位双精度浮点
5.2表类型
表示oracle数据库基本的存储对象，可以通过createtable来创建，当在数据库内部建表的时候，数据库会将一大片连续的快（数据库中的最小的存储单元）分配到叫做扩展区（extent）的逻辑实体中去。
接着按照需要将一个或多个扩展分区分配给表。
Oracle中的表的类型如下：
关系表，临时表，外部表，索引组织表，对象表
5.2.1关系表
关系表是在数据库中常见类型，由一个列的集合组成，在每个列中分配有名称、数据类型以及宽度。
表的数据存储在该表的行中，每一行组成一个单独的记录。
5.2.2临时表
临时表由createglobaltemporarytable命令所创建，用于存储短期的数据。
临时表中的数据仅对装载数据的会话可见，同时其他的一些操作也仅会影响会话中的数据。
临时表中的数据可以一直保存到实际事务完成时或者到该会话结束时，这将会在表创建的时候进行配置。
5.2.4外部表
外部表可以访问数据库的外部数据。
建表的时候，首先需要定义外部数据的格式，然后定义可以使用select语句查询oracle数据库的外部数据源。
可以通过使用函数来将外部表连接到其他oracle数据库表上，同时也可以将数据装载到其他oracle数据库的表中。
（外部表不需要数据库的内部空间）
Oracle9i仅允许读取外部表，不允许读写外部表。
Oracle10g允许对外部表进行读写操作。
仅能通过createtableasselect命令来创建和组装外部表，而不能对外部表接着发出insert，update，delete命令
当使用droptable命令删除外部表时，不会删除由该外部表创建的文件。
最后需要注意的是，如果外部表即将写入的文件已经存在，则外部表的创建操作会失败。
5.2.4索引组织表
索引组织表（IOT）实际上是一个B*索引树，看起来很像oracle数据库的表。
使用索引组织使得表中的记录基于该表的主键进行分组。
它可以减少用来访问给定数据集合的逻辑读操作的操作总数，为索引组织不需要访问索引表和关系表。
Oracle数据库也允许索引组织表创建辅助索引。
5.3约束
Oracle允许在表中和多个表之间定义多重约束或完整性约束。
这些约束定义了应用于表列中和多个表列之间的特殊规则。
约束类型
描述
Notnull
该列中不能包含null值
Foreignkey
用于在两个不同的表列之间强制建立父子关系。
为了创建外键，外部键的列必须是父表中主键的一部分或者是一个唯一键
Primarykey
用于唯一地标志表中每一行的一列或者多个累的合并。
Unique
唯一性约束，与主键类似。
Check
用于强制实施与给定列相关的特定数据的完整性和有效性规则
5.4表的属性
属性
描述
默认值
Pctfree
为更新已存在的行以确定将需要保留的数据块数量。
一旦数据块的使用数量达到或超过了pctfree属性所定义的值，将会从空闲列表中把数据块删除出去
10
Pctused
如果可用的空间降低到pctused属性所定义的值一下，则之前由于达到或超出pctfree阈值而从空闲表中删除的块将会重新添加到表
40
Initrans
定义分配给每一个数据块并发事务项的初始数量。
该数量的最大值取决于块的大小。
如果有大量并发的操作，则可以对该值进行修改，但数据库将根据需要动态地添加事务项
1
Maxtrans
确定用来更新给定数据块并发事务的最大数量
随着块的大小而变化
Storage子句的值
参数
意图
Initial
定义表中的第一个扩展区的大小
Next
定义表中下一个扩展区的大小
Minextents
定义分配给表的最小的扩展区数目
Maxextents
定义分配给表的最大的扩展区数目
Pctincrease
定义在每个扩展区操作之后next参数应该增加的百分比
Freelists
定义分配给对象的空闲表的数目
Freelistgroup
定义分配给对象的空闲表组的数目
Buffer_pool
定义在oracle中分配给对象的缓冲池
5.5并行处理
某些操作在oracle中可以并行地处理。
建表时，可以通过parallel子句为表的并行化定义默认值。
为表空间的默认值创建对象时，给定对象的并行度可以通过继承来得到。
另外可以使用SQL代码的提示可以指出并行操作。
定义了并行度之后，oracle将并行地访问基于这些设置的表。
Oracle执行并行操作时，一个单独的控制器进程会产生多个子进程，并将所执行的任务分配给这些子进程。
Oracle允许并行执行DML和DDL操作。
5.6分区
Oracle允许对表和索引进行分区，所支持的四种分区方法如下：
1范围：
基于表中一个或多个列值的范围对表进行分区
2Hash：
基于表中一个或多个列值进行计算所得到的hash值对表进行分区
3列表：
基于分区键特定值的列表对表进行分区
4组合：
以上方法的组合使用。
该方法可以创建分区表，并且可以进一步将分区表划分成多个子分区
分区可以改善查询性能，并且可以使大表的管理变得容易。
5.7其他相关特征
表数据压缩：
该特征仅能在oracle9i的release版本中可用
保留或再循环缓冲池的分配：
若要频繁地访问表，则最好将表放入缓冲池。
如果不再频繁地对对象进行读操作，则可以考虑将其分配到再循环缓冲池中，以防止对默认的缓冲区高速缓存造成紊乱。
检测增长：
可用用于快速更新表中的统计值
对象统计值：
当创建完表和任何相关的索引之后，需要对这些对象进行分析，使得优化器可以正确地对这些对象进行统计。
禁用日志记录：
logging，nologging，用来指定表的创建、任何与关联约束相关的索引、分区以及相关的LOB信息是否因该存储在联机重做日志中。
5.8创建表
5.8.1安全性需求
安全性要求来自两个方面：
1DDL安全性需求：
涉及到表的实际创建和管理
2DML安全性需求：
涉及到表创建后的访问
1.DDL安全性需求
在自己的模式中创建表，需要有createtable的系统权限。
若要在登录所使用的模式之外的另一个模式中创建表，则需要有createanytable的权限。
为了建表，必须在建表的表空间中有空间限额，或者拥有unlimitedtablespace的系统权限。
若空间限额不合适，则需要使用alteruser命令进行更正：
alteruserscottquota100monprod_data;
若在另一种用户模式中建表，则该用户必须在建表所在色表空间中拥有空间限额，或通过grant命令授权unlimitedtablespace的系统权限。
为了建外部表，首先需要通过createdirectory命令定义目录。
目录创建完成之后，使用grant命令来给创建和使用外部表的用户授予目录的read和write的权限。
必须在外部数据所在的目录中拥有readobject的权限。
另外必须拥有createtable或createanytable的系统权限。
2.DML安全性需求
DML语句
所需的权限
例子
Select
必须拥有表的select权限或者拥有selectanytable系统权限
Grantselectonmy_tabletomy_user;
Grantselectanytabletomy_user;
Insert
必须拥有表的insert权限或拥有insertanytable
Grantinsertonmy_tabletomy_user;
Grantinsertonmy_tabletomy_user;
Grantinseranytabletomy_user;
Update
必须拥有表的update权限或updateanytable权限
Grantupdateonmy_tabletomy_user;
Grantupdate（id）onmy_tabletomy_user;
Delete
拥有delete或deleteanytable的权限
Grantdeleteonmy_tabletomy_user;
Grantdeleteanytabletomy_user;
All
使用grantall的系统权限以拥有给定表的所有权限
Grantallonmy_tabletomy_user;
3.使用角色来管理安全性
角色可以用于简单的安全管理。
角色会分配权力和权限。
首先创建角色：
Createroledeveloperidentifiedbydeveloper;
分配某些权限：
Grantinsert,update,deleteonmy_tabletodeveloper;
Grantcreatesessiontodeveloper;
创建一个用户，将开发者的角色分配给该用户：
Createuserdev_001identifiedbydev_001defaulttablespaceprod_data;
给用户授予开发者角色：
Grantdeveloptodev_001;
4．关于系统权限和对象集权限的报告
DBA有时候也不能确定用户拥有的权限，这时候使用一些视图可以帮助DBA确定用户权限。
权力和权限有两种基本的形式：
系统权限和对象集权限。
关于系统权限的报告
通过视图DBA_SYS_PRIVS报告系统权限。
Selectgrantee,privilegefromdba_sys_privswheregrantee=’scott’;
检查某一角色是否给了某用户：
selectgrantee,grantee_rolefromdba_role_privswheregrantee=’scott’;
确定某一角色拥有哪些系统权限：
selectgrantee,privilegefromdba_sys_privswheregrantee=’scott’;
关于对象级权限的报告
对象级权限表示访问特殊对象的能力。
通过grant命令授予对象级权限，通过revoke命令删除对象级权限。
Selectgrantee,owner,table_name,grantor,privilegefromdba_tab_privswheregrantee=’scott’;
5.8.2对分配给本地管理表空间的表限定大小
Oracle会将storage子句的多个值结合起来，使得统一的扩展区大小尽可能接近初始的存储请求。
5.8.3使用createtable命令的例子
1.五分区表的例子
Createtableparts
（
Idnumberprimarykey,
Namevarchar2（30）,
Bin_codenumber,
Upcnumber
）;
一个更加复杂的例子
Createtableparts
（
Idnumbernotnull,
Versionnumbernotnull,
Namevarchar2（30）,
Bin_codenumbernotnull,
Upcnumbernumbernotnull
）tablespaceparts_tablespace
Pctfree20pctused60
Storage（initial10mnext10mpctincrease0）;
//创建一个名为parts的表，将其分配到parts_tablespace的表空间中，并且对pctfree，pctused和pctincrease参数进行赋值，使得在数据块中保留一部分空间用于更新，并保证数据块增长的一致性。
下面是一个更加复杂的例子：
Createtableparts
（
Idnumber,
Versionnumber,
Namevarchar2（30）,
Active_codevarchar2
（1）notnullconstraintch_parts_active_code_01check（upper（active_code）=’Y’orupper（active_code）=’N’）,
Constraintpk_partsprimarykey（id,version）usingindextablespaceparts_indexstorage（initial1mnext1m）
）tablespaceparts_tablespacepctfree20-pctused60storage（initial10mnext10mpctincrease0）;
Createtablepartstablespaceparts_tablespacepctfree20pctused60storage（initial100mnext100mpctincrease0）asselect*parts_external;
使用oracle数据并行查询来配置表
Createtableparts
（
Idnumberprimarykey,
Namevarchar2（30）,
）tablespaceparts_tablespaceparallel4;//并行度为4
索引组织表的例子
Createtableparts
（
Serial_numbernumberprimarykey,
Date_of_manufacturedate,
Start_datedate
）organizationindexstorage（initial10mnext10mpctincrease0）pctfree10tablespaceparts_tablespacepctthreshold20overflowtablespaceparts_overflowpctfree20pctused60storage（initial10mnext10mpctincrease0）;
//pctthreshold子句用来定义将分配给行头部的索引块的百分比。
将每一行分为头部和尾部，行的头部存储在索引组织表中，行的尾部存储在溢出段中（溢出段中的内容会被立刻覆盖）。
数据库计算行的大小，并且将大小超过pctthreshold与数据库块大小的乘积值的行存储在溢出段中。
Overflowtablespace子句定义溢出段。
溢出段是一个独立分配的段，用于存储所有插入到相关所以组织表中的记录尾部。
索引组织表可以使用键压缩，因为索引组织表时一棵B*索引树。
使用organizationindexcompress参数进行索引组织表的键压缩。
需要注意的是，减压缩有一个约束，就是必须定义一个多列主键。
如下：
Createtableworker_table
（
Worker_nonumberprimarykey,
Worker_namevarchar2（30）
）tablespaceparts_tablespace;
Createtablepart_serial_number
（
Serial_numbernumber,
Versionnumber,
Worker_codenumber,
Constraintpk_part_serial_numberprimarykey（serial_number,version）,
Constraintfk_part_serial_workerforeignkey（worker_code）refrenceworker_table（worker_no）
）organizationindexcompressstorage（initial10mnext10mPctincrease0）pctfree10tablespaceparts_tablespacepctthreshold20overflowtablespaceparts_overflowpctfree20pctused60storage（initial10mnext10mPctincrease0）;
2.分区表的例子
范围分区表的创建
Createtablestore_sales
（
Store_idnumber（6）,
Dept_idnumber,
Sales_datedate,
Dept_salesnumber（10,2）,
Partitionbyrange（sales_date）
（
Partitionsales_q1valueslesethan（to_date（‘01_apr_2003’,’DD-MON-YYYY’））,
Partitionsales_02valueslessthan（to_date（’01-jul-2003’,’DD-MON-YYYY’））,
Partitionsales_overflowvalueslessthan（maxvalue）
）
）;
HASH分区表的创建
使用HASH分区表可以使表中的数据得到最佳的分配，有助于在某些高并发性的应用程序中消除块冲突。
Createtablesite_log
（
Operation_idnumber,
Server_idnumber,
Ts_of_actiontimestamp,
Constraintcommendcheck（commendin（‘A’,’M’,’D’））
）pctfree5pctused70storage（initial100mnext100mFreelists10freelistgroup2）partitionbyhash（operation_id）partitions3storein（site_log_tbs1,site_log_tbs2,site_log_tbs1）;
列表分区表的创建
Createtablestore_sales
（
Store_idnumber（6）,
Dept_idnumber,
Store_statevarchar2
（2）
）partitionbylist（store_state）
（
Partitionoklahomavalues（‘OK’）,
Partitiontexasvalues（‘TX’）,
PartitionKansas（‘KS’）
）;
3.全局临时表的例子
全局临时表可以在标准的表结构中存储临时数据，全局临时表在许多方面都与其他一些表相似，但每个会话仅能使用该会话自己的数据，它还有一些限制：
1不能对全局临时表进行分区、簇操作、索引操作，也不能在其中定义任何外部件约束
2不能定义全局临时表的物理属性，如表空间或者存储子句
3临时表不支持分布事务和并行操作
临时表中的数据的保存时间可以通过oncommit子句来控制。
升级会员 