RE: Compare the *size* of different schemas

Linda,

    When I saw your reference to 'cloning' below, I first thought you were taking physical copies of your files. Perhaps this is something you might want to consider in the future. For one thing, you just have to add up file sizes to see how much space is required on the target machine. With exp/imp I think that you would make your life much simpler using first COMPRESS=N when exporting and then just checking total sizes. You will no longer need big contiguous chunks - having as much space on the target database as on the source database will usually be enough. I don't see why you want to get down to the extent level - it looks like an overkill.

HTH SF

>----- ------- Original Message ------- -----
>From: "Seley, Linda" <LSeley_at_IQNavigator.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Fri, 21 Nov 2003 07:14:43
>
>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: LSeley_at_IQNavigator.com
>
>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: ListGuru_at_fatcity.com (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).
>---------------------------------------------------
>------------------

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).