首页 / ORACLE / Oracle表空间查询
Oracle表空间查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle表空间查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3495字,纯文字阅读大概需要5分钟。
内容图文
![Oracle表空间查询](/upload/InfoBanner/zyjiaocheng/916/7215c50dac524fceb367fe385e60ae37.jpg)
表名带有“DBA”的需要有DBA权限才能访问。
表空间:SELECT * FROM DBA_TABLESPACES T;
表文件:SELECT * FROM DBA_DATA_FILES T;
控制文件 :SELECT * FROM V$CONTROLFILE;
日志文件:SELECT * FROM V$LOGFILE;
--查看表空间的名称及大小
SELECT T.TABLESPACE_NAME,
ROUND(SUM(BYTES / (1024 * 1024)), 0) || 'M' TS_SIZE
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME;
--查看表空间物理文件的名称及大小
SELECT TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
ROUND(BYTES / (1024 * 1024), 0) || 'M' TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
--查看回滚段名称及大小
SELECT SEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
(INITIAL_EXTENT / 1024) || 'K' AS INITIALEXTENT,
(NEXT_EXTENT / 1024) || 'K' AS NEXTEXTENT,
MAX_EXTENTS,
V.CUREXT CUREXTENT
FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
WHERE R.SEGMENT_ID = V.USN(+)
ORDER BY SEGMENT_NAME;
--查看表空间的使用情况
SELECT SUM(BYTES) / (1024 * 1024) || 'M' AS FREE_SPACE, TABLESPACE_NAME
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;
SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
TO_CHAR((B.BYTES * 100) / A.BYTES, 'fm9999990.00') AS "% USED ",
TO_CHAR((C.BYTES * 100) / A.BYTES, 'fm9999990.00') AS "% FREE "
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
--查看数据库库对象
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT#
FROM ALL_OBJECTS
GROUP BY OWNER, OBJECT_TYPE, STATUS;
--查看数据库的版本
SELECT VERSION
FROM PRODUCT_COMPONENT_VERSION
WHERE SUBSTR(PRODUCT, 1, 6) = 'Oracle';
--查看数据库的创建日期和归档方式
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
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(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY (TOTAL - FREE) / TOTAL DESC;
--查看数据量大的表
SELECT (T.BYTES / 1024 / 1024) || 'M', T.*
FROM USER_SEGMENTS T
WHERE T.BYTES IS NOT NULL
ORDER BY T.BYTES DESC;
--查看索引大小
SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 || 'M'
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
GROUP BY SEGMENT_NAME
ORDER BY SUM(BYTES) DESC;
--查看表大小
SELECT T.OWNER, T.SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 || 'M' AS BYTESM
FROM DBA_SEGMENTS T
WHERE T.TABLESPACE_NAME = 'USERS'
AND T.SEGMENT_TYPE = 'TABLE'
GROUP BY T.OWNER, T.SEGMENT_NAME
ORDER BY SUM(BYTES) DESC;
--查看当前用户名下所有表
SELECT * FROM ALL_TABLES WHERE OWNER = 'USERS';
--查看指定表空间下的所有表
SELECT *
FROM DBA_TABLES T
WHERE TABLESPACE_NAME = 'USERS'
AND T.OWNER = 'TA_TEST_GZNS'
ORDER BY T.TABLE_NAME;
SELECT * FROM DBA_DATA_FILES T;
SELECT T.TABLESPACE_NAME,
ROUND(SUM(BYTES / (1024 * 1024)), 0) || 'M' TS_SIZE
FROM DBA_DATA_FILES T
GROUP BY T.TABLESPACE_NAME;
内容总结
以上是互联网集市为您收集整理的Oracle表空间查询全部内容,希望文章能够帮你解决Oracle表空间查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。