表空间无法扩充的方法

最近接手一个项目，讯飞要导一些表去我的中间库，但是询问后一个单表为40多g，导入数据库时一直使用导入本地oracle数据库的方法，即根据dmp文件大小设置一个数据文件，设定表空间最大值。

--创建表空间,数据文件为'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF'，初始大小50M，递增10M，最大递增到2G
create tablespace sgshare datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF'
　　size 50M
　　autoextend on next 10M
　　maxsize 2048M;

生产考虑的时候，表会一直增加，所以开放表空间大小一直扩充的权限。

--改变用户表空间容量限制，不做限制
ALTER USER ankangreli QUOTA UNLIMITED ON ankangrelir_data;

表空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K、8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块（由操作系统决定），表空间数据文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。

表空间增大的语句，前几个博客中都有写到，但是，单个dmp文件有增大的限制，当到了最大的时候，就无法增加，就会报错。

截取的更多DB_BLOCK_SIZE作用如下：

• DB_BLOCK_SIZE作为数据库的最小操作单位，是在创建数据库的时候指定的，在创建完数据库之后便不可修改。要修改DB_BLOCK_SIZE，需要重建数据库。一般可以将数据EXP出来，然后重建数据库，指定新的DB_BLOCK_SIZE，然后再将数据IMP进数据库。
• DB_BLOCK_SIZE一般设置为操作系统块的倍数，即2K,4K,8K,16K或32K，但它的大小一般受数据库用途的影响。对于联机事务，其特点是事务量大，但每个事务处理的数据量小，所以DB_BLOCK_SIZE设置小点就足够了，一般为4K或者8K，设置太大话一次读出的数据有部分是没用的，会拖慢数据库的读写时间，同时增加无必要的IO操作。而对于数据仓库和ERP方面的应用，每个事务处理的数据量很大，所以DB_BLOCK_SIZE一般设置得比较大，一般为8K，16K或者32K，此时如果DB_BLOCK_SIZE小的话，那么I/O自然就多，消耗太大。
• 大一点的DB_BLOCK_SIZE对索引的性能有一定的提高。因为DB_BLOCK_SIZE比较大的话，一个DB_BLOCK一次能够索引的行数就比较多。
• 对于行比较大的话，比如一个DB_BLOCK放不下一行，数据库在读取数据的时候就需要进行行链接，从而影响读取性能。此时DB_BLOCK_SIZE大一点的话就可以避免这种情况的发生。

回到该问题，通过上面的信息我们得出：在本机单个表空间文件大小超过32G时，表空间容量就达到了最大值，数据库就不能继续增加信息了，那么该采取什么措施呢？

 

第一种办法：多个数据文件累加

将表空间存储为多个数据文件，每个文件不大于32GB（精确的值为32768M）

一个不够用那就在加一个，但是，讯飞导过来的一个单表为40G，所以这个扑该。

create tablespace sgshare 
logging  
datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF' 
size 50m  
autoextend on  
next 50m maxsize 32767m  
extent management local; 

 --为表空间增加数据文件
alter tablespace sgshare 
add datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE2.DBF' size 50m 
autoextend on next 50m maxsize 32767m;

第二种方法，修改db_block_size

但是这个我初略理解意思，但是看别人的回答，貌似都不可以解决掉

根据oracle的算法，我们很容易想到这个解决方法。数目衡定，但是db_block_size可以更改（db_block_size的最大大小为32KB）。如果把db_block_size扩大到32KB，那么我们的系统就可以支持单个数据文件最大128GB。

默认一般都为32个G吧，我之前就是当时得知这个消息的时候，我想把表空间扩展到40多g，好存储讯飞导过来的表，但是，表空间扩大到30多g的时候就已经报错了。

这个方案听起来好像很迷人，但是实际上并不是那么回事。因为要修改db_block_size并不是很容易的事。因为这个db_block_size在创建实例的时候就要指定。而且不能通过简单修改参数来指定db_block_size。db_block_size的默认值为8192 bytes，是不能被用户修改的。因为db_block_size对应于一个实例，所以意味着在数据库创建（建库）以后是不能修改的，如需修改，可行的方式是重新建库并把原库的数据export到新库。当然最好的方式是在建数据库之前就规划好，一般如果是OLTP系统，可以保持默认值；OLAP环境可以考虑适当调大。

3. 创建bigfile表空间

重头来了，我就是用这种方法解决了我的燃眉之急。

在oracle11g中引进了bigfile表空间，他充分利用了64位CPU的寻址能力，使oracle可以管理的数据文件总量达到8EB。单个数据文件的大小达到128TB，即使默认8K的db_block_size也达到了32TB。

但是唯一注意的一点是：使用bigfile表空间，它只能支持一个数据文件。也就是说这个文件的最大大小就是表空间最大大小，你不可能通过增加数据文件来扩大该表空间的大小。不能像方法一可以增加额外的数据文件。

下面是我使用这种方法解决我问题的语句：

--一、sqlplus登录sys或者system 建立Oracle Directory目录

 create directory dp_qianyi as 'D:\qianyi';  --同时应该在相应位置建立实际目录
--删除  drop directory dp_qianyi;
--授权  grant read,write on directory dp_qianyi to system scott;  

--二、一 导出 sgshare用户下的模式

--1、sqlplus登录sys或者system给sgshare授Directory目录权限
grant read,write on directory dp_qianyi to sgshare;
--2、退出sqlplus登录，在cmd模式下，执行expdp导出语句
expdp system/xyshare@xyshare directory=dp_qianyi schemas=sgshare dumpfile=qy_sgshare20181027.dmp logfile=qy_sgshare20181027.log
--3、导出结束后，将导出的文件压缩传送到要导入的数据库服务器上  

--三、删除原有数据库用户和表空间

 

1、以system用户登录查找需要删除的用户（普通用户没有删除权限）

select * from dba_users;

2、查询需要删除用户对应的表空间

select * from dba_data_files;

3、删除用户和表空间

drop user usernamecascade;

drop tablespace tablespacename including contents and datafiles cascade constraint;

 

 

 

删除表空间的时候，貌似发生了些问题，这个原本的dbf并没有给我关闭，我是在services里面关闭了oracle的服务，然后在去文件中将表空间的数据文件删除掉，。

--四、在目的地数据库服务器上建立默认表空间和临时表空间 、同名用户
     --4.1在原数据库中查询数据库所有用户信息dba_users（可以查询所要导入的用户对应的默认表空间和临时表空间）

select username,default_tablespace,t.* from dba_users t

查看原来的表空间是否被删除。

  --4.2新建用户相应表空间和临时表空间

    create bigfile tablespace sgshare datafile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF'
    size 102400m autoextend on next 128m maxsize 204800m extent management local;  --创建表空间  
    
    create temporary tablespace XYSHARE tempfile 'D:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE2.DBF'
    size 50M autoextend ON next 10M maxsize 100M;   --创建临时表空间

临时表空间我没有创建。

----- 再次授权：     
    grant read,write on directory dp_qianyi to system

  --五、导入xyshare用户下的模式

impdp system/xyshare@xyshare directory=dp_qianyi  dumpfile=qy_sgshare20181027.dmp logfile=qy_sgshare20181027.log

 

之前的信息都会存在压缩下的文件，所以表空间账号密码等都没有变。这样就可以正常使用了，以后再导入多大的表都不用担心了。当时创表空间的时候自己觉得设置为多大。还要考虑你的dmp存在的那个磁盘中的容量为多大。