oracle 表空间不够了
来源:互联网 发布:面料成衣模拟软件 编辑:程序博客网 时间:2024/05/01 23:03
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS ;
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
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 1
COL NAME FOR A60
SELECT FILE# AS FILE_NUMBER
,NAME AS NAME
,CREATION_TIME AS CREATION_TIME
,BLOCK_SIZE AS BLOCK_SIZE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"
,STATUS AS STATUS
,ENABLED AS ENABLED
FROM V$TEMPFILE;
SET LINESIZE 1200
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A60
SELECT TABLESPACE_NAME AS TABLESPACE_NAME
,FILE_NAME AS FILE_NAME
,BLOCKS AS BLOCKS
,STATUS AS STATUS
,AUTOEXTENSIBLE AS AUTOEXTENSIBLE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024,
MAXBYTES/1024/1024/1024)
AS "MAX_SIZE(G)"
,INCREMENT_BY AS "INCREMENT_BY"
,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE"
FROM DBA_TEMP_FILES;
SELECT *
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
SELECT se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
FROM v$sort_usage su, v$parameter p, v$session se, v$sql s
WHERE p.name = 'db_block_size'
AND su.session_addr = se.saddr
AND s.hash_value = su.sqlhash
AND s.address = su.sqladdr
ORDER BY se.username, se.sid;
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
FROM V$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
ALTER DATABASE TEMPFILE 'D:\NEWTEMP01.DBF' RESIZE 2G;
http://www.cnblogs.com/kerrycode/p/4006840.html
- oracle 表空间不够了
- Oracle表空间不够使用及解决方法
- 邮件服务器空间不够了。
- 表空间不够解决方法
- 磁盘空间不够时,oracle如何腾出空间
- 安装oracle提示/tmp空间不够
- ora-01653表空间不够
- ORACLE 错误 1659,数据库导入dmp临时表空间内存不够
- 数据库表空间不够,需要扩容
- Oracle安装目录空间不够,增加挂载
- oracle表空间删除了,恢复
- oracle 表空间满了 追加数据文件
- oracle 系统表空间满了
- oracle超出了表空间users的空间限量问题
- emulator 的空间不够
- DB2因表空间不够产生load表失败
- DB2 reorg表,因表空间不够出现错误
- DB2 reorg表,因表空间不够出现错误
- Markdown了解与入门(test)
- Java分割字符split与StringTokenizer
- 获取iOS系统emoji动画,并自定义emoji键盘
- libcudart.so.7.5: cannot open shared object file: No such file or directory
- java-js
- oracle 表空间不够了
- spring boot 开发环境搭建,基于spring boot 1.4.2
- 【JAVA 课后习题 12.5】+ 自定义异常类
- CDH5.8 安装记录
- C语言OJ项目参考(1045)插入有序数组中
- 仿微信朋友圈以及新浪微博
- 第14周 项目1 - (1)验证折半查找算法
- opencv3.1自带demo的介绍和运行操作。
- 作用是把一个char组成的字符串循环右移n个。比如原来是“abcdefghi”如果n=2,移位后应该是“hiabcdefgh