Hello my ODC friends ,
Today morning I wrote 2 number of SQL scripts checking tablespace used size on Oracle Database version 11.2.0.4.0, whom after I separately ran and noticed that there has a little different used size on several tablespace(s) being checked.
For better comparing those tablespaces' difference used size I specially put my per SQL script to a SPOOL command of SQL*Plus so that its output result will be saved as a TXT file on a specific location of my Linux server. After that I use this funny Linux tool "sdiff" to compare those 2 TXT file's difference.
The following is the entire process of playing aroung with it.
-- /home/oracle/tablespace_used_size_1.sql
SET ECHO OFF
SET FEEDBACK ON
SET NEWPAGE NONE
SET VERIFY OFF
SET DEFINE OFF
SET LINESIZE 1000
SET PAGESIZE 1000
SET TERMOUT OFF
SET TRIMSPOOL ON
SET HEADING ON
SET TIMING OFF
COLUMN ts_name FORMAT a25
COLUMN used_mb FORMAT 999,999,999.99
SPOOL "/home/oracle/tablespace_used_size_1.txt"
WITH a AS (SELECT tablespace_name
, SUM(bytes)/1024/1024 AS total
FROM dba_data_files
GROUP BY tablespace_name
),
b AS (SELECT tablespace_name
, SUM(bytes)/1024/1024 AS free
FROM dba_free_space
GROUP BY tablespace_name
),
-- c AS (SELECT tablespace_name
-- , SUM(bytes)/1024/1024 AS total
-- FROM dba_temp_files
-- GROUP BY tablespace_name
-- ),
d AS (SELECT tablespace_name
, SUM(bytes_used)/1024/1024 AS used
FROM v$temp_extent_pool
GROUP BY tablespace_name
)
SELECT a.tablespace_name AS ts_name
, a.total - b.free AS "USED_MB"
FROM a, b
WHERE a.tablespace_name = b.tablespace_name
UNION ALL
SELECT d.tablespace_name AS ts_name
-- c.tablespace_name AS ts_name
, d.used AS "USED_MB"
-- FROM c, d
FROM d
-- WHERE c.tablespace_name = d.tablespace_name
ORDER BY 1;
SPOOL OFF
-- /home/oracle/tablespace_used_size_2.sql
SET ECHO OFF
SET FEEDBACK ON
SET NEWPAGE NONE
SET VERIFY OFF
SET DEFINE OFF
SET LINESIZE 1000
SET PAGESIZE 1000
SET TERMOUT OFF
SET TRIMSPOOL ON
SET HEADING ON
SET TIMING OFF
COLUMN ts_name FORMAT a25
COLUMN used_mb FORMAT 999,999,999.99
SPOOL "/home/oracle/tablespace_used_size_2.txt"
SELECT tablespace_name AS ts_name
, sum(bytes)/1024/1024 AS used_mb
FROM dba_segments
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name AS ts_name
, SUM(bytes_used)/1024/1024 AS used_mb
FROM v$temp_extent_pool
GROUP BY tablespace_name
ORDER BY 1;
SPOOL OFF
-- /home/oracle/concurrent_execute.sql
@@tablespace_used_size_1.sql;
@@tablespace_used_size_2.sql;
SYS@XXXXXX> @concurrent_execute.sql;
SYS@XXXXXX>
[oracle@XXXXXX ~]$ sdiff tablespace_used_size_1.txt tablespace_used_size_2.txt | expand > sdiff_tablespace_used_size.txt
[oracle@XXXXXX ~]$
[oracle@XXXXXX ~]$ cat sdiff_tablespace_used_size.txt
TS_NAME USED_MB TS_NAME USED_MB
------------------------- --------------- ------------------------- ---------------
SYSAUX 55,906.00 | SYSAUX 55,902.38
SYSTEM 799.38 | SYSTEM 794.38
WWW_XXXXXXXXXXX 506,414.69 | WWW_XXXXXXXXXXX 512,493.75
WWW_XXXXXXXXXXX_TEMP .00 WWW_XXXXXXXXXXX_TEMP .00
WWW_YYYYYYYYYYY 1.00 | WWW_YYYYYYYYYYY 10.00
TEMP 1.00 TEMP 1.00
UNDOTBS1 5,133.31 | UNDOTBS1 5,132.31
USERS 2,626.13 | USERS 2,625.13
8 rows selected. 8 rows selected.
As you can see the last step, the line having sign "|" indicates that it appears a different used size of tablespace from 2 TXT files. It's why?
Could you help me please? Thanks beforehand.
Best Regards
QuanwenZhao