среда, 15 июля 2009 г.

How to monitor space in tablespace?

SELECT *
FROM
(
SELECT t.name
, max(tablespace_size*block_size/1024/1024) size_meg
, round(max(tablespace_usedsize*block_size/1024/1024)) used_meg
, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) t
FROM dba_hist_tbspc_space_usage u
, v$tablespace t
, dba_tablespaces d
WHERE t.ts# = tablespace_id
and d.tablespace_name = t.name
GROUP BY
t.name
, trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
ORDER BY
name, t DESC
)
WHERE ROWNUM <= 300
;

Update BLOB

set define off DECLARE    vb1 CLOB := 'long text';    vb2 CLOB :=                 'long text';    vb3 CLOB :=              ...