Home » RDBMS Server » Server Administration » Monitor TABLESPACES 10g
Monitor TABLESPACES 10g [message #253701] Tue, 24 July 2007 11:33 Go to next message
garagonp
Messages: 1
Registered: July 2007
Junior Member
Hi, Im trying to use a script to detect tablespace usage, basically, total space, space used and %, but I have found that for 9i and 10g there is a difference in the way oracle uses temp ts, I have following script:

SELECT
d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Management",
to_char(NVL(a.bytes/1024/1024, 0),'999,999.999') "Size MB",
to_char((NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024),'999,999.999') "Used MB",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM
sys.dba_tablespaces d,
(select
tablespace_name, sum(bytes) bytes
from
dba_data_files
group by
tablespace_name) a,
(select
tablespace_name, sum(bytes) bytes
from
dba_free_space
group by
tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT
d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Management",
TO_CHAR(NVL(a.bytes/1024/1024, 0),'999,999.999') "Size MB",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used MB",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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) 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'
order by 7 desc;

but this seems not to be working for TEMP ts in 10g, can u pls send any advice on this? Am I missing something?

Tks
GAP
Re: Monitor TABLESPACES 10g [message #253702 is a reply to message #253701] Tue, 24 July 2007 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if it is working or not as it is unreadable.

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Monitor TABLESPACES 10g [message #253705 is a reply to message #253701] Tue, 24 July 2007 11:53 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Come on Michel - just paste the query & run it...
Re: Monitor TABLESPACES 10g [message #253810 is a reply to message #253701] Tue, 24 July 2007 22:10 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

In script no problem. Use EM for it.
Re: Monitor TABLESPACES 10g [message #260276 is a reply to message #253705] Fri, 17 August 2007 23:40 Go to previous messageGo to next message
bulk
Messages: 2
Registered: August 2007
Junior Member
try this

SELECT d.status "Status",
d.tablespace_name "Name",
h.GROUP_NAME,
d.contents "Type",
d.extent_management "Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,999') "Size (M)",
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '999,999,999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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) bytes
from v$temp_extent_pool
group by tablespace_name) t,
DBA_TABLESPACE_GROUPS h
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'
and h.tablespace_name = d.tablespace_name
ORDER BY 2;
Re: Monitor TABLESPACES 10g [message #260277 is a reply to message #253701] Fri, 17 August 2007 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member

SQL> SELECT d.status "Status",
  2  d.tablespace_name "Name",
  3  h.GROUP_NAME,
  4  d.contents "Type",
  5  d.extent_management "Extent",
  6  TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,999') "Size (M)",
  7  TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '999,999,999') "Used (M)",
  8  TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
  9  FROM sys.dba_tablespaces d,
 10  (select tablespace_name, sum(bytes) bytes
 11  from dba_temp_files
 12  group by tablespace_name) a,
 13  (select tablespace_name, sum(bytes_cached) bytes
 14  from v$temp_extent_pool
 15  group by tablespace_name) t,
 16  DBA_TABLESPACE_GROUPS h
 17  WHERE d.tablespace_name = a.tablespace_name(+)
 18  AND d.tablespace_name = t.tablespace_name(+)
 19  AND d.extent_management like 'LOCAL'
 20  AND d.contents like 'TEMPORARY'
 21  and h.tablespace_name = d.tablespace_name
 22  ORDER BY 2; 

no rows selected

I am NOT impressed!
Re: Monitor TABLESPACES 10g [message #260286 is a reply to message #260277] Sat, 18 August 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I get the same thing... and the same conclusion.

Regards
Michel
Re: Monitor TABLESPACES 10g [message #260442 is a reply to message #260286] Sun, 19 August 2007 22:14 Go to previous messageGo to next message
bulk
Messages: 2
Registered: August 2007
Junior Member
SQL> SELECT d.status "Status",
2 d.tablespace_name "Name",
3 d.contents "Type",
4 d.extent_management "Extent",
5 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,999') "Size (M)",
6 TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '999,999,999') "Used (M)",
7 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
8 FROM sys.dba_tablespaces d,
9 (select tablespace_name, sum(bytes) bytes
10 from dba_temp_files
11 group by tablespace_name) a,
12 (select tablespace_name, sum(bytes_cached) bytes
13 from v$temp_extent_pool
14 group by tablespace_name) t
15 WHERE d.tablespace_name = a.tablespace_name(+)
16 AND d.tablespace_name = t.tablespace_name(+)
17 AND d.extent_management like 'LOCAL'
18 AND d.contents like 'TEMPORARY'
19 ORDER BY 2;

Status Name Type Extent Size (M) Used (M) Used %
--------- ------------------------------ --------- ---------- ----------- ------------ -------
ONLINE TEMP11 TEMPORARY LOCAL 49,400 31,571 63.91
ONLINE TEMP12 TEMPORARY LOCAL 16,640 7,978 47.94
ONLINE TEMP13 TEMPORARY LOCAL 16,640 0 0.00
ONLINE TEMP21 TEMPORARY LOCAL 16,640 0 0.00
ONLINE TEMP22 TEMPORARY LOCAL 16,640 0 0.00
ONLINE TEMP23 TEMPORARY LOCAL 16,640 0 0.00
ONLINE TEMP24 TEMPORARY LOCAL 16,640 0 0.00

7 rows selected.
Re: Monitor TABLESPACES 10g [message #260443 is a reply to message #253701] Sun, 19 August 2007 22:20 Go to previous message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
The fact that you get rows returned is useless to me & others who also get "no rows returned".
Previous Topic: installing two oracle_home
Next Topic: DBA task levels
Goto Forum:
  


Current Time: Sun Dec 04 14:44:24 CST 2016

Total time taken to generate the page: 0.15825 seconds