Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why used size of several tablespace is different when separately running 2 SQL scripts checking tabl

Quanwen ZhaoNov 1 2019 — edited Nov 10 2019

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

This post has been answered by Jonathan Lewis on Nov 1 2019
Jump to Answer
Comments
Post Details
Added on Nov 1 2019
5 comments
580 views