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

Report message to a moderator

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: 68686
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

Report message to a moderator

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...

Report message to a moderator

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.

Report message to a moderator

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;

Report message to a moderator

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: 26766
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!

Report message to a moderator

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: 68686
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I get the same thing... and the same conclusion.

Regards
Michel

Report message to a moderator

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.

Report message to a moderator

Re: Monitor TABLESPACES 10g [message #260443 is a reply to message #253701] Sun, 19 August 2007 22:20 Go to previous message
BlackSwan
Messages: 26766
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".

Report message to a moderator

Previous Topic: installing two oracle_home
Next Topic: DBA task levels
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 07:36:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.