ORA-01652 表空间不够解决方案

前章：出现表空间不足不要手动强制删除对应数据文件存储目录下的DBF文件，需要用SQL语句进行数据文件的DROP，否则会导致ORA-01033报错，因为我没有开启数据库的归档所以不能通过RECOVER的形式找回数据文件最后只能重装本地ORACLE。

1.检查表空间使用情况，可带临时表也可不带

--查询所有表空间利用率（无TEMP临时表空间）
select dbf.tablespace_name,dbf.totalspace "总量(M)",dbf.totalblocks as 总块数,dfs.freespace "剩余总量(M)",dfs.freeblocks "剩余块数",(dfs.freespace / dbf.totalspace) * 100 "空闲比例"from (select t.tablespace_name,sum(t.bytes) / 1024 / 1024 totalspace,sum(t.blocks) totalblocksfrom dba_data_files tgroup by t.tablespace_name) dbf,(select tt.tablespace_name,sum(tt.bytes) / 1024 / 1024 freespace,sum(tt.blocks) freeblocksfrom dba_free_space ttgroup by tt.tablespace_name) dfswhere trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

结果集：

select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name

结果集：

–2.查看文件是否自动扩展

select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d---如果想查看临时表空间文件是否自动扩展select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;

–3.在检查表文件时 发现 表空间escalade使用率接近100%，且不可扩展

select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d  

–4.修改文件自动可扩展性

alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM02.DBF' 
autoextend on;

–5.查看当前用户各表所占表空间

Select Segment_Name,Sum(bytes)/1024/1024 
From User_Extents Group By Segment_Name

–6.未使用的表空间大小

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

–7.使用如下语句查看一下谁在用临时段

SELECT se.username, se.SID, se.serial#, se.sql_address, se.machine
, se.program, su.TABLESPACE,  su.segtype,  su.CONTENTS
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;

–8.增加表空间大小的四种方法

select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d

–8.1：给表空间增加数据文件

alter tablespace SYSTEM 
add datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF' 
size 20G;

–8.2：新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE app_data ADD DATAFILE'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50MAUTOEXTEND ON NEXT 5M MAXSIZE 100M;

–8.3：允许已存在的数据文件自动增长

ALTER DATABASE DATAFILE '/home/oracle/app/oracle/oradata/helowin/users01.dbf' 
AUTOEXTEND ON NEXT 5M MAXSIZE 10240M;alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM02.DBF' 
autoextend on;

–8.4：手工改变已存在数据文件的大小

ALTER DATABASE 
DATAFILE 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM03.DBF'
RESIZE 30G;

–9.查询数据库的最大表空间是多少

在sqlplus 中执行show parameter db_block_size------本机是8192—8K

Oracle数据文件默认大小上限是32G，如果要数据文件大于32G，需要在数据库创建之初就设置好。

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

即：

4k最大表空间为：16384M=16G

8K最大表空间为：32768M=32G

16k最大表空间为：65536M=64G

32K最大表空间为：131072M=128G

64k最大表空间为：262144M=256G

在windows下只能使用2K，4K，8K，16K的块大小。