Home » SQL & PL/SQL » SQL & PL/SQL » Sum of length of column data of all tables (oracle 11g)
Sum of length of column data of all tables [message #561660] Tue, 24 July 2012 11:34 Go to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi everybody, i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
can anyone help me?
Re: Sum of length of column data of all tables [message #561661 is a reply to message #561660] Tue, 24 July 2012 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
>can anyone help me?
I don't understand what exactly you desire.
can you post an example of the results or desired output?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Sum of length of column data of all tables [message #561662 is a reply to message #561660] Tue, 24 July 2012 11:39 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK, let's stay with a single user - Scott. Here are some information about its EMP table:
SQL> l
  1  select column_name, data_type, data_length, data_precision, data_scale
  2  from user_Tab_columns
  3* where table_name = 'EMP'
SQL> /

COLUMN_NAM DATA_TYPE  DATA_LENGTH DATA_PRECISION DATA_SCALE
---------- ---------- ----------- -------------- ----------
EMPNO      NUMBER              22              4          0
ENAME      VARCHAR2            10
JOB        VARCHAR2             9
MGR        NUMBER              22              4          0
HIREDATE   DATE                 7
SAL        NUMBER              22              7          2
COMM       NUMBER              22              7          2
DEPTNO     NUMBER              22              2          0

8 rows selected.

SQL>

This are EMP table's NUMBER columns and their contents:
SQL> select empno, mgr, sal, comm, deptno from emp;

     EMPNO        MGR        SAL       COMM     DEPTNO
---------- ---------- ---------- ---------- ----------
      7369       7902        800                    20
      7499       7698       1600        300         30
      7521       7698       1250        500         30
      7566       7839       2975                    20
      7654       7698       1250       1400         30
      7698       7839       2850                    30
      7782       7839       2450                    10
      7788       7566       3000                    20
      7839                  5000                    10
      7844       7698       1500          0         30
      7876       7788       1100                    20
      7900       7698        950                    30
      7902       7566       3000                    20
      7934       7782       1300                    10

14 rows selected.

SQL>

So, what do you want to get as a result?
Re: Sum of length of column data of all tables [message #561664 is a reply to message #561662] Tue, 24 July 2012 11:46 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
i want the result as..
select sum(length(empno)) from emp; -----here the data type is number so i want this info for all the tables and their columns of data type (number)present in the database

select sum(length(ename)) from emp;------ here the data type is varchar2 so i want this info for all the tables and their columns of data type(varchar2) present in the database

as select sum(length(empno)) from emp
select sum(length(deptno)) from dept and so on....of all the tables present in the database.
thanks in advance

Re: Sum of length of column data of all tables [message #561665 is a reply to message #561664] Tue, 24 July 2012 11:52 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use VSIZE instead of length.
So spool
select 'SELECT SUM(VSIZE('||column_name||')) FROM '||owner||'.'||table_name||';'
from dba_tab_columns
/

and execute the spool.

Regards
Michel
Re: Sum of length of column data of all tables [message #561666 is a reply to message #561664] Tue, 24 July 2012 11:54 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
select owner,table_name,column_name,data_type,sum(length(column_name)) from all_tab_columns where data_type='varchar2' ...i know the above query wont wok... but want the output like this.....can any one help me out??
Re: Sum of length of column data of all tables [message #561667 is a reply to message #561666] Tue, 24 July 2012 11:57 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Aha! I've just seen Michel's answer - didn't know that VSIZE trick. I was thinking about dynamic SQL, such as
SQL> declare
  2    cursor c1 is
  3      select column_name, table_name
  4        from user_tab_columns
  5        where table_name = 'EMP'
  6          and data_type in ('NUMBER', 'VARCHAR2');
  7    str       varchar2(200);
  8    l_sum_len number;
  9  begin
 10    for cur_r in c1 loop
 11      str := 'select sum(length(' ||
 12              cur_r.column_name   ||
 13             ')) from '           ||
 14              cur_r.table_name;
 15
 16      execute immediate (str) into l_sum_len;
 17      dbms_output.put_line(cur_r.table_name  ||'.' ||
 18                           cur_r.column_name ||': '||
 19                           l_sum_len
 20                          );
 21    end loop;
 22  end;
 23  /
EMP.EMPNO: 56
EMP.ENAME: 70
EMP.JOB: 96
EMP.MGR: 52
EMP.SAL: 54
EMP.COMM: 11
EMP.DEPTNO: 28

PL/SQL procedure successfully completed.

SQL>

Anyway: what do you plan to do with the result of such an operation? How will you use it? What benefits do you see behind that?
Re: Sum of length of column data of all tables [message #561668 is a reply to message #561666] Tue, 24 July 2012 11:57 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
@ Michael..but i want to get it for all the tables of same data_type..like for number data_type , similarly for nvarchar2 data_type and so on..

thank u!
Re: Sum of length of column data of all tables [message #561669 is a reply to message #561668] Tue, 24 July 2012 11:59 Go to previous messageGo to next message
BlackSwan
Messages: 22686
Registered: January 2009
Senior Member
I hope this is just a homework assignment.
Re: Sum of length of column data of all tables [message #561670 is a reply to message #561668] Tue, 24 July 2012 12:01 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
@ little foot thanks for the proc..but what if i want it for all the tables and its columns at a time can i get it???...

i migrated data from one(orcl) db to sql db so i want to check/test the data in sql as well as orcl with this.
Re: Sum of length of column data of all tables [message #561673 is a reply to message #561670] Tue, 24 July 2012 12:09 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You'd modify this code, such as
SQL> declare
  2    cursor c1 is
  3      select owner, column_name, table_name
  4        from all_tab_columns
  5        where data_type in ('NUMBER', 'VARCHAR2')
  6         and owner in ('SCOTT', 'FRANJO')
  7       order by owner, table_name;
  8    str       varchar2(200);
  9    l_sum_len number;
 10  begin
 11    for cur_r in c1 loop
 12      str := 'select sum(length(' ||
 13              cur_r.column_name   ||
 14             ')) from '           ||
 15              cur_r.owner         ||'.'||
 16              cur_r.table_name;
 17
 18      execute immediate (str) into l_sum_len;
 19      dbms_output.put_line(cur_r.owner       ||'.' ||
 20                           cur_r.table_name  ||'.' ||
 21                           cur_r.column_name ||': '||
 22                           l_sum_len
 23                          );
 24    end loop;
 25  end;
 26  /
FRANJO.BONUS.COMM:
FRANJO.BONUS.SAL:
FRANJO.BONUS.JOB:
FRANJO.BONUS.ENAME:
FRANJO.DEPT.DNAME: 33
FRANJO.DEPT.LOC: 27
FRANJO.DEPT.DEPTNO: 8
FRANJO.EMP.ENAME: 70
FRANJO.EMP.EMPNO: 56
FRANJO.EMP.DEPTNO: 28
FRANJO.EMP.COMM: 11
FRANJO.EMP.SAL: 54
FRANJO.EMP.MGR: 52
FRANJO.EMP.JOB: 96
FRANJO.PROBA.COL1: 3
FRANJO.SALGRADE.HISAL: 20
FRANJO.SALGRADE.LOSAL: 19
FRANJO.SALGRADE.GRADE: 5
FRANJO.STAGE.TABLE_NAME: 22
FRANJO.STAGE.RECORDCOUNT: 10
SCOTT.BONUS.JOB:
SCOTT.BONUS.SAL:
SCOTT.BONUS.COMM:
SCOTT.BONUS.ENAME:
SCOTT.DEPT.DNAME: 33
SCOTT.DEPT.DEPTNO: 8
SCOTT.DEPT.LOC: 27
SCOTT.EMP.DEPTNO: 28
SCOTT.EMP.COMM: 11
SCOTT.EMP.SAL: 54
SCOTT.EMP.MGR: 52
SCOTT.EMP.JOB: 96
SCOTT.EMP.EMPNO: 56
SCOTT.EMP.ENAME: 70
SCOTT.SALGRADE.LOSAL: 19
SCOTT.SALGRADE.GRADE: 5
SCOTT.SALGRADE.HISAL: 20
SCOTT.STAGE.TABLE_NAME: 22
SCOTT.STAGE.RECORDCOUNT: 10

PL/SQL procedure successfully completed.

SQL>

Modify cursor's WHERE clause; see whether you'll select certain users, or omit another ones. I suppose you don't want to compare SYS or SYSTEM or similar users.
Re: Sum of length of column data of all tables [message #561674 is a reply to message #561673] Tue, 24 July 2012 12:16 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
@little foot thank u very much for this help! yes i need not to compare sys user tables...but what if i have 50-60 tables??? i need to enter all those table names??... or else can we use it where owner not in the 20 users ..??
Re: Sum of length of column data of all tables [message #561675 is a reply to message #561674] Tue, 24 July 2012 12:19 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what you need to change:
and owner in ('SCOTT', 'FRANJO')

Now, either name all users you want, or use
and owner NOT in (...)
and remove all users you don't want. Up to you.
Re: Sum of length of column data of all tables [message #561676 is a reply to message #561675] Tue, 24 July 2012 12:21 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
hmm yes thank u very much...this site and u people helping me a lot!! may all your wishes come true!!

[Updated on: Tue, 24 July 2012 12:24]

Report message to a moderator

Re: Sum of length of column data of all tables [message #561726 is a reply to message #561675] Wed, 25 July 2012 01:03 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
hey on exec abv proc, it is compiled scuccessfully but unable to retrieve the ouput..????
Re: Sum of length of column data of all tables [message #561727 is a reply to message #561726] Wed, 25 July 2012 01:04 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you SET SERVEROUTPUT ON?
Re: Sum of length of column data of all tables [message #561738 is a reply to message #561727] Wed, 25 July 2012 01:41 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
yes i did it but still am not getting the output
Re: Sum of length of column data of all tables [message #561739 is a reply to message #561738] Wed, 25 July 2012 01:44 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Post your SQL*Plus session (just like I did) so that we could see what you did and how Oracle responded.

Please, properly format your code and enclose it into the [code] tags to preserve formatting (have a look here if you are unsure of how to do that).
Re: Sum of length of column data of all tables [message #561742 is a reply to message #561739] Wed, 25 July 2012 01:54 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
SQL> declare cursor xyz is select owner,column_name,table_name from             
  2  all_tab_columns where data_type in ('number','varchar2') and
  3  owner in('SCOTT','HR') order by owner,table_name;
  4  str varchar2(300);
  5  sum_len number;
  6  begin
  7  for cur_r in xyz loop 
  8  str:= 'select sum(length('|| cur_r.column_name  || '))  from  ' ||
  9  cur_r.owner   ||'.'|| cur_r.table_name;
 10  
 11  execute immediate (str) into sum_len;
 12  
 13  dbms_output.put_line(cur_r.owner  ||'.'|| cur_r.table_name|| '.'||
 14  cur_r.column_name ||':'|| sum_len );
 15  end loop;
 16  end;
 17  /
     
Re: Sum of length of column data of all tables [message #561745 is a reply to message #561742] Wed, 25 July 2012 02:08 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is far from formatted. You didn't click a link I posted, did you? Please, do it before posting your next message; it is difficult to read unformatted code.

Also, you didn't pay attention to what I posted. Compare DATA_TYPE values in your code and my example.
Re: Sum of length of column data of all tables [message #561749 is a reply to message #561745] Wed, 25 July 2012 02:34 Go to previous messageGo to next message
Maaher
Messages: 7045
Registered: December 2001
Senior Member
At first glance: what are the valid data types in the "all_tab_columns" columns? Upper or lower case?

MHE
Re: Sum of length of column data of all tables [message #561750 is a reply to message #561749] Wed, 25 July 2012 02:57 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
now i got it!! but i didnt understand the thing which negotiated the output when i tried the same code with the required space but not as long spaces between the table_name and owner and column_name..

I got the output for which i exec exactly your(@little foot) script.
Re: Sum of length of column data of all tables [message #561753 is a reply to message #561750] Wed, 25 July 2012 03:03 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood
Quote:
i didnt understand the thing which negotiated the output when i tried the same code with the required space but not as long spaces between the table_name and owner and column_name.

but I suppose that it doesn't matter as you got the result you wanted.
Re: Sum of length of column data of all tables [message #561754 is a reply to message #561753] Wed, 25 July 2012 03:06 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
ohk..... but i executed or typed the procedure the way i posted ...so i didnt thought of formatting the code. so pasted it as it is.!


and also can u pls say me how to edit the sql ( in linux)
in windows we can type ed and press enter and can correct the previously exec command and can save it and close it..
but here in linux how can i do it?.. how to save and close the editor?

[Updated on: Wed, 25 July 2012 03:08]

Report message to a moderator

Re: Sum of length of column data of all tables [message #561758 is a reply to message #561754] Wed, 25 July 2012 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In the same way, you just have to set the _editor variable to your favorite editor.
For instance, "set _editor=vi"

Regards
Michel

Re: Sum of length of column data of all tables [message #561828 is a reply to message #561758] Wed, 25 July 2012 09:09 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
ohk and how can i get the output in a text or excel file?? with out using spool??

thanks in advance
Re: Sum of length of column data of all tables [message #561836 is a reply to message #561828] Wed, 25 July 2012 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't, there is only one write command in SQL*Plus: spool (of course you could do it yourself in PL/SQL with UTL_FILE).

Regards
Michel
Re: Sum of length of column data of all tables [message #561871 is a reply to message #561836] Wed, 25 July 2012 23:41 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
ohk...so can u say me how can i use the utl_file with the above code(little foot's)?
Re: Sum of length of column data of all tables [message #561878 is a reply to message #561871] Thu, 26 July 2012 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you search here, in the documentation and on the web, there are many examples.

Regards
Michel
Re: Sum of length of column data of all tables [message #561904 is a reply to message #561878] Thu, 26 July 2012 04:28 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
Hi michale i tried with the following code but ..it is throwwin an
error : invalid arguments utl_file.put_line ( cur_r.owner ||)

[Updated on: Thu, 26 July 2012 04:33]

Report message to a moderator

Re: Sum of length of column data of all tables [message #561905 is a reply to message #561904] Thu, 26 July 2012 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That might be the result of you making up your own syntax.
Re: Sum of length of column data of all tables [message #561907 is a reply to message #561905] Thu, 26 July 2012 04:34 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
    declare
     fhandle utl_file.file_type;  
      cursor c1 is
        select owner, column_name, table_name
          from all_tab_columns
          where data_type in ('NUMBER', 'VARCHAR2')
           and owner in ('SCOTT', 'FRANJO')
         order by owner, table_name;
      str       varchar2(200);
      l_sum_len number;
   begin
    fhandle := UTL_FILE.FOPEN('temp_test','Sfg2.txt','w');
     utl_file.new_line (fhandle);
     for cur_r in c1 loop
       str := 'select sum(length(' ||
               cur_r.column_name   ||
              ')) from '           ||
               cur_r.owner         ||'.'||
               cur_r.table_name;
 
       execute immediate (str) into l_sum_len;
       utl_file.put_line(cur_r.owner       ||
                            cur_r.table_name  ||
                            cur_r.column_name ||
                            l_sum_len
                           );
     end loop;
     
     UTL_FILE.FCLOSE(fhandle);
  
   end;
   /
   




this was the code am using....
and created a new dir and granted read ,write permissions to public and
when i execute the floowing statement , it is showing no value.
select value from v$parameter where name ='utl_file_dir;

[Updated on: Thu, 26 July 2012 04:36]

Report message to a moderator

Re: Sum of length of column data of all tables [message #561911 is a reply to message #561907] Thu, 26 July 2012 04:37 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you create a directory?

Nowadays, it can be found by querying
select * from all_directories

[Updated on: Thu, 26 July 2012 04:38]

Report message to a moderator

Re: Sum of length of column data of all tables [message #561912 is a reply to message #561911] Thu, 26 July 2012 04:41 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
create directory test as '/u02/test' ;
Re: Sum of length of column data of all tables [message #561913 is a reply to message #561912] Thu, 26 July 2012 04:43 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK. So ...?
a) did you find the directory?
b) did you fix the UTL_FILE.PUT_LINE syntax?
Re: Sum of length of column data of all tables [message #561915 is a reply to message #561913] Thu, 26 July 2012 04:44 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
no i didnt fixed that syntax.. and i didnt find the value for utl_fil in the v$parameter..
yes i find the directory
Re: Sum of length of column data of all tables [message #561916 is a reply to message #561915] Thu, 26 July 2012 04:47 Go to previous messageGo to next message
Littlefoot
Messages: 19469
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no value for the directory in V$PARAMETER; I told you to look at ALL_DIRECTORIES instead (which you did and yes, you found it).

Now, open UTL_FILE in Oracle documentation, search for PUT_LINE and fix the syntax.
Re: Sum of length of column data of all tables [message #561918 is a reply to message #561915] Thu, 26 July 2012 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
create directory test as '/u02/test' ;

Quote:
fhandle := UTL_FILE.FOPEN('temp_test','Sfg2.txt','w');


Regards
Michel
Re: Sum of length of column data of all tables [message #561920 is a reply to message #561918] Thu, 26 July 2012 04:53 Go to previous messageGo to next message
sarma.shp
Messages: 111
Registered: February 2012
Senior Member
ops its a mistake...
directory is temp_test only...

create directory temp_test as '/u02/temp_test';
Re: Sum of length of column data of all tables [message #561926 is a reply to message #561920] Thu, 26 July 2012 05:04 Go to previous messageGo to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TEMP_TEST must be in UPPER case in the FOPEN call.

Regards
Michel
Previous Topic: Need help with SQL statement
Next Topic: Oracle table design
Goto Forum:
  


Current Time: Wed Aug 20 22:11:16 CDT 2014

Total time taken to generate the page: 0.09291 seconds