Solved

get size of an empty column in oracle

Posted on 2010-11-17
5
326 Views
Last Modified: 2012-05-10
Hi there,

 I have a heap of tables in a new database that are all blank with some changes dripping in every now and again to the design.

I want to get the size in bytes of the columns in each table... not the length of the data in the column as there is none. So I need to add up the size of number, size of nvarchar 100 etc for a table.  I can only see Avg row size and functions to give me the size of data contained in the row.

PS.. a nice cursor or something that would take in table names and do this would be cool

thanks M
0
Comment
Question by:MickyMc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34153594
would you be happy querying this system view:
http://ss64.com/orad/DBA_TAB_COLUMNS.html

which has the information you are asking for?
0
 

Accepted Solution

by:
MickyMc earned 0 total points
ID: 34153643
ok,

I did this but not sure if its correct...

select TABLE_NAME, SUM(DATA_LENGTH) from all_tab_Columns
where TABLE_NAME like 'New_%'
GROUP BY TABLE_NAME

I use Like 'New' as all the new tables are prefixed with this but later this will be removed...

Is this the correct way to do it... thanks M
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34153677
yes, that sounds like a good start :)
0
 

Author Comment

by:MickyMc
ID: 34153726
Cool Angel... this was easier than working out the data size in a row..lol  so this is correct... if so I will award the points... thanks for the link...rgds m
0
 

Author Closing Comment

by:MickyMc
ID: 34186410
thanks Angel
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question