Database Size Nedir?
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/Tablespace Size & Usage ?
COLUMN size_gb FORMAT 999,990.99 HEADING 'SIZE(GB)'
COLUMN free_gb FORMAT 999,990.99 HEADING 'FREE(GB)'
COLUMN used_gb FORMAT 999,990.99 HEADING 'USED(GB)'
COLUMN used_pct FORMAT 990.99 HEADING 'USED %'
COLUMN max_size_gb FORMAT 999,990.99 HEADING 'MAX SIZE(GB)'
COLUMN used_of_max_pct FORMAT 990.99 HEADING 'USED OF MAX %'
COLUMN remaining_to_max_gb FORMAT 999,990.99 HEADING 'REMAIN TO MAX(GB)'
SELECT
df.tablespace_name,
ROUND(df.total_gb,2) AS size_gb,
ROUND(NVL(fs.free_gb,0),2) AS free_gb,
ROUND(df.total_gb-NVL(fs.free_gb,0),2) AS used_gb,
ROUND((df.total_gb-NVL(fs.free_gb,0))/df.total_gb*100,2) AS used_pct,
ROUND(df.max_gb,2) AS max_size_gb,
ROUND((df.total_gb-NVL(fs.free_gb,0))/df.max_gb*100,2) AS used_of_max_pct,
ROUND(df.max_gb-(df.total_gb-NVL(fs.free_gb,0)),2) AS remaining_to_max_gb
FROM
(
SELECT
tablespace_name,
SUM(bytes)/1024/1024/1024 AS total_gb,
SUM(CASE WHEN maxbytes=0 THEN bytes ELSE maxbytes END)/1024/1024/1024 AS max_gb
FROM dba_data_files
GROUP BY tablespace_name
) df
LEFT JOIN
(
SELECT
tablespace_name,
SUM(bytes)/1024/1024/1024 AS free_gb
FROM dba_free_space
GROUP BY tablespace_name
) fs
ON df.tablespace_name=fs.tablespace_name
ORDER BY used_of_max_pct DESC;Datafile Size & Growth ?
SET LINESIZE 220
SET PAGESIZE 100
SET VERIFY OFF
SET FEEDBACK ON
SET TRIMSPOOL ON
SET TAB OFF
COLUMN file_name FORMAT A85 HEADING 'FILE NAME'
COLUMN tablespace_name FORMAT A20 HEADING 'TABLESPACE'
COLUMN size_gb FORMAT 9990.99 HEADING 'SIZE(GB)'
COLUMN max_size_gb FORMAT 9990.99 HEADING 'MAX SIZE(GB)'
COLUMN autoextensible FORMAT A5 HEADING 'AUTO'
SELECT SUBSTR(file_name,1,85) file_name,
tablespace_name,
ROUND(bytes/1024/1024/1024,2) size_gb,
ROUND((CASE WHEN maxbytes=0 THEN bytes ELSE maxbytes END)/1024/1024/1024,2) max_size_gb,
autoextensible
FROM dba_data_files
ORDER BY tablespace_name,file_name;Fast Recovery Area (FRA) ne kadar , Ne kadarı Kullanılıyor?
TEMP Usage ?
Kim Kullanıyor bu tempi, ve ne yapıyor ?
SET LINESIZE 240
SET PAGESIZE 100
COLUMN inst_id FORMAT 999 HEADING 'INS'
COLUMN username FORMAT A18 HEADING 'USER'
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial# FORMAT 99999 HEADING 'SER#'
COLUMN program FORMAT A25 HEADING 'PROGRAM'
COLUMN tablespace FORMAT A12 HEADING 'TEMP TS'
COLUMN temp_gb FORMAT 9990.99 HEADING 'TEMP(GB)'
COLUMN temp_pct FORMAT 990.99 HEADING 'TEMP %'
COLUMN sql_id FORMAT A13
WITH temp_total AS (
SELECT tablespace_name,
SUM(bytes)/1024/1024/1024 total_gb
FROM dba_temp_files
GROUP BY tablespace_name
)
SELECT s.inst_id,
NVL(s.username,'-') username,
s.sid,
s.serial#,
SUBSTR(s.program,1,25) program,
u.tablespace,
ROUND(u.blocks*t.block_size/1024/1024/1024,2) temp_gb,
ROUND((u.blocks*t.block_size/1024/1024/1024)/tt.total_gb*100,2) temp_pct,
s.sql_id
FROM gv$session s
JOIN gv$tempseg_usage u
ON s.inst_id=u.inst_id
AND s.saddr=u.session_addr
JOIN dba_tablespaces t
ON u.tablespace=t.tablespace_name
JOIN temp_total tt
ON u.tablespace=tt.tablespace_name
WHERE u.blocks>0
ORDER BY temp_pct DESC;UNDO Usage ?
Kim Kullanıyor bu Undo tbs’i ne yapıyor ?
SET LINESIZE 240
SET PAGESIZE 100
COLUMN inst_id FORMAT 999 HEADING 'INS'
COLUMN username FORMAT A18 HEADING 'USER'
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN serial# FORMAT 99999 HEADING 'SER#'
COLUMN program FORMAT A25 HEADING 'PROGRAM'
COLUMN undo_gb FORMAT 9990.99 HEADING 'UNDO(GB)'
COLUMN undo_pct FORMAT 990.99 HEADING 'UNDO %'
COLUMN sql_id FORMAT A13
WITH undo_total AS (
SELECT SUM(bytes)/1024/1024/1024 total_gb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
)
SELECT s.inst_id,
NVL(s.username,'-') username,
s.sid,
s.serial#,
SUBSTR(s.program,1,25) program,
ROUND(t.used_ublk*p.value/1024/1024/1024,2) undo_gb,
ROUND((t.used_ublk*p.value/1024/1024/1024)/ut.total_gb*100,2) undo_pct,
s.sql_id
FROM gv$transaction t
JOIN gv$session s
ON t.inst_id=s.inst_id
AND t.ses_addr=s.saddr
JOIN gv$parameter p
ON t.inst_id=p.inst_id
AND p.name='db_block_size'
CROSS JOIN undo_total ut
ORDER BY undo_pct DESC;
Bir yanıt yazın