I have a doubt regarding oracle sql

by Siddhartha
(Bangalore)

How to find unused tables in Particular Schema in Oracle?

Comments for I have a doubt regarding oracle sql

Average Rating starstarstarstarstar

Click here to add your own comments

Sep 26, 2016
Rating
starstarstarstarstar
Finding unused tables
by: John

The answer depends on how you define an unused table. It could mean one that is currently empty - in which case this is a simple problem to solve.

Fortunately for us every Oracle database includes a data dictionatu. This contains a significant amount of metadata - data about the data - including the name of every obhect in the database (tables, columns, indexes, constraints, views, privileges, triggers, PL/SQL procedures, PL/SQL functions etc) as well as the ownwer of the object and a number of other attributes in a varity of views, the most pertinent of which in this case is USER_TABLES.

This view will tell you for each of your tables how many blocks of data it has. Note however that a table may still be using 1 more blocks of data even when it contains no rows if its high water mark has not been reset.

Issuing the SQL command TRUNCATE my_table; will reset the high water mark (to 0) but deleting rows from a table won't (even after a commit) so this is not a foolproof way of finding out which of your tables have no rows.

A better way to do this would be to select the NUM_ROWS column in USER_TABLES for each of your tables. One important point to be are of though is that neither the number of blocks used by a table nor the number of rows it contains will be stored in USER_TABLES until statistics have been gathered on the table. Before then both thses columns will have NULL values.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.