Administer Tablespace columns

Toad for Oracle
1

I couldnt find a column view option for bigfile.
Im not sure if there is another setting or Can a column be added to the tablespace viewer for big file?
big and small files seem to be more relevant now and with 23ai switching default to bigfile it would be useful to know. I added c.bigfile to your query and the results returned the same with and without in a few tests.

image
image343×323 18.3 KB

select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
round(maxbytes/1048576) Max,
c.bigfile,c.status, c.contents
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(
select ts.name tablespace_name, sum(fs.blocks) * ts.blocksize bytes_free
from DBA_LMT_FREE_SPACE fs, sys.ts$ ts
where ts.ts# = fs.tablespace_id
group by ts.name, ts.blocksize
) b,
dba_tablespaces c
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = c.tablespace_name
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,
c.bigfile,c.status, c.contents
from (SELECT DISTINCT * FROM SYS.V_$TEMP_SPACE_HEADER) h,
(SELECT DISTINCT * FROM SYS.V_$TEMP_EXTENT_POOL) p,
dba_temp_files f,
dba_tablespaces c
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
and f.tablespace_name = c.tablespace_name
group by h.tablespace_name, c.status, c.contents,c.bigfile
ORDER BY 1;

Thanks
Dave

2

Hi Dave.

There is no way to see that in Database -> Administer -> Tablespaces, but I can add that.

FYI: You can see it in the Schema Browser:

image
image1164×527 46.3 KB

-John

3

I know you can see it there but Im needy and want it in 2 places.

Just kidding but I like the administer tablespace tab it has a lot of different data available fast and im needy :smile:

4

No problem.

There is a lot of overlap between SB-Tablespaces and Database -> Administer -> Tablespaces. I wish we could consolidate all of the latter in SB but they are just different enough so that will probably never happen.

You can look at the "Max" column in Administer Tablespaces. If it says 32 TB then it's a bigfile.

-John

1 Like