Home » SQL & PL/SQL » SQL & PL/SQL » View for total length in bytes for all columns in tables
View for total length in bytes for all columns in tables [message #353977] Wed, 15 October 2008 23:43 Go to next message
vipull.sayal
Messages: 4
Registered: February 2008
Junior Member

Hello,

can anyone please tell me how can I see in the total length in bytes for all columns of a table(or all tables in schema).

So for e.g. I create a table..

create table employee (
emp_id number(5),
emp_name varchar2(30),
emp_dept varchar2(15)
);

Now to calculate otal length of all columns I have to manually do 5+30+15 = 50 bytes.

Is their any view/shortcut for this??

I have around 150 columns on avg in a table. Their are around 106 tables.. Is their any shortcut to calculate this for all tables in a schema??

Thnx,
Vipull
Re: View for total length in bytes for all columns in tables [message #353989 is a reply to message #353977] Thu, 16 October 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_tab_columns.data_length
user_tab_columns.avg_col_len
user_tables.avg_row_len

These are the only information you can get and the last ones only if you gathered statistics on the table.

Regards
Michel
Re: View for total length in bytes for all columns in tables [message #353999 is a reply to message #353977] Thu, 16 October 2008 00:56 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Also here

you can see the used bytes in a table:

First you analyze your table, then

Execute the query:

SELECT table_name, num_rows * avg_row_len
FROM user_tables;
--------------------
Another is

you can use "LENGTHB" function to the column bytes

SELECT LENGTHB (NAME)
FROM ins;
Re: View for total length in bytes for all columns in tables [message #354094 is a reply to message #353999] Thu, 16 October 2008 07:14 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
VSIZE() is another function you can use.
Previous Topic: Where the cursor's (implicit/explicit) result is stored...?
Next Topic: Using FORALL
Goto Forum:
  


Current Time: Sat Dec 10 10:31:41 CST 2016

Total time taken to generate the page: 0.07802 seconds