Query oracle space occupation

最新推荐文章于 2024-12-19 10:56:56 发布

转载最新推荐文章于 2024-12-19 10:56:56 发布 · 97 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/cyy1029/p/4664782.html

文章标签：

#数据库

本文介绍如何通过SQL命令调整Oracle数据库的数据文件大小，并提供了查询表空间使用情况的方法，包括已用空间、剩余空间及百分比。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' resize 4096M; SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)", FREE.FREE_BYTES AS "FREE SPACE(IN GB)" FROM (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED INNER JOIN (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME); select df.tablespace_name "Tablespace", totalusedspace "Used MB", (df.totalspace - tu.totalusedspace) "Free MB", df.totalspace "Total MB", round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name from dba_segments group by tablespace_name) tu where df.tablespace_name = tu.tablespace_name ;

转载于:https://www.cnblogs.com/cyy1029/p/4664782.html