Recently I copied an Oracle database from one machine to another.
- Original: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
- Copy: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production For some reason the following query works on the original but not on the copy.
If a t.tdt_id is substituted in place of tdt_id in the group by the query works (as per td_tablespaces t )
Can someone explain why this is and if it has something to do with the installation. The data is the exact same in both.
SELECT
tdt_name as "name",
tdt_contents as "Content",
tdt_status as "Status",
round(( subsize.SubSize_MB ), 2 ) as "size used",
round(( sum( MaxMB ) - subsize.SubSize_MB ), 2 ) as "size free"
FROM
td_tablespaces t
INNER JOIN
(
SELECT
tdt_id,
CASE
WHEN upper( d.df_autoextend ) = 'YES' THEN round(( sum( df_maxsize ) / 1024 ), 2 )
WHEN upper( d.df_autoextend ) = 'NO' THEN round(( Sum( df_size ) / 1024 ), 2 )
END as MaxMB
FROM
td_datafiles d
GROUP BY
tdt_id,
d.df_autoextend,
df_maxsize,
df_size
) sub
ON
t.tdt_id = sub.tdt_id
INNER JOIN
(
SELECT
tdt_id,
sum( df_size / 1024 ) -( df_free / 1024 ) SubSize_MB,
df_free / 1024 SubSize_Free
FROM
td_datafiles df
GROUP BY
tdt_id,
df_free
) subsize
ON
t.tdt_id = subsize.tdt_id
WHERE
ins_id = 2096
GROUP BY
tdt_id,
tdt_name,
tdt_contents,
tdt_status,
subsize.SubSize_MB,
SubSize_Free
ORDER BY tdt_contents
