0

I’ve been tasked with doing some housekeeping on an Oracle schema I have access to. In a nutshell, I’d like to drop any tables that have not been ‘used’ in the last 3 months (tables that haven’t been queried or had data manipulated in the last 3 months). I have read/write access to the schema but I’m not a DBA; I run relatively basic DML/DDL queries in Oracle.

I’m trying to figure out if there’s a way for me to identify old/redundant tables; here’s what I’ve tried (mostly unsuccessfully)

  • USER_TABLES was my first port of call, but the LAST_ANALYZED date in this table doesn’t seem to be the last modified/queried date I’m looking for

  • Googling has brought DBA_Hist tables to my attention, I’ve tried querying some of these (i.e. DBA_HIST_SYSSTAT) but I’m confronted with (ORA-00942: table or view does not exist)

  • I’ve also tried querying V$SESSION_WAIT, V$ACTIVE_SESSION_HISTORY and V$SEGMENT_STATISTICS, but I get the same ORA-00942 error

I’d be grateful for any advice about whether the options above actually offer the sort of information I need about tables, and if so what I can do to work around the errors I’m getting. Alternatively, are there any other options that I could explore?

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
Bal
  • 13
  • 1
  • 5
  • Accessed by whom? other schema's? applications logging in with the same schemaID? Do you have DEPENDENCIES enabled on any of the tables? This enables [ora-rowscn](https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) and would give you a column which can translate to a datetime as to when a record was last changed. or reference: https://stackoverflow.com/questions/14830875/find-out-the-history-of-sql-queries for "recent" sql executed. You could talk to a db about getting permission to DBA_HIST_SYSSTAT.. it exists, you just may not have permission. – xQbert Feb 19 '18 at 14:32
  • Thanks for your thoughts. Tables are read and written to almost exclusively by an app user and some tables do have dependencies. The ora-rowscn pseudocolumn ticks the box with regard to table updates, is there some way to return the SCN’s for multiple tables rather than running on one table at a time, ideally I want to run a schema wide query returning table name where SCN_TO_TIMESTAMP(ORA_ROWSCN) < 90 days? V$SQL may prove useful if I can get the necessary access to query it; how do I pinpoint which tables have been queried, do I need to delve into the SQL_FULLTEXT to extract this? – Bal Feb 19 '18 at 16:19
  • Unfortunately yes, you'd have to delve into sql_fulltext. You could use system table `user_tables` to dynamically generate a SQL statement that would union all the tables together and query something like `SELECT 'SELECT ' ||chr(39) || table_name ||chr(39)|| ' as TableName ' || 'FROM ' || table_name || ' UNION ' FROM user_tables WHERE ...` but this is dependent on each table in the user's schema having dependencies enabled with valid row_Scn's you just delete the last union statement after the result generates and then execute the dynamic SQL. Enabling audit for a few months seems best – xQbert Feb 19 '18 at 16:33

1 Answers1

1

Probably the easiest thing to do, to be 100% sure, is to enable auditing on the Oracle tables that you're interested in (possibly all of them). Once enabled, Oracle has an audit table (dba_audit_trail) that you can query to find if the table(s) have been accessed. You can enable auditing by issuing: AUDIT on . BY SESSION;

I chose "by session" so that you only get a single record per session, no matter how many times the session performs the operation (to minimize the records in the audit table).

Example:

audit select on bob.inventory by session;

Then you can query the dba_audit_trail after some time passes to see if any records show up for that table.

You can disable auditing by issuing the "noaudit" command.

Hope that helps.

-Jim

Jim Wartnick
  • 1,974
  • 1
  • 9
  • 19
  • Thanks Jim, don't have access to dba_audit_trail; access seems to be half the issue! Appreciate your input, I've got something to go back with and try and get my access bumped up. – Bal Feb 19 '18 at 16:22