Tuesday, March 17, 2009

Find Tablespace Usage Percent

I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement.

It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query.


Caveat: This query is not optimised for performance. Hence, you might observe slow performance.




set linesize 100
set pagesize 10000
set feedback off verify off
col tablespace_name format a18
col num_files format 999
col total_space format 9999999.99
col free_space format 9999999.99
col used_percent format 999.99



select /*+ parallel(tbs) */
tbs.tablespace_name,
count(tbs.file_id) num_files,
round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB,
sum(FREE_SPACE) FREE_SPACE_MB,
round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT
from
(select
ddf.file_id file_id,
ddf.file_name,
ddf.tablespace_name tablespace_name,
round(decode(ddf.autoextensible,'NO',ddf.bytes/1024/1024,'YES',ddf.maxbytes/1024/1024),2) "TOTAL_SPACE",
ddf.autoextensible,
case
when ddf.maxbytes < ddf.bytes
then round(((ddf.bytes/1024/1024) - nvl((dfs.free_space),0)),2)
else round(ddf.bytes/1024/1024,2)
END "USED_SPACE",
case
when ddf.maxbytes < ddf.bytes
then nvl(dfs.free_space,0)
else round(((ddf.maxbytes/1024/1024) - (ddf.bytes/1024/1024)),2)
END "FREE_SPACE"
/*, case autoextensible
when 'NO' then round(((((ddf.bytes/1024/1024) - nvl(dfs.free_space,0)) / (ddf.bytes/1024/1024)) * 100),2)
when 'YES' then round((((ddf.bytes/1024/1024) / (ddf.maxbytes/1024/1024)) * 100),2)
END '%' "USED_PERCENT" */
from dba_data_files ddf,
(select file_id, round(sum(bytes)/1024/1024,2) free_space from dba_free_space group by file_id) dfs
where
ddf.file_id = dfs.file_id(+)
) tbs
group by tbs.tablespace_name
order by 5 desc;



Wednesday, March 4, 2009

EXCEPTION_ACCESS_VIOLATION in Jinitiator and IE crashes

The Environment
Apps Version : 11.5.10.2
Jinitiator Version : 1.3.1.26
Internet Explorer Version : 6

The Issue
One of our end users was receiving a very strange error while using jinitiator. The Internet explorer window would crash (close by itself) after creating a file HS_ERR_PID<####>.log on the desktop, where #### stands for the Process ID of the f60webmx session.

This error is intermittent and not easily reproducible. Moreover, the user would have to redo his/her work and hence this issue was turning out to be very vexing indeed.

The error log is as below:

An unexpected exception has been detected in native code outside the VM.
Unexpected Signal : EXCEPTION_ACCESS_VIOLATION occurred at PC=0x6D043D1AFunction name=Java_sun_java2d_loops_DefaultComponent_IntIsomorphicCopy
Library=C:\Program Files\Oracle\JInitiator 1.3.1.26\bin\awt.dll
Current Java thread:
at sun.java2d.loops.DefaultComponent.IntIsomorphicCopy(Native Method)
at sun.java2d.loops.IntRgbToIntRgb.OpaqueBlit(Unknown Source)
at sun.java2d.loops.RasterOutputManager.performOpaqueBlit(Unknown Source)
at sun.java2d.loops.RasterOutputManager.compositeSrcDst(Unknown Source)
at sun.java2d.loops.RasterOutputManager.renderImage(Unknown Source)
at sun.java2d.SunGraphics2D.renderingPipeImage(Unknown Source)
at sun.java2d.SunGraphics2D.drawImage(Unknown Source)
at sun.java2d.SunGraphics2D.drawImage(Unknown Source)
at sun.awt.windows.WGraphics.drawImage(Unknown Source)
at sun.awt.windows.WGraphics.drawImage(Unknown Source)
at oracle.ewt.lwAWT.SharedPainter.paint(Unknown Source)
at oracle.ewt.lwAWT.BufferedFrame.paint(Unknown Source)
at sun.awt.RepaintArea.paint(Unknown Source)
at sun.awt.windows.WComponentPeer.handleEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Dynamic libraries:
0x00400000 - 0x0049B000 C:\Program Files\Internet Explorer\iexplore.exe
0x7C900000 - 0x7C9B0000 C:\WINNT\system32\ntdll.dll
0x7C800000 - 0x7C8F5000 C:\WINNT\system32\kernel32.dll
0x77DD0000 - 0x77E6B000 C:\WINNT\system32\ADVAPI32.dll
0x77E70000 - 0x77F02000 C:\WINNT\system32\RPCRT4.dll
0x77FE0000 - 0x77FF1000 C:\WINNT\system32\Secur32.dll
0x77F10000 - 0x77F58000 C:\WINNT\system32\GDI32.dll
0x7E410000 - 0x7E4A0000 C:\WINNT\system32\USER32.dll
0x77C10000 - 0x77C68000 C:\WINNT\system32\msvcrt.dll
0x77F60000 - 0x77FD6000 C:\WINNT\system32\SHLWAPI.dll
0x7C9C0000 - 0x7D1D6000 C:\WINNT\system32\SHELL32.dll
0x774E0000 - 0x7761D000 C:\WINNT\system32\ole32.dll
0x78130000 - 0x78257000 C:\WINNT\system32\urlmon.dll
0x77120000 - 0x771AB000 C:\WINNT\system32\OLEAUT32.dll
0x78000000 - 0x78045000 C:\WINNT\system32\iertutil.dll
0x77C00000 - 0x77C08000 C:\WINNT\system32\VERSION.dll
......
....
....


Initial Troubleshooting
Uninstalling and reinstalling jinitiator did not work. So, probed in Metalink to find out whether it is a known issue.

Found only one relevant hit in Metalink - Doc ID: 422761.1 - for this issue.

The Cause
As per the Note 422761.1, "This is a work station hardware issue related to a device driver associated with usually video cards. It also could cause by other device drivers."

The Solution
Again, as per the Note 422761.1, there are 2 solutions.

Solution 1
1. Add the following parameter to your Jinitiator Java Runtime Parameters:2. Control Panel > Open Jinitiaitor 1.3.1.24 (or whatever version) > [Tab] Basic > Enter the following in your Java Runtime Parameters field:
-Dsun.java2d.noddraw=true
3. Click Apply
4. Open your browser, then clear browser cache, and test.

Solution 2
To add to the environment:
1. Start->Control Panel->System
2. Click the Advanced Tab
3. Click the Environment Variables button
4. Click the New button
5. For Variable name:_JAVA_OPTIONS
6. For Variable value -Dsun.java2d.noddraw=true [or -Dsun.java2d.d3d=false ]
7. Click OK

Followed solution 1 mentioned above and Voilà, the issue is resolved.