ORACLE数据库开发基础第三章表及索引文档格式.docx

1、table_name 表名column 列名（字段名），ORACLE7 最多254 列，ORACLE8 可达1000 个列。87datatype 列数据类型DEFAULT 当前列的缺省值（常数）Column constraint 列约束Table_constraint 表约束PCTFREE 用于更新（update）的空间百分比（1-99）0 表示在插入时完全填满数据块，缺省为10PCTUSED 为表的每个数据块保留的可用空间的最小百分比. 取值1-99，缺省为40。PCTFREE 和PCTUSED 的组合决定了将插入的数据放入已存在的数据块还是放入一个新的块中。INITRANS 指定一个在每一

2、个数据块中分配的事务入口的初始数1-255，缺省为1，每一个更新块的事务都需要在块中有一个事务入口（大小依OS），一般不需要指此参。MAXTRANS 指定用于更新分配给表的数据块的并发事务的最大数，1-255，用户一般不应改此参。TABLESPACE 表空间。如果缺省则表建在用户缺省的表空间（如果建立用户不指定表空间则该用户的缺省表空间为system）。STORAGE 存储分配参数INITIAL integer 初始大小NEXT integer 下一次的大小MINEXTENTS integer 最小分配次数MAXEXTENTS integer 最大分配次数PCTINCREASE integer

3、 增长百分比（=0）ENABLE 激活完整性约束DISABLE 取消完整性约束As subquery 建表中查出数据给新表，此语句如果使用，则表的数据类型不需指定，而是继承原表的类型。FREELIST GROUP 在并行服务器中指定表或分类、索引组的列表数目。FREEUST 在并行服务器中指定表、簇、索引的列表数。提示1：一般情况下，如果表含有long 字段，这样势必需大量的空间，系统会在每次插入新记录时，经常分配空间给表，不久就会出现：ORA-01547:Failed to allocate extent of size xxxxx intablespace xxxx 此种情况如果表空间还剩

4、较多的连续空间的话。则可能是该表分配的空间次数已达最大值。为了对该表能插入新数据，需对该表的存储参数作修改，比如：SQLalter table xxx storage（MAXEXTENTS 999 ）;提示2:建议不要对表结构或索引使用 pctincrease 大于0 的参数以避免将来在运行中产生空间超支问题.提示3:建立表结构最重要的部分是存储参数（STORAGE）的说明。设置者要特别重视存储参数的估计，设置合理的大小。详细见Oracle8i/9i 初级数据库管理883.1.2 建立表结构例子例 1：在SCOTT 模式下建立表emp,并指定表空间和存储参数：Create table scot

5、t.emp（Empno number（5） primary key,Ename varchar2（15） not null,Job varchar2（10）,Mgr number（5）,Hiredate date default sysdate,Sal number（7,2） CHECK（sal100）,Comm number（3） default 0.0 ,Dept number constraintdept_fkey References scott.dept）Tablespace usersPCTFREE 10PCTUSED 70STORAGEINITIAL 50KNEXT 50kMAX

6、EXTENTS 10）;例2：在建立表过程中对有限制的列使用NOT NULL:CREATE TABLE CHECKUP_HISTORY（CHECKUP_NO NUMBER（10,0） NOT NULL,ID_NO NUMBER（10,0）,CHECKUP_TYPE VARCHAR2（30）,CHECKUP_DATE DATE,DOCTOR_NAME VARCHAR2（50）;本例除了要求CHECKUP_NO 非空外，其它无任何限制.例3：在建立表时指定列CHECKUP_TYPE 为外部列：CREATE TABLE SEAPARK.CHECKUP_HISTORYCHECKUP_NO NUMBER

7、（10） NOT NULL,89DOCTOR_NAME VARCHAR2（50）,FOREIGN KEY （CHECKUP_TYPE） REFERENCESSEAPARK.CHECKUP （CHECKUP_TYPE）,PRIMARY KEY （CHECKUP_NO）PCTFREE 20PCTUSED 60INITRANS 2MAXTRANS 255STORAGE （ INITIAL 1250KNEXT 2KMINEXTENTS 1MAXEXTENTS 121Pctincrease 0）TABLESPACE user_data;例子指定了所有者，主键，外部键,表空间及存储参数等，主键和外部键在后

8、面章节介绍。3.1.3 建立临时表结构Oracle 现在可以使用 CREATE GLOBAL TEMPORARY TABLE 命令来实现建立临时表结构。这样的表它的数据只在用户会话期间存在，当会话完成后就自动清除。看下面例子： create global temporary table myemp as select * from emp;表已创建。 desc myemp名称 空? 类型- - -ENAME VARCHAR2（20）SAL NUMBER（9,2）DEPTNO NUMBER（4）TEL VARCHAR2（20） select * from myemp;未选定行 insert in

9、to myemp values（赵元杰,32456.99,10,12）;90已创建 1 行。ENAME SAL DEPTNO TEL- - - -赵元杰 32456.99 10 12 connect sys/sys已连接。 connect zhao/zhao l1* select * from myemp /从上面可看出当连接到SYS 在连接回来后数据就不存在了。对于临时表，可以用DROP TABLE来删除其结构。如： drop table myemp;表已丢弃。3.3 修改表结构修改表结构是对已经创建完成（实际是存放在数据库字典里）的表的结构进行修改。不同的Oracle 版本允许对表的修改也

10、不一样。新版的Oracle8i 可以对表中的列进行删除。3.3.1 修改表结构命令修改表结构的命令由 ALTER TABLE 来完成。该命令的参数较多，下面仅给出一些基本的部分。详细请参考ORACLE8i SQL REFERENCE 。ALTER TABLE user. tableADD （colum_element|table_constraint,column_element|table_constraint.）MODIFY（column_element,column_element.）DROP CONSTRAINT constraint.91PCTFREE integerPCTUSED

11、integerINITRANS integerMAXTRANS integerSTORAGE storageBACKUPALTER TABLE 可以作的操作有：增加一个列（字段）宽度;减少一个列（字段）宽度（该列必须无数据）;增加一个列（字段）;修改列的定义 ;或一个限制;（如数据类型，NOT NULL）;仅当某列的值为空时才能修改其类型;去掉限制;修改存储分配;记录表已作过 BACKUP;删除已存在的列（仅 Oracle8i 及以后版本）;重新定位和组织表（仅 Oracle8i 及以后版本）;将表标识为不可用（仅 Oracle8i 及以后版本）。3.3.2 修改表结构例子例1：对已经存在的表

12、增加一新的列：alter table dept add （ headcount number（3） ）;对表的列修改其大小：alter table dept modify（ Dname char（20） ）;如果被修改的列没有空（已有数据），则被提示：ORA-01439: Column to be modified must be empty to changedatatypeORA-01441: Column to be modified must be empty to decreasecolumn length复制一个表：CREATE TABLE HOLD_TANK AS SELECT

13、TANK_NO, CHIEF_CARETAKER_NAMEFROM TANK;92例4：参照某个已存在的表建立一个表结构（不需要数据）create table emp2 as select * from emp where rownum=18 and age0 and sal+comm=5000 ）建议：在设计数据库表结构时，建议你分析用户的数据的取值范围，从而将那些取值范围一定的字段用CHECK 进行描述。以保证以后数据的正确性。3.3.5 使用UNRECOVERABLE 创建表对于特殊的需要，可以考虑将表创建成为不需恢复（UNRECOVERABLE）的表。如复制一个已存在的表就可以采用这种

14、方法以减少系统的开销。例：参考 emp 表创建一个新的emp_new 表： create table new_emp as select * from emp UNRECOVERABLE;或CREATE TABLE new_emp AS select * from emp NOLOGGING;注：虽然上面提到 UNRECOVERABLE，但是Oracle 推荐你使用NOLOGGING 或LOGGING;3.3.6 将表移动到新的数据段或新的表空间最新的 Oracle8i 版本可以用Alter table MOVE 语句将表移动到一个新的段或新表空间上，这样可以实现对不合理存储参数进行修改，包括

15、用一般的ALTER TABLE 不能修改的参数。通过移动来实现存储参数的修改：Alter table emp MOVESTORAGE（INITIAL 1m next 512k minextents 1 maxextents 999 pctincrease 0 ）;例 2：将那些使用system 表空间的对象移动到合适的表空间中：1）移动前表所使用的表空间情况：95 select tablespace_name,table_name,initial_extent from user_tables;TABLESPACE_N TABLE_NAME INITIAL_EXTENT- - -SYSTEM

16、ABC 65536SYSTEM BONUS 65536SYSTEM DEPT 65536SYSTEM EMP 65536SYSTEM EMP2 65536SYSTEM EMP3 65536SYSTEM EMP4 65536USERS PAY_LST_DET 1048576SYSTEM PLAN_TABLE 65536SYSTEM SALGRADE 65536USERS UNIT_INF 104857611 rows selected.2）用Alter table . . . MOVE 语句对表进行移动，下面例子对表进行移动并重新指定存储参数。 alter table emp move tabl

17、espace user_data2 storage（initial 128k next 128k minextents 1 pctincrease 0）;Table altered. alter table dept move tablespace user_data alter table BONUS move tablespace user_data3）移动后的表及表空间的情况：- - -USER_DATA BONUS 13107296USER_DATA DEPT 131072USER_DATA EMP 1310723.3.7 手工分配表的存储空间使用 ALTER TABLE 加ALLOC

18、ATE EXTENT 选项来实现分配一个指定的空间。ALTER TABLE empALLOCATE EXTENT （SIZE 5K INSTANCE 4）;3.3.8 标记不使用的列和删除不使用的列前面介绍过，新版的 Oracle8I 可以删除某个列。从 LONG_TAB 表中将LONG_PICS 列删除掉：ALTER TABLE LONG_TAB DROP COLUMN LONG_PICS;可以使用 ALTER TABLE . . . SET UNUSED 语句实现将表中的列设置为不用的状态以达到快速处理的目的。其结果是：1）在显示结果时看不到该列;2）不删除该列的数据（但可以将该列删掉）; select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - -