查看表空间: (系统管理员登录)显示该实例下所有的表空间大小
SELECT T.TABLESPACE_NAME,ROUND(SUM(BYTES/(1024*1024)),0) TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
查询相关用户的表空间:(系统管理员登录)
SELECT USERNAME, DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE USERNAME = 'TEST'
查看当前用户的表存放在哪个表空间:(当前用户登录)
SELECT TABLE_NAME, TABLESPACE_NAME FROM USER_TABLES
查看剩余空间:
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
查看用户下表占的空间大小:
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'TABLE'
GROUP BY SEGMENT_NAME
查看索引占用空间:
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
GROUP BY SEGMENT_NAME