Solved

get size of an empty column in oracle

Posted on 2010-11-17
5
321 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
  • 3
  • 2
5 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Author Comment

by:MickyMc
Comment Utility
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
Comment Utility
thanks Angel
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now