DB version: 11.2.0.4
Platform: Oracle Linux 6.4
I am trying to join DBA_DATA_FILES and DBA_FREE_SPACE views to display the total space allocated and free space left in a particular tablespace.
The query I created works well for Scenario1 but not for Scenario 2 shown below. Any idea why my LEFT OUTER JOIN query is not working for Scenario 2 ?
I know this post is a bit long Actually , you only have to look at Scenario 2. I posted Scenario 1 just to give the background info
-- Scenario1
SQL> select * from v$version where rownum < 2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> create tablespace TEST_TS datafile '+DATA_DG' size 20m autoextend off;
Tablespace created.
select extent_management, allocation_type, segment_space_management,
initial_extent/1024 initial_extent_KB, next_extent, pct_increase
from dba_tablespaces
where tablespace_name = 'TEST_TS';
EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT_KB NEXT_EXTENT PCT_INCREASE
---------- --------- ------ ----------------- ----------- ------------
LOCAL SYSTEM AUTO 64
set lines 200 pages 300
col file_name format a58
col tablespace_name format a12
set numf 999999
select
file_id,
file_name,
tablespace_name,
bytes/power(1024,2) BytesMB,
maxbytes/power(1024,2) MaxBytesMB,
autoextensible,
status,
online_status
from dba_data_files where tablespace_name = 'TEST_TS';
FILE_ID FILE_NAME TABLESPACE_N BYTESMB MAXBYTESMB AUT STATUS ONLINE_
------- ---------------------------------------------------------- ------------ ------- ---------- --- --------- -------
139 +DATA_DG/msblprd/datafile/test_ts.410.885559083 TEST_TS 20 0 NO AVAILABLE ONLINE
--- since it is fixed characterset, each insert will be 1KB in size
SQL> create table test3 (c1 char(1024)) tablespace TEST_TS;
Table created.
--- Below output shows that INITIAL_EXTENT size is 64KB and next_extent size is 1MB
SQL> select INITIAL_EXTENT, next_extent from dba_tables where table_name= 'TEST3' and owner= 'SYS';
INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
65536 1048576
--- The following INSERT loop will insert 14MB of data to TEST3 table (14,000 iterations mean 14 MB)
BEGIN
FOR i IN 1..14000 LOOP
insert into test3 values ('abcd');
commit;
END LOOP;
END;
/
SQL> SELECT bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE segment_name = 'TEST3' and segment_type = 'TABLE';
MB
-------
16
SQL> SELECT SUM (bytes/1024/1024) freespace_mb FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME= 'TEST_TS';
FREESPACE_MB
------------
3
select
ddf.tablespace_name,
sum(ddf.bytes/power(1024,2)) TotalSizeMB,
sum (dfs.bytes/power(1024,2)) FreeMB
from dba_data_files ddf inner join dba_free_space dfs
on (ddf.tablespace_name = dfs.tablespace_name)
where ddf.tablespace_name = 'TEST_TS'
group by ddf.tablespace_name;
TABLESPACE_NAME TOTALSIZEMB FREEMB
------------------------------ ----------- ----------
TEST_TS 20 3
--- Trying to INSERT another 5MB of Records. It will print 'iterated 1000 times' for every 1MB inserted
BEGIN
FOR i IN 1..6000 LOOP
insert into test3 values ('abcd');
if(MOD(i, 1000) = 0)
then
dbms_output.put_line('iterated 1000 times');
commit;
end if;
END LOOP;
END;
/
--- Output shows another 2MB was INSERTed
iterated 1000 times
iterated 1000 times
BEGIN
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TEST3 by 128 in tablespace TEST_TS
ORA-06512: at line 3
--- So, now, when there is no free space left in the TBS, DBA_FREE_SPACE.BYTES has gone NULL
SQL> SELECT SUM (bytes/1024/1024) freespace_mb FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME= 'TEST_TS';
FREESPACE_MB
------------
SQL> SELECT bytes/1024/1024 MB FROM DBA_SEGMENTS WHERE segment_name = 'TEST3' and segment_type = 'TABLE';
MB
----------
19
--- Since DBA_FREE_SPACE.bytes column for TEST_TS now returns NULL, it will have no matching records with dba_data_files.tablespace_name
select
ddf.tablespace_name,
sum(ddf.bytes/power(1024,2)) TotalSizeMB,
sum (dfs.bytes/power(1024,2)) FreeMB
from dba_data_files ddf inner join dba_free_space dfs
on (ddf.tablespace_name = dfs.tablespace_name)
where ddf.tablespace_name = 'TEST_TS'
group by ddf.tablespace_name;
no rows selected
--- You need to do an outer join
---- ANSI Syntax
select
ddf.tablespace_name,
sum(ddf.bytes/power(1024,2)) TotalSizeMB,
sum (dfs.bytes/power(1024,2)) FreeMB
from dba_data_files ddf LEFT OUTER JOIN dba_free_space dfs
on (ddf.tablespace_name = dfs.tablespace_name)
where ddf.tablespace_name = 'TEST_TS'
group by ddf.tablespace_name;
TABLESPACE_NAME TOTALSIZEMB FREEMB
------------------------------ ----------- ----------
TEST_TS 20
--- Equivalent Oracle syntax for the above ANSI query
select
ddf.tablespace_name,
sum(ddf.bytes/power(1024,2)) TotalSizeMB,
sum (dfs.bytes/power(1024,2)) FreeMB
from dba_free_space dfs , dba_data_files ddf
where ddf.tablespace_name = dfs.tablespace_name
and ddf.tablespace_name = 'TEST_TS'
group by ddf.tablespace_name;
TABLESPACE_NAME TOTALSIZEMB FREEMB
------------------------------ ----------- ----------
TEST_TS 20
--- Scenario2
---- The above query (scenario 1) works fine.
---But, When I use the same query for a bigger production TBS with several datafiles, it gives wrong output
SQL> select sum (bytes/1024/1024/1024) TotalSizeGB from dba_data_files where tablespace_name = 'OPMCOM_ORDER_DATA';
TOTALSIZEGB
-----------
9369.59668
SQL>
SQL> select sum (dfs.bytes/1024/1024/1024) FreeGB from dba_free_space dfs where tablespace_name = 'OPMCOM_ORDER_DATA';
FREEGB
----------
4719.13196
But, when I use the same query as above, it gives wrong info
select
ddf.tablespace_name,
sum(ddf.bytes/1024/1024/1024) TotalSizeGB,
sum (dfs.bytes/1024/1024/1024) FreeGB
from dba_data_files ddf LEFT OUTER JOIN dba_free_space dfs
on (ddf.tablespace_name = dfs.tablespace_name)
where ddf.tablespace_name = 'OPMCOM_ORDER_DATA'
group by ddf.tablespace_name;
TABLESPACE_NAME TOTALSIZEGB FREEGB
------------------------------ ----------- ----------
OPMCOM_ORDER_DATA 91971961 1689129.7 -------> totally wrong
--- INNER JOIN also gives wrong output
select
ddf.tablespace_name,
sum(ddf.bytes/1024/1024/1024) TotalSizeGB,
sum (dfs.bytes/1024/1024/1024) FreeGB
from dba_data_files ddf INNER JOIN dba_free_space dfs
on (ddf.tablespace_name = dfs.tablespace_name)
where ddf.tablespace_name = 'OPMCOM_ORDER_DATA'
group by ddf.tablespace_name;
| TABLESPACE_NAME | TOTALSIZEGB | FREEGB |
------------------------------ ----------- ----------
| OPMCOM_ORDER_DATA | 91943852.2 1689031.81 |
The correct output should be
TABLESPACE_NAME TOTALSIZEGB FREEGB
------------------------------ ----------- ----------
OPMCOM_ORDER_DATA 9369.59668 4719.13196