表空间不足问题-表空间文件自增

查看表 空间命令

--查看表空间
SELECT * from dba_tablespaces;

--查看表空间文件路径

select tablespace_name,file_id,file_name from dba_data_files order by 1,2;

--查看用户和默认表空间的关系
SELECT username,default_tablespace from      dba_users;

简单处理 表空间增长问题

-- 扩大原表空间文件
-- 单文件32g 问题注意 --根据系统环境 ，4k 16g  8k 32g

ALTER DATABASE DATAFILE 'C:\ORANT\ORADATA\xxx\xxx.ORA' AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 因为单文件大小限定,所以以增加文件的方式解决问题
alter tablespace tablespace_all.tablespace_name  add datafile  all_file_name  SIZE 1024M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED

终极方案 自动扩展表空间

--使用率超过90%则进行新增表文件创建
create or replace procedure prc_job_auto_add_datafile is

all_file_name varchar2(500);
tablespace_all varchar2(500);
vs_sql varchar2(500);
filecount varchar2(100);
relativepath varchar2(500);

cursor c_tablespace is
SELECT tablespace_name,Total_MB,Used_MB,Round(Used_MB/Total_MB*100,2) AS User_Pct from
(
    select tablespace_name,Round(Sum(bytes)/1024/1024,2) AS Used_MB,Round(sum(maxbytes)/1024/1024,2) AS Total_MB from dba_data_files
    where tablespace_name <> 'SYSAUX' AND tablespace_name <> 'USERS' AND tablespace_name NOT LIKE 'UNDOTBS%'
    group by tablespace_name
);

Begin
  relativepath:= 'D:\app\Administrator\oradata\orcl\';--云中心：+DATA/zhsw/datafile/  153：D:\app\Administrator\oradata\orcl\
  for tablespace_all in c_tablespace  loop
        If tablespace_all.User_Pct >=90 Then
            all_file_name :=  relativepath|| tablespace_all.tablespace_name;
            select count(*)+1 into filecount from dba_data_files where tablespace_name=tablespace_all.tablespace_name;
            if filecount<=9 then 
              filecount:='0'||filecount; 
            end if;
            all_file_name := all_file_name||filecount||'.dbf';
            vs_sql := 'alter tablespace '||tablespace_all.tablespace_name||' add datafile '''||all_file_name||''' SIZE 1024M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED';
            --dbms_output.put_line(vs_sql);
            Execute Immediate vs_sql;
       End If;
  end loop;

exception
  when others then
    dbms_output.put_line(sqlerrm);
End prc_job_auto_add_datafile;

--创建oracle job 频率为周
begin
  sys.dbms_scheduler.create_job(job_name            => 'auto_add_datafile',
                                job_type            => 'STORED_PROCEDURE',
                                job_action          => 'prc_job_auto_add_datafile',
                                start_date          => to_date('24-07-2018 09:00:00', 'dd-mm-yyyy hh24:mi:ss'),
                                repeat_interval     => 'Freq=Weekly',
                                end_date            => to_date(null),
                                job_class           => 'DEFAULT_JOB_CLASS',
                                enabled             => true,
                                auto_drop           => true,
                                comments            => '');
end;
/


-- 观察是否生成  model_data07  表空间

中途问题

1. 需要dba 权限

2.查询能找到 dba_data_files 视图，但是执行存储过程时报未找到

需要以dba 身份重新赋予权限  

grant select on dba_data_files to shpzjzs;

3.注意代码默认命名的增长方式，防止文件已经存在