首先看一下表空间
--查询表空间的使用情况
select upper(f.tablespace_name)"表空间名",
d.tot_grootte_mb "表空间大小(m)",
d.tot_grootte_mb-f.total_bytes "已使用空间(m)",
to_char(round((d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb*100,2),'990.99')||'%'"使用比",
f.total_bytes "空闲空间(m)",
f.max_bytes "最大块(m)"
from(select tablespace_name,
round(sum(bytes)/(1024*1024),2)total_bytes,
round(max(bytes)/(1024*1024),2)max_bytes
from sys.dba_free_space
group by tablespace_name)f,
(select dd.tablespace_name,
round(sum(dd.bytes)/(1024*1024),2)tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name)d
where d.tablespace_name=f.tablespace_name
order by 1;
尝试修改表空间大小
alter datdabase datafile ‘/mnt/disk/oracle/CSS_APP01.dbf’ resize 30720M;
查询当前用户下的分区表
select * from user_part_tables --查询有分区的表
根据表名查询分区
select partition_name from user_tab_partitions where table_name = 'EQUIPMENT_LOAD_COUNT' order by partition_name --查询分区
一般分区都是按照时间生成的
循环删除一年的分区
declare
v_date date;
v_part_name varchar(50);
begin
v_date := date'2018-12-31';
while v_date >= date'2018-01-01' LOOP
v_part_name := 'P_' || to_char(v_date,'yyyymmdd');
dbms_output.put_line(v_part_name);
execute immediate 'ALTER TABLE EQUIPMENT_LOAD_COUNT DROP PARTITION ' || v_part_name;
v_date := v_date -1;
END LOOP;
end;
删除单个分区
alter table EQUIPMENT_LOAD_COUNT drop partition P_1701_1712 --删除单个分区