I'm wondering if someone has a better solution than mine (see below) to the following: We have a number of schemas that get cloned from our production schema (more on that later). I need to be able to compare the size of the production schema to the target schema and determine how much the objects have grown. The part I'm working on/struggling with is how to determine whether or not the target tablespace has enough space to handle the additional space required.
What I have so far: I have a "statistics" database that I've modified to store object information for each schema (which database/tablespace it came from, how big it is now, how big the next extent will be, etc). I've also got the query that says this schema is x bytes/blocks larger in the production database than the target database. From this I'm able to figure out how many extents will be needed in the target database to handle the size growth. My problem: I can't just compare the size of the next extent to the largest free chunk in the tablespace. While that's useful information it won't alert me if I've only got room for one extent but will need two. If the target schemas were refreshed regularly then this might work since any given object should not have extended more than once (or a small number of times) but sometimes weeks or months go between refreshes. Along the same lines I can't add all of the extents and try to fit them in the total free space because the blocks may not be contiguous. (We have a mixture of extent sizes, I'll convert someday, really I will!) In addition, if there are 5 tables that have grown I'd like to be able to determine if table 1 is going to use up all of the free space and tables 2-5 won't have enough space to extend. Etc. My 'best' solution: Build a table of existing free space for each target database/tablespace and do mock updates attempting to mimic Oracle's behavior then, from that, determine if I will run out of space. This seems cumbersome and time-consuming but it's the only reasonably accurate solution I've come up with. Does anyone have a better idea? Has anyone done something similar? Some background about our environment: Currently we're exporting/importing to get the production data into the other schemas. In some instances we drop the tables first then import, in others we truncate then import. In the future some of the tables that are being truncated will be incrementally updated (unless the structure changes then they'll be dropped and re-imported). The table structures are identical, in general the initial and next extents are identical but that isn't true for all objects. The target schemas are used for development, test, reporting, etc. Thanks for taking the time to read this! Linda -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seley, Linda INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
