数据库常用脚本

查询ORACLE版本

SELECT * FROM V$VERSION;

查询表空间使用情况

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(BATES) FREE
	FROM DBA_FREE_SPACE
	GROUP BY TABLESPACE_NAME) A,
	(SELECT
		TABLESPACE_NAME,
		SUM(BATES) TOTAL
	FROM DBA_DATA_FILES
	GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY A.TABLESPACE_NAME;

查询剩余未分配表空间情况

SELECT
	T.NAME,
	T.TATAL_MB / 1024 AS "总大小(G)",
	T.FREE_MB / 1024 AS "剩余大小(G)",
	T.FREE_MB * 100 / T.TOTAL_MB AS "剩余百分比(%)",
	(T.TOTAL_MB - T.FREE_MB) / 1024 AS "使用大小(G)"
FROM V$ASM_DISKGROUP T;

查询dump目录

SELECT * FROM DBA_DIRECTORIES;

查询表大小

select segment_name, bytes
from dba_segments
where segment_type = ‘TABLE’;