Oracle使用——Oracle表空间处理

创建表空间

• 查看表空间信息（名称、大小）

   select t1.name tablespace_name ,t2.name file_local ,t2.bytes / (1024 * 1024) file_size 
   from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;

• 创建临时表空间
  • 概念：临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候，并且当PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序。像数据库中一些操作： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN等都可能会用到临时表空间。当操作完成后，系统会自动清理临时表空间中的临时对象，自动释放临时段。这里的释放只是标记为空闲、可以重用，其实实质占用的磁盘空间并没有真正释放。这也是临时表空间有时会不断增大的原因。临时表空间存储大规模排序操作(小规模排序操作会直接在RAM里完成，大规模排序才需要磁盘排序Disk Sort)和散列操作的中间结果.它跟永久表空间不同的地方在于它由临时数据文件（temporary files）组成的,而不是永久数据文件（datafiles）。临时表空间不会存储永久类型的对象，所以它不会也不需要备份。另外，对临时数据文件的操作不产生redo日志，不过会生成undo日志。创建临时表空间或临时表空间添加临时数据文件时，即使临时数据文件很大，添加过程也相当快。这是因为ORACLE的临时数据文件是一类特殊的数据文件:稀疏文件（Sparse File）,当临时表空间文件创建时，它只会写入文件头部和最后块信息(only writes to the header and last block of the file)。它的空间是延后分配的.这就是你创建临时表空间或给临时表空间添加数据文件飞快的原因。另外，临时表空间是NOLOGGING模式以及它不保存永久类型对象，因此即使数据库损毁，做Recovery也不需要恢复Temporary Tablespace。
  • 创建：

    create temporary tablespace test_temp tempfile 'D:\soft\oracle\app\oradata\orcl\TEST_TEMP.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;  

    PS:红色部分为临时表空间存储路径，在oracle安装路径中可以找到。

•  创建数据表空间
  • 概念：表空间是一个用来管理数据存储逻辑概念，表空间只是和数据文件（ORA或者DBF文件）发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。
  • 创建

    create tablespace test_data logging datafile 'D:\soft\oracle\app\oradata\orcl\TEST_DATA.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; 

• 创建用户并指定表空间
  • 创建

    create user test identified by test default tablespace test_data temporary tablespace test_temp; 

删除表空间

• 删除数据表空间

  drop tablespace test_data including contents and datafiles cascade constraint;

修改表空间

• 修改已有表空间数据文件
  • 修改表空间数据文件大小

    --扩展空间，将数据文件扩大至32G
    alter database datafile 'D:\soft\oracle\app\oradata\orcl\ZXGXH_DATA.dbf' resize 32000m;

  • 修改表空间属性，已有表空间数据文件大小实现自动扩展
    

    --自动增长，表空间不足时增加200MB，最大扩展32G
    alter database datafile 'D:\soft\oracle\app\oradata\orcl\ZXGXH_DATA.dbf' autoextend on next 200m maxsize 32000m;

  • 修改表空间数据文件大小为无限大
    

    --扩展空间，将数据文件为无限大
    alter database datafile 'D:\soft\oracle\app\oradata\orcl\ZXGXH_DATA.dbf' autoextend on maxsize unlimited; 

• 追加新的表空间数据文件
  • 在表空间内追加一个新的固定大小的数据文件

    alter tablespace ZXGXH_DATA add datafile 'D:\soft\oracle\app\oradata\orcl\ZXGXH_DATA2.DBF' size 200m;

  • 在表空间内追加一个新的可自动增长大小的数据文件

    alter tablespace ZXGXH_DATA add datafile 'D:\soft\oracle\app\oradata\orcl\ZXGXH_DATA2.DBF' size 200m autoextend on next 200m maxsize 32000m;

  • Next

扩展

• 将maxsize设置为unlimited也并非能无限扩展，还受限于区块大小，即blocksize大小，如一般blocksize默认8k，数据文件大小最大也只能扩展到32GB。当然可以设置bigfile（大文件表空间），8k的blocksize表空间文件理论上最大可以扩展32TB，但是实际上受操作系统的文件系统限制。使用大文件表空间（bigfile tablespace）可以大幅度增强Oracle数据库的存储能力，简化数据库管理工作。与此同时，付出的代价是增加备份与恢复的时间。如果是中小型项目，数据量在TB级以内，建议使用小文件表空间。一个小文件表空间（smallfile tablespace）最多可以包含1022个数据文件（datafile）。