Hello,
In "Database/Administer/Tablespaces" page when I click "Include Pluggable Databases" it throws "ORA-00904 TS.CON_ID invalid identifier" error. It is a container database 12.2 and Toad version is 14.2. Never faced this issue earlier Toad versions. I think this is a bug.
Hi,
I connected with Toad 14.2 to an Oracle 12cR2 container database, went to the tablespaces window and set my options like yours. I did not get any error. Please go to Main Menu -> Database -> Spool SQL -> Spool to Screen and try it again. This will give you the SQL that Toad is running. It's possible that if your database was upgraded from an earlier version, that this column is missing.
The query on my 12cR2 database looks like this:
SELECT t.con_id, t.tablespace_name, 'Datafile' file_type,
t.status tablespace_status, d.status file_status,
d.bytes - NVL(f.sum_bytes, 0) used_bytes,
NVL(f.sum_bytes, 0) free_bytes, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents, t.pct_increase,
d.file_name, d.file_id, d.relative_fno, d.autoextensible, d.maxblocks,
d.maxbytes / 1048576 maxbytes, nvl(d.increment_by,0) increment_by, t.block_size, t.chunk_tablespace, t.bigfile
FROM
(select con_id, tablespace_name, file_id, sum(bytes) sum_bytes
from cdb_free_space
group by con_id, tablespace_name, file_id) f,
CDB_DATA_FILES d,
CDB_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.relative_fno
AND t.con_id = d.con_id
AND f.con_id(+) = d.con_id
GROUP BY t.con_id, t.tablespace_name, d.file_name, d.file_id, d.relative_fno, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, t.status, d.bytes, f.sum_bytes, d.status,
d.AutoExtensible, d.maxblocks, d.maxbytes, d.increment_by, t.block_size, t.chunk_tablespace, t.bigfile
UNION ALL
SELECT h.con_id, h.tablespace_name,
'Tempfile',
ts.status,
t.status,
SUM(NVL(p.bytes_used, 0)),
SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)),
-1, -- initial extent
-1, -- initial extent
-1, -- min extents
-1, -- max extents
-1, -- pct increase
t.file_name,
t.file_id,
t.relative_fno,
t.autoextensible, t.maxblocks, t.maxbytes / 1048576 maxbytes, nvl(t.increment_by, 0) increment_by, ts.block_size, ts.chunk_tablespace, ts.bigfile
FROM containers("SYS"."V_$TEMP_SPACE_HEADER") h,
containers("SYS"."V_$TEMP_EXTENT_POOL") p,
sys.CDB_TEMP_FILES t,
sys.cdb_tablespaces ts
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND h.file_id = t.file_id
AND h.tablespace_name = t.tablespace_name
and ts.tablespace_name = h.tablespace_name
and p.con_id(+) = h.con_id
and h.con_id = t.con_id
and ts.con_id = h.con_id
GROUP BY h.con_id, h.tablespace_name, t.status, t.file_name, t.file_id, t.relative_fno, ts.status,
t.autoextensible, t.maxblocks, t.maxbytes, t.increment_by, ts.block_size, ts.chunk_tablespace, ts.bigfile
ORDER BY 1, 2, 6 DESC;
Hi John,
When I run query that you have sent, it works, there is no error. However, I spool out query in my Toad version and it has some different parts than yours. I find out that my Toad version is "Toad for Oracle Professional". I share query below and mark different parts:
SELECT t.con_id, t.tablespace_name, 'Datafile' file_type,
t.status tablespace_status, d.status file_status,
d.bytes - NVL(f.sum_bytes, 0) used_bytes,
NVL(f.sum_bytes, 0) free_bytes, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents, t.pct_increase,
d.file_name, d.file_id, d.relative_fno, d.autoextensible, d.maxblocks,
d.maxbytes / 1048576 maxbytes, nvl(d.increment_by,0) increment_by, t.block_size, t.chunk_tablespace, t.bigfile
FROM
(select con_id, tablespace_name, file_id, sum(bytes) sum_bytes
from cdb_free_space
group by con_id, tablespace_name, file_id) f,
CDB_DATA_FILES d,
CDB_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name
AND f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.relative_fno
AND t.con_id = d.con_id
AND f.con_id(+) = d.con_id
GROUP BY t.con_id, t.tablespace_name, d.file_name, d.file_id, d.relative_fno, t.initial_extent,
t.next_extent, t.min_extents, t.max_extents,
t.pct_increase, t.status, d.bytes, f.sum_bytes, d.status,
d.AutoExtensible, d.maxblocks, d.maxbytes, d.increment_by, t.block_size, t.chunk_tablespace, t.bigfile
UNION ALL
SELECT h.con_id, h.tablespace_name,
'Tempfile',
ts.status,
t.status,
SUM(NVL(p.bytes_used, 0)),
SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)),
-1, -- initial extent
-1, -- initial extent
-1, -- min extents
-1, -- max extents
-1, -- pct increase
t.file_name,
t.file_id,
t.relative_fno,
t.autoextensible, t.maxblocks, t.maxbytes / 1048576 maxbytes, nvl(t.increment_by, 0) increment_by, ts.block_size, ts.chunk_tablespace, ts.bigfile
FROM SYS.V_$TEMP_SPACE_HEADER h,
SYS.DBA_TEMP_FILES t,
** SYS.DBA_TABLESPACES ts,**
** (select tablespace_name, file_id, sum(bytes_used) bytes_used**
** from SYS.GV_$TEMP_EXTENT_POOL**
** group by tablespace_name, file_id) p**WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND h.file_id = t.file_id
AND h.tablespace_name = t.tablespace_name
and ts.tablespace_name = h.tablespace_name
and p.con_id(+) = h.con_id
and h.con_id = t.con_id
and ts.con_id = h.con_id
GROUP BY h.con_id, h.tablespace_name, t.status, t.file_name, t.file_id, t.relative_fno, ts.status,
t.autoextensible, t.maxblocks, t.maxbytes, t.increment_by, ts.block_size, ts.chunk_tablespace, ts.bigfile
ORDER BY 1, 2, 6 DESC;
The differences in our queries is because you are connected to a RAC database. I can reproduce the problem there and will fix it. Thank you for reporting it.
