ORACLE检查并创建表空间和表分区的脚本

su – oracle

# 清理归档日志
rman target /
rman> delete archivelog all completed before 'sysdate-7';
rman> exit
# 清理回收站
sqlplus / as sysdba
purge dba_recyclebin;
exit
# 查找并清理大文件
find /u01/app/oracle -type f -size +1g -exec ls -lh {} \;

-- 增加数据文件
alter tablespace <tablespace_name>
add datafile '/data/oracle/database/orcl/表空间文件名称.dbf'
size 2048m autoextend on next 100m maxsize unlimited extent management local;
-- 或扩展现有数据文件,该操作需确认是否要使用
alter database datafile '/data/oracle/database/orcl/表空间文件名称.dbf ' resize 20g;

-- 创建根据前文查询缺失的分区
alter table 表名称 
add partition 分区名称values less than ('截止日期，例如20250505')
tablespace 对应表空间名称
pctfree 10
initrans 1
maxtrans 255
storage(
	initial 8m
	next 1m
	minextents 1
	maxextents unlimited
);

-- 查找高水位线（hwm）较高的表
select table_name, round((blocks * 8) / 1024, 2) "高水位线(mb)",
       round((num_rows * avg_row_len / 1024 / 1024), 2) "实际数据大小(mb)",
       round((blocks * 8 ) / 1024, 2) - round((num_rows * avg_row_len / 1024 / 1024), 2) "可回收空间(mb)"
from dba_tables
where owner = 'your_owner'
  and round((blocks * 8) / 1024, 2) > round((num_rows * avg_row_len / 1024 / 1024), 2)
order by "可回收空间(mb)" desc;
-- 当表经过大量delete操作后，有很多碎片空间时，对表进行移动和收缩（例如对表my_table）, 操作期间会锁定表，建议在业务低峰期执行
alter table your_owner.my_table enable row movement;
alter table your_owner.my_table shrink space cascade;

purge recyclebin; -- 清除当前用户的回收站
purge dba_recyclebin; -- 需要dba权限，清除整个数据库的回收站

alter table your_owner.your_partitioned_table drop partition <partition_name>;

create procedure sys_create_tablespace
/**************************************************************
  *  存储过程名称: sys_create_tablespace
  *  建立日期    : 2025/10/16
  *  作者        : 宋
  *  作用        ：自动创建表空间和表分区
  *  输出        : 无返回值
  *-------------------------------------------------------------
  * 修改历史
  * 序号        日期       修改人    修改原因
  *   1       2025/10/16  宋       新建
  *
  **************************************************************/
is
    -- 声明游标：获取未来3年（含当前年份）的每个季度的名称，例如2025_q1 2025_q2
    cursor cur_date is
        select to_char(add_months(trunc(sysdate, 'year'), (level - 1) * 3), 'yyyy')     as qyear,
               to_char(add_months(trunc(sysdate, 'year'), (level - 1) * 3), 'yyyymm')   as qmonth,
               to_char(add_months(trunc(sysdate, 'year'), (level - 1) * 3), 'yyyy') || '_q' ||
               to_char(add_months(trunc(sysdate, 'year'), (level - 1) * 3), 'q')        as qname,
               to_char(add_months(trunc(sysdate, 'year'), (level - 1) * 3), 'yyyymmdd') as qdate
        from dual
        connect by level <= 12;
    -- 变量声明
    maxrows     number default 100000;
    q_year      dbms_sql.varchar2_table; -- 年份
    q_month     dbms_sql.varchar2_table; -- 月份
    q_name      dbms_sql.varchar2_table; -- 表空间和表分区的起名规则
    q_date      dbms_sql.varchar2_table; -- 表分区截止时间
    v_proc_name varchar2(50);
    v_err_msg   varchar2(1024); -- 错误描述
    i_code      number;
    v_sqlcode   number;
    v_sqlerrm   varchar2(4000);
    v_sql       varchar2(4000);
begin
    v_proc_name := 'sys_create_tablespace';
    open cur_date;
    loop
        -- 批量获取季度数据
        fetch cur_date bulk collect into q_year, q_month, q_name, q_date limit maxrows;
        -- 退出条件：当没有数据时退出循环
        exit when q_name.count = 0;
        -- 遍历每个季度
        for i in 1 .. q_name.count loop
            -- 获取所有需要创建表空间和表分区的表信息
            for cur_table in (
                select owner, 
                       table_name, 
                       table_name || '_' || q_name(i) as table_name_alias
                from all_part_tables
                where owner in ('aaaa', 'bbbb')
            ) loop
                -- 跳过不需要创建表空间的表
                if cur_table.table_name = 'xxxxxx' then
                    continue;
                end if;
                -- 只为xxxxxx表创建表空间和分区
                if cur_table.table_name = 'xxxxxx' then
                    -- 创建表空间（如果不存在）
                    begin
                        -- xxxxxx只创建年份的（只在第一季度创建）
                        if q_name(i) not like '%_q1' then
                            continue;
                        end if;
                        v_sql := 'create tablespace ' || cur_table.table_name || '_' || q_year(i) || 
                                 ' datafile ''/data/oracle/database/orcl/' || cur_table.table_name || '_' || q_year(i) || '.dbf'' ' ||
                                 'size 2048m autoextend on next 100m maxsize unlimited extent management local';
                        execute immediate v_sql;
                    exception
                        when others then
                            v_sqlcode := sqlcode;
                            v_sqlerrm := sqlerrm;
                            sp_passys_errhandle(v_proc_name, v_sqlcode, v_sqlerrm);
                    end;
                    -- 创建分区
                    begin
                        v_sql := 'alter table ' || cur_table.owner || '.' || cur_table.table_name || 
                                 ' add partition cp' || q_year(i) || 
                                 ' values less than (''' || q_month(i) || ''') ' ||
                                 ' tablespace ' || cur_table.table_name || '_' || q_year(i);
                        execute immediate v_sql;
                    exception
                        when others then
                            v_sqlcode := sqlcode;
                            v_sqlerrm := sqlerrm;
                            sp_passys_errhandle(v_proc_name, v_sqlcode, v_sqlerrm);
                    end;
                end if;
            end loop; -- 结束表循环
        end loop; -- 结束季度循环
    end loop; -- 结束主循环
    close cur_date;
exception
    when others then
        -- 异常处理：确保游标关闭
        if cur_date%isopen then
            close cur_date;
        end if;
        raise;
end sys_create_tablespace;
/