Year: 2014

Oracle Temporary Table Space size

arcsdegeoLeave a comment

As a DBA you may face a problem with your database if TEMP tablespace gets full. Usually, TEMP table space is used for “sorting and aggregate operations“.

One of the reasons your TEMP tablspace get full and expanding, is that the application queries are not well optimized/tuned.

the following queries will eventually help you in diagnosis:

To Find SQL Statements that is causing TEMP SPACE TO BE FILLED:
//——————————————————————–

select s.sid || ‘,’ || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by s.sid;
/ /———————————————————————
To Find Current TEMP SPACE SIZE:

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper(‘&&temp_tsname’) group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper(‘&&temp_tsname’) group by b.tablespace_name);

Convert BLOB TO CLOB column – METHOD 2

arcsdegeo1 Comment

in my previous post, i have provided a way to convert BLOB to CLOB.

There is another way to do the conversion, based on Oracle Metalink DocID: 9876

*** Let us assume that the table name is Z.EMPLOYEES . And, let us assume that the column name that has a ‘BLOB’ data type is BIN_EMP. and the Temp column with ‘CLOB’ data type has the name WTF.

1. take export backup (expdp) of the ORIGINAL table.

 

2. add new temp column:

alter table Z.EMPLOYEES add (WTF CLOB);

3. start initialization:

UPDATE Z.EMPLOYEES SET WTF = empty_clob() WHERE BIN_EMP IS NOT NULL;

4. create the procedure for conversion, the following procedure will perform the conversion:

create or replace procedure Z.blob_to_clob_PROC as
a number;
b number;
c number := 1000;
d number;
offset number := 1;
v_clob clob;
amount binary_integer;
buffer1 raw(1000);
buffer2 varchar2(1000);
cursor c1 is select BIN_EMP, rowid from Z.EMPLOYEES where BIN_EMP is not null;
begin
for i in c1 loop
select WTF into v_clob from Z.EMPLOYEES where rowid = i.rowid for update;
a := dbms_lob.getlength(i.BIN_EMP);
b := ceil(a/c);
for j in 1..b loop
if (c*j <= a) then
d:= c;
else
d:= a – c*(j-1);
end if;
DBMS_LOB.READ ( i.BIN_EMP, d, offset, buffer1);
buffer2 := utl_raw.cast_to_varchar2(buffer1);
DBMS_LOB.WRITEAPPEND(v_clob, d, buffer2);
offset := offset + c;
end loop;
offset := 1;
update Z.EMPLOYEES set WTF = v_clob where rowid=i.rowid;
end loop;
commit;
end;
/

5. execute the following statements:

EXECUTE Z.blob_to_clob_PROC;
commit;

Remark: you can develop Unix Shell script for automating the execution of the procedure to be scheduled.

6. drop the original column with BLOB data type (BIN_EMP)

7.  rename the column WTF to BIN_EMP