Oracle将system表空间的数据文件变小

问题描述：今天闲的蛋疼，在自己破电脑上面跑了一下这个过程：

create or replace procedure pro_test_pga is

begin

  insert into t_test2

  select t.*

  from t_test t,(select rownum from dual connect by rownum <= 10 )

  order by t.object_name;

  commit;

end pro_test_pga;

当时t_test有2883968行数据，这个过程跑下来各种direct path read temp因为我的pga_aggregate_target只有可怜的194M，其实开始跑这个过程的目的是验证把pga_aggregate_target调小会不会让速度更慢，因为这个数字是我建库的时候用的默认值，结果跑了5455.62秒，心都碎了，我的机器上的数据库是开了归档的，反正硬盘只这么多，他一边产生我一边删，不然空间一没，就停在那了，本来还想来个把pga_aggregate_target设置为10M试试的，但后来想想还是算了，至少这周算了，以后有时间试试，挂一晚就是。

更可怜的是，我当时是直接用的create table t_test as select * from dba_objects

然后n次的insert into t_test  select * from t_test产生的t_test这张表，嘿嘿，我用sys用户登录的，所以数据都产生在了system表空间，还好我的system表空间的文件大小是自动扩展的，完事后system的唯一一个数据文件大小是3930M，没错，有这么大，磁盘空间对于我等屌丝是有多重要就不多说了，马上把t_test2给干掉，我用的truncate，不过truncate只是把空间还给了Oracle，而没有还给磁盘，我还是不能用释放出来的空间，因为我的system用的数据文件大小还是3930M，坚挺的傲立在那里，嘲笑着我这个实验有多么弱智，没办法，哥就是屌丝，反正空间我得找回来，我还有别的用途，于是就有了下面的故事。

好了，回归正题：

1.在将t_test2 truncate之前，我检查了一下，我目前的数据文件大小。

select tablespace_name,file_id, file_name, round(bytes/(1024 *1024 ),0 ) total_space

from dba_data_files

order by tablespace_name;

TABLESPACE_NAME     FILE_ID     FILE_NAME                                                                       TOTAL_SPACE
SYSTEM                         1            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF     3930
UNDOTBS1                     2            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\UNDOTBS01.DBF     250
SYSAUX                         3            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSAUX01.DBF      160
USERS                           4            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\USERS01.DBF           5

2.truncate之后，再查

select tablespace_name, file_id, file_name, round (bytes /(1024 *1024 ),0 ) total_space 

from dba_data_files

order by tablespace_name;

TABLESPACE_NAME     FILE_ID     FILE_NAME                                                                       TOTAL_SPACE
SYSTEM                         1            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF     3930
UNDOTBS1                     2            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\UNDOTBS01.DBF     250
SYSAUX                         3            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSAUX01.DBF      160
USERS                           4            D:\ORACLE\ORADATA\MYORACLE\MYORACLE\USERS01.DBF           5

当然得一样，如果不一样，那不逆天了。

3.下面我得想办法找回了，回来DBA最爱的命令行，sqlplus下

在回到sqlplus之前，我看看了，truncate这张表之后，system表空间的这个数据文件到底是用了多少空间，虽说屌丝的空间很重要，但是别个已经用了的你不能硬抢过来啊，更何况是system表空间呢，屌丝也不能不讲理吧。

来看看system表空间的数据文件实际用了多少：

select file_name, a.file_id, sum (a.bytes )/1024 /1024 as MB

from dba_extents a, dba_data_files b

where a.file_id= b.file_id

group by file_name, a.file_id;

FILE_NAME                                                                            FILE_ID     MB
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF     1     726.0625
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\UNDOTBS01.DBF  2     247.9375
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSAUX01.DBF     3     144.0625
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\USERS01.DBF       4     0.4375

还好，truncate了之后只用了726M了，这个勉强还是能够忍受。

回到sqlplus下，将这个system表空间的这个数据文件的大小重置

SQL> ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF'
RESIZE 1000;
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF' RESIZ
E 1000
*
第 1 行出现错误:
ORA-03214: 指定的文件大小小于所需的最小值

SQL> ALTER DATABASE DATAFILE 1 RESIZE 3300;
ALTER DATABASE DATAFILE 1 RESIZE 3300
*
第 1 行出现错误:
ORA-03214: 指定的文件大小小于所需的最小值

--我戳，不能改小，正在我倍感失落的时候，一个想法冒了出来，是不是一定要shutdown一下才能将system表空间的数据文件由大改小呢，由大改更大肯定是没有问题的，我不试了。

关闭数据库，反正是自己的测试库，shutdwon不用打报告。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             188745980 bytes
Database Buffers          415236096 bytes
Redo Buffers                7094272 bytes
数据库装载完毕。
数据库已经打开。

再试试：改为800M
SQL> ALTER DATABASE DATAFILE 1 RESIZE 800m;

数据库已更改。

上面的1就是我那个数据文件的file_id嘛，菜鸟都知道，空间也找回来了，直接进去数据文件所在的文件夹里面查看数据文件的大小，确实是800M，Oracle没有骗我。

再查查实际用了多少空间

select file_name, a.file_id, sum (a.bytes )/1024 /1024 as MB

from dba_extents a, dba_data_files b

where a.file_id= b.file_id

group by file_name, a.file_id;

FILE_NAME                                                                            FILE_ID     MB
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSTEM01.DBF     1     726.0625
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\UNDOTBS01.DBF  2     247.9375
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\SYSAUX01.DBF     3     144.0625
D:\ORACLE\ORADATA\MYORACLE\MYORACLE\USERS01.DBF       4     0.4375

很显然嘛，虽然屌丝给了我800M的空间，但是我实实在在只用了726啊！！