oracle查看表空间数据文件使用情况

-- 查看表空间数据文件使用情况
select a.*, round(a.usedgb/a.maxgb*100) || '%' usedPer from (
select t.TABLESPACE_NAME,
       t.FILE_NAME,
       round(bytes / 1024 / 1024 / 1024, 2) usedGB,
       round(maxbytes / 1024 / 1024 / 1024, 2) maxGB
  from dba_data_files t
 order by tablespace_name) a;

-- 查看表大小
select b.TABLESPACE_NAME, b.TABLE_NAME, b.NUM_ROWS, round(bytes / 1024 / 1024 / 1024, 2) usedGB
  from DBA_SEGMENTS t
  inner join all_tables b on t.segment_name = b.TABLE_NAME
 where t.owner = 'JHPT'
   and t.segment_type = 'TABLE'
 order by 4 desc;

-- 查看索引大小
select t.segment_name, round(sum(bytes) / 1024 / 1024 / 1024, 2) usedGB
  from DBA_SEGMENTS t
 where t.owner = 'JHPT'
   and t.segment_type = 'INDEX'
 group by t.segment_name
 order by 2 desc;
 
 

　　一个表空间最大32G

如果不够需要在添加一个空间文件

-创建表空间
create tablespace JHPT
datafile 'D:\orcldata\JHPT.dbf' size 10240m reuse
autoextend on next 1024m;

alter tablespace JHPT
add datafile 'D:\orcldata\JHPT2.dbf' size 10240m reuse
autoextend on next 1024m;

转载于:https://www.cnblogs.com/wlzhang/p/4845555.html