ora-01652无法通过128(在表空间temp中)扩展temp段

 

 

 

SELECT TMP.file_name FILENAME, BYTES,autoextensible, TS.NAME TABLESPACE FROM dba_temp_files TMP, V$TABLESPACE TS WHERE TMP.tablespace_name=TS.name

·
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;
##不计算MAXBYTES
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

 

##计算MAXBYTES (在所有tempfile autoextend 为on 的情况下，执行这条sql,不然以下sql 可能会报错，如果报错，使用上面的sql 执行即可)

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(MAXBYTES) / 1024 / 1024 total
    FROM dba_temp_files
    GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name

 或者

 

##不计算扩展性。
SELECT
Round(Nvl(used_space, 0) / SPACE * 100, 0) "USED_RATE(%)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name='TEMP'

##计算扩展性。
SELECT
Round(Nvl(used_space, 0) / SPACE * 100, 0) "USED_RATE(%)"
FROM (SELECT tablespace_name,
Round(SUM(MAXBYTES) / ( 1024 * 1024 ), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
AND D.tablespace_name='TEMP'

 

##关于临时表空间查询，更详细的， 包括cacheed , 当前使用的curren_used

https://blog.51cto.com/peenboo/2089116

SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024 / 1024, 0), '99,999,990.900') "Size (G)",
TO_CHAR(NVL(t.hwm, 0) / 1024 / 1024 / 1024, '99999999.999') "HWM (G)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % ",
TO_CHAR(NVL(t.bytes / 1024 / 1024 / 1024, 0), '99999999.999') "Using (G)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
-----------------------------------
©著作权归作者所有：来自51CTO博客作者PengChonggui的原创作品，请联系作者获取转载授权，否则将追究法律责任
ORA-1652:临时表空间异常优化处理
https://blog.51cto.com/peenboo/2089116

 

 ##### 监控临时表空间的用量https://www.modb.pro/db/46752

使用ASH报告基本上都能定位到具体消耗大量临时段的SQL语句，但是这个分析，有时候需要耗费很长时间，在How Can Temporary Segment Usage Be Monitored Over Time? (文档 ID 364417.1)里面介绍了如何一直监控临时段的使用情况。如下所示：

-- Create a table to hold your temporary space monitoring 
-- 最好根据具体情况放入一个表空间，不要放入系统表空间

CREATE TABLE MONITOR_TEMP_SEG_USAGE 
( 
   DATE_TIME  DATE, 
   USERNAME   VARCHAR2(30), 
   SID        VARCHAR2(6), 
   SERIAL#    VARCHAR2(6), 
   OS_USER    VARCHAR2(30), 
   SPACE_USED NUMBER, 
   SQL_TEXT   VARCHAR2(1000) 
); 


--创建存储过程，将消耗临时段的块超过阀值的SQL插入MONITOR_TEMP_SEG_USAGE

CREATE OR REPLACE PROCEDURE MONITOR_TEMP_SEG_USAGE_INSERT IS
BEGIN

INSERT INTO MONITOR_TEMP_SEG_USAGE
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP' --输入具体临时表空间
AND a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) > 1024;
COMMIT;

END;
/


--创建作业,每5分钟运行一次，捕获消耗临时段超过阀值的SQL语句。

SQL> SELECT JOB FROM DBA_JOBS;

       JOB
----------
       141
       142


BEGIN
DBMS_JOB.ISUBMIT(JOB => 20,
WHAT => 'MONITOR_TEMP_SEG_USAGE_INSERT;',
NEXT_DATE => SYSDATE,
INTERVAL => 'SYSDATE + (5/1440)');
COMMIT;
END;
/

 

停下job. Mark the DBMS_JOB job as broken

EXEC DBMS_JOB.BROKEN(20,TRUE);
truncate table MONITOR_TEMP_SEG_USAGE;

 查询文件的大小

SQL> SELECT TMP.NAME FILENAME, BYTES/1024/1024 "M", TS.NAME TABLESPACE
FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;

@

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 temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m AUTOEXTEND ON NEXT 1G MAXSIZE 32g;

alter tablespace <tablespace_name> add datafile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 4096m AUTOEXTEND ON NEXT 1G;

//FOR RAC

alter tablespace temp add tempfile '+NEW_DATA' size 5m AUTOEXTEND OFF;

alter database tempfile '+NEW_DATA/FULLPATH' resize 200m;

 ##删除临时文件

 alter database tempfile  '/oracle/temp02.dbf.20210304051709' drop;

 

 

 查看文件可扩展性

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';

 

print "检查临时空间和undo 表空间的自动扩展性 "
select file_name,tablespace_name,autoextensible from dba_data_files where tablespace_name like 'UNDO%' 
union 
select df.file_name,df.tablespace_name,df.autoextensible from dba_temp_files df

 

print " "

print "如果上一步检查临时空间和undo 表空间的自动扩展性是打开的，可以关闭自动扩展 "
select 'alter database datafile '''||file_name||''' autoextend off;' from dba_data_files where tablespace_name like 'UNDO%' 
union 
select 'alter database tempfile '''||file_name||''' autoextend off;' from dba_temp_files "

 

 

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

 

 

--issue 3：

 

###
sql slowly:  等待事件； event:direct path read

144892689q1bn:

select '- username= ->', username "username", '- time_stamp= ->', to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') "time_stamp", '- statement= ->', action_name "statement", '- os_username= ->', os_username "os_username", '- userhost= ->', userhost "userhost", '- code= ->', returncode||decode(returncode, '1004', '-Wrong Connection', '1005', '-NULL Password', '1017', '-Wrong Password', '1045', '-Insufficient Priviledge', '0', '-Login Accepted', '--') "returncode" from sys.dba_audit_session
where (sysdate - timestamp)*24 < 0.05 and returncode <> 0 and username != 'DB' order by timestamp

 

fix: 清空aud$文件：

-- 取消所有已配置的审计项目
NOAUDIT ALL PRIVILEGES;
NOAUDIT ALL STATEMENTS;
NOAUDIT CREATE SESSION BY DBMONOPR;
NOAUDIT CREATE SESSION BY IQMSDATA;
NOAUDIT CREATE SESSION BY IQMSOPR;
NOAUDIT PUBLIC SYNONYM;
NOAUDIT DATABASE LINK;
NOAUDIT ROLE;
NOAUDIT PROFILE;
NOAUDIT DIRECTORY;
NOAUDIT SYSTEM GRANT;
NOAUDIT EXEMPT ACCESS POLICY;

-- 确认已没有审计项目
select * from DBA_PRIV_AUDIT_OPTS;
select * from DBA_STMT_AUDIT_OPTS;

-- 删除已有的审计记录
select count(*) from sys.aud$;
CREATE TABLE backup_aud AS SELECT * from sys.aud$;
truncate table aud$;

 

 

#########sample 2

碰到了lob temp segment 太大，并且只要连接一直没断开，这个lob temp segment 就没办法释放

（

The space is not released until the session exits. That can easily lead to an ORA-1652 error when multiple concurrent sessions are doing a huge LOB operations and not exiting, thus the freed space by DBMS_LOB.FREETEMPORARY is only available within the calling session but not for the other sessions.

）

解决办法

1.修改程序，加入显性释放信号

 

dbms_lob.freetemporary(clb);

 

或者

 

开发提交几百万笔数据后，就自动端口释放资源，然后重新连接，重新使用temp

 

2. DBA 级别可以设置如下隐含参数，或者强行kill 掉 事务来释放

比如

alter session set events '60025 trace name context forever';

 

比如：

logon触发器来实现该功能。

create or replace trigger sys.login_db after logon on database

begin

execute immediate 'alter session set events ''60025 trace name context forever''';

end;

 

 

 

How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.5.0 and later
Information in this document applies to any platform.

GOAL
How to release Temp LOB Segment that has been created explicitly or implicitly by Oracle for intermediate stages of database processing.

SOLUTION
Prior to 10.2.0.4
=============

Actually we have different kinds of Temporary Segments. Oracle often requires temporary work space for intermediate stages of database processing. There are different kinds of temporary segments in the database. Some of them are created explicitly by the users. The others are created and accessed for the user by the system.

Use the view V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS or V$SORT_SEGMENT to determine how much space is being used by temporary lobs. We can create an explicit temporary BLOB or CLOB and its corresponding index in the user's default tablespace calling DBMS_LOB.CREATETEMPORARY procedure, and free them by calling DBMS_LOB.FREETEMPORARY.

When calling DBMS_LOB.CREATETEMPORARY, TWO temporary extents are allocated to store LOB_DATA and one temporary extent to store LOB_INDEX in 8i. So, a total of three temporary extents are allocated in 8i. However, in 9i (Release 2) and up, only one temporary extent is allocated .

DBMS_LOB.CREATETEMPORARY can be used with limited success prior to 10.2.0.4.

The only true solution prior to 10.2.0.4 and the setting of the event (as discussed below) is to terminate the session that created the temporary lob.

 

10.2.0.4 and above
===============

Two approaches are available:

1- You can use DBMS_LOB.FREETEMPORARY where the LOB locator that was freed is marked as invalid.

DBMS_LOB.FREETEMPORARY frees space from temp tablespace and it is available to that same session, but the temp segment is not released and made available to other sessions. So if the session creates another temp lob after freetemporary, the space is reused by that session.
The space is not released until the session exits. That can easily lead to an ORA-1652 error when multiple concurrent sessions are doing a huge LOB operations and not exiting, thus the freed space by DBMS_LOB.FREETEMPORARY is only available within the calling session but not for the other sessions.

-- ========
-- Session1
-- ========
-- SQL*Plus: Release 10.2.0.4.0 - Production on Tue Apr 7 09:06:31 2009

-- Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

-- Connected to:
-- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
-- With the Partitioning, OLAP, Data Mining and Real Application Testing options

declare
clb clob;
ch varchar2(32767);
k number;
begin
dbms_lob.createtemporary(clb,true,dbms_lob.call);
for i in 1..1500 loop
ch:=lpad('o',32767,'Y');
dbms_lob.writeappend(clb,length(ch),ch);
end loop;
k:=dbms_lob.getlength(clb);
dbms_lob.freetemporary(clb);
dbms_output.put_line('the clob length: '||k);
end;
/

-- PL/SQL procedure successfully completed.

select u.tablespace, u.contents, u.segtype, u.extents, u.blocks, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
AND UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;
TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1
declare
clb clob;
ch varchar2(32767);
k number;
begin
dbms_lob.createtemporary(clb,true,dbms_lob.call);
for i in 1..1500 loop
ch:=lpad('o',32767,'Y');
dbms_lob.writeappend(clb,length(ch),ch);
end loop;
k:=dbms_lob.getlength(clb);
dbms_lob.freetemporary(clb);
dbms_output.put_line('the clob length: '||k);
end;
/

-- PL/SQL procedure successfully completed.

select u.tablespace, u.contents, u.segtype, u.extents, u.blocks, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
and UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;
TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1

<<<<<<<<<<<<< Only 4 rows selected >>>>>>>>>>>>>>>>
-- =========
-- session2
-- =========

declare
clb clob;
ch varchar2(32767);
k number;
begin
dbms_lob.createtemporary(clb,true,dbms_lob.call);
for i in 1..1500 loop
ch:=lpad('o',32767,'Y');
dbms_lob.writeappend(clb,length(ch),ch);
end loop;
k:=dbms_lob.getlength(clb);
dbms_lob.freetemporary(clb);
dbms_output.put_line('the clob length: '||k);
end;
/

-- PL/SQL procedure successfully completed.

select u.tablespace, u.contents, u.segtype, u.extents, u.blocks, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
and UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;
TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1

6 rows selected.
-- When we disconnect session 2 now and run the same query from session 1 again we will get 4 rows only

select u.tablespace, u.contents, u.segtype, u.extents, u.blocks, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
and UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;
TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1

2-Exiting the session where are the TEMP segments will be freed completely.

10.2.0.4 and above
===============

In addition to the above approaches For 10.2.0.4 and above a new event introduced (event 60025) where when set if there are no active temp lobs in the session (ie: both cache temp lob and no-cache temp lobs used are zero) then the temp segment itself will also be freed releasing the space for other sessions to use. Note that this change is disabled by default. You can set this using alter system in the system level also.

alter session set events '60025 trace name context forever';

 

 

 

 

##########sample 3

参考文档 https://blog.csdn.net/zonelan/article/details/8448407

UNDO 表空间重建（清理）

 

Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).
查看表空间使用情况
SELECT a.tablespace_name,ROUND (a.total_size) "total_size(MB)",ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",ROUND (b.free_size, 3) "free_size(MB)",ROUND (b.free_size / total_size * 100, 2) || '%' free_rate FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size FROM dba_data_files GROUP BY tablespace_name) a, ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size FROM dba_free_space GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name(+);

现在我们可以采用如下步骤回收UNDO空间:
1.确认文件
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';
2.检查UNDO Segment状态
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
3.创建新的UNDO表空间
SQL> create undo tablespace undotbs3 datafile '/u01/app/oradata/TJ/undotbs301.dbf' size 150M reuse AUTOEXTEND ON;
Tablespace created.

4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs3;
System altered.

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

6.删除原UNDO表空间
alter tablespace undotbs1 offline
drop tablespace undotbs1 including contents and datafiles;
7.检查空间情况
日后增加数据文件方法
ALTER TABLESPACE undotbs3 ADD DATAFILE '/u01/app/oradata/TJ/undotbs303.dbf' size 150M reuse AUTOEXTEND ON;