查总空间
select a. tablespace_name, a.gb freegb,b.gb-a.gb usergb,b.gb totalgb from (
select s.TABLESPACE_NAME,rround(sum(s.BYTES)/1024/1024/1024,2) gb from dba_free_space s
group by s.TABLESPACE_NArME) a,
(select s.TABLESPACE_NAME,round(sum(s.BYTES)/1024/1024/1024,2) gb from dba_data_files s
group by s.TABLESPACE_NAMrE) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME;
查表的大小
select s.owner, s.tablespace_name,s.segment_type, s.segment_name,round(sum(s.BYTES)/1024/1024/1024,2) gb from dba_segments s
where 1=1 /* and s.tablespace_name like 'USERS%' AND s.segment_type LIKE 'TABLE%'*/
and s.owner='BDC_NEW_USER2'
--and s.segment_name ='BDC_NEW_001'
--and s.segment_name not in ('BDC_NEW_001','BDC_NEW_002','BDC_DMS_001''BDC_DMS_002')
group by s.segment_name,s.tablespace_name,s.segment_type,s.owner
order by 5 desc ;
再查表空间对应的文件,用下面的命令改路径增加空间
select s.FILE_NAME,s.BYTES/1024/1024 mb ,s.MAXBYTES/1024/1024 mb2
from dba_data_files s where s.TABLESPACE_NAME='TS_BDCNEW';
--AND s.BYTES/1024/1024<500;
–增加表空间
ALTER TABLESPACE TS_BDCNEWADD DATAFILE '/DATA1/orcl/datafile/user005.bdf' SIZE 1024M AUTOEXTEND OFF;
–查看剩余空间:
select * from v$asm_diskgroup ;
select * from v$asm_file;
select * from v$asm_filesystem;