How to get the list of busiest tables (insert, update) in Oracle

Printer-friendly version

To obtain a rough list of tables with more movements of the database can query the table contents and cross dba_tables the current status of each table in the database. This may make sense when we make a list of tables that must be updated periodically or statistics we control the amount of information generated by a particular application. The data we get from each table are always compared to the previous analysis of the same.

The following way is a little "rock" but useful at a time:

  1. We connect to the database as system and run the following query that will return a list of selects with all tables in the database (it is better not to include filtered to the system tables or include only a particular user .)In the example we will get only those of a particular user:
    select 'select''' | | table_name | |'''as TABLE,''' | | sysdate | |
    '''As FECHA_ACTUAL,''' | | last_analyzed | |
    '''As ULTIMO_ANALISIS, count (*) as COUNT,' | | num_rows | |
    'As RECUENTO_ANALISIS, to_date (''' | | sysdate | |
    ''',''DD / MM / YYYY'') - to_date (''' | | last_analyzed | |
    ''','' DD / MM / YYYY'') as DIAS_DESDE_ANALISIS, count (*) - '| | num_rows | |
    'As DIFERENCIA_RECUENTO (count (*) -' | |
    num_rows | | ') / (to_date (''' | | sysdate | |
    ''',''DD / MM / YYYY'') - to_date (''' | | last_analyzed | |
    ''','' DD / MM / YYYY'')) as INCREMENTO_DIARIO from '| | owner | |'. " | |
    table_name | | 'union'
    from dba_Tables
    WHERE owner = 'USER'

    Example of the result with plsql:

  2. Copy the entire column to the clipboard and remove the last union. Obtain the following result:

    We can see the table with data from the latest analysis of the table regarding the current and the variation with average daily number of records (taking into account that an insert (1row) + delete (1row) = 0movimientostaprix.com / sites / all / modules / fckeditor / fckeditor / editor / images / smiley / msn / tounge_smile.gif "alt =" "/>)

If we add other information such as sizes of row, if the table has indexes and what happens to us we can make other "craft" as accumulating these results in a table to see what's happening in our database. Of course, everyone can adapt this technique to your liking for your needs

 
Google
 
     

SQL08: Update table statistics dynamically throughout a database
As in Oracle there is a table which lists all tables in the database (dba_tables) and we can use it to do maintenance dynamically in SQL Server can do the same querying the table...
Pentaho Kettle Reading Solutions
Good. A few days ago I got the book Pentaho Kettle Solutions , courtesy of Roland Bouman (co-author of the book) and Wiley . Pentaho Kettle Solutions: Building Solutions...
New block events
We have created a new block that shows recent events which have been published, sorted descending by the date on which event will the event. So we always consider what is to come...
Detection of noisy data in databases using OLAP tools
Summary To perform a proper audit of systems is necessary that the data contained in databases having quality, it is first necessary to consider whether it is possible to...
Sales Facts table. Set physical design and ETL load process. Talend contexts.
We will develop processes to load the fact table sales of our project using Talend. Before this, we will make some considerations about the frequency of load processes will allow...
Video tutorial iReport / Struts (2 of 4)
Hello People, This is the second part of the tutorial video iReport / Struts. Greetings Mariano