13管理表

方案对象管理---管理表1-2什么是Oracle数据库方案？•方案是用户使用的一系列数据库对象的集合。而用户是用来连接数据库并完成存取数据库对象的数据。•一个用户一般对应一个方案，该用户的方案名等于用户名，并作为该用户的缺省方案。这也就是在企业管理器的方案下看到方案名都为数据库用户名的原因。•如果SCOTT用户创建了表EMP，那么SCOTT就是表EMP的所有者，SCOTT访问SCOTT用户下的EMP表时不用在表名前面加上表的所有者：SELECT*FROMEMP;•如果其他用户要访问表EMP，要在表名前面加上表的所有者：SELECT*FROMscott.emp;否则会显示“没有此表”。1-33Oracle中的方案对象类型•Table：表•Index：索引•View：视图•Synonym:同义词（方案对象的一个别名，简化访问，提高安全）•Sequence：序列（命名的顺序编号生成器）•Procedure、function、trigger等存储过程、函数、触发器（是用户利用PL/SQL编写的命名的程序块）1-44本章内容•表概述•数据完整性与约束条件•管理表的策略•管理表创建表、更改表、删除表及删减表•管理约束•查看表信息及约束信息•OEM中管理表1-5515.1表概述•表是Oracle数据库最基本的对象，其他许多数据库对象（如索引、视图）都以表为基础。表被用于实际存储数据。表中有列，列中存储多行数据。在关系数据库中，不同表中的数据彼此之间可能存在一定的关系。•从用户角度看，表是一张二维表，即：表由行和列两部分组成，表通过行和列来组织数据。通常称表中的一行为一条记录，称表中的一列为一个字段。1-66表和列的命名规则长度30个字节之内。必须以字母开头。由字母、数字、_、#、$组成。不能使用保留字。非法的命名，可以用“”引着。一个表内列名要唯一。表名在名称空间内必须唯一。1-77Oracle中的数据类型•字符型——char、ncharvarchar2、nvarchar2•大对象数据类型——clob、nclobblob、bfile(二进制)•二进制数据类型——Row、LongRow•数字型——number(p,s)•时间和日期——Date，..•行数据类型——RowID、URowID1-815.2数据完整性与约束条件•数据完整性是关系数据库模型的基本原则，是用户在表上定义的一系列规则或约束条件，以及在表之间定义的一系列相互关系。数据完整性的作用，简单地说，就是强制要求数据库中只能接受正确的、合理的数据，防止错误的或无效的数据被插入到表中。•分三种类型：•实体完整性：主键的各个属性都不能为空值。•参照完整性：外键或者取空值，或者等于被参照关系中的主键的某个值。•用户自定义完整性：属性（或属性组）的取值应当满足用户定义的约束条件。1-9DEPTNO的值必须与表DEPT中的DEPTNO值匹配ENAME的每列都必须有一个值EMPNO每行都必须有惟一的值1-101015.2数据完整性与约束条件数据完整性•实体完整性•参照完整性•用户自定义完整性约束条件•NotNull•Unique•Check•Primarykey•Foreignkey约束级别•列级约束•表级约束1-111115.3管理表的策略•设计表的结构•表的类型1.标准表(按堆组织)2.索引表（以B树索引段）3.外部表（操作系统文件中）4.分区表5.簇表•表的位置及大小•使用建表的相关选项（nologging）1-12创建表•如果要在自己的方案中创建表，要求用户必须具有CREATETABLE系统权限。•如果要在其他方案中建表，则要求用户必须具有CREATEANYTABLE系统权限。•创建表时，Oracle会为该表分配相应的表段。表段的名称与表名完全相同，并且所有的数据都会被存放在该表段中。•要求表的创建者必须指定表空间上具有空间配额或具有UNLIMITEDTABLESPACE系统权限。1-1313Createtable语句•Createtable[schema.]table_name(column1datatype1[defaultexp1][column1_constraint],column2datatype2[defaultexp2][column2_constraint],…[table_constraint])[oncommit{delete|preserve}rows][organizition{heap..|index…|external…}][partitionby…(…)][tablespacetablespace_name][Logging|nologging][compress|nocompress][asquery]1-1414创建标准表createtabledepartment(deptidnumber(9)notnull,deptnochar(2)default01notnull,namevarchar2(20),addressvarchar2(30),constraintpk_deptidprimarykey(deptid)validate,constraintuniq_nameunique(name)novalidate,constraintchk_addresscheck(addressin('1号楼','2号楼','3号楼','4号楼'))validate)1-1515临时表•Oracle的临时表分为事务型和会话型。•事务型临时表就是指在事务处理过程中插入的记录只在事务提交或回滚之前有效，一旦事务完成，表中的记录便被自动清除。•会话型临时表指临时表中的数据在本次会话期间一直有效，直到会话结束。会话结束后表中针对此次会话的数据会自动清空。•当在临时表上执行DML操作时，既不会加锁也不会将数据变化写到重做日志中。因此不能用临时表保存永久数据。1-1616创建临时表Createglobaltemporarytabletable_name(…)oncommitdeleterows|oncommitpreserverows•其中：•globaltemporarytable指定表的类型为临时表。•oncommitdeleterows指定表为事务临时表。•oncommitpreserverows指定表为会话临时表。1-17事务临时表例子：1-18会话临时表的例子：1-19索引表•对于标准表而言，其数据以无序方式存储；而对于索引表来说，其数据以B-树结构来组织，并且其叶子数据块既包含键列数据，也包含非键列数据。•一般情况下，带有索引的标准表的表数据及其索引数据分别存放在对应的表段和索引段中，当在where子句中引用索引列时，首先定位索引数据并取得对应记录的ROWID，然后根据ROWID取得表的数据。•建立索引表时，Oracle会将表数据及其主键索引的数据一起放到索引段中。当在WHERE子句中引用主键列时，Oracle可以直接根据主键索引值取得表行数据。所以，如果要经常基于主键列检索表数据，那么Oracle建议使用索引表。1-20•索引表和带索引的标准表是绝对不同的。1-21•索引表的优点：在执行针对主键查询，索引表远远比具有索引的标准表要快的多。•但索引表不宜存放变动太大的数据。开销太大。1-22创建索引表CreatetableIOT1(idnumber(9),namevarchar2(20),addressvarchar2(30),notevarchar2(40),constraintPK_IDprimarykey(ID)validate)organizationindexpctthreshold40includingaddressoverflowtablespaceusers;1-23•执行上述语句后，会建立索引表IOT1，其键列和非键列会存放到主键约束所对应的索引段PK_ID中，而溢出数据则会存放到溢出段SYS_IOT_OVER_n(n:索引表的对象号）中。•定义索引表时，主键约束和ORGANIZATIONINDEX选项时必须指定的。选项的作用如下：•ORGANIZATIONINDEX：用于指定创建一个索引表，•PCTHRESHOLD:用于指定数据块中为键列和部分非键列数据所预留空间的百分比。如果数据块剩余空间低于PCTHRESHOLD设置，Oracle会将其他数据存放到溢出段。•OVERFLOWTABLESPACE指定溢出段所在表空间。•INCLUDING：当有溢出情况时，指定控制哪些非键值列与键值列存储在一起。1-24•INCLUDINGclumn：该选项用来控制哪些非键列要与键列存储在一起。•OVERFLOWTABLESPACE:用于指定溢出段所在的表空间。1-2525通过查询创建表(CTAS方式)•SQLcreatetableemp1asselect*fromempnologging;--在创建大表时，建议带。查询创建表，约束及默认值定义不会被复制。查询创建表，不能带tablespace子句。可以复制表的结构，复制数据。可以复制表中的个别字段。1-2626外部表•Oracle允许以只读方式访问外部表中的数据。•外部表指不是数据库中的表，而是操作系统（简称OS）文件中的表。•使用createtable语句的organizationexternal子句创建外部表。•使用Insert语句将OS文件中的数据装载到数据库中，从而实现SQL*Loader的功能。•最大用途：通过外部表可以把OS文件中的数据装载到Oracle数据库中。1-2727创建外部表•Step0:在C:\准备一个department.txt的文件。•Step1:建立目录对象。connsys/o123assysdba;createdirectorydept_diras'C:\';•Step2:创建外部表（createtable语句）包括两部分，一部分描述表列的数据类型，另外一部分描述OS文件数据与表列对应关系。•Step3:使用外部表。1-28•Createtableext_department•(DeptIdnumber(9),•DeptNochar(2),•Namevarchar2(20),•Addressvarchar2(30)•)•Organizationexternal•(typeORACLE_LOADER•Defaultdirectorydept_dir•Accessparameters•(recordsdelimitedbynewline•Fieldsterminatedby‘,’•Missingfieldvaluesarenull•(DeptId,DeptNo,Name,Address)•)•Location(‘department.txt’));1-29department.txt1-30•外部表ext_department里面已经有数据了，我们就可以把这些数据导入到真正的Oracle的表中。如：insertintodepartmentselect*fromext_department;•可以使用user_external_tables来查看当前用户可以使用的外部表。1-31更改、删除外部表•修改默认目录对象Createdirectorytest_dir1AS‘E:\external1’;Altertabletest_deptdefaultdirectorytest_dir1;•修改文件位置Altertableext_departmentlocation(’new.txt’);•修改访问参数Altertabletest_deptaccessparameters(fieldsterminatedby‘;’);•删除外部表Droptabletest_dept;•删除目录对象Dropdirectorytest_dir;1-323215.5更改表•修改表Altertabletable_nameAddcolumndatatype[,…]|Modify(columndatat