Skip to main content
P
pham_mn
Known Participant
December 28, 2021
Question

Clean up/remove unused tables

  • December 28, 2021
  • 2 replies
  • 405 views

Hello all:

I am assigned to clean up or remove some unused tables in our Oracle database.  I have never done it before therefore I am not sure what is best approaching. 

I asked my DB administrator if we could generate the report from backend that shows the statistics of each table.  The statistics should include the date and time when it hit last time.  He said they "scan" the DB daily, so all tables get hit daily.

I wonder if we can approach the task using frontend via ColdFusion.  I wonder if there is anyone out there who has done it before.

Thank you very much for your time

Hung Pham

pham_mn@yahoo.com

This topic has been closed for replies.

2 replies

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
December 28, 2021

Hung, while you COULD of course add that sort of code-based tracking/auditing to every such query or so call, that would seem very time-consuming and not completely reliable (what if you missed some code).

 

Someone might point out that this might be a job for the jdbc driver or library, but I'll say I did some digging and didn't find anything compelling, at least not readily. (You or others may want to dig in further.)

 

But following on your exchange with BKBK, I will note that oracle does have auditing. And though your dba asserts that their reports /stats would be complicated by hits from backend jobs, I suspect this auditing could be told to ignore that backend process. For more, see (or point them) to resources like this:

 

https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50000

/Charlie (troubleshooter, carehart. org)
    P
    pham_mnAuthor
    Known Participant
    December 28, 2021

    Thank you so much for your time, Charlie.  I will talk to my DBA about your input/comment

    Hung Pham

    P
    pham_mnAuthor
    Known Participant
    December 28, 2021

    I am wondering if there is a script or CF Codes to generate a report that shows some <cfquery> that never been in used or called?

    Thank you

     

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    December 28, 2021

    Sounds very much like a database question, rather than a programming one. Search the web for an Oracle forum and ask the question there.  

    P
    pham_mnAuthor
    Known Participant
    December 28, 2021

    Thank you.  Yes, I am very much thinking this is database related...unless we could write code like below

     

    <cfquery name="aQuery" datasource="aDataSource" result="result">
    SELECT
    FROM aTable
    </cfQuery>
    <cfinvoke method="writeAuditLog" queryResult="#result#" whatTable = "aTable">

     

    Where 'writeAuditLog' is a function to store when/what table is called in a table

    Just q quick thought though

     

    Thanks

     

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices