oracle 表（下）

　　

　　需要说明一点的是，此处讲表，更多的是一些概念与管理，至于创建的参数细节更多与业务有关。

 

 

创建表                                                                                  

　　创建一个表，create table 命令说简单将非常简单，说复杂巨复杂，在实际的生产中，并不像我们前面创建一个表那简单的指定两个个字段就OK了。而且我们前面介绍的那么多种表的类型也是这一条命令稿定的。

创建自动管理与手动管理两个表空间：

创建个一个自动管理的表空间：
SQL> create tablespace assm datafile
  2  '/ora10/product/oradata/ora10/assm_1.dbf' size 100M
  3  extent management local uniform size 128k segment space management auto ;

创建一个手动管理的表空间：
SQL> create tablespace mssm datafile
  2  '/ora10/product/oradata/ora10/mssm_1.dbf' size 100M
  3  extent management local uniform size 128k segment space management manual ;

查看创建的表空间：
SQL> !ls -l /ora10/product/oradata/ora10
总计 1189996
-rw-r----- 1 ora10  dba  104865792  09-24 21:54  assm_1.dbf
-rw-r----- 1 ora10  dba  104865792  09-24 22:01  mssm_1.dbf
.............................

 

创建两个用户：

用户名密码都为：as1 
SQL> create user as1 identified by as1 default tablespace assm;

User created.

用户名密码都为：ms1 
SQL> create user ms1 identified by ms1 default tablespace mssm;

User created.

为两个用户赋予权限：
SQL> grant connect,resource to as1;

Grant succeeded.

SQL> grant connect,resource to ms1;

Grant succeeded.

 

用创建的用户登录：

登录：
SQL> conn as1/as1
Connected.
查看当前用户：
SQL> show user
USER is "AS1"
create table tt(id int,name char(10)) storage(initial 128k next 128k pctincrease 0 minextents 1 maxextents 5) tablespace assm;

 

创建一个表：

创建一个表，加一些参数限定：
SQL> create table tt(id int,name char(10))
  2  storage(initial 128k next 128k pctincrease 0 minextents 1 maxextents 5) tablespace assm;

SQL> select segment_name,segment_type from user_segments;

SEGMENT_NAME                     SEGMENT_TYPE
--------------------------------------------------------------------------------
TT                                     TABLE

添加这些参数的指定值会影响数据库的性能。

Initial  当前的extents 大小为128KB

Next   随着表数据的增加会申请新extents ，申请的下一个extents 的大小。

Pctincrease   增长的百分比，这个参数一般默认情况下为0 。

Minextents    表示这个表最少只能有1个extents 

Maxextents   表示最多可以有5个extents 

tablespace assm   指定当前表所属的表空间为 assm ，其实默认不指定也是assm表空间，因为当前的用户权限范围已经指定为assm表空间。

　　其实，对于assm（自动管理）表空间来说，修改上面的参数是无效的，。但对于mssm（手动管理）的表空间就会起作用，在非常了解数据库的情况下，调整参数会提高数据库的性能。

创建表的原则 

 * 把表放在不同的表空间里

 * 减少磁盘空间碎片（定期整理碎片，不同对象整理方式也不同） 

 *  尽量少使用几种标准的extents 盘区，用于减少磁盘空间。

 *  使用临时表

 

临时表                                                                           

 

　　什么是临时表，用户做一个操作查询出几百几千条数据，我们可以把数据放在内存中。当有很多用户都这样做，内存空间不足，这个时候就需要把数据保存在磁盘上。对于oracle就提供了一种临时表用于存放这些数据。

创建临时表：

创建
SQL> create global temporary table hr.employees_temp ;
查看
SQL>select * from hr.employees_temp ;

也可以把上面两句合并
SQL> create global temporary table hr.employees_temp as select * from  hr.employees_temp ;

一个session 可以包含多个事务。那么临时表的生命周期只作用于一个事务或一个session (会话)。 事务临时表与会话临时表级别不同，也会有一些差异。

对于临时表不用加DML锁，相对速度会比普通表快。

临时表你可以创建索引、视图和触发器。

这里我们需要理解：当我们创建一个普通的表时，系统会给我们分配存储空间，我们创建临时表的时候不会分配空间，更像是创建的一种规则，当用户使用时会生成临时表，他们放不同的临时表空间中 ，所以每个用户的临时表只有自己可看到。

临时表也会产生undo 和redo信息，但它产生的redo信息要远远小于普通表。

 

下面演示创建一个事务级别与会话级别的两个临时表，（这个演示有点啰嗦，但完全可以根据下面的步骤，操作验证。）

SQL> conn as1/as1  登录用户
Connected.

SQL> desc tt;  查看tt表结构
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               CHAR(10)

创建sessione级别的临时表
SQL> create global temporary table tmp_session on commit preserve rows as select * from tt where 1=0;
Table created.

on commit preserve rows 表示创建的表是session 级别，只要用户不退出，临时表一直有效。
select * from tt where 1=0;  将tt表的结构拷贝过来使用

创建事务级别的临时表

SQL> create global temporary table tmp_transaction on commit delete rows as select * from tt where 1=0; Table created.

on commit delete rows 表示创建的表是事务级别，这个临时表的作用范围只在一个事务内有效。

 

验证两种临时表的作用范围：

SQL> select * from tt;
no rows selected  //查看tt表为空

向表中插入两条数据：
SQL> insert into tt values(0,'chongshi');
1 row created.
SQL> insert into tt values(1,'chongshi');
1 row created.   

SQL> commit;  //提交事务
Commit complete.

SQL> select * from tt;   //查看表中插入的数据
        ID NAME
---------- --------------------
         0 chongshi
         1 chongshi
----------------------------------------------------------------------------------------------------------------------------------------

//把tt表信息放到session级别临时表中
SQL> insert into tmp_session select * from tt;
2 rows created.

//把tt表信息放到事务级别临时表中
SQL> insert into tmp_transaction select * from tt;
2 rows created.

//查看两张表信息：
SQL> select count(*) from tmp_session;
  COUNT(*)
----------
         2
SQL> select count(*) from tmp_transaction;
  COUNT(*)
----------
         2           //我们可以看到session临时表与事务临时表都是有数据的。


SQL> commit;      //提交事务
Commit complete.


//再来查看两张临时表信息：
SQL> select count(*) from tmp_session;

  COUNT(*)
----------
         2     //session级别的临时表依然有数据
SQL> select count(*) from tmp_transaction;

  COUNT(*)
----------
         0    //事务级别的临时表数据消失了。


SQL> quit    退出
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[ora10@localhost /]$ sqlplus as1/as1   重新登录

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 6 12:20:08 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from tmp_session;   

  COUNT(*)
----------
         0     再来查看session 级别的临时表也变为空了。

 

 

 

行迁移与链接                                                                       

Row Migration

　　在前面的学习中有介绍到数据块的概念，块中存放着一条一条的数据，每个数据块都会预留的有剩余的空间用一条数据的扩展。

　　当剩余的空间不够一条数据的扩展时，那么这条数据就需要迁移，迁移到可存放这条数据的数据块中。那么怎样找到新数据的位置呢，就需要在原来存放数据的位置放一个指针，用于指向新的数据。迁移数据带来的后果就是oracle的性能下降。

Row Chaining

　　如果一条记录的某列内容非常长了，任何一个块都放不下。Oracle会把他们分成不同的部分，每一部分被称为行片（row pieces）。每一个块中都会有指针帮助oracle组装成一条完整的记录。造成row chaining的根本原因是因为数据块设置的太小了。Row chaining同样会造成oracle的性能下降。

　　如果减少row chaining一种方法是可以甚至增加块的大小，加一个种方法是将表拆分成多个小表。

 

 

表操作                                                                               

 

修改表的参数

SQL> alter table hr.employees Ppctfree 30   pctused  50  Storage(next 500k minextents 2  maxextents 100);

需要注意的是此设置不会对已经存在的数据造成影响，只会规定到新创建的数据。 

 

手动的为一个表分配盘区

SQL> alter table hr.employees Allocate extent(size 500k datafile '/disk3/data01.dbf');  

extent(size 500k datafile '/disk3/data01.dbf') 用于指这在哪个数据文件中指定盘区以及大小。 

 

表的改编

一张表可能由于我们初始设置不当，或者由于后期业务发生改变，表的机构需要调整与重新组织。

重新组织命令：

SQL> alter table hr.employees move tablespace data1;

演示：此命令只对普通的表有用，或分区表中的一个表有用。

SQL> show user;
USER is "AS1"

//查看当前用户下的表
SQL> select table_name from user_tables;
TABLE_NAME                        TABLESPACE_NAME
--------------------------------------------------------------------------------------
TT                                    ASSM
TMP_SESSION
TMP_TRANSACTION


SQL> select * from tt;   //查看表结构
        ID NAME
---------- --------------------
         0 chongshi
         1 chongshi


将表移动另一个表空间
SQL> alter table tt move tablespace mssm;
Table altered.

/*在本章的开始，我们创建了两个表空间，自动管理表空间（ASSM）与手动管理表空间（MSSM）*/


//再来查看当用户下的表：
SQL> select table_name from user_tables;
TABLE_NAME                        TABLESPACE_NAME
--------------------------------------------------------------------------------------
TMP_SESSION
TMP_TRANSACTION
TT                                    MSSM

那么对表的改编有很多方法，一种就是导出、导入。一种创建一个新表，将数据一列一列的移动到新表中。 

 

清空表

Truncating a table 相当把表中的内容全部清空，但表及其结构依然存在，像刚创建的一个新表。

SQL> truncate table hr.employees;

清空表不会产生undo信息，也就是执行些操作无法进行回滚，只能通过其它办法恢复数据。  

表的索引也会被一并清掉。

如果一个表被外界引用，那么它就不能被truncate。

 

删除表

Dropping a table 

SQL> drop table hr.employees  cascade constraints;

如果这个表被删除掉，那么它占用的空间也会被释放。 

如果要删除的表与其它表有关联，则需要加上cascade constraints 命令

 

删除一列数据

Dropping a column 的操作使用不是很频繁，但也需要知道如何操作。

SQL> alter table hr.employees drop column comments cascade constraints  checkpoint 1000;

     删除列操作会产undo信息，如果数据量过大可能会把undo表空间撑满，checkpoint 1000 会每执行1000条数据发起一个检查点，发起检查点就是会把操作的数据写到磁盘的数据文件中。