Hot to get most used table in databse

[manju3606]

Hi to all,

I am new for oracle please can any one give me a query to get most used tables in the database. Please help me....



Thanks
Manjunath

[C CSR]

Explain what you mean by "to get" most used tables, and what does "most used" mean?

[manju3606]

I have 450 tables in a database i want to know which tables are using most of the time and which tables are not used from the date of created table

Thanks

[C CSR]

Does the database provide logs. Your activity should be in there. Of course, you can check any date/time fields you find in the tables. Which database are you using?

Size is a clue. The database should provide some stats on that and any timed procedures stored would be revealing. Back-ups usually maintain logs. Give me more to go on.

[manju3606]

I want to know which tables are used between the date like from 1/12/2011 to 15/01/2012 and also how many times that tables are used in that date. Please give me the query..

Thanks

[C CSR]

Sorry manju, I don't have what your asking for. Hang in there for an Oracle guru.

[Rabbit]

You can do it if usage tracking was set up on the database.

[amitsukte]

Try this Manju.. This may Help..
SELECT COUNT (*), object_name
FROM v$sql_plan
WHERE operation = 'TABLE ACCESS'
GROUP BY object_name
ORDER BY COUNT (*) DESC

[Syed naseer]

Thanks. amitsukte. It was very helpful.
Can you provide the query to check the connections between tables. In my database we have around 2300 tables i need to find the links between the schame and the tables

[pritikumari]

Most common problem how to find most and least used tables in SQL Server. This will help us in cleaning database and organize tables which we use and delete the unnecessary tables.

I created a SQL job runs every 3 hours to take backup of above view and incrementally load the temp table with new data. In this approach even the view data is lost, we still have the backup data in temp tables. After a month or so we have enough data to identify recently used tables and their last access and modified date. First we will create a Temp table with similar columns like view sys.dm_db_index _usage_stats.

BEGIN
IF :SYSTEM.LAST_RE CORD = 'TRUE'
THEN
MESSAGE ('At last record.');
END IF;
END;

Join Oracle Course In Noida.