自动扩展表空间大小
1、CREATE OR REPLACE PROCEDURE PRO_ADD_DATAFILE
IS
V_TABLESPACE_NAME VARCHAR2(20);
V_TABLESPACE_USE NUMBER;
V_DATA_FILE_NUM NUMBER;
V_ADD_DATA_FILE_SQL VARCHAR2(400);
BEGIN
V_TABLESPACE_NAME :='TS_320';
--获取表空间使用率
SELECT round((TOTAL_M-FREE_M)/TOTAL_M,3) INTO V_TABLESPACE_USE FROM
(
SELECT A.TABLESPACE_NAME,B.TOTAL/1024/1024 TOTAL_M,(B.TOTAL-A.USE)/1024/1024 FREE_M FROM
(
select c.tablespace_name,(c.p_use-d.p_free) as use from
(
select tablespace_name,sum(bytes) as p_use from dba_data_files
where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
group by tablespace_name
) C,
(
select tablespace_name,sum(bytes) as p_free from dba_free_space
where tablespace_name NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
group by tablespace_name
) D
where C.tablespace_name=D.tablespace_name
) A ,
(
WITH TABLESPACE_TOTAL AS
(
SELECT tablespace_name,sum(MAXBYTES) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='YES' group by tablespace_name
UNION ALL
SELECT tablespace_name,sum(bytes) TOTAL FROM DBA_DATA_FILES T
WHERE T.TABLESPACE_NAME NOT IN ('USERS','SYSAUX','UNDOTBS1','SYSTEM','UNDOTBS2')
AND T.AUTOEXTENSIBLE='NO' group by tablespace_name
)
SELECT TABLESPACE_NAME,SUM(TOTAL) TOTAL FROM TABLESPACE_TOTAL GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
) S WHERE S.TABLESPACE_NAME=V_TABLESPACE_NAME;
--获取最大数据文件号
SELECT MAX(TO_NUMBER(SUBSTR(FILE_NAME,28,2))) INTO V_DATA_FILE_NUM FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME=V_TABLESPACE_NAME and file_name like '%zxx%';
if V_TABLESPACE_USE > 0.75 then
V_DATA_FILE_NUM :=V_DATA_FILE_NUM+1;
if V_DATA_FILE_NUM >= 10 then
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_0'||V_DATA_FILE_NUM||'.dbf '||''''||'
size 30G';
else
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_00'||V_DATA_FILE_NUM||'.dbf '||''''||'
size 30G ';
end if;
execute immediate V_ADD_DATA_FILE_SQL;
end if;
END;
如果需要使用该脚本在你库中需要修改的地方（用粗体标记）：
1.V_TABLESPACE_NAME的初始化值
2.获取最大数据文件号
3.更改使用率的阈值
4.修改拼接字符串
如果需要对多个表空间大小自动扩展需要编写cursor循环判断！
1、单引号转义
拼接字符串中出现单引号需要转义
select'''' from dual;
结果为：'
select ''''||'name'||'''' from dual;
结果为：'name'
2、拼接字符串引起的ORA-00911: invalid character
正确拼接
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_0'||V_DATA_FILE_NUM||'.dbf '||''''||'
size 30G';
错误拼接：最后多了一个分号 “;”
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_0'||V_DATA_FILE_NUM||'.dbf '||''''||'
size 30G;';
3、执行存储过程引起的ORA-01031: insufficient privileges
尽管该用户已经有dba权限，但是还需要针对赋予对应权限
grant select on dba_data_files to zxx;
grant select on dba_free_space to zxx;
grant alter tablespace to zxx;
zxx是只用用户
4、添加表空间引起的ORA-15124:ASM file name包含无效字符
正确脚本：
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_00'||V_DATA_FILE_NUM||'.dbf'||''''||'
size 30G ';
错误脚本：dbf和单引号之间存在空格
V_ADD_DATA_FILE_SQL := 'alter tablespace '||V_TABLESPACE_NAME||' add datafile '||''''||'+DATADG/orcl/datafile/zxx_00'||V_DATA_FILE_NUM||'.dbf '||''''||'
size 30G ';
