·
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
--
查找问题发生时间段15分钟的ash 报告，定位i等待事件是direct path write temp 的sql
select count(*) as y0_ from CHK_RECORD_VIEW this_
insert into OPERATION_LOG (BUSINESS_REF_NO, CREATE_DATETIME, CREATOR, CURRENT_VALUE, MODIFIED_VALUE, MODIFIER, MODIFY_DATETIME, OPERATION_DESC, OPERATION_TYPE, OPERATION_LOG_ID) values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )
@
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 GV$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
@
4、扩展临时表空间：
--方法一、增大临时文件大小：
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m; --方法二、将临时数据文件设为自动扩展： SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited; --方法三、向临时表空间中添加数据文件： SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m AUTOEXTEND ON NEXT 1G MAXSIZE 10g;
alter tablespace <tablespace_name> add datafile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 4096m AUTOEXTEND ON NEXT 1G;
查看文件可扩展性
select file_name,autoextensible from dba_data_files where tablespace_name = 'UNDOTBS1';
select df.tablespace_name,df.autoextensible from dba_temp_files df where df.autoextensible='NO';
5.
--常规查询表空间使用情况 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
--极端查询表空间使用情况 (有表空间已经100%)
select a.tablespace_name,all_sum "all_sum(M)",
to_char(nvl(free_sum,0),'9,999,990.00')||'M' free_,
to_char(100*nvl(free_sum,0)/all_sum,'900.00')||'%' free_percentage
from
(select tablespace_name,sum(bytes)/1024/1024 all_sum
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_sum
from dba_free_space
group by tablespace_name) c
where
a.tablespace_name = c.tablespace_name(+)
order by 4;
--查询表空间使用率
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB, Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 - free.MB / total.MB ) * 100, 2) || '%' AS Used_Pct FROM (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total WHERE free.tablespace_name = total.tablespace_name;
select file_id,file_name,tablespace_name,autoextensible from dba_data_files;
SQL> alter tablespace users add datafile ‘/u01/app/oracle/oradata/orcl/data02.dbf’ size 100m AUTOEXTEND ON NEXT 1G MAXSIZE 10g;
d
@
http://www.cnblogs.com/vipsoft/archive/2012/11/28/2792431.html
3.拼接成一句话查询表空间的方法，这里考虑到 表空间可以扩展到的最大空间（maxbytes）,而不是当前大小(current bytes)
SELECT TRIM(DBMS_LOB.SUBSTR(WM_CONCAT(DATAVAL))) VALUE
FROM (SELECT 'CNT=' || MAX(FLAG) AS DATAVAL
FROM (SELECT '0' FLAG
FROM (SELECT a.tablespace_name
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(CASE autoextensible
WHEN 'YES' THEN
maxbytes
ELSE
bytes
END) maxbytes,
SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
and b.tablespace_name NOT IN
('UNDOTBS1', 'UNDOTBS2')
and round((total - free) / b.maxbytes, 4) * 100 < 90)
UNION ALL
SELECT '1' FLAG
FROM (SELECT a.tablespace_name
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(CASE autoextensible
WHEN 'YES' THEN
maxbytes
ELSE
bytes
END) maxbytes,
SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
and b.tablespace_name NOT IN
('UNDOTBS1', 'UNDOTBS2')
and round((total - free) / b.maxbytes, 4) * 100 >= 90)
UNION ALL
SELECT '2' FLAG
FROM (SELECT a.tablespace_name
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
SUM(CASE autoextensible
WHEN 'YES' THEN
maxbytes
ELSE
bytes
END) maxbytes,
SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
and b.tablespace_name NOT IN
('UNDOTBS1', 'UNDOTBS2')
and round((total - free) / b.maxbytes, 4) * 100 >= 95))
UNION ALL
SELECT TRIM(DBMS_LOB.SUBSTR(WM_CONCAT(ABC))) DATAVAL
FROM (SELECT 'NAME=' || TABLESPACE_NAME || ':SUM=' || SUM_SPACE ||
':USED=' || USED_SPACE AS ABC
FROM (SELECT D.TABLESPACE_NAME,
SPACE SUM_SPACE,
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) USED_SPACE
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)) A));
---
这个sql 文件如果执行的结果是cnt=0的话，应该表空间会小于90%。
只有出现CNT=1,或者CNT=2的时候，才会出现这个报警。那就是表空间大于90%，或者大于95%。
---表空间碎片查询
set pages 999
col tsname format a16 justify c heading 'Tablespace'
col nfrags format 999,990 justify c heading 'Free|Frags'
col mxfrag format 999,999 justify c heading 'Largest|Frag (MB)'
col totsiz format 999,999 justify c heading 'Total|(MB)'
col avasiz format 999,999 justify c heading 'Available|(MB)'
col pctusd format 990 justify c heading 'Pct|Used'
select total.TABLESPACE_NAME tsname,
D nfrags,
C/1024/1024 mxfrag,
A/1024/1024 totsiz,
B/1024/1024 avasiz,
(1-nvl(B,0)/A)*100 pctusd
from
(select sum(bytes) A,
tablespace_name
from dba_data_files
group by tablespace_name) TOTAL,
(select sum(bytes) B,
max(bytes) C,
count(bytes) D,
tablespace_name
from dba_free_space
group by tablespace_name) FREE
where
total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
/
---
通过dbms_lob.substr（）转换clob字段为varchar2类型
http://blog.csdn.net/liou825/article/details/7971865
--
首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用
准备测试数据http://blog.csdn.net/ojerryzuo/article/details/53927057
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
