I would like to calculate the size of a given table without using dba_tables or user_tables. Also, is there a way to retrieve the information of how often a table is accessed?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You can use DBMS_STATS package, specifically the GET_TABLE_STATS procedure.
If you are on 9i you can use DBMS_WM package to track all changes to the tables. Though not entirely sure about SELECTs on a table.
see this link http://asktom.oracle.com/p
To retrieve information on how often a table is accessed you would need to turn on auditing for that table. Oracle does not capture this information by default. Be aware that auditing has a performance penalty and can generate vast amounts of data that you need to manage. If you collect this auditing data in the database (it can be collected in a file instead) you may fill up and/or fragment your SYSTEM tablespace if you do not manage it carefully.
i) show the physical amount of space that a table currently occupies :-
select round(sum(bytes)/1024/1024
from sys.dba_extents
where segment_name = 'MY_TABLE';
ii) Use the AUDIT command to audit access to a table :-
http://download-uk.oracle.
as markgeer said, this has an overhead in that the audit data is written
to the SYSTEM tablespace. this can not be over-ridden.
You or your DBA's should manage this : the data shoould periodically be
moved elsewhere and/or removed.
The three views: dba_table, all_tables, and user_tables have exactly the same size information, so you can use all_tables to get that. Here is a query that I use to get size information on tables:
select table_name, NUM_ROWS, BLOCKS +1 "Blocks",
EMPTY_BLOCKS "Empty", AVG_SPACE, pct_free, CHAIN_CNT, AVG_ROW_LEN "Avg sz",
to_char(last_analyzed,'MM-
tablespace_name
from all_tables
where table_name like upper('&table_name');
You can try this query:
select substr(name,1,30) "Parameter", substr(value,1,35) "Value"
from v$parameter where name like 'db_block%';
You may or may not have access to "v$parameter" though.
In most shops "the database stuff" is the responsibility of the DBA who does have access to this information. That makes me wonder who's in charge of "the database stuff" in your organization? Do you have a DBA in house, or not? If yes, he/she should be taking care of this. If not, your management better be aware of the risks of running Oracle without a DBA.
Table sizes could be calculated!
Not only queried.
Estimate approximatelly the average size of a row in your table (take in account the empty colimn values). Use the query SELECT count(*)*average_row_lengt
Business Accounts
Answer for Membership
by: markgeerPosted on 2003-06-03 at 08:20:05ID: 8639505
You can use dba_extents to get the size of a table (it contains the blocks allocated to a table, but it does not indicate whether those blocks are used, partly full or empty). Dba_table, all_tables and user_tables certainly have more precise information on table sizes, if your tables are analyzed regularly. Can you tell us why you don't want to use the best source for this information?
If you want to estimate the size manually, and you know the number of records and the size (number of bytes) of each record, you can just multiply those two values. Of course the actual size will always be larger because of some overhead space in each block. Also, unless you have pctfree set to 0 there will usually be some free space in each block as well. If your application allows deletes, there will be additional free space where deletes occured.