我的Oracle 9i学习日志（15）-- 表的管理

最新推荐文章于 2024-10-30 11:02:01 发布

weixin_33875839

最新推荐文章于 2024-10-30 11:02:01 发布

阅读量61

点赞数

文章标签：数据库运维操作系统

原文链接：http://blog.51cto.com/luotaoyang/291181

版权

几种存储用户数据的方法：Regular table（table）、partitioned table、Index-organized table、clustered tables。

Regular tables通常称为表或堆表，管理员很难控制表中行的存储位置。插入行时，只要表中有合适的空间就将行放置在哪。

Clustered table：将一些相关的表通过一个cluster key关联在一起，在物理上储存在一块。这些表物理上可能共享一列，但逻辑上是分开的。

Oracle内置数据类型：

标量数据类型：

字符数据：字符数据可以作为长度固定或长度可变的字符串存储在数据库中。长度固定的字符数据类型（如CHAR 和NCHAR）存储时带有填补空格。NCHAR 是“全球化支持” 包含的一种数据类型，既可以存储宽度固定字符集，也可以存储宽度可变字符集。其最大大小取决于存储一个字符所需要的字节数，上限为每行2,000 个字节。缺省值为1 个字符或1 个字节，具体取决于字符集。

长度可变的字符数据类型仅使用存储实际列值所需要的字节数，并且每行的大小可以不同，最大可达4,000 字节。VARCHAR2 和NVARCHAR2 就是长度可变的字符数据类型的例子。

Varchar与varchar2的区别：

数字数据类型：Oracle 数据库中的数字始终以长度可变的数据存储。最多可以存储38 个有效数位。数字数据类型需要：

• 指数用1 个字节

• 尾数中的每两个有效数位用1 个字节

• 负数用1 个字节（如果有效数位少于38 个字节）

DATE 数据类型：Oracle 服务器将日期存储在包含七个字节的固定长度字段中。Oracle DATE 始终包括时间。

TIMESTAMP 数据类型：此数据类型存储日期和时间，包括零点几秒，最高可达9 位小数。

TIMESTAMP WITH TIME ZONE 和TIMESTAMP WITH LOCAL TIME ZONE 可以使用时区设定时间，如夏时制。TIMESTAMP 和TIMESTAMP WITH LOCAL TIME ZONE 可用于主键，而TIMESTAMP WITH TIME ZONE 则不能。

RAW 数据类型：可以使用此数据类型存储小型二进制数据。在网络中的计算机之间传输RAW 数据时，或者使用Oracle 实用程序将RAW 数据从一个数据库移到另一个数据库时，Oracle 服务器不执行字符集转换。存储实际列值所需要的字节数大小随每行大小而异，最多为2,000 字节。

LONG、LONG RAW 和大型对象(LOB) 数据类型：

Oracle 为存储LOB 提供六种数据类型：

• CLOB 和LONG 用于存储大型的、宽度固定的字符数据

• NCLOB 用于存储大型的、宽度固定国家字符集数据

• BLOB 和LONG RAW 用于存储非结构化数据

• BFILE 用于存储操作系统文件中的非结构化数据

LONG 和LONG RAW 数据类型以前用于非结构化数据，如二进制图像、文档或地理信息，

目前主要用于向后兼容。这两种数据类型已由LOB 数据类型代替。LOB 数据类型与LONG和LONG RAW 不同，不能互换。LOB 不支持LONG 应用程序编程接口(API)，反之亦然。最好与旧的数据类型（LONG 和LONG RAW）相比较来讨论LOB 功能。在下文中，LONG指LONG 和LONG RAW 数据类型，而LOB 指所有LOB 数据类型。除非其大小小于VARCHAR2 数据类型的最大大小（4,000 字节），否则，LOB 在表中存储一个定位器，而将数据存储在另一位置；LONG 则将所有数据存为一行。此外，LOB 允许将数据存储在单独的段和表空间中，或者存储在主机文件中。LOB 支持对象类型属性（NCLOB 除外）和复制；而LONG 不支持。LONG 主要存储为一连串的行片段，每一块中有一个行片段指向存储在另一块中的下一行片段。因此，需要按顺序访问这些行片段。相反，LOB 通过类似文件的接口支持以片段方式随机访问数据。

ROWID 和UROWID 数据类型：

ROWID 是一种可以和表中其它列一起查询的数据类型。它具有以下特征：

• ROWID 是数据库中每行的唯一标识符。

• ROWID 并不显式地作为一个列值存储。

• 虽然ROWID 并不直接给出一行的物理地址，但它可以用来定位行。

• ROWID 为访问表中的行提供了最快的方法。

• ROWID 存储在索引中来指定具有一组给定的键值的行。

在Oracle8.1 版中，Oracle 服务器提供一种称为通用ROWID 或UROWID 的数据类型。它支持外表（非Oracle 表）的ROWID，并且可存储各种类型的ROWID。例如：要存储按索引组织的表(IOT) 中存储的行的ROWID，必须使用UROWID 数据类型。要使用UROWID，参数COMPATIBLE 的值必须设置为Oracle8.1 或更高。

集合数据类型：

有两种集合数据类型可用来为表中的一个给定行存储重复的数据。在Oracle8i 以前，定义和使用集合需要“对象” (Objects) 选项。下面简要论述这些类型。

变化数组(VARRAY)：变化数组对于存储包含少量组成元素的列表（如客户的电话号码）非常有用。

VARRAY 具有以下特征：

• 数组即一组有序的数据组成元素。

• 一个给定数组的所有组成元素的数据类型相同。

• 每个组成元素都有索引，即与数组中组成元素的位置相对应的编号。

• 数组中组成元素的数目决定了数组的大小。

• Oracle 服务器允许数组的大小可以变化，这就是它们被称为VARRAY（意为变化数组）的原因，但在声明数组类型时必须指定最大大小。

嵌套表：嵌套表提供一种将一个表定义为另一个表内一列的方法。嵌套表可用来存储可能

包含大量记录的集合（比如一个订单中的若干条目）。

嵌套表一般具有以下特征：

• 嵌套表是一组无次序的记录或行。

• 嵌套表中的各行结构相同。

• 嵌套表中的行与父表分别存储，并且父表中的对应行有一个指针。

• 嵌套表的存储特点可由数据库管理员来定义。

• 嵌套表没有预先确定的最大大小。

关系数据类型(REF)：

关系类型在数据库内用作指针。使用这些类型需要“对象” (Objects) 选项。这里给出一个例子：订购的每一项都可以指向或引用PRODUCTS 表中的一行，而不必存储产品代码。

Oracle 用户定义的数据类型：

Oracle 服务器允许用户定义抽象的数据类型并在应用程序内使用这些数据类型。

RoweID 格式：

Rowid是每一个表都有的一列，在select *查询时隐藏了。

SQL> select rowid, id, name from test;

ROWID ID NAME

------------------ ---------- --------------------

AAAHw5AANAAAAAKAAA 0 hello

AAAHw5AANAAAAAKAAB 1 world

AAAHw5AANAAAAAKAAC 3 bbb

AAAHw5AANAAAAAKAAD 2 aaa

AAAHw5AANAAAAAKAAE 3 bbb

Rowie总共占10bytes存储空间，采用base64编码后显示出来的是18个字符，包括以下组件：

•Data object number:指示每个数据对象，如：表或索引，当这些对象创建时唯一的标示每个对象。

•Relative file number：唯一的确定表空间里的每个数据文件。

•Block number：指示某一行所在的块的位置。

•Row number：标识每行，在块头中有一个行目录slot与之对应。

其中object number 占32bits显示出6个字符， Relative file number占10bits显示出3个字符， Block number 占22bits显示出6个字符，Row number占16bits显示出3个字符。

行结构：

行是以可变长数据记录存储的，通常存储行是按定义列时的顺序存储的，并且最后一列如果是null则不存储。对于null列如果不是最后一列则须分配1byte的空间作为column length。

Row header：记录行内的列数，链接信息和锁状态。

Row data：对于每一列第一个byte存储列长度（如果列长度超过250bytes则用三个bytes存储长度），紧跟着是列值。行与行之间没有空闲空间。在表头上有一个row目录，其中（的一个slot）指出了每一行的开始。

创建表与改变表：

创建表举例：

SQL> CREATE TABLE hr.departments(

2 department_id NUMBER(4),

3 department_name VARCHAR2(30),

4 manager_id NUMBER(6),

5 location_id NUMBER(4))

6 STORAGE(INITIAL 200K NEXT 200K

7 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

8 TABLESPACE data;

修改表参数举例：

ALTER TABLE hr.employees

PCTFREE 30

PCTUSED 50

STORAGE(NEXT 500K

MINEXTENTS 2

MAXEXTENTS 100);

Initial：初始化大小200k。

Next：下一次分配空间200k。

注意：

以上两个值要跟 tablespace 的相关参数要对应，如：创建 tablespace 时指定了 extent management local uniform size 128k ，则这时创建表时指定的分配空间原则会失效。

Pctincrease：下次分配空间比上次分配的大小增长百分比，例如：pctincrease 10，则第三次分配空间为220k，第四次为242k… …

其他参数：

注意：

• 表的 INITIAL 值不能修改。

• 指定的 NEXT 的值将舍入为块大小的一个倍数，该值大于或等于指定的值。

说明：

创建表空间原则：

-将各个表分别置于不同表空间。

-使用locally-managed表空间避免碎片（local管理方式会自动将碎片整理）。

-使用小的标准extent大小以减少碎片。

临时表：

临时表用来保存仅在事务处理或会话期间存在的会话专用数据。

DML locks are not acquired on the data.

可以在临时表上创建索引，视图和触发器。

创建临时表：

CREATE GLOBAL TEMPORARY TABLE hr.employees_temp [on commit delete|preserve rows] AS SELECT * FROM hr.employees;

• ON COMMIT DELETE ROWS：事务级别

• ON COMMIT PRESERVE ROWS：会话级别

实验：

SQL> select * from test;

ID NAME

---------- --------------------

0 hello

1 world

3 bbb

2 aaa

3 bbb

SQL> create global temporary table sess_temp on commit preserve rows

2 as select * from test;

表已创建。

# 如果只是想拷贝表结构而不要数据，则加上 where 1=0.

SQL> select * from sess_temp;

ID NAME

---------- --------------------

0 hello

1 world

3 bbb

2 aaa

3 bbb

SQL> create global temporary table tran_temp on commit delete rows

2 as select * from test;

表已创建。

SQL> select * from tran_temp;

未选定行

SQL> insert into tran_temp select * from test;

已创建5行。

SQL> insert into sess_temp select * from test;

已创建5行。

SQL> select count(*) from sess_temp;

COUNT(*)

----------

SQL> select count(*) from tran_temp;

COUNT(*)

----------

SQL> commit;

提交完成。

SQL> select count(*) from tran_temp;

COUNT(*)

----------

SQL> select count(*) from sess_temp;

COUNT(*)

----------

SQL> quit

重新登录

SQL> select count(*) from tran_temp;

COUNT(*)

----------

设置pctfree 和pctused

行的迁移和链接

迁移：

链接：

当没有任何一个block能容纳下行时，行被分为几小段，每一段称为row pieces。Row piece会带着一个检索和组合其他piece的指针。

迁移和链接会增加I/O操作，降低性能。所以应该尽量避免。可以将更新较频繁或原来初始值是null以后会更新值的表存储的segment的pctfree增大。为了减少行链接的发生，应增大块大小或将表拆分成小表。

手动分配存储空间：

ALTER TABLE [schema.]table

ALLOCATE EXTENT(SIZE 500K

DATAFILE ‘/DISK3/DATA01.DBF’);

一般用在这两种情况：

• 控制一个表的区在文件之间的分配

• 在大量加载数据前避免表的动态扩展

如果没指定size这个参数，则使用next_extent(从dba_tables视图中可以查到此参数)指定大小。Datafile必须是属于表所在的表空间。这条命令不影响next_extent这个值。

重组织非分区表

ALTER TABLE hr.employees MOVE TABLESPACE data1;

用于将表移动倒不同的表空间或重新组织extent。这样可以不必使用导出导入工具，另外，允许重新设置存储参数。还可以消除行迁移。还有一种方法是创建和旧表一样的新表，然后删除旧表。Create table table.new as select * from table.old。

移动表后必须重建索引，否则会发生错误。

Trunncate

TRUNCATE TABLE hr.employees;

TRUNCATE TABLE [schema.] table [{DROP | REUSE} STORAGE]

删除表中所有行，并且释放已用空间，但同时索引也被truncate。

执行这条语句不会产生undo，因为truncate是DDL命令，这条语句是隐式提交的。

如果其他表有应用这个表的外键，则这个表不能被truncate。

命令执行时这个表的删除触发器不会被触发。

Drop

DROP TABLE [schema.] table [CASCADE CONSTRAINTS]

删除一个表后，该表所使用的区将得以释放。如果这些区是相邻的，则可以在以后某个时间自动或手动将它们合并。

如果该表是外键关系中的父表，就必须使用CASCADE CONSTRAINTS 选项

Drop 列：

可以使用Oracle 服务器从表的行中删除列。删除列可清除未使用但可能占用大量空间的列，而不必导出或导入数据及重新创建索引和约束。删除一列可能要用相当长的时间，因为该列的所有数据都将从表中删除。

在Oracle8i 以前的发行版中，无法删除表中的列。

删除列时使用检查点：

ALTER TABLE hr.employees DROP COLUMN comments CASCADE CONSTRAINTS CHECKPOINT 1000;

删除列可能需要很长时间，并且且需要大量的还原空间。从大型表中删除列时，可以指定检查点来尽量减少还原空间的使用。在上例中，每1,000 行出现一个检查点。在操作运行完成前，该表一直被标记为INVALID。如果操作过程中例程失败，则该表在启动后仍将处于INVALID 状态，因此该操作必须完成。使用下面的语句可恢复中断的删除操作：

SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

如果表处于VALID 状态，则使用此语句将生成错误。

增加一列：SQL> alter table t add num integer;

改变一列的字段类型：SQL> alter table t modify name varchar2(10);

重命名列：

ALTER TABLE hr.employees RENAME COLUMN hire_date TO start_date;

在 Oracle 9i Database Release 2 后的版本中提供了为关系表重命名列的功能，但不能重命名带 index 的表。如果要重命名这种表必须先删除索引。列被重命名后，功能性索引和 check 约束依然有效，但在表上的视图，触发器， domain indexes ，函数， procedures 和 packages 将失效。

Renaming is allowed for tables with materialized views and tables involved in replication. If errors in the materialized views occur subsequently, you must modify the materialized views to work out the issues. The syntax is:

SQL> ALTER TABLE [schema.]table_name

2 RENAME COLUMN old_column_name

3 TO new_column_name;

使用UNUSED 选项

• 将列标记为未使用：

ALTER TABLE hr.employees SET UNUSED COLUMN comments CASCADE CONSTRAINTS;

• 删除未使用的列：

ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;

• 继续执行删除列操作：

ALTER TABLE hr.employees DROP COLUMNS CONTINUE CHECKPOINT 1000;

除将列从表中删除以外，还可以先将列标记为“未使用”，以后再删除。因为没有删除数据，所以此操作不回收磁盘空间，因而具有速度比较快的优点。被标为“未使用” 的列可在以后系统活动较少时从表中删除。未使用的列就像不属于表一样。查询时看不到未使用列中的数据。此外，在执行DESCRIBE 命令时，也不会显示这些列的名称和数据类型。用户可以添加与未使用的列同名的新列。如果想删除同一表中的两列，则可先将列设置为“未使用” 然后再删除。在删除两列时，表中的所有行都会更新两次；但如果将这些列设置为“未使用” 然后再删除，则所有的行仅更新一次。

确定包含未使用列的表要确定包含未使用列的表，可以查询视图DBA_UNUSED_COL_TABS。该查询可获取包含未使用列的表的名称及表中标记为未使用列的数目。下面的查询显示HR 拥有的表EMPLOYEES 含有一个未使用的列：

SQL > SELECT * FROM dba_unused_col_tabs;

OWNER TABLE_NAME COUNT

----- -------------- ------

HR EMPLOYEES 1

要确定已完成一部分DROP COLUMN 操作的表，可查询DBA_PARTIAL_DROP_TABS视图。

SQL > SELECT * FROM dba_partial_drop_tabs;

OWNER TABLE_NAME COUNT

----- -------------- ------

no rows selected

删除列的限制

不能执行下列操作：

• 从对象类型表中删除列

• 从嵌套表中删除列

• 删除一个表中的所有列

• 删除分区键列

• 从SYS 拥有的表中删除列

• 从按索引组织的表中删除主键列

• 如果有未使用但未删除的LONG 或LONG RAW 列，将无法向表中添加LONG 或LONG RAW 列。（即使表的说明显示没有LONG 或LONG RAW 列也是如此。）

实验：

SQL> desc mm

名称是否为空? 类型

----------------------------------------- -------- ----------------------

NUM NUMBER(38)

NAME CHAR(10)

STORE CHAR(20)

SQL> alter table mm set unused column store cascade constraints;

表已更改。

SQL> desc mm

名称是否为空? 类型

----------------------------------------- -------- ----------------------

NUM NUMBER(38)

NAME CHAR(10)

SQL> select * from user_unused_col_tabs;

TABLE_NAME COUNT

------------------------------------------------------------ ----------

MM 1

SQL> alter table mm drop unused columns checkpoint 1000;

表已更改。

SQL> select * from user_unused_col_tabs;

未选定行

SQL> desc mm

名称是否为空? 类型

----------------------------------------- -------- ----------------------

NUM NUMBER(38)

NAME CHAR(10)

获取信息：

DBA_TABLES

DBA_OBJECTS

1 Create the following tables as user SYSTEM for an order entry system that you are implementing now. The tables and the columns are shown below.

Note: When using OEM be sure to set DATE_OF_DELY to NULL.

In addition, you have been informed that in the table ORDERS, rows will be inserted without a value for DATE_OF_DELY, and it will be updated when the order is fulfilled. Use tablespace USERS. You can use the default storage settings.

2 Run the lab11_02.sql script to insert rows into the tables.

3 Find which files and blocks contain the rows for the ORDERS table.

Hint: Query the DBA_EXTENTS data dictionary view.

解析：

SQL> select segment_name,file_id,blocks,block_id from dba_extents

2 where segment_name='ORDERS' and owner='SYSTEM';

SEGMENT_NAME FILE_ID BLOCKS BLOCK_ID

-------------------- ---------- ---------- ----------

ORDERS 1 8 47537

SQL> SELECT FILE_NAME,TABLESPACE_NAME FROM DBA_DATA_FILES

2 WHERE FILE_ID=1;

FILE_NAME TABLESPACE_NAME

----------------------------------------- -------------------------------------

/u01/oradata/lty/system01.dbf SYSTEM

4 Check the number of extents used by the ORDERS table.

5 Allocate an extent manually, with default size, for the ORDERS table and confirm that the extent has been added as specified.

解析：

SQL> alter table orders allocate extent(datafile '/u01/oradata/lty/system01.dbf');

Table altered.

SQL> select segment_name,file_id,blocks,block_id from dba_extents

2 where segment_name='ORDERS' and owner='SYSTEM';

SEGMENT_NAME FILE_ID BLOCKS BLOCK_ID

-------------------- ---------- ---------- ----------

ORDERS 1 8 47537

ORDERS 1 8 47545

6 Create another table, ORDERS2 as a copy of the ORDERS table in the USERS tablespace, with MINEXTENTS equal to 10. Verify that the table has been created with the specified number of extents.

7 Truncate the ORDERS table without releasing space and check the number of extents to verify that extents have not been deallocated.

8 Truncate the ORDERS2 table, releasing space. How many extents does the table have now?

解析：

SQL> truncate table orders reuse storage;

Table truncated.

SQL> select segment_name,file_id,blocks,block_id from dba_extents

2 where segment_name='ORDERS' and owner='SYSTEM';

SEGMENT_NAME FILE_ID BLOCKS BLOCK_ID

-------------------- ---------- ---------- ----------

ORDERS 1 8 47537

ORDERS 1 8 47545

SQL> truncate table orders ;