查询某张表的大小：
select sum(bytes)/1024/1024 "表大小(M)" from user_segments where segment_name='table_name'; --注，仅表数据的大小，不含索引、分区、LOB类型
select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name having Segment_Name='table_name';
查询某表空间内表的大小：
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
FROM dba_segments
WHERE tablespace_name = 'SYSTEM'
ORDER BY BYTES DESC)
WHERE ROWNUM < 10;
select A.SEGMENT_NAME,SUM(A.BYTES)/1024/1024/1024 GB,A.SEGMENT_TYPE
from dba_extents a,v$datafile b,v$tablespace C
where b.TS#=C.TS# AND C.NAME='SYSTEM' AND A.FILE_ID=B.FILE#
GROUP BY A.SEGMENT_NAME,A.SEGMENT_TYPE ORDER BY GB DESC;
select DECODE( PARTITION_NAME, NULL, segment_name, segment_name || ':' || PARTITION_NAME) segment_name ,
segment_type, nvl(initial_extent, 0) initial_extent, nvl(next_extent, 0) next_extent,
nvl(extents, 0) extents, nvl(bytes, 0) bytes, Owner, nvl(max_extents, 0)max_extents
from dba_segments
where tablespace_name = 'SYSTEM'
order by bytes desc;
select owner from dba_tables where table_name='表名';
查询表所属哪个表空间：
select table_name,tablespace_name from user_tables where table_name='AUD$'
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ SYSTEM
select b.file_name,a.owner,a.segment_name from dba_extents a,dba_data_files b
where a.file_id=b.file_id and a.segment_name=‘$tablename’ and a.owner=‘&user’
根据区找到数据文件号，然后根据文件号在dba_data_files找到数据文件的路径及名字
如：
select b.file_name,a.owner,a.segment_name from dba_extents a,dba_data_files b
where a.file_id=b.file_id and a.segment_name='AUD$' and a.owner='SYS';
查看某个表空间中有哪些数据文件：
SELECT ddf.file_name,ddf.bytes/1024/1024 "Sum MB"
FROM Dba_Data_Files ddf
where ddf.tablespace_name = 'SYSTEM';
例：
/oracleDB/DATA/hebsjb/system01.dbf 32730
/oracleDB/DATA/hebsjb/SYSTEM3.dbf 10240
/oracleDB/DATA/hebsjb/system02.dbf 20480
/oracleDB/DATA/hebsjb/USER5.dbf 20480
/oracleDB/DATA/hebsjb/system04.dbf 10240
/oracleDB/DATA/hebsjb/system05.dbf 10240
/oracleDB/DATA/hebsjb/system06.dbf 10240
/oracleDB/DATA/hebsjb/system07.dbf 10240
/oracleDB/DATA/hebsjb/system08.dbf 10240
/oracleDB/DATA/hebsjb/system09.dbf 10240
/oracleDB/DATA/hebsjb/system10.dbf 10240
/oracleDB/DATA/hebsjb/system11.dbf 10240
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29812751/viewspace-1717418/ ，如需转载，请注明出处，否则将追究法律责任。
