linux oracle 磁盘满了

• 问题：

使用oracle数据库时容易报错，无法大量插入数据。把账户表清空了也无法减少空间。而且select *查空表的速度也非常慢。

原因：

---

• Linux磁盘满了

---

• 解决方法：

  ---

   

1. 1、df命令查磁盘大小

---

1. 2、试图减少表空间

第一步：查看所有表空间及表空间大小：

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

第二步：查看所有表空间对应的数据文件：

select tablespace_name,file_name from dba_data_files;

第三步：修改数据文件大小

alter database datafile 'H:\ORACLE\PRODUCT\10.1.0\ORADATA\ORACLE\USERS01.DBF' RESIZE 10240M; 

结果返回失败：

ORA-03297: file contains used data beyond requested RESIZE value

第四步：使用官网的自动化脚步调整表空间

REM Script is meant for Oracle version 9 and higher
REM -----------------------------------------------
 
set serveroutput on
exec dbms_output.enable(1000000);
 
declare
 
cursor c_dbfile is
select f.tablespace_name,f.file_name,f.file_id,f.blocks,t.block_size
,decode(t.allocation_type,'UNIFORM',t.initial_extent/t.block_size,0) uni_extent
,decode(t.allocation_type,'UNIFORM',(128+(t.initial_extent/t.block_size)),128) file_min_size
from dba_data_files f,
dba_tablespaces t
where f.tablespace_name = t.tablespace_name
and t.status = 'ONLINE'
order by f.tablespace_name,f.file_id;
 
cursor c_freespace(v_file_id in number) is
select block_id, block_id+blocks max_block
from dba_free_space
where file_id = v_file_id
order by block_id desc;
 
/* variables to check settings/values */
dummy number;
checkval varchar2(10);
block_correction1 number;
block_correction2 number;
 
/* running variable to show (possible) end-of-file */
file_min_block number;
 
/* variables to check if recycle_bin is on and if extent as checked is in ... */
recycle_bin boolean:=false;
extent_in_recycle_bin boolean;
 
/* exception handler needed for non-existing tables note:344940.1 */
sqlstr varchar2(100);
table_does_not_exist exception;
pragma exception_init(table_does_not_exist,-942);
 
/* variable to spot space wastage in datafile of uniform tablespace */
space_wastage number;
 
begin
 
/* recyclebin is present in Oracle 10.2 and higher and might contain extent as checked */
begin
select value into checkval from v$parameter where name = 'recyclebin';
if checkval = 'on'
then
recycle_bin := true;
end if;
exception
when no_data_found
then
recycle_bin := false;
end;
 
/* main loop */
for c_file in c_dbfile
loop
/* initialization of loop variables */
dummy :=0;
extent_in_recycle_bin := false;
file_min_block := c_file.blocks;
 
begin
 
space_wastage:=0; /* reset for every file check */
 
<<check_free>>
 
for c_free in c_freespace(c_file.file_id)
loop
/* if blocks is an uneven value there is a need to correct
with -1 to compare with end-of-file which is even */
block_correction1 :&#