Mustafa Bektaş Tepe

Merhabalar aşağıda ki sorgu veritabanımızda ki temp tablespaceler hakkında ayrıntılı bilgi veriyor.

SET PAGESIZE 60
SET LINESIZE 300
COL TABLESPACE_NAME FOR a40
COL TS_Size FOR 999999999.99
COL ALLOCATED_Size FOR 999999.99
COL FREE_Size FOR 999999

SELECT TABLESPACE_NAME,
       TABLESPACE_SIZE / 1024 / 1024 / 1024 TS_Size,
       ALLOCATED_SPACE / 1024 / 1024 / 1024 ALLOCATED_Size,
       FREE_SPACE / 1024 / 1024 / 1024 FREE_Size
  FROM dba_temp_free_space;

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe

Merhabalar aşagıda ki sorgu veritabanımızda ki tablespaceler hakkında ayrıntılı bilgi veriyor.

Sorgu 1 :

SET LINES 250 PAGES 100 FEEDBACK OFF
COLUMN tablespace_name        FORMAT a30             HEADING 'Tablespace|(TBS)|Name'
COLUMN autoextensible         FORMAT a6              HEADING 'Can|Auto|Extend'
COLUMN files_in_tablespace    FORMAT 999             HEADING 'Files|In|TBS'
COLUMN total_tablespace_space FORMAT 99,999,999,999 HEADING 'Total|Current|TBS|Space'
COLUMN total_used_space       FORMAT 99,999,999,999 HEADING 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space FORMAT 99,999,999,999 HEADING 'Total|Current|Free|Space'
COLUMN total_used_pct              FORMAT 999.99      HEADING 'Total|Current|Used|PCT'
COLUMN total_free_pct              FORMAT 999.99      HEADING 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      FORMAT 99,999,999,999 HEADING 'TBS|Max|Size'
COLUMN total_auto_used_pct         FORMAT 999.99      HEADING 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         FORMAT 999.99      HEADING 'Total|Max|Free|PCT'
 
TTITLE LEFT _date CENTER Tablespace SPACE Utilization Status REPORT SKIP 2
 
WITH tbs_auto
     AS (SELECT DISTINCT tablespace_name, autoextensible
           FROM dba_data_files
          WHERE autoextensible = 'YES'),
     files
     AS (  SELECT tablespace_name,
                  COUNT (*) tbs_files,
                  SUM (BYTES) total_tbs_bytes
             FROM dba_data_files
         GROUP BY tablespace_name),
     fragments
     AS (  SELECT tablespace_name,
                  COUNT (*) tbs_fragments,
                  SUM (BYTES) total_tbs_free_bytes,
                  MAX (BYTES) max_free_chunk_bytes
             FROM dba_free_space
         GROUP BY tablespace_name),
     AUTOEXTEND
     AS (  SELECT tablespace_name, SUM (size_to_grow) total_growth_tbs
             FROM (  SELECT tablespace_name, SUM (maxbytes) size_to_grow
                       FROM dba_data_files
                      WHERE autoextensible = 'YES'
                   GROUP BY tablespace_name
                   UNION
                     SELECT tablespace_name, SUM (BYTES) size_to_grow
                       FROM dba_data_files
                      WHERE autoextensible = 'NO'
                   GROUP BY tablespace_name)
         GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible WHEN 'YES' THEN 'YES' ELSE 'NO' END
          AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes / 1024 / 1024 total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes) / 1024 / 1024
          total_used_space,
       fragments.total_tbs_free_bytes / 1024 / 1024
          total_tablespace_free_space,
       ( ( (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
          / files.total_tbs_bytes)
        * 100)
          total_used_pct,
       ( (fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100)
          total_free_pct,
       AUTOEXTEND.total_growth_tbs / 1024 / 1024 max_size_of_tablespace,
       ( ( (AUTOEXTEND.total_growth_tbs
            - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes))
          / AUTOEXTEND.total_growth_tbs)
        * 100)
          total_auto_used_pct,
       ( ( (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
          / AUTOEXTEND.total_growth_tbs)
        * 100)
          total_auto_free_pct
  FROM dba_tablespaces a,
       files,
       fragments,
       AUTOEXTEND,
       tbs_auto
 WHERE     a.tablespace_name = files.tablespace_name
       AND a.tablespace_name = fragments.tablespace_name
       AND a.tablespace_name = AUTOEXTEND.tablespace_name
       AND a.tablespace_name = tbs_auto.tablespace_name(+)
	   order by 10 desc;

Sorgu 2 : 

SET PAGESIZE 200
SET LINESIZE 250
COLUMN "Tablespace Name" FORMAT a40
COLUMN "TS Used Size GB" FORMAT 999999
COLUMN "TS Allocate Size GB" FORMAT 999999
COLUMN "TS Free Size GB" FORMAT 999999
COLUMN "TS Allocate Occupancy %" FORMAT 999999
COLUMN "TS Max Size GB" FORMAT 999999
COLUMN "TS Max Free Size GB" FORMAT 999999
COLUMN "TS Max Occupancy %" FORMAT 999999

SELECT TS.name "Tablespace Name",
         ROUND (
            ( (SUM (df.bytes))
             - (SELECT SUM (fs.bytes)
                  FROM dba_free_space fs, dba_data_files df
                 WHERE FS.FILE_ID = DF.FILE_id AND FS.TABLESPACE_NAME = TS.name))
            / 1024
            / 1024
            / 1024)
            "TS Used Size GB",
         ROUND (SUM (df.bytes) / 1024 / 1024 / 1024) "TS Allocate Size GB",
         (SELECT ROUND (SUM (fs.bytes) / 1024 / 1024 / 1024)
            FROM dba_free_space fs, dba_data_files df
           WHERE FS.FILE_ID = DF.FILE_id AND FS.TABLESPACE_NAME = TS.name)
            "TS Free Size GB",
         ROUND (
            100
            - ( (SELECT SUM (fs.bytes)
                   FROM dba_free_space fs, dba_data_files df
                  WHERE FS.FILE_ID = DF.FILE_id
                        AND FS.TABLESPACE_NAME = TS.name)
               / ( (SUM (df.bytes)) / 100)))
            "TS Allocate Occupancy %",
         ROUND (SUM (df.maxbytes) / 1024 / 1024 / 1024) "TS Max Size GB",
         ROUND (
            (SUM (df.maxbytes)
             - ( ( (SUM (df.bytes))
                  - (SELECT SUM (fs.bytes)
                       FROM dba_free_space fs, dba_data_files df
                      WHERE FS.FILE_ID = DF.FILE_id
                            AND FS.TABLESPACE_NAME = TS.name))))
            / 1024
            / 1024
            / 1024)
            "TS Max Free Size GB",
         ROUND (
            100
            - ( ( (SUM (df.maxbytes)
                   - ( ( (SUM (df.bytes))
                        - (SELECT SUM (fs.bytes)
                             FROM dba_free_space fs, dba_data_files df
                            WHERE FS.FILE_ID = DF.FILE_id
                                  AND FS.TABLESPACE_NAME = TS.name)))))
               / ( (SUM (df.maxbytes)) / 100)))
            "TS Max Occupancy %"
    FROM v$tablespace ts, dba_data_files df
   WHERE TS.name = DF.tablespace_name
GROUP BY TS.name
ORDER BY 8 DESC;

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe