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