Oracle修改表空间大小

表空间

使用Oracle10g建立数据库后，向数据库中导入了部分数据，第二天继续向数据库中导入数据表时发生错误：

查了很多资料发现原来是Oracle表空间限制，导致无法继续导入数据的原因。如果在建立数据库时没有设置，

Oracle 默认的表空间大小为400M，当数据库中数据量达到这个值，再向数据库中导入数据就会报错。解决方法是

扩展表空间。可以选择将表容量扩大，比如扩展到5G，或者当表空间不够时每次自动增加一定的容量,如每次自增200M。

下面列出详细过程：

1.通过sql plus 命令登录数据库。

　　在命令行下输入sqlplus “登录用户名/口令 as 登录类型”就可以登录，系统内建的用户名常用的是sys，密码是在安装oracle过程中设置的密码，清务必牢记，如果用sys帐户登录，登录类型一定要是sysdba。

2.查看各表空间分配情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files  

 group by tablespace_name;  

3.查看各表空间空闲情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;  

4.更改数据表大小(10G)

alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；

5.设置表空间不足时自动增长

5.1查看表空间是否自动增长

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

5.2 设置表空间自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//打开自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//每次自动增长200m，数据表最大不超过1G

修改原因其中之一

存盘分区系统不同

如图：

文件系统NTFS和FAT32

NTFS盘里的文件大小可以不断扩展很大

FAT32盘里的文件大小最多存放4G，字节2的32次方相当于4G。所以临时文件超过4G就会报错

临时表空间

以下的操作是用数据库的sys超级用户操作

--------------------------------系统管理员身份登录
C:\>sqlplus / as sysdba
刚开始打算把临时表空间的数据文件重新缩小就好了
执行：
SQL> alter database tempfile
2 '/oracle/oms/oradata/temp/temp01.dbf' resize 10240M;
数据库报错，重新设置的空间大小不能满足需要。
看来需要重新建立新的临时表空间替换当前的表空间了
1、首先查看当前的数据库默认表空间：
SQL>select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
确认当前的临时表空间为TEMP
2、查看目前临时表空间的大小：
SQL>select file_name,tablespace_name,bytes/1024/1024 "MB",autoextensible from dba_temp_files；

3、创建新的临时表空间：（先在其他的磁盘空间借用一下空间）
SQL> create temporary tablespace temp02
2 tempfile '/oracle/oms/oradata/undo/temp02.dbf'
3 size 512M;
4、把新建的临时表空间却换成数据库的默认临时表空间
SQL> alter database default temporary tablespace temp02;

5、确认目前数据库的默认临时表空间
SQL>select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
确认temp02为当前的数据库默认表空间
6、在删除temp临时表空间之前，先把运行在temp临时表空间的sql语句kill掉，这样的sql语句多为排序的语句
SQL>Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hashvalue=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;
查询出来之后，kill掉这些sql语句：
SQL>alter system kill session '524,778'; (假如某一条运行的sql语句的SID为524，serial#为778)
确认在temp临时表空间中没有运行的sql语句之后，则可以删除temp临时表空间数据文件了
7、删除temp临时表空间
SQL> drop tablespace temp including contents and datafiles;
这样很快就可以删除了临时表空间的数据文件
8、现在temp02临时表空间占据了别人的磁盘空间，需要重新把临时表空间建立在原来的位置，重新建立temp临时表空间
SQL> create temporary tablespace temp
2 tempfile '/oracle/oms/oradata/temp/temp01.dbf'
3 size 512M autoextend on maxsize 15G;
新建一个512M的自动扩展临时表空间，最大的扩展为15G。
查看新建的temp临时表空间是否正确：
SQL>select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files；
9、把新建的temp临时表空间却换成数据库的默认临时表空间
SQL> alter database default temporary tablespace temp;

10、确认目前数据库的默认临时表空间
SQL>select * from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
确认temp为当前的数据库默认表空间

11、目前把原来的temp临时表空间变成了512M，把剩余的磁盘空间空了出来，temp02临时表空间就没有用了，删除temp02临时表空间
SQL> drop tablespace temp02 including contents and datafiles;
from:http://jxpengtao.javaeye.com/blog/438455
--------------------------------------------------------------------------------
1.数据库版本信息
sys@ora10g> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2.查看临时表空间信息
sys@ora10g> col FILE_NAME for a40
sys@ora10g> col TABLESPACE_NAME for a10
sys@ora10g> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;

TABLESPACE FILE_NAME M
---------- ---------------------------------------- ----------
TEMP /oracle/oradata/ora10g/temp01.dbf 100

3.查看临时表空间数据文件占用操作系统的空间
sys@ora10g> !ls -l /oracle/oradata/ora10g/temp01.dbf
-rw-r----- 1 oracle oinstall 104865792 07-24 08:35 /oracle/oradata/ora10g/temp01.dbf

4.关键的一部就在这里！RESIZE it！
sys@ora10g> alter database tempfile '/oracle/oradata/ora10g/temp01.dbf' resize 50m;

Database altered.

5.确认已经修改成功
sys@ora10g> select tablespace_name,file_name,bytes/1024/1024 M from dba_temp_files;

TABLESPACE FILE_NAME M
---------- ---------------------------------------- ----------
TEMP /oracle/oradata/ora10g/temp01.dbf 50

sys@ora10g> !ls -l /oracle/oradata/ora10g/temp01.dbf
-rw-r----- 1 oracle oinstall 52436992 07-24 08:42 /oracle/oradata/ora10g/temp01.dbf

6.OK，修改成功。

另外还有一种修改临时表空间大小的方法，详见《【实验】重建临时表空间解决临时表空间过大问题》
http://space.itpub.net/519536/viewspace-607567

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/aicon/archive/2010/07/28/5771660.aspx