SQLTeam.com | Weblogs | Forums

How to convert Bytes to GB using sql query

sql2012

#1

Im trying to verify value that have been changed in the DB using query. Howeverm it return the value I passed (in GB) to bytes. I wanted to verify to esnure value stored in (bytes) is correct when I make comparison with gigabytes.

set serveroutput on
DECLARE
static_value VARCHAR2(4000);
BEGIN
SELECT value
INTO  static_value
FROM   v$parameter WHERE  name = '&1'; 
DBMS_OUTPUT.PUT_LINE('&1 is set to '|| RTRIM(static_value));
DBMS_OUTPUT.PUT_LINE(&2);
DBMS_OUTPUT.PUT_LINE(RTRIM(static_value));
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to set parameter because of '|| SQLCODE);DBMS_OUTPUT.PUT_LINE('Error       message - '|| SUBSTR(SQLERRM,1,128));
END;/

The result above return static_value = 6442450944 and &2 = 6g (which i specified in shell script that I call and pass the parameter value to the sql query above.

How can I convert from bytes to gb in this case and compare static_value with the &2 values?


#2

What database are you using? That doesn't look like T-SQL to me


#3

That's Oracle.


#4

divide value by 1073741824


#5

GB = (Bytes + (102410241024 - 1)) / (102410241024) -- This yields the ceiling
GB = (Bytes) / (102410241024) -- This yields the floor