learning about type of dml activity on an oracle table

[bob hollis]

I am trying to manage an 8.1.7 oracle database that supports an application.
Some of Oracle's management suggestions depend on how a table is used - just inserts - lots of updates, etc.

Is there some way to collect information about a table over time, and then be able to make some educated guesses about the type of changes?

What I've started to do is weekly analyzes, then run a script to collect some information from dba_tables and dba_indexes. Our application has been running for over 2 years, and I'm going to be rebuilding the database in a month or so. I'd like to know what tables might benefit from changes to pctfree or initrans or other changes. I've been able to identify tables with no records, tables with no growth, and tables that grow. Now I just need to take it a bit farther. What tables have pretty much all inserts with few updates? What tables have a lot of updates?
Suggestions?
Thanks

[Michael Kelly_5]

Bob,
have a look at the V$SQL* views in the database. You might be able to extract nad postprocess the data you want.

HTH,
Michael.

[Alexander M. Ermes]

Hi there.
Ever thought about using the Oracle Enterprise Manager ? Uselful tool.
What about doing an export of the database structure ( full export with no rows / compress extents =y ) ?
That might give you an idea, how much space you will need.
You can edit the export file and extract the create statements for the tablespaces.
Rgds
Alexander M. Ermes

[Steven Gillard_2]

In 8.1.7 you can configure a table for monitoring using:

SQL> alter table monitoring;

Then, use the dbms_stats package to periodically collect table statistics. The dba_tab_modifications view will be updated with this information (number of inserts, updates, deletes etc). Under 8i this information is only updated every 3 hours so you may have to wait for a while to see the data.

If you've got metalink access have a look at note 102334.1 for full details.

Regards,
Steve

[bob hollis]

Thanks for all the suggestions, I'm checking them out.
Just to clarify my issue.

I need a way to look at a table and recognize that
this table is pretty much all inserts with few updates
where another table would have a lot of updates.

Like I said before, I am collecting periodic statistics, so I
have a good idea which tables are growing. I'm trying to find out
what tables have a lot of chaining or migration. The same for indexes,
which ones need to be rebuilt, or have parameters changed?

There is a lot of information on metalink, but it's dependant on knowing
the characteristics of a particular table - how do I find that out?

Thanks again