oracle查看表空间剩余

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;