Already a member?
Sign in
Welcome! This is a website that everyone can build together. It's easy!
Location: The Official Oracle Wiki
Discussion: how to find the size of table in oracle
Keyword tags:
ACBS
content management
DBA Certitification
ibr
idc
middleware
oracle
oracle applications
Oracle eAM
site studio
stellent
ucm
Watch
|
ajay_kang ajay_kang |
how to find the size of table in oracle
Jun 12 2008, 2:04 AM EDT 1)how to find the size of table in oracle? Please suggest. 2)I am trying to truncate a table have millons of records and having 11k partitions. ddl statement (truncate) doesnt comes out after hours. when I used drop table commands it succeed. Please explain why? 3) Is it ok to rebuild indexes on every monday and analyse them on every friday? Thanks in advance. 0 out of 3 found this valuable. Do you? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:06 PM EDT Hi, 1) I use this sqlplus script to know the size of a particular table and their indexes. define W_OWNER = &Informe_OWNER; define W_TABLE = &Informe_TABLE; col Object format a45; col Bytes format 999G999G999G999G999; BREAK on REPORT on SEGMENT_TYPE; COMPUTE SUM label 'Total....: 'of BYTES on REPORT SEGMENT_TYPE; select SEGMENT_TYPE ,OWNER || '.' || SEGMENT_NAME as "Object" ,BYTES ,TABLESPACE_NAME ,EXTENTS ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS ,MAX_EXTENTS ,PCT_INCREASE from DBA_SEGMENTS where OWNER='&W_OWNER' and SEGMENT_NAME in (select TABLE_NAME from DBA_TABLES where OWNER='&W_OWNER' and TABLE_NAME='&W_TABLE' union select INDEX_NAME from DBA_INDEXES where OWNER='&W_OWNER' and TABLE_NAME='&W_TABLE' union select SEGMENT_NAME from DBA_LOBS where OWNER='&W_OWNER' and TABLE_NAME='&W_TABLE') order by 1,2,3 / clear BREAK; 2) I don't know. 3) I don't think a good strategy rebuild your indexes every week. Actualy, it's not good rebuild at all. I've read some articles in asktom.oracle.com, and Tom Kyte recomend DO NOT rebuild your indexes. I don't have now the link for this page. However I will post part of the page. 3 out of 4 found this valuable. Do you? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:09 PM EDT The First ask for Tom. I have a query regarding Index rebuild . what according to you should be time lag between index rebuilds. We are rebuilding indexes every week .but we found it is causing lot of fragmentation. is there any way we could find out whether we should rebuild the indexes and also what indexes we should rebuild instead of rebuilding all the indexes. Please also suggest whether it is good to drop and recreate the indexes ( to avoid fragmentation ) or to rebuild them. Do you find this valuable? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:12 PM EDT The answer and we said... The time lag between index rebuilds should be approximately FOREVER. Tell me -- why why why WHY are you rebuilding them on a schedule? What is the scientific basis in reality that is driving you to do this???? Here is an email I got once: 0 out of 1 found this valuable. Do you? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:13 PM EDT The second question Why does an rebuilding an index cause increased redolog generation AFTER the index has been built? I have a table 35 million rows and an index (nothing is partitioned) Transactions against this table are constant. It's always 500,000 rows per day. This generally creates 10 logs a day Once a month the indexes are rebuit. (Alter index rebuild) On the day following the indexes rebuild 50 logs are created On the following days 45...40...35...30....25....down to 10 at 10 logs this remains constant at 10 Mining the logs we see that we have increase INTERNAL INDEX UPDATES Why does this happen?? Is this always the case?? Do you find this valuable? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:14 PM EDT The answer (part 1) So, what do you think was happening here? Well, the fact is that indexes, like people, have a certain "weight" they like to be at. Some of us are chubby -- some skinny -- some tall -- some short. Sure, we can go on a diet -- but we tend to gravitate BACK to the weight we were. The same is true for indexes -- what happened to this person is their index wanted to be wide and fat and EVERY MONTH they rebuilt it (put it on a diet). It would spend the first half of the month then getting fat again and generating gobs of redo due to the block splits it was undergoing to get there. In this case, rebuilding the index on their system had these effects: o the system would generate 4.5 times the redo o the system would run slower o the system would consume more resources (CPU, IO, latching, etc) o the system would not be able to handle the same user load until the system got back to where the system actually wanted to be. And then -- AND THEN -- they (the dba's) would do it all over again!!!! They would destroy the equilibrium that the system worked so hard to get to. Bravo!! (can you tell what my opinion is on regularly scheduled index rebuilds??? I despise them). Do you find this valuable? |
|
yusukeyurameshi yusukeyurameshi |
RE: how to find the size of table in oracle
Jun 14 2008, 6:14 PM EDT The answer (part 2) If you are suffering from fragmentation -- your problem is not the index rebuilds. It would be because you are using DICTIONARY managed tablespaces. Switch over to locally managed tablespaces and you'll never have fragmentation again. So, my suggestion -- one last index rebuild: o create a locally managed tablespace o alter index <iname> rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k ); and then forget about them. You will rebuild an index in response to IDENTIFIED and DEGRADED performance -- period. You will not rebulid indexes based on a schedule. Period. It is the RARE index that needs to be rebuilt. It is not a rule that indexes need to be rebuilt. There are extreme cases, particularly with regards bitmapped indexes after mass data loads, where rebuilding is suggested. But in a day to day system that is transactional -- the need to rebuild indexes is sooooo rare (that I've never actually done it myself in 15 years except to MOVE an index from one physical location to another -- and even then, we don't need to do that very much today at all with logical volumes and their like) 2 out of 3 found this valuable. Do you? |
