--1.查询被X锁的记录信息
SELECT S.SID, O.OBJECT_NAME, F.NAME FILE_NAME, S.ROW_WAIT_BLOCK# LOCKED_BLOCK, S.ROW_WAIT_ROW# LOCKED_ROW
FROM V$SESSION S, USER_OBJECTS O, V$DATAFILE F
WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID
AND S.ROW_WAIT_FILE# = F.FILE#
AND S.SID IN (SELECT DISTINCT SID FROM V$LOCK WHERE REQUEST = 6)
--2.查询某用户每个表占用的空间大小（owner需要根据实际情况录入，注意大写）
SELECT OWNER 用户,
SEGMENT_NAME 段名,
PARTITION_NAME 分区名,
SEGMENT_TYPE 段类型,
TABLESPACE_NAME 表空间,
BYTES 大小b,
BYTES / 1024 / 1024 大小m
FROM DBA_SEGMENTS
WHERE OWNER = 'TEST';
--3.表空间占用率的统计
SELECT TABLESPACE_NAME, --表空间名称
TO_CHAR(ROUND(SUM_MAX / 1024 / 1024, 2)) || ' M' AS CAPA_MAX, --从扩充后角度看，最大大小
TO_CHAR(ROUND((SUM_ALLOC - NVL(SUM_FREE, 0)) / 1024 / 1024, 2)) || ' M' AS CAPA_USED, --目前使用空间大小
TO_CHAR(ROUND((SUM_MAX - SUM_ALLOC + NVL(SUM_FREE, 0)) / 1024 / 1024,
2)) || ' M' AS CAPA_FREE, --从扩充角度看，剩余空间大小
TO_CHAR(ROUND(100 * (SUM_ALLOC - NVL(SUM_FREE, 0)) / SUM_MAX, 2)) || '%' AS CAPA_USED_P, --从扩充角度看，占用空间百分率
TO_CHAR(ROUND(100 * (SUM_MAX - SUM_ALLOC + NVL(SUM_FREE, 0)) /
SUM_MAX,
2)) || '%' AS CAPA_PER_P, --从扩充角度看，剩余空间百分率
TO_CHAR(ROUND(SUM_ALLOC / 1024 / 1024, 2)) || ' M' AS MAX, --目前分配空间大小
TO_CHAR(ROUND((SUM_ALLOC - NVL(SUM_FREE, 0)) / 1024 / 1024, 2)) || ' M' AS USED, --目前使用空间大小
TO_CHAR(ROUND(NVL(SUM_FREE, 0) / 1024 / 1024, 2)) || ' M' AS FREE, --目前剩余空间大小
TO_CHAR(ROUND(100 * (SUM_ALLOC - NVL(SUM_FREE, 0)) / SUM_ALLOC, 2)) || '%' AS USED_P, --目前空间使用百分比
TO_CHAR(ROUND(100 * NVL(SUM_FREE, 0) / SUM_ALLOC, 2)) || '%' AS PER_P --目前空间剩余百分比
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES) AS SUM_ALLOC, --分配空间大小
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) AS SUM_MAX --扩展后大小
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME),
(SELECT TABLESPACE_NAME AS FS_TS_NAME, SUM(BYTES) AS SUM_FREE --空间空间大小
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE TABLESPACE_NAME = FS_TS_NAME(+);