Re: Report what tables or columns being used and how often.

On Sep 18, 12:57 pm, sriv..._at_hotmail.com wrote:
> You could use sys.COL_USAGE$.
> This is actually used by the dbms_stats package while generating
> column statistics.
> regards
> srivenu

Presuming the OP has DBA access. But, the output from that table/view is rather cryptic:

      OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

---------- ---------- -------------- -------------- -----------------
----------- ---------- ---------- ---------
        16          1            272          23800
0        1067          0          0 18-SEP-08
        16          2          22617           1683
0           0          5          0 18-SEP-08
        16          4          22828              0
0           0          0          0 18-SEP-08
        16          5          22542              0
0           0          0          0 18-SEP-08
        16         20          22344              0
0           0          0          0 18-SEP-08
        18          1           2223           5177
0           2          0          0 18-SEP-08
        18          3           1654           6006
89           0          0          0 18-SEP-08
        18          4           5433           1870
0           0       1804          0 18-SEP-08
        18          6           1256              0
0           0          1       1047 18-SEP-08

Possibly a bit more help, in the form of an example query, might better serve the purpose here? Let's add in DBA_OBJECTS and DBA_TAB_COLUMNS to provide names and owners for those tables/columns:

select o.owner, o.object_name, c.column_name,

       u.equality_preds+u.equijoin_preds+nonequijoin_preds+range_preds +like_preds+null_preds usage_ct,

       u.timestamp last_used
from sys.col_usage$ u, dba_objects o, dba_tab_columns c where o.object_id = u.obj#

and c.table_name = o.object_name
and c.column_id = u.intcol#
and o.object_type = 'TABLE'
and o.owner = upper('&owner')

The output from that query is much easier to interpret:

OWNER OBJECT_NAME

COLUMN_NAME                      USAGE_CT LAST_USED
------ ----------------------------------------
------------------------------ ---------- ---------
SYS    DIM$
OBJ#                                    1 29-AUG-08

       RLS_GRP$
OBJ#                                    1 29-AUG-08

       RLS_CTX$
OBJ#                                    1 29-AUG-08

       EXPPKGOBJ$
PREPOST                                 1 29-AUG-08

       CDC_CHANGE_TABLES$
CHANGE_TABLE_SCHEMA                     1 29-AUG-08

       FGR$_FILE_GROUPS
FILE_GROUP_ID                           1 29-AUG-08

       FGR$_FILE_GROUP_VERSIONS
FILE_GROUP_ID                           1 29-AUG-08

       FGR$_FILE_GROUP_EXPORT_INFO
VERSION_GUID                            1 29-AUG-08

       FGR$_FILE_GROUP_FILES
VERSION_GUID                            1 29-AUG-08

       FGR$_TABLESPACE_INFO
VERSION_GUID                            1 29-AUG-08

       FGR$_TABLE_INFO
VERSION_GUID                            1 29-AUG-08

Again, this presumes the user running the query has DBA privilege or access to SYS.COL_USAGE$. If not, well ...

David Fitzjarrell Received on Thu Sep 18 2008 - 15:13:38 CDT