finding modified tables/partitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: finding modified tables/partitions

Hybrid View

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    finding modified tables/partitions

    As part of our backup/recovery process we don a RMAN backup along with
    a full export of a 4.5TB database. To limit what is pumped out I would
    like to limit my export to only those tables/partitons, which have changed
    for the current day or maybe the day before.

    By change, I mean any inserts,updates or deletes.....

    Does anybody know of such a query that will give me this information,
    which I can use to dynamically build a parfile through a shell script than
    invoke expdp.

    Any examples would be greatly appreciated.

    Thanks to all who answer.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Do you have a last_update_date column in those tables? If so, you can filter on those. Otherwise, no (with respect to Data Pump, and an export is not a backup). What would be exported for deletes?

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    No I dont have an update column on alot of these tables.

    What I was looking for was a last modified time in the data dictionary for each table and or partition...

    Just out curiosity, how does a RMAN incremental backup keep track of the
    changes.... there must be some logic to figure out what blocks have changed

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    there is no option to find last modified DML time. RMAN tracks this using block change tracking option which needs to enable explicitly.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Out of curiosity... may I ask what's the purpose of taking a full export of a 4.5TB database when you already have in place an RMAN based backup/recover strategy?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width