创建表空间和创建表过程分析

一、创建表空间

   1：创建临时表空间
    create temporary tablespace user_temp  
      tempfile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_temp.dbf' 
      size 50m  
      autoextend on  
      next 50m maxsize 20480m  
      extent management local;  
 
  2：创建数据表空间
    create tablespace user_data  
      logging  
      datafile 'Q:\oracle\product\10.2.0\oradata\Test\xyrj_data.dbf' 
      size 50m  
      autoextend on  
      next 50m maxsize 20480m  
      extent management local;  
 
  3：创建用户并指定表空间
   create user username identified by password  
     default tablespace user_data  
     temporary tablespace user_temp;  
 
  4：给用户授予权限
    grant connect,resource,dba to username;

   Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长.

     
临时表空间的主要作用：
   索引create或rebuild
   Order by 或 group by
   Distinct 操作
   Union 或 intersect 或 minus
   Sort-merge joins
   analyze

在oracle数据库中：extent management 有两种方式 extent management local（本地管理）; extent management dictionary（数据字典管理）,默认的是local
      本地管理表空间与字典管理表空间相比大大提高了管理效率和数据库性能，其优点如下：
1.减少了递归空间管理
　　本地管理表空间是自己管理分配，而不是象字典管理表空间需要系统来管理空间分配，本地表空间是通过在表空间的每个数据文件中维持一个位图来跟踪在此文件中块的剩余空间及使用情况。并及时做更新。这种更新只对表空间的额度情况做修改而不对其他数据字典表做任何update操作，所以不会产生任何回退信息，从而大大减少了空间管理，提高了管理效率。同时由于本地管理表空间可以采用统一大小分配方式(UNIFORM),因此也大大减小了空间管理，提高了数据库性能。
　　2.系统自动管理extents大小或采用统一extents大小
　　本地管理表空间有自动分配(AUTOALLOCATE)和统一大小分配(UNIFORM)两种空间分配方式，自动分配方式(AUTOALLOCATE)是由系统来自动决定extents大小，而统一大小分配(UNIFORM)则是由用户指定extents大小。这两种分配方式都提高了空间管理效率。
　　3.减少了数据字典之间的竞争
　　因为本地管理表空间通过维持每个数据文件的一个位图来跟踪在此文件中块的空间情况并做更新，这种更新只修改表空间的额度情况，而不涉及到其他数据字典表，从而大大减少了数据字典表之间的竞争，提高了数据库性能。
　　4.不产生回退信息
　　因为本地管理表空间的空间管理除对表空间的额度情况做更新之外不修改其它任何数据字典表，因此不产生回退信息，从而大大提高了数据库的运行速度。
　　5.不需合并相邻的剩余空间
　　因为本地管理表空间的extents空间管理会自动跟踪相邻的剩余空间并由系统自动管理，因而不需要去合并相邻的剩余空间。同时，本地管理表空间的所有extents还可以具有相同的大小，从而也减少了空间碎片。
　　6.减少了空间碎片
　　7.对临时表空间提供了更好的管理

二、创建表

-- Create table
create table NM_DEV_VER
(
  serialnum       VARCHAR2(32) not null,
  ne_id           VARCHAR2(32) not null,
  ne_name         VARCHAR2(200) not null,
  ipaddress       VARCHAR2(20) not null,
  check_time      DATE,
  check_status    VARCHAR2(10),
  ne_ver          VARCHAR2(256),
  ver_status      CHAR(1),
  sys_oid         VARCHAR2(64),
  syso_name       VARCHAR2(128),
  failure_reasons VARCHAR2(1024),
  master_patch    VARCHAR2(64),
  salve_patch     VARCHAR2(64),
  iseque          VARCHAR2(20)
)
tablespace STN_O_RESOURCE
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 1
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table NM_DEV_VER
  is '版本管理当前表';
-- Add comments to the columns 
comment on column NM_DEV_VER.serialnum
  is '流水号';
comment on column NM_DEV_VER.ne_id
  is '网元ID';
comment on column NM_DEV_VER.ne_name
  is '网元名称';
comment on column NM_DEV_VER.ipaddress
  is '网元IP';
comment on column NM_DEV_VER.check_time
  is '巡检时间';
comment on column NM_DEV_VER.check_status
  is '巡检结果，0：正常，负值：异常';
comment on column NM_DEV_VER.ne_ver
  is '设备版本，网元设备上的版本';
comment on column NM_DEV_VER.ver_status
  is '版本状态，1：已认证2：未认证3：测试';
comment on column NM_DEV_VER.sys_oid
  is '版本类型ID';
comment on column NM_DEV_VER.syso_name
  is '版本类型';
comment on column NM_DEV_VER.failure_reasons
  is '失败原因';
comment on column NM_DEV_VER.master_patch
  is '主补丁';
comment on column NM_DEV_VER.salve_patch
  is '次补丁';
comment on column NM_DEV_VER.iseque
  is '主次是否一致';
-- Create/Recreate primary, unique and foreign key constraints 
alter table NM_DEV_VER
  add constraint PK_NM_DEV_VER primary key (SERIALNUM)
  using index 
  tablespace STN_O_RESOURCE_IDX
  pctfree 10           //   %空闲
  initrans 2            // 初始事物数
  maxtrans 255     //  最大事物数
  storage               
  (
    initial 64K         // 初始大小
    next 1M             //下一个大小
    minextents 1     //最小数量
    maxextents unlimited       //最大数量
  );

    数据库的逻辑结构如下：数据库是由一系列表空间(tablespace)组成，表空间由若干段(segment)组成，段由若干区(extent)组成，区由若干块(block)组成
    当在表空间中创建表时，系统先分配一个初始空间，这个空间大小由initial这个参数决定，此处为64KB,minextents 表示建好表后至少要分配几个区，这里是1个，maxextents 表示表空间最多能分配几个区，这里是无限制

1. PCTFREE
要形容一个 BLOCK 的运作，我们可以把一个 BLOCK 想成一个水杯。侍者把水倒入放在我们面前的水杯，要多满呢，我们要求他倒 9 分满好了，这时候 PCTFREE 代表着设定为 10 ，意思就是说，当 BLOCK 使用到达 90% 的时候，就不可以在使用了，这个 BLOCK 应该从 FREELIST 列表中移除 (un-link) 。为何要保留 10% 的空间呢？这是为了提供 update 数据时所可能增加的空间使用，如果空间保留的太小，就容易发生 row chaining 。
2. PCTUSED
PCTUSED 代表着这杯水什么时候可以添加，假设 PCTUSED 为 40 ，代表当我们把水杯的水喝到剩下 40% 以下时，侍者就会知道需要加水了。你想想看，如果说在餐厅里妳每喝一口水侍者就来加水，你会不会觉得很烦，对餐厅来说，也要派很多人不断帮每桌客人加水，这生意还能做吗？所以说， PCTUSED 代表着 re-link 回 FREELIST 的意义，如果说 PCTUSED 设的太大，例如 70 好了，代表这杯水你随便喝一口侍者就要来加水了，这隐含的意义是，这个杯子的利用率增加，但是侍者频繁的服务造成了负荷 (I/O Overhead) 。 PCTUSED 设小一点，例如 10 ，代表当水喝到剩下 10% 的时候 ( 如同 DELETE 事务操作 ) ，才须要放回 FREELIST ，代表可以加水了 ( 如同 INSERT 事务操作 ) 。
简单的说，如果希望储存空间发挥最大使用效益，可以把PCTUSED设大一点，相反的，如果想要提高IO效能，应该把PCTUSED设定小一点。
FREELIST 储存着可使用的 BLOCK 信息，当 BLOCK 被 DELETE 数据使用量下降到 PCTUSED 时，就会重新放置到 FREELIST 上，让其它交易新增数据使用。 FREELIST 存放在 TABLE/INDEX 的 Segment Header 中，他管理着所有可以新增数据的 BLOCK 信息。想想，如果有多人同时要新增数据而要求 FREELIST 提供可使用的 BLOCK 信息， FREELIST 不就也会成为另一个 Overhead ？所以依照 TABLE 使用的需求调整 FREELIST 的数量也是 IO Tuning 的一个重点。
这个参数在ASSM中已经没有用了，只剩下PCTFREE是生效的了。
3. INITRANS
INITRANS 指的是一个 BLOCK 上初始预分配给并行交易控制的空间 (ITLs)
( 当 BLOCK 上某笔 ROW 被交易更新锁定时，会在 BLOCK header ITL allocate 一个锁，当下一个交易要更新同一笔 row 时，就会发现他已经被先前的交易持有锁了，会先去检查该交易是否 active? 如果是，后来的该笔交易就会被 blocking ，等待 ) 如果一个表格需要同时有大量交易存取，你应该设定 INITRANS 大一点，可以减少 ITL 还要动态扩充的 Overhead 。
For tables INITRANS defaults to 1 for indexes 2
4. MAXTRANS
MAXTRANS 指的是如果 INITRANS 空间不够用了，就会自动扩展 ITL ，直到最大值也就是 MAXTRANS 值为止，预设是 255 。但是，如果 BLOCK 空间已经不足，也有可能无法持续扩充到 255 个 ITS 空间喔。