Oracle: usage of the tablespaces (permanent and temporary)
Friday, March 28th, 2008SET pagesize 10000
SET COLSEP ‘|’
SET VERIFY off
SET serveroutput ON SIZE 1000000
BREAK ON report
COLUMN tablespace_name format a30 heading ‘TABLESPACE’
COLUMN sizegb format 9999999999D9 heading ‘SIZE-Gb’
COLUMN usedproc format 999D99 heading ‘USED-%’
COLUMN status format a10 heading ‘STATUS’
COMPUTE SUM LABEL ‘Total size:’ OF sizegb ON report
SELECT b.tablespace_name ,
b.bytes/1024/1024/1024 AS sizegb ,
NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
[...]
List the tablespaces in backup mode
Wednesday, February 28th, 2007Small query to list Oracle tablespaces, that are in backup mode:
SELECT d.tablespace_name, b.time
FROM dba_data_files d, v$backup b
WHERE
d.file_id = b.FILE#
AND b.STATUS = ‘ACTIVE’ ;
Usage of the temp tablespace in Oracle
Friday, February 24th, 2006First find the block size:
select value “BLOCK_SIZE” from v$parameter where name=’db_block_size’ ;
Now check the usage of the tablespace:
select s.username “USER”,s.sid,s.osuser,
u.tablespace “TS” ,
sum(u.blocks) * &BLOCK_SIZE./1024/1024 MB,
x.sql_text
from v$session s,v$sort_usage u,v$sqltext x
where s.saddr=u.session_addr and s.sql_address=x.address
group by s.sid, s.username, osuser, tablespace, sql_text, address, piece
order by sid, piece asc;