Oracle数据库查看表空间sql语句

 

​​Oracle数据库查看表空间sql语句​​

 ​

1、oracle查看表空间当前用户

select 
 username,default_tablespace 
from user_users;

• • 
    

2、oracle 查看表所属表空间

SELECT 
 TABLE_NAME,TABLESPACE_NAME 
FROM USER_TABLES 
where TABLE_NAME = 'test_table'

• 
  

3、oracle查看表空间大小(单位不是GB)

SELECT 
 a.tablespace_name                        "表空间名",
 total                                    "表空间大小",
 free                                     "表空间剩余大小",
 ( total - free )                         "表空间使用大小",
  Round(( total - free ) / total, 4) * 100 "使用率   %"
FROM   (SELECT tablespace_name,
               Sum(bytes) free
        FROM   DBA_FREE_SPACE
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name,
               Sum(bytes) total
        FROM   DBA_DATA_FILES
        GROUP  BY tablespace_name) b
WHERE  
  a.tablespace_name = b.tablespace_name

 

4、oracle查看表空间大小 -单位GB

SELECT 
 a.tablespace_name    "表空间名", 
 total                "表空间大小", 
 free                 "表空间剩余大小", 
 (total - free)        "表空间使用大小", 
 total / (1024 * 1024 * 1024) "表空间大小(G)", 
 free / (1024 * 1024 * 1024)   "表空间剩余大小(G)", 
 (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
       FROM dba_free_space 
        GROUP BY tablespace_name) a, 
    (SELECT tablespace_name, SUM(bytes) total 
     FROM dba_data_files 
       GROUP BY tablespace_name) b 
WHERE 
a.  tablespace_name = b.tablespace_name;