有时候,你会在alert日志中看到如题所示的错误信息:
ORA-1653: unable to extend table xxx by xxx in tablespace xxx

发生这样的错误的时候,请检查下数据库的表空间的使用率,可能是希望操作的表空间满,导致操作被阻塞。

查看表空间使用率:
set linesize 300;

col “Tablespace Name” for a24
col “Datafile List” for a60
col “Usage Percent” for a18

select
b.tablespace_name “Tablespace Name”,b.totalmb “Total MB”,(b.totalmb – a.freemb) “Used MB”,a.freemb “Free MB”,
Round((1- a.freemb/b.totalmb)*100,2)||’ %’ “Usage Percent”
from
(select tablespace_name,sum(bytes)/1024/1024 freemb from dba_free_space group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 totalmb,wm_concat(file_name) datafilelist from dba_data_files group by tablespace_name) b
where
a.tablespace_name=b.tablespace_name
;

然后,查看下数据文件的情况:
set linesize 400

col file_name for a55
col tablespace_name for a24

select file_name,file_id,tablespace_name,status,bytes/1024/1024 “MB”,status from dba_data_files

根据:
1. file_name
2. bytes

创建新增的数据文件:
alter tablespace xxx add datafile ‘xxx’ size [xxx]M;

操作成功后,再次查看表空间使用率即可。
——————————————————————
Done。

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

隐藏
变装