OEM 2.2 Storage Manager with Oracle 7.2 db, tablespace display never comes back

From: Billy <kyyb6_at_yahoo.com>
Date: 25 Jun 2003 18:50:57 -0700
Message-ID: <2ae225f2.0306251750.218ce8db_at_posting.google.com>

Found this on MetaLink, sounds similar to my problem, but...

Doc ID: Note:1075036.6
Subject: Enterprise Manager, Delay in Expanding Tablespaces in Storage Manager
Type: PROBLEM
Status: PUBLISHED
 Content Type: TEXT/PLAIN
Creation Date: 09-AUG-1999
Last Revision Date: 15-APR-2002  

Using SQL*Plus, connect to the same database with the same username/password that you were using for Storage Manager. Run the following SQLstatement :

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", TO_CHAR((a.bytes / 1024 / 1024),'99,999,990.900') "Size (M)", (a.bytes - DECODE(f.bytes, NULL, 0, f.bytes))/1024/1024 ||'/'||a.bytes/1024/1024 "Used (M)" 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(+);

If the query "hangs" or appears to take as long to answer as Storage Manager did, then it is definitely something wrong at the database level. Breaking the statement into smaller components may narrow the problem down further.

I tried the above query on my 7.2 database using SQLplus, got an error since contents is not a valid column in dba_tablespaces.

So I bounced the database, tried storage manager again, then ran a script that gets all SQL that's in the sqlarea cache. Below, from that, is the query that Storage Manager is trying to run. But Storage Manager just clocks (hourglasses) and never comes back.

In Instance Manager, I can see that my session is not active. In Storage Manager, I can click on an indvidual tablespace and I get the
display instantly for that tablespace (space allocated/used, etc). Other displays, for datafiles and rollbacks work okay too.

It's just this one niggling problem of Storage Manager not displaying space for all tablespaces.

---

First load time: 2003-06-25/20:33:06

Buffer gets:           322 ratio       322
Disk reads:              0 ratio         0
Rows delivered          24 ratio        24
Executions               1
Parses                   1
Memory               53262
Sorts                    8
Invalidations            0

/* OracleOEM */ SELECT v.status "Status", d.file_name "Name", d.tablespace_name "Tablespace", TO_CHAR((d.bytes / 1024 / 1024), '99999990.000') "Size (M)", NVL(d.bytes - s.bytes, d.bytes)/1024/1024 || '/'
|| d.bytes/1024/1024 "Used (M)", TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) /
d.bytes * 100), '990.00') "Used %" FROM sys.dba_data_files d, v$datafile v,
(SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY
file_id)
s WHERE (s.file_id (+)= d.file_id) AND (d.file_name = v.name)

I ran the above sql statement in sqlplus and it works fine.

Yeah, I know, I probably shouldn't be using OEM 2.2 with such an old database (7.2), but I probably won't be doing many alterations with OEM 2.2 tools, will instead use SQL scripts thru sqlplus. However, it would be nice to use OEM 2.2 for some things.

Please don't respond to this email address, I hardly ever check it, please respond to this newsgroup instead.

Thanks,
B Received on Thu Jun 26 2003 - 03:50:57 CEST