Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: January 25, 2018 - 10:43 am UTC

Last updated: January 28, 2018 - 5:26 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi,

Is there an accurate way of determining the size of a column in a table. Mutiplying the DATA_LENGTH from dba_tab_columns for that column with the number of rows in the table makes sense?

Actually, we got request from App team that they want us to encrypt couple of columns and i'm trying to find the size of the data i'm dealing with and take necessary precaution before proceeding with it.

and Chris said...

Data_length just tells you the maximum length allowed in the column. To find the real size, you need to sum up the lengths of all the rows:

create table t as 
  select cast(lpad('x', level, 'x') as varchar2(30)) x from dual
  connect by level <= 10;
  
select x, length(x) siz, sum(length(x)) over () tot_size
from   t;

X            SIZ   TOT_SIZE   
x                1         55 
xx               2         55 
xxx              3         55 
xxxx             4         55 
xxxxx            5         55 
xxxxxx           6         55 
xxxxxxx          7         55 
xxxxxxxx         8         55 
xxxxxxxxx        9         55 
xxxxxxxxxx      10         55


I'm not sure why this is necessary for encryption though. Typically when you encrypt data, the output is a fixed length determined by the cipher you use.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up

Ram Dittakavi, January 25, 2018 - 11:17 am UTC

Thanks a lot, Chris for the quick response.

My column type is NUMBER. I'm not sure on the best algorithm to use. Suppose i go with the default AES192 algorithm with NO SALT, can you tell me what is the space overhead i'm looking at?
Chris Saxon
January 25, 2018 - 3:40 pm UTC

The encrypted raw will be a multiple of 32 bytes depending on the size of the input:

create or replace procedure encr ( input_string varchar2 ) as
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   num_key_bytes      NUMBER := 192/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES192
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
   
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
         typ => encryption_type,
         key => key_bytes_raw,
         iv  => null
      );
 
   DBMS_OUTPUT.PUT_LINE ('Encrypted raw len: ' || lengthb(encrypted_raw)); 
end encr;
/

exec encr('1');

Original string: 1
Encrypted raw len: 32

exec encr('123456789ABCDEF');

Original string: 123456789ABCDEF
Encrypted raw len: 32

exec encr('123456789ABCDEF0');

Original string: 123456789ABCDEF0
Encrypted raw len: 64

exec encr('123456789ABCDEF0123456789ABCDEF0');

Original string: 123456789ABCDEF0123456789ABCDEF0
Encrypted raw len: 96


That said, you shouldn't choose your encryption based on "space overhead"! Choose based on your security requirements

Follow up

Ram Dittakavi, January 28, 2018 - 3:10 am UTC

Hi Chris,

I'm trying to find the size of a column in my database using the sql you gave below but it throws syntax error.
Connor McDonald
January 28, 2018 - 5:26 am UTC

"...but it throws syntax error."

CRYSTAL_BALL

Nope...even with our crystal ball we could not see the error *you* are getting.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.