17.管理表

建立普通表

下面以建立DEPARTMENT表为例,说明使用CREATE TABLE语句建立普通表的方法.

CREATE TABLE department(

Deptno NUMBER(4),dname VARCHAR2(14), loc VARCHAR2(13)

)PCTFREE 20 PCTUSERD 50 TABLESPACE user01

STORAGE(MINEXTENTS 3);

PCTFREE:用于指定数据块为UPDATE操作所预留空间的百分比.当数据块剩余空间低于20%(PCTFREE=20)时,不好在该数据块中继续插入数据,并且这些剩余空间是为UPDATE操作所保留的.合理地设置PCTFREE,可以避免出现行迁移.

PCTUSED:用于指定数据块上可以重新插入数据的已用空间最低百分比.当数据占用空间达到80%后,该数据块将不能插入数据;删除部分数据后,如果数据时间占用空间低于50%(PCTUSED=50),那么可以在该数据块上重新插入数据.

TABLESPACE:用于指定表段所在表空间,如果不指定该选项,oracle会将表段建立在用户的默认表空间上.

STORAGE;用于指定表段的存储参数.其中,MINEXTENTS用于指定表段DEPARTMENT的初始区个数为3,通过查询数据字典视图USER_SEGMENTS,可以显示段所在的表空间,区个数以及尺寸.

SELECT tablespace_name,extents,bytes, FROM user_segments

WHERE segment_name=’DEPARTMENT’;

建立临时表

临时表用于存放会话或事务的私有数据,建立临时表后,其结果会一直存在,但其数据只在当前事务内或当前会话内有效.需要注意,当在临时表上执行DML操作时,既不会加锁,也不会将数据变化写到重做日志中.

1,事务临时表是指数据只在当前事务内有效的临时表.如果建立临时表时没有指定ON COMMIT选项,则默认为事务临时表.通过指定ON COMMIT DELETE ROWS选项,也可以指定事务临时表.下面以建立和使用事务临时表TEMP1为例,说明建立和使用的方法.

CREATE GLOBAL TEMPORARY TABLE temp1(cola INT)

ON COMMIT DELETE ROWS;

事务临时表TEMP1的数据只在当前事务内可以查看;当使用COMMIT或ROLLBACK结束事务后,其临时数据会被自动清除.

2,会话临时表时数据只在当前会话内有效的临时表.建立临时表时,通过使用ON COMMIT PRESERVE ROWS选择.可以指定会话临时表.

修改普通表

当给表增加列,删除列,重新组织表,分配和释放表空间时,需要使用ALTER TABLE修改表.

1,修改表的物理属性.

使用ALTER TABLE改变块空间使用参数PCTFREE和PCTUSED时,新设置对所有块都起作用,但对应已分配块不会立即生效,而使用ALTER TABLE改变事务入口INITRANS时,该设置只会对将来分配的数据库生效.

ALTER TABLE department

PCTFREE 40 PCTUSED 35 INITRANS 3;

2,重新组织表

若在表上频繁地执行DML操作时,会产生空间碎片和行迁移.当使用ALTER TABLE重新组织表时,还可以使用TABLESPACE选项将表移动到其他表空间.

ALTER TABLE department MOVE TABLESPACE user02;

执行ALTER TABLE命令重新组织表时,因为ROWID会发生改变,从而导致表的所有索引转变为无效状态,所有在重新组织表之后必须重新建立索引.

3,手工分配和释放空间

扩展表空间

ALTER TABLE department ALLOCATE EXTENT

(SIZE 500K DATAFILE ‘g:demouser02.dbf’);

如果表段实际占用空间多于所需空间,那么可以释放所占用的剩余空间.默认情况下释放剩余表空间后表段的区个数不会低于MINEXTENTS,而如果要释放MINEXTENTS下的剩余空间,需要带有KEEP 0 选项.

4,修改列定义

使用ALTER TABLE … MODIFY命令可以修改列定义,包括列的数据类型,列的长度和列的默认值.

ALTER TABLE department MODIFY loc VARCHAR2(15) DEFAULT ‘BEIJING’;

需要注意,当缩减CHAR类型列的长度时,如果该表已经包含数据,就必须将初始化参数blane trimming设置为TRUE

5增加列

使用ALTER TABLE … ADD 命令可以增加列,增加列时需要指定列名及其数据类型,另外还可以指定默认值和NOT NULL约束.

ALTER TABLE department ADD remark VARCHAR2(200) DEFAULT ‘GOOD’;

需要注意,如果表采用压缩选项(COMPRESS),在增加表列时将不能为其指定默认值.

6,修改列名.

使用ALTER TABLE … RENAME COLUMN 可以修改表列的名称,但修改列名会使视图,过程等相关对象转变为无效状态.

ALTER TABLE department RENAME COLUMN loc TO location;

7,删除列

使用ALTER TABLE …. DROP COLUMN 可以删除一列.使用ALTER TABLE … DROP 选项可以删除多列.

ALTER TABLE emp DROP COLUMN comm.;

ALTER TABLE department DROP (phone,manager);

8,使用UNUSED选项删除列

删除表列时,如果该表包含大量数据,删除列的时间会很长.在数据库运行的高峰阶段,删除表列会明显地影响性能.在这种情况下,如果确定某些表列不再需要,应该首先将表列标记为UNUSED,然后在数据库空闲阶段删除表列.

使用ALTER TABLE … SET UNUSED COLUMN选项可以将列标记为UNUSED列,

使用ALTER TABLE…. DROP UNUSED COLUMN可以删除UNUSED列.

ALTER TABLE emp SET UNUSED COLUMN comm.;

ALTER TABLE emp DROP UNUSED COLUMN CHECKPOINT 1000;

删除表列时,CHECKPOINT 1000用于指定每删除1000行发出一次检查点,以节省UNDO段的空间使用.如果在删除表列的过程中出现例程失败,那么在重新启动数据库之后使用CONTINUE选项可以继续删除操作.

ALTER TABLE emp DROP COLUMNS CONTINUE CKECKPOINT 1000;

管理索引表

索引表以B-树结构来组织表的数据,它是主键B-树索引的变种.对于普通表而言,其数据以无序方式存储;而对于索引表来说,其数据以B-树结构来组织,并且其叶块既包含键列数据,也包含非键列数据.一般情况下,表及其索引数据分别存放在表段和索引段中.当在WHERE子句中引用索引列时,首先定位索引数据并取得ROWID,然后根据ROWID取得表的数据.如果要经常基于主键列检索表数据,那么ORACLE建议使用索引表,建立索引表时,oracle会将表及其主键索引的数据一起存放到索引段中.当在WHERE子句中引用主键列时,oracle可以根据主键索引值阻断表行数据.

建立索引表是使用CREATE TABLE语句完成的,需要注意,建立索引表是,必须指定ORGANIZATION INDEX关键字,并且必须定义主键约束.下面以建立索引表SALES_INFO为例,说明建立索引表的方法

CREATE TABLE sales_info(

Id NUMBER(6) CONSTRAINT pk_sale PRIMARY KEY,

Customer_name VARCHAR2(30),sales_amount UNMBER(10,2),

Sales_date DATE,remark VARCHAR2(2000)

) ORGANIZATION INDEX TABLESPACE user01

PCTTHRESHOLD 20 INCLUDING remark

OVERFLOW TABLESPACE user02;

执行上述语句后,会建立索引表SALES_INFO,其键列和非键列的数据会放到主键约束所对应的索引段PK_SALE中,而溢出数据则会存放到溢出段SYS_IOT_OVER_n(n:索引表的对象号)中,定义索引表时,主键约束和ORGANIZATION INDEX选项时必须指定的,而PCTTHRESHOLD,INCLUDING和OVERFLOW TABLESPACE 选项既可以指定,也可以不指定.

ORGANIZATION INDEX:用于指定索引表,而TABLESPACE则用于指定主键索引所在的表空间.

PCTTHRESHOLD:用于指定数据块中为键列和部分非建列数据所预留空间的百分比

INCLUDING column:该选项用于指定数据被存放到溢出段所在的表空间!

修改索引表

修改索引表是使用ALTER TABLE命令完成的.与修改普通表一样,所有修改选项(ADD,MODIFY,DROP COLUMN, DROP CONSTRAINT)都可以在说要表上使用.需要注意,索引表的主键约束不能被删除,延期和禁止.

1,移动索引表

ALTER TABLE sales_info MOVE TABLESPACE user01;

2,增加溢出段

ALTER TABLE iot1 ADD OVERFLOW TABLESPACE user02;

3,修改其他选项

当修改索引表时,OVERFLOW选项之前的所有选项只使用于索引段,而OVERFLOW之后的选项只使用于溢出段.

ALTER TABLE sales_info

INITRANS 4 PCTTHRESHOLD 15 INCLUDING remark

OVERFLOW INITRANS 6;

4,转换索引表为普通表.

建立索引表后,使用CREATE TABLE AS SELECT 语法可以将其转变为普通表.

CREATE TABLE sales_info_new AS SELECT * FROM sales_info;

管理外部表

外部表是表结构被存放在数据字典,而表数据被存放在OS文件中的表.通过使用外部表,不仅可以在数据库中查询OS文件的数据,还可以使用INSERT方式将OS文件数据装载到数据库中,从而实现SQL*Loader所提供的功能,建立外部表后,可以查询外部表的数据,在外部表上执行连接查询,或对外部表进行排序.需要注意,在外部表上不能执行DML修改,也不能在外部表上建立索引.

(具体省略)

截断和删除表

1,截断表

当表结构必须保留,而表数据不再需要时,可以使用TRUNCATE TABLE命令截断表.执行改命令时,会删除表的所有数据,并释放表所占用的空间,但会保留表的结构

TRUNCATE TABLE emp;

2,删除表

当表不再需要时,可以使用DROP TABLE命令删除表.执行DROP TABLE时,不仅会删除表的所有数据,也会删除表结构.

DROP TABLE department CASCADE CONSTRAINTS;

CASCADE CONSTRAINTS用于级联删除,如果被删除表与其他表具有主从关系,那么删除主表时必须带有该选项.

显示表信息

1,显示特定用户的表

DAT_TABLES 可以显示所有数据库表的详细信息

ALL_TABLES 可以显示用户可以访问的所有表信息

USER_TABLES 可以显示当前用户所有表的信息

SELECT table_name,num_rows,pct_free,blocks,chain_cnt

FROM dba_tables WHERE owner=’SCOTT’;

Table_name用于标识表名,num_rows用于标识表所包含的行数.pct_free用于标识PCTFREE的值,blocks用于标识表数据已经占用的块个数,chain_cnt用于标识链行的个数,owner用于标识对象所有者.

2,显示列信息.

DBA_TAB_COLUMNS,可以显示数据库任何表的列信息.

ALL_TAB_COLUMNS,可以显示用户可访问的列信息

USER_TAB_COLUMNS,可以显示当前用户所拥有表的列信息.

3,显示表的注释信息

DBA_TAB_COMMENTS,显示数据库任何表或视图的注释;

ALL_TAB_COMMENTS,显示用户可访问表的注释

USER_TAB_COMMENTS,显示当前用户所拥有表的注释.

4,显示列的注释信息

DBA_COL_COMMENTS,

ALL_COL_COMMENTS,

USER_COL_COMMENTS.

5,显示UNUSED列信息

DBA_UNUSED_COL_TABS,可以显示任何表的UNUSED列个数

ALL_UNUSED_COL_TABS,可以显示用户可访问表的UNUSED列个数

USER_UNUSED_COL_TABS,显示当前用户所拥有表的UNUSED列个数.

6,显示外部表的信息

DBA_EXTERNAL_TABLES,显示数据库所有外部表的信息.

ALL_EXTERNAL_TABLES,显示用户可访问的所有外部表的信息.

USER_EXTERNAL_TABLES,可以显示当前用户所拥有外部表的信息

7,显示外部表的位置

DBA_EXTERNAL_LOCATIONS,显示数据库所有外部表的位置.

ALL_EXTERNAL_LOCATIONS

USER_EXTERNAL_LOCATIONS

8,显示表段信息

DBA_SEGMENTS,可以显示数据库所有段的详细信息.

USER_SEGMENTS,可以显示当前用户段的信息

9显示行所在的实际位置

执行INSERT操作时,oracle会将数据插入到表段的相应数据块中,并且oracle会生成唯一的ROWID对应于该行数据.因为ROWID是页码格式存放的,所有用于不能直接读懂其内容.通过使用DBMS_ROWID包,可以将ROWID转变成行所在文件号,块号以及行号.