dbms_output oddities in Oracle 11.1

You’ve all seen countless scripts that show you how much space is being used in each tablespace, for example:

--------------------------------------------------------------------------------------
-- Tablespace Name         Size       Used       Free    Used Percent                  
--------------------------------------------------------------------------------------
-- SYSAUX                  0.97 G     0.83 G     0.14 G  85.71 [XXXXXXXXXXXXXXXXX---]    
-- UNDOTBS1                0.34 G     0.01 G     0.32 G   4.28 [--------------------]    
-- INDEX_01               28.54 G    28.50 G     0.04 G  99.85 [XXXXXXXXXXXXXXXXXXX-]   
-- UNDOTBS4               32.00 G     0.05 G    31.95 G   0.14 [--------------------]   
-- USERS                   5.29 G     5.07 G     0.22 G  95.77 [XXXXXXXXXXXXXXXXXXX-]   
-- SYSTEM                  0.68 G     0.55 G     0.13 G  80.53 [XXXXXXXXXXXXXXXX----]   
-- UNDOTBS3                0.07 G     0.03 G     0.04 G  44.42 [XXXXXXXX------------]    
-- DATA_01                27.49 G    27.01 G     0.48 G  98.27 [XXXXXXXXXXXXXXXXXXX-]   
-- UNDOTBS2                0.07 G     0.00 G     0.07 G   5.00 [X-------------------]    
-- AMCHECK_DATA            1.00 G     0.01 G     0.99 G   1.40 [--------------------]    
--------------------------------------------------------------------------------------

but generally speaking they fail to take into account the fact that some of the data files in a tablespace might be set to autoextend. So, I wrote a little procedure to show the current space usage both in terms of currently allocated space and also in terms of total available space.

The output looks something like this (sorry for the scroll bar but the lines are a little on the long side):

------------------------------------------------------------------------------------------------------------------------------
-- Tablespace Name       Max Size     Size       Used       Free    Used Percent                  Max Percent
------------------------------------------------------------------------------------------------------------------------------
-- SYSAUX                 32.00 G     0.97 G     0.83 G     0.14 G  85.71 [XXXXXXXXXXXXXXXXX---]   2.61 [--------------------]
-- UNDOTBS1               32.00 G     0.34 G     0.01 G     0.32 G   4.28 [--------------------]   0.05 [--------------------]
-- INDEX_01               32.00 G    28.54 G    28.50 G     0.04 G  99.85 [XXXXXXXXXXXXXXXXXXX-]  89.05 [XXXXXXXXXXXXXXXXX---]
-- UNDOTBS4               32.00 G    32.00 G     0.05 G    31.95 G   0.14 [--------------------]   0.14 [--------------------]
-- USERS                  32.00 G     5.29 G     5.07 G     0.22 G  95.77 [XXXXXXXXXXXXXXXXXXX-]  15.84 [XXX-----------------]
-- SYSTEM                 32.00 G     0.68 G     0.55 G     0.13 G  80.53 [XXXXXXXXXXXXXXXX----]   1.72 [--------------------]
-- UNDOTBS3               32.00 G     0.07 G     0.03 G     0.04 G  44.42 [XXXXXXXX------------]   0.10 [--------------------]
-- DATA_01                64.00 G    27.49 G    27.01 G     0.48 G  98.27 [XXXXXXXXXXXXXXXXXXX-]  42.21 [XXXXXXXX------------]
-- UNDOTBS2               32.00 G     0.07 G     0.00 G     0.07 G   5.00 [X-------------------]   0.01 [--------------------]
-- AMCHECK_DATA            1.00 G     1.00 G     0.01 G     0.99 G   1.40 [--------------------]   1.40 [--------------------]
------------------------------------------------------------------------------------------------------------------------------

All very nice but although this is superior to the first one it doesn’t take into account how much space is actually available in the containing mountpoint or ASM diskgroup or disk partition.

So just recently I’ve been working on a procedure that takes into account limits imposed by the ‘container’. Doing this for ASM diskgroups is a doodle as all of the information you need is contained in v$asm_diskgroup. But for mountpoint limitations I’ve had to use a bit of java and issue a ‘df’ command for each datafile It’s not very elegant and its causing me problems on Oracle version 11.1.

Here’s a simplified version of the code that illustrates the problem:

CREATE OR REPLACE VIEW PA_TABLESPACE_SUMMARY AS
SELECT RPAD('-- ' || tablespace_name,27) ||
RPAD(TO_CHAR(tablespace_max_size/(1024 * 1024 * 1024),'999,990.99') || ' GB',14) ||
RPAD(TO_CHAR(tablespace_size/(1024 * 1024 * 1024),'9,990.99') || ' GB',12) ||
RPAD(TO_CHAR(used_bytes/(1024 * 1024 * 1024),'9,990.99') || ' GB',12) ||
RPAD(TO_CHAR((tablespace_size - used_bytes)/(1024 * 1024 * 1024),'9,990.99') || ' GB',12) ||
RPAD(TO_CHAR(DECODE(tablespace_size,NULL,0,NVL(ROUND((tablespace_size - (tablespace_size - used_bytes))/(tablespace_size)*100,2),100)),'990.99') || ' %', 8) ||
RPAD(CASE WHEN (tablespace_size IS NULL)
THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE((tablespace_size - used_bytes),
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (tablespace_size - used_bytes)/(tablespace_size) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
END, 22) ||
RPAD(TO_CHAR(DECODE(tablespace_max_size,NULL,0,NVL(ROUND((tablespace_max_size - (tablespace_max_size - used_bytes))/(tablespace_max_size)*100,2),100)),'990.99') || ' %', 8) ||
RPAD(CASE WHEN (tablespace_max_size IS NULL)
THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE((tablespace_max_size - used_bytes),
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (tablespace_max_size - used_bytes)/(tablespace_max_size) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
END, 22) AS string
FROM
(SELECT tablespace_name,
tablespace_size,
tablespace_max_size,
used_bytes
FROM   (SELECT t.tablespace_name,
t.tablespace_size,
t.tablespace_max_size,
u.used_bytes
FROM   (SELECT tablespace_name,
SUM(totalb) AS tablespace_size,
SUM(maxb)   AS tablespace_max_size
FROM   (SELECT tablespace_name AS tablespace_name,
SUM(LEAST(DECODE(NVL(maxbytes,0),0,bytes,maxbytes), CONTAINER_MAX_BYTES(file_name))) AS maxb,
SUM(bytes) AS totalb
FROM   dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
SUM(bytes),
SUM(bytes)
FROM  dba_data_files
WHERE autoextensible = 'NO'
GROUP BY tablespace_name)
GROUP BY tablespace_name) t,
(SELECT   tablespace_name,
SUM(bytes) AS used_bytes
FROM     dba_segments
GROUP BY tablespace_name) u
WHERE   u.tablespace_name = t.tablespace_name)
);

The CONTAINER_MAX_BYTES looks like this:

CREATE OR REPLACE FUNCTION PA_OWNER.container_max_bytes (
p_file_name IN dba_data_files.file_name%TYPE
)
RETURN NUMBER
IS
v_container_max_bytes  INTEGER;
v_dbfiles_max_bytes    INTEGER;
v_host_command         VARCHAR2(400);
v_output               DBMS_OUTPUT.chararr;
v_lines                INTEGER:=1;

BEGIN
-- Value returned is in bytes
dbms_output.enable(buffer_size => 1000000);
IF (p_file_name LIKE '+%')
THEN
-- ASM
SELECT (total_mb)*1024*1024 INTO v_container_max_bytes FROM v$asm_diskgroup WHERE name = SUBSTR(p_file_name,2,INSTR(p_file_name, '/')-2);
ELSE
DBMS_JAVA.set_output(1000000);
-- dk -P is in 'k'
v_host_command := '/bin/df -P  ' || p_file_name || ' | /usr/bin/tail -1  | /usr/bin/sed ''s/\//A/g'' | /usr/bin/sed ''s/\-/A/g'' | /usr/bin/sed ''s/  */\ /g'' | /usr/bin/cut -d'' '' -f4 ';
host_command(v_host_command);

DBMS_OUTPUT.get_lines(v_output, v_lines);
v_container_max_bytes := TO_NUMBER(v_output(1) * 1024);
END IF;

SELECT DECODE(NVL(maxbytes,0),0,bytes,maxbytes)  INTO v_dbfiles_max_bytes FROM dba_data_files WHERE file_name = p_file_name ;
RETURN LEAST(v_container_max_bytes,v_dbfiles_max_bytes) ;

END container_max_bytes;
/

The host_command is something I’ve seen online a few times (I think Tom Kyte had a version).

Anyhow, I can select from my view in pl/sql and it all looks tickedy boo e.g.:

BEGIN
FOR space_rec IN (SELECT string FROM pa_tablespace_summary)
LOOP
DBMS_OUTPUT.PUT_LINE(space_rec.string);
END LOOP;
END;
/
-- SYSAUX             32.00 GB     0.97 GB      0.84 GB     0.13 GB  86.23 [XXXXXXXXXXXXXXXXX---]   2.62 [--------------------]
-- UNDOTBS1           32.00 GB     0.34 GB      0.01 GB     0.32 GB   4.28 [--------------------]   0.05 [--------------------]
-- INDEX_01           32.00 GB    28.54 GB     28.50 GB     0.04 GB  99.85 [XXXXXXXXXXXXXXXXXXX-]  89.05 [XXXXXXXXXXXXXXXXX---]
-- UNDOTBS4           32.00 GB    32.00 GB      0.20 GB    31.80 GB   0.63 [--------------------]   0.63 [--------------------]
-- USERS              32.00 GB     5.29 GB      5.07 GB     0.22 GB  95.82 [XXXXXXXXXXXXXXXXXXX-]  15.85 [XXX-----------------]
-- SYSTEM             32.00 GB     0.68 GB      0.55 GB     0.13 GB  80.53 [XXXXXXXXXXXXXXXX----]   1.72 [--------------------]
-- UNDOTBS3           32.00 GB     0.07 GB      0.03 GB     0.04 GB  44.42 [XXXXXXXX------------]   0.10 [--------------------]
-- DATA_01            64.00 GB    27.49 GB     27.02 GB     0.47 GB  98.30 [XXXXXXXXXXXXXXXXXXX-]  42.22 [XXXXXXXX------------]
-- UNDOTBS2           32.00 GB     0.07 GB      0.00 GB     0.07 GB   5.00 [X-------------------]   0.01 [--------------------]
-- AMCHECK_DATA        1.00 GB     1.00 GB      0.01 GB     0.99 GB   1.40 [--------------------]   1.40 [--------------------]

(the figures are a bit different but that’s because the one above is a day out of date)

Now if I add in some text for headers etc. it looks something like this (not a great example as the mount points are huge and not imposing a further limit beyond maxfilesize):

Tablespace             Max Size   Allocated        Used     Free     %Used                         %Max
--------------------------------------------------------------------------------------------------------------------------------------------
-- SYSAUX             32.00 GB     0.97 GB      0.84 GB     0.13 GB  86.23 [XXXXXXXXXXXXXXXXX---]   2.62 [--------------------]
-- UNDOTBS1           32.00 GB     0.34 GB      0.01 GB     0.32 GB   4.28 [--------------------]   0.05 [--------------------]
-- INDEX_01           32.00 GB    28.54 GB     28.50 GB     0.04 GB  99.85 [XXXXXXXXXXXXXXXXXXX-]  89.05 [XXXXXXXXXXXXXXXXX---]
-- UNDOTBS4           32.00 GB    32.00 GB      0.20 GB    31.80 GB   0.63 [--------------------]   0.63 [--------------------]
-- USERS              32.00 GB     5.29 GB      5.07 GB     0.22 GB  95.82 [XXXXXXXXXXXXXXXXXXX-]  15.85 [XXX-----------------]
-- SYSTEM             32.00 GB     0.68 GB      0.55 GB     0.13 GB  80.53 [XXXXXXXXXXXXXXXX----]   1.72 [--------------------]
-- UNDOTBS3           32.00 GB     0.07 GB      0.03 GB     0.04 GB  44.42 [XXXXXXXX------------]   0.10 [--------------------]
-- DATA_01            64.00 GB    27.49 GB     27.02 GB     0.47 GB  98.30 [XXXXXXXXXXXXXXXXXXX-]  42.22 [XXXXXXXX------------]
-- UNDOTBS2           32.00 GB     0.07 GB      0.00 GB     0.07 GB   5.00 [X-------------------]   0.01 [--------------------]
-- AMCHECK_DATA        1.00 GB     1.00 GB      0.01 GB     0.99 GB   1.40 [--------------------]   1.40 [--------------------]

BUT if I try the same thing on 11.1 I get the following error:

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "PA_OWNER.CONTAINER_MAX_BYTES", line 34
ORA-06512: at line 5

Line 34 is this one:

v_container_max_bytes := TO_NUMBER(v_output(1) * 1024);

Any ideas?

For completeness, here is the source for the 2 java objects (taken in their entirety from the interweb – if they’re yours please let me know):

CREATE OR REPLACE PROCEDURE PA_OWNER.host_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/</pre>
and for 'Host'
<pre>CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
public static void executeCommand(String command) {
try {
String[] finalCommand;
if (isWindows()) {
finalCommand = new String[4];
// Use the appropriate path for your windows version.
finalCommand[0] = "C:\\windows\\system32\\cmd.exe";  // Windows XP/2003
//finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";  // Windows NT/2000
finalCommand[1] = "/y";
finalCommand[2] = "/c";
finalCommand[3] = command;
}
else {
finalCommand = new String[3];
finalCommand[0] = "/bin/sh";
finalCommand[1] = "-c";
finalCommand[2] = command;
}

final Process pr = Runtime.getRuntime().exec(finalCommand);
pr.waitFor();

new Thread(new Runnable(){
public void run() {
BufferedReader br_in = null;
try {
br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
String buff = null;
while ((buff = br_in.readLine()) != null) {
System.out.println(buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_in.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process output.");
ioe.printStackTrace();
}
finally {
try {
br_in.close();
} catch (Exception ex) {}
}
}
}).start();

new Thread(new Runnable(){
public void run() {
BufferedReader br_err = null;
try {
br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
String buff = null;
while ((buff = br_err.readLine()) != null) {
System.out.println("Process err :" + buff);
try {Thread.sleep(100); } catch(Exception e) {}
}
br_err.close();
}
catch (IOException ioe) {
System.out.println("Exception caught printing process error.");
ioe.printStackTrace();
}
finally {
try {
br_err.close();
} catch (Exception ex) {}
}
}
}).start();
}
catch (Exception ex) {
System.out.println(ex.getLocalizedMessage());
}
}

public static boolean isWindows() {
if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
return true;
else
return false;
}

};
/

The following were run as sys prior to creation of the above:

EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '&lt;&lt;ALL FILES&gt;&gt;', 'read ,write, execute, delete');
-- N.B. WARNING - the privileges above are too generous for a production system.. Use the following 5 instead (uncomment)
EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '/usr/bin/sed', 'read ,execute');
EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '/bin/df', 'read , execute');
EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '/bin/ls', 'read , execute');
EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '/usr/bin/tail', 'read , execute');
EXEC DBMS_JAVA.grant_permission('PA_OWNER', 'java.io.FilePermission', '/usr/bin/cut', 'read, execute');

EXEC Dbms_Java.Grant_Permission('PA_OWNER', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('PA_OWNER', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');

I’ll upload these along with the rest of the DBA PA soon but I would like to upload something that works properly on 11.1! so all help would be gratefully received 🙂

Cheers

Tony

Advertisements

About dnadba

I work with databases and stuff
This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s