Re: SQL Help

I guess I have to reformat the question:

This is what I am trying to achieve:
I have a production database "prod" and a test database "test". I have to create one more db environment for develop purposes. Instead of creating a separate instance/database with the SGA, we are planning to create a separate schema under the TEST instance and use it as a logical database. This is what we are planning to do:

1. take an export of PROD
2. create a new user "develop" under TEST instance.
3. Import "PROD" into the TEST instance under the "DEVELOP" schema.

For doing so, I want to make sure that the TEST instance's tablespaces have enought space before I import "PROD" into it. I have a dblink from TEST to PROD. My sql look like this:

select a.tablespace_name, sum(a.bytes) "Free in TEST", sum(b.bytes) "used in PROD", sum(a.bytes) - sum(b.bytes) "Fit" from dba_free_space a, dba_segmets_at_prod b where a.tablespace_name=b.tablespace_name group by a.tablepsace_name;

When I run this query, I do not get any errors. Instead the values reported for "Free in TEST" is kind of sum of free space from TEST and PROD.

"John Findlay" <john_findlay_at_non.agilent.com> wrote in message news:3D57EE78.5060300_at_non.agilent.com...
> Daniel Morgan wrote:
> > Joe Philip wrote:
> >
> >
> >>I want to find the free space and used space from a database. I wrote
the
> >>following sql but does not work:
> >>
> >>select sum(a.bytes) free, sum(b.bytes) used
> >>from dba_free_space a, dba_segments b
> >>where a. tablespace_name=b.tablespace_name
> >>group by a.tablespace_name
> >>/
> >>
> >>What am I doing wrong?
> >
> >
> > A number of things. But the most obvious is that you did not post the
error
> > message or the version which should be a standard part of every posting.
> >
> > In this case the most obvious problem is that you have a GROUP BY clause
and
> > nothing being grouped.
> >
> > Group by would only be of value if you rewrote your query in this form:
> >
> > select A.TABLESPACE_NAME, sum(a.bytes) free, sum(b.bytes) used
> > from dba_free_space a, dba_segments b
> > where a. tablespace_name=b.tablespace_name
> > group by a.tablespace_name
> > /
> >
> > Daniel Morgan
> >
>
> Daniel is quite correct. You may wish to try the following query:
>
> /* If running this in a narrow (80 character) terminal emulator */
> /* you may wish to cut the linesize down to match, in which case the
> lines will wrap */
> set pages 30
> set linesize 200
> clear columns
> clear breaks
> column tbsp format A25 heading 'Tablespace'
> column filenam format A30 heading 'File'
> column fsz format 99999.999 heading 'File Size MB'
> column tott format 99999.999 heading 'Free MB'
> column numm format 999999 heading 'No. of chunks'
> column maxx format 99999.999 heading 'Max Chunk MB'
> column avgg format 99999.999 heading 'Avg Chunk MB'
> column minn format 99999.999 heading 'Min Chunk MB'
> break on tbsp skip1
> select
> substr(ts1.tablespace_name,1,25) tbsp,
> df1.file_id, substr(df1.file_name,1,30) filenam,
> df1.bytes/(1024*1024) fsz,
> sum(fsp1.bytes)/(1024*1024) tott,
> count(*) numm,
> max(fsp1.bytes)/(1024*1024) maxx,
> avg(fsp1.bytes)/(1024*1024) avgg,
> min(fsp1.bytes)/(1024*1024) minn
> from
> sys.dba_tablespaces ts1,
> sys.dba_data_files df1,
> sys.dba_free_space fsp1
> where
> df1.tablespace_name = ts1.tablespace_name
> and fsp1.file_id(+) = df1.file_id
> group by ts1.tablespace_name, df1.file_id, df1.file_name,
> df1.bytes/(1024*1024)
> /
>
> The result will tell you not only how much space there is but also
> the number of chnuks it comes in etc..
>
> You'll need to remove the formatting if using it in a tool other than
> SQL*Plus (e.g Toad). Run it as SYS or another account with relevant
> privileges.
>
> John S. Findlay
>
Received on Mon Aug 12 2002 - 15:04:25 CDT