To Check free/used space per all tablespaces
SELECT a.tablespace_name,
ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND(a.used_percent, 2) AS "USED_PERCENT"
FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
DBA_TABLESPACES b
ON a.tablespace_name = b.tablespace_name
WHERE a.used_percent>80;
ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
ROUND(a.used_percent, 2) AS "USED_PERCENT"
FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
DBA_TABLESPACES b
ON a.tablespace_name = b.tablespace_name
WHERE a.used_percent>80;
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
(OR)
select a.tablespace_name name,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024) Total_MB,
round((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Used_MB,
round((sum(a.bytes)/count( distinct b.file_id ))/1024/1024) Free_MB,
round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))) pct_used,
round(100 - round(100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )))) "PCT_FREE(%)"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name
order by pct_used;
(OR)
To check the tablespace freespace below 6%
SELECT F.TABLESPACE_NAME as Tablespace,
TO_CHAR (T.TOTAL_SPACE, '999,999') as "TOTAL(MB)",
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') as "USED(MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE(MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' "FREE_PER"
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 6 order by FREE_PER;
To Check free/used space per one tablespace
set feedback off
set pages 1000
set linesize 123
col tablespace_name format a30
col file_name format a45
col free_bytes format 9999999999999
col Used_bytes format 9999999999999
col max_bytes format 9999999999999
col num_files format 999
SELECT ts_usage.tablespace_name,
ts_usage.num_files num_files,
ts_usage.free_bytes free_bytes,
ts_usage.total_bytes Max_bytes, ts_usage.file_bytes Used_Bytes,
ceil((ts_usage.used_bytes*100)/ts_usage.total_bytes) percent_used
FROM
(select d.tablespace_name tablespace_name,
d.num_files num_files,
nvl(f.bytes,0) free_bytes,
d.file_bytes file_bytes,
case when d.maxbytes<d.file_bytes then d.file_bytes else d.maxbytes END total_bytes,
d.file_bytes-nvl(f.bytes,0) used_bytes
from (select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) f,
(select tablespace_name, count(distinct(file_id)) num_files, sum(bytes) file_bytes,
sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
from dba_data_files group by tablespace_name) d
where d.tablespace_name = f.tablespace_name (+)
and d.tablespace_name not like '%UNDO%' and d.tablespace_name like '&tsname'
order by d.tablespace_name ) TS_USAGE
WHERE ceil((ts_usage.used_bytes*100)/ts_usage.total_bytes) between 80 and 100;
To Check Datafiles associated with Tablespace
set lines 200
set pages 200
col FILE_NAME for a50
select file_name, bytes/1024/1024 sizem,maxbytes/1024/1024 msizem,autoextensible,increment_by from dba_data_files where tablespace_name='&Tablespace_Name';
To add a Datafile:
alter tablespace xxxxx add datafile '/u1/oracle/data/xxxxxx05.dbf' size 1024M autoextend on next 100M maxsize 4096M;
###################################################################################
On ASM:
alter tablespace users add datafile ‘+data1’ size 5M;
alter tablespace TS_MASTER add datafile '+DATA' size 1g autoextend on;
ALTER TABLESPACE USERS ADD DATAFILE '+WEBLOGDB_DATA3' SIZE 100M AUTOEXTEND ON MAXSIZE 10240M;
alter tablespace APPS_TS_MEDIA add datafile '+DATAC2' size 10240M autoextend on next 2048M maxsize 30720M;
New Steps for Adding Datafile to a tablespace
1) Capture Info before and verify
set lines 200
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';
2) Identify sequence of datafile
select max(regexp_replace(substr(substr(file_name,-6),1,2),
'[^0-9]', '')) from dba_data_files where
tablespace_name='<tablespace_name>';
3) Create command:
select 'alter tablespace ' ||
tablespace_name ||
' add datafile '''||
substr(file_name,1,25) ||
to_char(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '') + 1) ||
'.dbf'' size 2048M autoextend on next ' ||
INCREMENT_BY ||
' maxsize ' ||
maxbytes/1024/1024 ||
'M;'
from dba_data_files
where tablespace_name='<tablespace_name>'
and regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '')=(select
max(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', ''))
from dba_data_files
where tablespace_name='<tablespace_name>');
tablespace_name ||
' add datafile '''||
substr(file_name,1,25) ||
to_char(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '') + 1) ||
'.dbf'' size 2048M autoextend on next ' ||
INCREMENT_BY ||
' maxsize ' ||
maxbytes/1024/1024 ||
'M;'
from dba_data_files
where tablespace_name='<tablespace_name>'
and regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', '')=(select
max(regexp_replace(substr(substr(file_name,-6),1,2), '[^0-9]', ''))
from dba_data_files
where tablespace_name='<tablespace_name>');
Note: We need to replace
<tablespace_name> with actual tablespace name in alert in above sqls.
4) Verify and execute the result of above
query -- this will add the data file to tablespace
5) Capture Info after and verify
set lines 200
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';
set pages 200
col file_name for a55
select file_name, bytes/1024/1024,autoextensible, increment_by, maxbytes/1024/1024 from dba_data_files where tablespace_name='<tablespace_name>';
No comments:
Post a Comment