Oracle表空间创建删除，omf，分区表创建

 

现在有个每天至少300W数据的表（mt），打算以每月分区的方式建成分区表，分区时，《让Oracle跑的更快2》P21说：

[sql]

1. SQL>alter system set db_create_file_dest='F:\oradata\orclyxkj\tbs_test_t_mt_2012';  

[sql]

1. SQL>create tablespace ts_mt_2012_1 datafile size 50M autoextend on;  

第一句的意思是设定一个数据文件存放路径，让ORACLE自动在该路径下创建文件，并使用oracle自己的方式为数据文件命名。确实，我执行上面两句话后，成这样了：

可以发现，oracle自己建了ORCLYXKJ\DATAFILE这两个文件夹，并且建成的.dbf数据文件也不是我写的ts_mt_2012_1.dbf，但是oracle自己建的那个文件大小确实是我刚指定的50M，说明是表空间的数据文件。为什么？因为：

alter system set db_create_file_dest='F:\oradata\orclyxkj\tbs_test_t_mt_2012';

这句话的意思，就是开启的oracle的OMF，即oracle自动管理文件，所以文件的命名什么的都是oracle自动完成的。一般，单数据库实例，基本都不开启OMF。

开启OMF，还有另外一种方法，就是创建数据库时：

建库时，选择第 3 项了，这是omf 管理方式，oracle自动管理数据文件名，所以才不是你命名的名称。正常 选择 第 2 项。 

然后，我看到那个文件以及目录，不是我希望看到的有规律的命名，我就不想以这种方式来创建表空间了，所以决定把刚建的那个表空间删掉（有语句直接删除表空间和数据文件），但是我这时没有用语句删，而是直接在PL/SQL中，找到Tablespace文件夹下面的ts_mt_2012_1，然后反键，删掉。

删完后，到'F:\oradata\orclyxkj\tbs_test_t_mt_2012'目录发现之前oracle自己建的那个文件还在，我又不敢手动删，怕删完之后和数据文件不一致导致数据打不开。其实这个时候已经不存在这个问题了，因为表空间已经删掉了。除非表空间没有删掉而你把数据文件没有offline或者删掉了，才会导致数据库起不来。
问题已经描述完毕，结论：

1.创建表空间到指定目录：

[sql]

1. SQL> create tablespace wfcrmdb datafile 'e:/tools/oracle/oradata/wfcrm/wfcrmdb.dbf' size 100m autoextend on next 50m maxsize unlimited;         --<span style="color:#ff0000;">一般关掉表空间自动扩展，因为自动扩展可能会导致无预兆的数据库宕机。解决方案：看下面的“分区表”</span>  

插补几句：
--新创建表空间时，数据文件的扩展名是ora，可是系统表空间的数据文件却是dbf的，还有一些已有的表空间的数据文件是dat文件，请问有什么区别吗？

--只是通过扩展名来标识文件的类型而已，对于数据文件不管是ora/dat/dbf，都是一样的，没有什么区别。一般数据库新增datafile什么的时候都会给你一个默认的后缀滴，就按照这个结合2楼的说法即可，其实错了也没啥，没有影响，实在要严格要求自己的话，大不了rename一把好了。扩展名只是规范文件命名而已。没有特别严格的要求，但一个好的应用系统，在对象命名上最好也规范一些。
.dbf-数据文件， .tmp-临时文件， .log-重作日志文件(redo log file)， .ctl-控制文件
.ora-参数文件， .dat-Oracle系统文件
问：

创建表空间，用哪个语句比较好：
1.
CREATE SMALLFILE TABLESPACE gzxm DATAFILE 'E:\oracle\product\10.2.0\oradata\gzxm'
SIZE 512M AUTOEXTEND ON
NEXT 128M MAXSIZE
UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

2.
SQL> create tablespace wfcrmdb datafile 'e:/tools/oracle/oradata/wfcrm/wfcrmdb.dat' size 100m autoextend on next 50m maxsize unlimited;

答：

都不好

CREATE SMALLFILE TABLESPACE gzxm DATAFILE 'E:\oracle\product\10.2.0\oradata\gzxm'
SIZE 512M AUTOEXTEND OFF
把数据文件自动扩展关掉。如果开启的话，要是你的系统出现问题，大量插入数据，会表空间爆掉，导致数据库宕机
问：要开启呀，现在数据增长还不是很稳定啊。可能突然进几个大客户，数据量一下就起来了，而且这种几率非常大~
答：

这个需要设计，自动扩展不利于后期维护
首先要预估一下 大约有多少数据
例如 表空间 100g
可以设置每个 文件 4g ，共25 个文件
以后在增加空间，继续添加文件即可
好处是，如果某个数据文件坏了，可以将数据库减少到最少损失
问：

拿我即将分区的那个表来说。以后是每天300W（不稳定），一个月就是9000W。我准备按月分区，一共12个分区就是12个表空间，每个表空间9000M，如果不扩展，一年是105个G。
如果扩展，我开始只会给每个表空间50M的大小，之后的数据自动增长，这一个月增长完之后，会转到已经建好的50M的下一个表空间，这样有些表空间一个月没有用到9000M的，空间就节省了，超过9000M的，会自动增长。这样他们的空间可以互补，我也不用担心某个时候突然表空间位置不够了。

答：

2.删除表空间以及该表空间的物理数据文件

[sql]

1. drop tablespace xxx including contents and datafiles;   --连空间带文件一起删除  

不用执行 alter database datafile '...' offline drop;这个语句

如果表空间删除了，数据文件还在，手工到目录下删除文件即可 
 

如何查看OMF是否开启/关闭，以及修改：

查看

[sql]

1. select * from v$parameter where name like '%db_create%'  

关闭

[sql]

1. alter system set db_create_file_dest='';  

没有值是关闭，有值就是开启了。

分区表

mt表（每天300w，一个月至少9G。一次insert之后一次update某几列后，该表就只有统计用），mx表（每天300W+，一次insert后，多次select和update），mt表分区策略：

1.oracle建议，一个表大小超过2g就要考虑分区。也有人说，分区只有2种情况可以用，要慎用。我还是偏向分区。

表分区优点：

分区表 ，维护起来比较容易，也比较灵活。例如，你要删除其中某一个月分区的数据，可以使用 truncate  分区，普通表的话 就必须使用 delete ，太占用资源。

建立分区表，是大数量的表为了方便管理，跟表空间没啥关系啊

2.分区前，要设计表空间，以及表空间使用的每个数据文件的大小。自动扩展不利于后期维护，首先要预估一下，大约有多少数据。例如，表空间100g，可以设置每个数据文件4g ，共25 个文件。以后在增加空间，继续添加文件即可。好处是，如果某个数据文件坏了，可以将数据库减少到最少损失。

表空间自动扩展缺点：

比如向你插入数据的系统，出现错误 ，不断向你的表插入数据。你的表空间不断自动增大，例如 你的磁盘 空间 500g ，都被灌满后，数据库宕机了，你的单个数据 300g

你怎么办 ？一切皆有可能，不要存在侥幸心理，这是设计数据库必须需要考虑的问题。你能控制自己的数据库系统，你控制不了别人的系统。

另外单个数据文件不能太大 ：

 windows
    FAT16 文件系统    单个最大不能超过 2g
    FAT32 文件系统    单个最大不能超过 4g
    NFTS  文件系统    单个文件最大达到 2TB
  linux
     ext2 和 ext3 文件系统 理想 单个文件能支持 2TB

现在来说mt表建立方式：

1.mt表每天300W，一个月就是9000W，将近9G，一年是108G，这是最保守的估计。我的硬盘现在F盘有135G NTFS可以用，那么把mt表专用的表空间tbs_mt_2012放到这个盘里面，物理分给tbs_mt_2012表空间33个数据文件，每个文件4g，共132g（注意：数据文件建立后，就不能删除了，数据是平均分配到 每个数据文件。）

问：意思是，我管理，只用管理表空间的大小 
答：对， 增加表空间大小两种方法：
1.增加数据文件
2.或者   resize 数据文件 ，在原有基础上 增加或减少，不能删除

问： resize 数据文件的时候，要知道resize 哪个数据文件吧。数据平均分配到每个数据文件，到时候怎么知道应该resize 哪个数据文件呢
答：无论增加和减少随便哪个文件都可以。注意，减少的话就的看文件空间是否都被占用了，如果没有，可以减少。
在操作系统中，查看每个文件的大小，都是你创建的文件的大小，所以只能看表空间的使用情况来判断数据文件的使用情况

简单的sql（查看表空间的空闲率）：

[sql]

1. <p>SQL> select tablespace_name,sum(bytes/1024/1024) MB from dba_free_space group by tablespace_name;  --这里看的都是表空间空闲率</p><p>TABLESPACE_NAME                        MB   
2. ------------------------------ ----------   
3. UNDOTBS1                        5571.4375  
4. SYSAUX                             5.9375  
5. USERS                              1.8125  
6. YDSOFT_BASE                     1181.5625  
7. SYSTEM                             4.6875  
8. EXAMPLE                           22.3125  
9. YDSOFT_BASE_INDEX                255.9375</p>  

复杂点的，全一点的：

[sql]

1. SQL> select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%"  
2.   2  　　from  
3.   3  　　(select tablespace_name,round(sum(bytes)/1024/1024) totalspace  
4.   4  　　from dba_data_files  
5.   5  　　group by tablespace_name) df,  
6.   6  　　(select tablespace_name,round(sum(bytes)/1024/1024) freespace  
7.   7  　　from dba_free_space  
8.   8  　　group by tablespace_name) fs  
9.   9  　　where df.tablespace_name=fs.tablespace_name;  
10.   
11. 表空间名                          总空间M  剩余空间M    使用率%  
12. ------------------------------ ---------- ---------- ----------   
13. UNDOTBS1                             5670       5571       1.75  
14. SYSAUX                                440          6      98.64  
15. USERS                                   5          2         60  
16. YDSOFT_BASE                          5120       1182      76.91  
17. SYSTEM                                500          5         99  
18. EXAMPLE                               100         22         78  
19. YDSOFT_BASE_INDEX                     256        256          0  
20.   
21. 7 rows selected  

其中，我的undo表空间UNDOTBS1对于其他的来说，已经非常大了。

问：我的undo表空间怎么这么大

答：不用担心，即使 99% 也是没事的，自己会释放的。undo 从建立起一般就只增大不减少。所以undo 表空间的数据文件一定不能设置自动扩展！
举个例子：假如表空间 undo 4g

delete 表 aa  4g 的表  ，不 commit

这时 undo 就会分配 4g 空间的 存放 aa 表，这时你还想 delete 4g 的 bb 表，那么就会停在那，因为没有 undo 空闲空间了。这时 你 commit aa 表，空间就空闲出来，bb表就会被顺利删除 ，否则bb删除时一直等待。

分区实现：

建立表空间

数据表空间 tbs2012     目标大小 200g 每个数据文件 4g,共 50个文件
索引表空间 tbs2012_idx 

分区表 tbl 建立在 tbs2012 表空间上，根据月份 建立 12 个分区，分区也建立在 tbs2012 空间上

分区表的索引 建立 tbs2012_idx 表空间上。

如果有操作的话，最好建立索引，而且要建立分区索引，不占多大地方

执行全表扫描代价很大。

总结起来，就是创建一个135G的表空间tbs_mt_2012，给这个表空间34个数据文件（手动创建），那么表空间数据均匀分布在这34个数据文件上。mt表按照每月分区的方式，在tbs_mt_2012建12个分区。以后再自己管理这些数据文件。表空间不够了，就手动增加数据文件（位置自己定），多出来了就resize其中任何一个数据文件的大小。

创建表空间：

[sql]

1. create tablespace tableau datafile   
2. 'E:\Oracle\ORADATA\data\tableau01.DBF' size 4096m autoextend off,   
3. 'E:\ORACLE\ORADATA\data\tableau02.DBF' size 4096m autoextend off,   
4. 'E:\ORACLE\ORADATA\data\tableau03.DBF' size 4096m autoextend off,   
5. 'E:\ORACLE\ORADATA\data\tableau04.DBF' size 4096m autoextend off,   
6. 'E:\ORACLE\ORADATA\data\tableau05.DBF' size 4096m autoextend off,   
7. 'E:\ORACLE\ORADATA\data\tableau06.DBF' size 4096m autoextend off,   
8. 'E:\ORACLE\ORADATA\data\tableau07.DBF' size 4096m autoextend off,   
9. 'E:\ORACLE\ORADATA\data\tableau08.DBF' size 4096m autoextend off,   
10. 'E:\ORACLE\ORADATA\data\tableau09.DBF' size 4096m autoextend off,   
11. 'E:\ORACLE\ORADATA\data\tableau10.DBF' size 4096m autoextend off,   
12. 'E:\ORACLE\ORADATA\data\tableau11.DBF' size 4096m autoextend off,   
13. 'E:\ORACLE\ORADATA\data\tableau12.DBF' size 4096m autoextend off,   
14. 'E:\ORACLE\ORADATA\data\tableau13.DBF' size 4096m autoextend off,   
15. 'E:\ORACLE\ORADATA\data\tableau14.DBF' size 4096m autoextend off,   
16. 'E:\ORACLE\ORADATA\data\tableau16.DBF' size 4096m autoextend off,   
17. 'E:\ORACLE\ORADATA\data\tableau17.DBF' size 4096m autoextend off,   
18. 'E:\ORACLE\ORADATA\data\tableau18.DBF' size 4096m autoextend off,   
19. 'E:\ORACLE\ORADATA\data\tableau19.DBF' size 4096m autoextend off,   
20. 'E:\ORACLE\ORADATA\data\tableau20.DBF' size 4096m autoextend off,   
21. 'E:\ORACLE\ORADATA\data\tableau21.DBF' size 4096m autoextend off,   
22. 'E:\ORACLE\ORADATA\data\tableau22.DBF' size 4096m autoextend off,   
23. 'E:\ORACLE\ORADATA\data\tableau23.DBF' size 4096m autoextend off,   
24. 'E:\ORACLE\ORADATA\data\tableau24.DBF' size 4096m autoextend off,   
25. 'E:\ORACLE\ORADATA\data\tableau25.DBF' size 4096m autoextend off    
26. EXTENT MANAGEMENT LOCAL AUTOALLOCATE;  

创建分区表（看，分区表中的每个分区指定的tablespace 都是一样的）：

[sql]

1. create table sale_data  
2. (sale_id      number(5),  
3.  saleman_name varchar2(30),  
4.  sales_amount number(10),  
5.  sales_date   date)  
6. partition by range (sales_date)  
7. (  
8. partition sales_2009_1 values less than(to_date('01/02/2009','dd/mm/yyyy')) tablespace mobile,  
9. partition sales_2009_2 values less than(to_date('01/03/2009','dd/mm/yyyy')) tablespace mobile,  
10. partition sales_2009_3 values less than(to_date('01/04/2009','dd/mm/yyyy')) tablespace mobile,  
11. partition sales_2009_4 values less than(to_date('01/05/2009','dd/mm/yyyy')) tablespace mobile,  
12. partition sales_2009_5 values less than(to_date('01/06/2009','dd/mm/yyyy')) tablespace mobile,  
13. partition sales_2009_6 values less than(to_date('01/07/2009','dd/mm/yyyy')) tablespace mobile,  
14. partition sales_2009_7 values less than(to_date('01/08/2009','dd/mm/yyyy')) tablespace mobile,  
15. partition sales_2009_8 values less than(to_date('01/09/2009','dd/mm/yyyy')) tablespace mobile,  
16. partition sales_2009_9 values less than(to_date('01/10/2009','dd/mm/yyyy')) tablespace mobile,  
17. partition sales_2009_10 values less than(to_date('01/11/2009','dd/mm/yyyy')) tablespace mobile,  
18. partition sales_2009_11 values less than(to_date('01/12/2009','dd/mm/yyyy')) tablespace mobile,  
19. partition sales_2009_12 values less than(to_date('01/01/2010','dd/mm/yyyy')) tablespace mobile,  
20. partition sales_other values less than(maxvalue) tablespace  mobile  
21. );  

再说个知识点，32bit的系统要安装32位的oracle，64位的安64位的。

对于32位的oracle来说，oracle中内存最大只有1.7g，所以这时就算你服务器内存再大，oracle也只能使用1.7g的内存，速度还是相当的慢。而64位的oracle就没有限制。所以要安装64位的系统和64位的oracle。