Oracle 统计超过1GB的单表

最新推荐文章于 2024-07-16 19:29:55 发布

Eamon_Jun

最新推荐文章于 2024-07-16 19:29:55 发布

阅读量373

点赞数

分类专栏： sql 文章标签： oracle dba 数据库

本文链接：https://blog.csdn.net/m17601624170/article/details/128202981

版权

sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SELECT
	*
FROM
	(
	SELECT
		OWNER,
		TABLE_NAME,
		SUM(tabsize) table_size
	FROM
		(
		SELECT
			owner,
			SEGMENT_NAME AS table_name,
			SUM(BYTES)/ 1024 / 1024 / 1024 AS tabsize
		FROM
			DBA_SEGMENTS
		WHERE
			(OWNER,
			SEGMENT_NAME) IN (
			SELECT
				OWNER,
				TABLE_NAME
			FROM
				DBA_TABLES
			WHERE
				OWNER NOT IN ('SYS',
				'SYSTEM',
				'DBSNMP'))
		GROUP BY
			SEGMENT_NAME,
			owner
	UNION ALL
		SELECT
			lob.owner,
			lob.table_name,
			SUM(seg.bytes)/ 1024 / 1024 / 1024 AS tabsize
		FROM
			dba_lobs lob,
			dba_segments seg
		WHERE
			lob.segment_name = seg.segment_name
			AND lob.OWNER NOT IN ('SYS',
			'SYSTEM',
			'DBSNMP')
		GROUP BY
			lob.owner,
			lob.table_name )
	GROUP BY
		OWNER,
		TABLE_NAME
	ORDER BY
		3 DESC)
WHERE
	table_size>1 ;