oracle查看表空间剩余

833kh7ze0g

with t1 as (
select tablespace_name
,round(sum(bytes)/1024/1024/1024 ,3 ) 剩余空间G
from dba_free_space
group by tablespace_name
),t2 as (
select tablespace_name
,round(sum(bytes)/1024/1024/1024 ,3 ) 空间大小G
from dba_data_files
group by tablespace_name
)
select
t1.tablespace_name as 表空间
,t2.空间大小G
,t1.剩余空间G
,round(100*t1.剩余空间G/t2.空间大小G,2) as "空间剩余%"
from t1
join t2 on t1.tablespace_name = t2.tablespace_name
order by 4 ;
--**************************************************************--
--解决临时表空间不足问题。
select username,default_tablespace,temporary_tablespace
from dba_users
where username='STG';

select file_name,tablespace_name
from dba_data_files
where tablespace_name='STG';

create TEMPORARY tablespace TEMPBI TEMPFILE 'F:\oradata\TEMPBI01.dbf' size 30G REUSE;
ALTER USER stg TEMPORARY TABLESPACE TEMPBI;