Oracle数据库查询表空间在硬盘占用情况

1.查看各表空间的硬盘占用情况

SELECT
	A.TABLESPACE_NAME 表空间名称,
	Round( A.BYTES, 2 ) "总空间(G)",
	Round( B.BYTES, 2 ) "未用空间(G)",
	Round( A.BYTES - B.BYTES, 2 ) "已用空间(G)",
	ROUND( ( ( A.BYTES - B.BYTES ) / A.BYTES ) * 100, 2 ) "使用率%" 
FROM
	( SELECT TABLESPACE_NAME, SUM( BYTES ) / ( 1024 * 1024 * 1024 ) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) A,
	( SELECT TABLESPACE_NAME, SUM( BYTES ) / ( 1024 * 1024 * 1024 ) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) B 
WHERE
	A.TABLESPACE_NAME = B.TABLESPACE_NAME 
ORDER BY
	( ( A.BYTES - B.BYTES ) / A.BYTES ) DESC;

SELECT
	Upper( F.TABLESPACE_NAME ) "表空间名",
	D.TOT_GROOTTE_MB "表空间大小(Mb)",
	To_char( D.TOT_GROOTTE_MB ) || ' Mb' "表空间大小(Mb)",
	D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(Mb)",
	To_char( Round( ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2 ), '990.99' ) || '%' "使用比",
	F.TOTAL_BYTES "空闲空间(Mb)",
	F.MAX_BYTES "最大块(Mb)" 
FROM
	(
	SELECT
		TABLESPACE_NAME,
		Round( Sum( BYTES ) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES,
		Round( Max( BYTES ) / ( 1024 * 1024 ), 2 ) MAX_BYTES 
	FROM
		SYS.DBA_FREE_SPACE 
	GROUP BY
		TABLESPACE_NAME 
	) F,
	(
	SELECT
		DD.TABLESPACE_NAME,
		Round( Sum( DD.BYTES ) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB 
	FROM
		SYS.DBA_DATA_FILES DD 
	GROUP BY
		DD.TABLESPACE_NAME 
	) D 
WHERE
	D.TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY
	Round( ( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2 ) DESC

2 给表空间扩容

ALTER TABLESPACE 表空间名称 ADD DATAFILE 'D:\app\JC2\oradata\表空间名称\xxx.DBF' SIZE 32000M;

3 查询密码有效期

如下图所示，表示密码即将过期

查询密码有效期

 SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

设置密码永不过期

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;