-
Hi All,
I have to get table and index spaceing reports everyday in the production environment. I wrote the script, displayed the following columns, tablename, tablespacename, blocks used, free blocks, avg.lenth. from the dba_tables/user_tables...
I calculated the Initial in MB, based on the num_rows and avg_row_len columns.
like, (num_rows * avg_row_len / 2^20)
Samething, I want to evaluate, next, pctincrease and so...
Please, update me asap, If anyone have the formula of the above...
Thanks
-Tami
-
I am not clear with what you wanted, I guess this z what you wanted... try running this and see the results are what you wanted...This z at tablespace level.
rem -------------------------------------------------------------
rem Tablespace Usage
rem -------------------------------------------------------------
set pagesize 66
set line 132
clear breaks
clear computes
column "Total Bytes" format 9,999,999,999
column "SQL Blocks" format 999,999,999
column "VMS Blocks" format 999,999,999
column "Bytes Free" format 9,999,999,999
column "Bytes Used" format 9,999,999,999
column "% Free" format 9999.999
column "% Used" format 9999.999
break on report
compute sum of "Total Bytes" on report
compute sum of "SQL Blocks" on report
compute sum of "VMS Blocks" on report
compute sum of "Bytes Free" on report
compute sum of "Bytes Used" on report
compute avg of "% Free" on report
compute avg of "% Used" on report
select substr(fs.FILE_ID,1,3) "ID#",
fs.tablespace_name,
df.bytes "Total Bytes",
df.blocks "SQL Blocks",
df.bytes/512 "VMS Blocks",
sum(fs.bytes) "Bytes Free",
(100*((sum(fs.bytes))/df.bytes)) "% Free",
df.bytes-sum(fs.bytes) "Bytes Used",
(100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id(+) = fs.file_id
group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
order by fs.tablespace_name;
Try getting info (@ table level)you wanted from DBA_TABLES on the similar lines... I have one at work, if you want it... I can send it tomorrow.
[Edited by sreddy on 12-28-2000 at 09:19 PM]
-
I took the following columns, table_name, tablespace_name, num_rows, avg_row_len from the user_tables, after I executed the analyze table command...
Followed, I'm calculating the Initial, next, pctincrease values based on the above, num_rows and avg_row_len colums.
Already, I calculated the Initial values, based on the following method,
Initial value (in bytes)= (num_rows * avg_row_len) / (2 ^ 20)
Samething, I want to calculate the other column values, next and pctincrease...
....
[QUOTE][i]Originally posted by sreddy [/i]
[B]I am not clear with what you wanted, I guess this z what you wanted... try running this and see the results are what you wanted...
rem -------------------------------------------------------------
rem Tablespace Usage
rem -------------------------------------------------------------
set pagesize 66
set line 132
clear breaks
clear computes
column "Total Bytes" format 9,999,999,999
column "SQL Blocks" format 999,999,999
column "VMS Blocks" format 999,999,999
column "Bytes Free" format 9,999,999,999
column "Bytes Used" format 9,999,999,999
column "% Free" format 9999.999
column "% Used" format 9999.999
break on report
compute sum of "Total Bytes" on report
compute sum of "SQL Blocks" on report
compute sum of "VMS Blocks" on report
compute sum of "Bytes Free" on report
compute sum of "Bytes Used" on report
compute avg of "% Free" on report
compute avg of "% Used" on report
select substr(fs.FILE_ID,1,3) "ID#",
fs.tablespace_name,
df.bytes "Total Bytes",
df.blocks "SQL Blocks",
df.bytes/512 "VMS Blocks",
sum(fs.bytes) "Bytes Free",
(100*((sum(fs.bytes))/df.bytes)) "% Free",
df.bytes-sum(fs.bytes) "Bytes Used",
(100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id(+) = fs.file_id
group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
order by fs.tablespace_name;
[/B][/QUOTE]
-
I got you, but don't have as such formula on top of my head.
I never even think about any formula in calculating my next extent size from the fragmentation point of view. I will make sure that I will end up having my Initial and Next extents sizes are same always and thats how I try to avoid fragmentation.
Same case with percentage increase and I will keep it at Zero.
This is what big guys/Oracle Support advise... you know !
I don't understand one thing here. Are you trying to caluculate this stuff for documentation and reporting purpose or creating the Objects with redefinded storage clauses?
Sorry ! Im not of much help to you...
-
Sreddy,
Please put the script on dba_table and dba_index here
when you get a chance. Thanks!
Linda
-
Linda, what do you mean by dba_table and dba_index... you mean reporting script/ something else... clarify me.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|