Oracle Tablespace Maintenance scripts excluding Undo and Temp tablespaces


To Find % of free space left
in Tablespace

select
(BYTES/1024)/1024
“Used Space(MB)”,
total  “allocated size(MB)”,
maxi
“maximum allowable (MB)”,
maxi-(BYTES/1024)/1024
“effectivefree(MB)”,
–maxi-total
“free(MB)”,
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2)
“% FREE”
from
SM$TS_USED,(select
sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
dba_data_files
where tablespace_name in (‘&tbs’)) where
tablespace_name
in (‘&tbs’);


To list all the datafiles of
a given tablespace

col
file_name for a60
set
lines 170
set
pages 200 
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files
where
tablespace_name=’&tablespace_name’ order by file_name ;

To list a specific datafile
space details using file_id

col
file_name for a40
set
pagesize 100 
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files
where
file_id=128 order by file_name;

Space left to extend for
autoextensible files in a mount point

 (replace File_name variable to the associated
mountpoint)

col
file_name for a40
set
lines 170 pages 0 
BREAK
on REPORT
compute
SUM of space_left_to_extend on report
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible,(maxbytes/1024/1024)-(bytes/1024/1024)
space_left_to_extend from dba_data_files
where
file_name like ‘%/u01/oradata/%’ and autoextensible=’YES’ order by file_name;

To check autoextendable
files of a mountpoint

col
file_name for a40
set
pagesize 100 
select
tablespace_name,file_name,bytes/1024/1024 ,maxbytes/1024/1024,autoextensible from
dba_data_files
where
file_name like ‘%/tbarti/oradata%’ and autoextensible=’YES’ order by file_name;

To check Current size of an
autoextendable file

col
file_name for a40
set
pagesize 100 
select
SUM(bytes/1024/1024) from dba_data_files
where
file_name like ‘%/tmzg1s/oradata%’ and autoextensible=’YES’;

To check Total space that is
supposed to be extendable for autoextensible files of a mountpoint

col
file_name for a40
set
pagesize 100 
select
(SUM(maxbytes/1024/1024)-SUM(bytes/1024/1024)) EXTENSIBLESIZE from
dba_data_files
where
file_name like ‘%/tmzg1s/oradata%’ and autoextensible=’YES’;

To check files in a
Mountpoint

col
file_name for a40
set
pagesize 100 
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from
dba_data_files
where
file_name like ‘%/tnhh1o/oradata/data01/orabpel.dbf_back%’ order by file_name ;

col
file_name for a40
set
pagesize 100 
select
tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files
where
file_name like ‘%/tmzg1s/oradata%’ and autoextensible=’YES’ order by file_name
;

col
file_name for a40
set
pagesize 100 
select
count(file_name) from dba_data_files
where
file_name like ‘%/dcybxi/oradata/%’ and autoextensible=’YES’;

Checking total space
available within TS (Effective MB)

select
tablespace_name, sum(bytes/1024/1024) “mb” from dba_free_space
group
by tablespace_name having tablespace_name=upper(‘&tname’);

Tablespace with less than
25% free space

set
linesize 100 pagesize 30 feedback 1 echo off wrap on
column
tblspace format a12 heading “Tablespace”
column
“Used Space(MB)” format 999999999
column
“allocated size(MB)” format 999999999
column
“maximum allowable (MB)” format 999999999
column
“effective free(MB)” format 999999999
column
“%FREE” format 999.99
select
a.tsdf
tblspace,
b.used_space
“Used Space(MB)”,
nvl(a.file_space,0)
“allocated size(MB)”,
a.extn_space  as “maximum allowable (MB)”,
(a.extn_space-b.used_space)
as “effective free(MB)”,
round((((a.extn_space-b.used_space)/a.extn_space)*100),2)
as “%FREE”
from
(select
tablespace_name tsdf,sum(bytes)/1048576
file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extn_space from
dba_data_files
group by tablespace_name) a, (select tablespace_name tsfs,(bytes/1048576)
used_space from sm$ts_used) b where
a.tsdf
= b.tsfs (+) and
a.tsdf
like upper(‘%%’)
and
round((((a.extn_space-b.used_space)/a.extn_space)*100),2) <= 25 and
(a.extn_space-b.used_space)<=10240
order
by a.tsdf;

To add a datafile to a given
tablespace manually

alter
tablespace &tablespace_name add datafile ‘&filefullpath’ size 200M
autoextend on next 20M maxsize 1800M;

alter tablespace INDEX1 add datafile
‘/oradata12/ora816/SLA2/database/index1_26.dbf’ size 1024M;

To resize a datafile of a
given tablespace

For autoextensible file

alter
database datafile ‘&filefullpath’ autoextend on next 20M maxsize 1800M;

For static file without
autoextension

alter database datafile
‘/SID/oradata/data01/a_txn_data01.dbf’ resize 1800M;

Example
alter database datafile
‘/SID/oradata/data01/statsdata02.dbf’ resize 1800M;
alter
database datafile ‘/SID/oradata/data01/a_nolog01.dbf’ autoextend on maxsize
120M;
alter
database datafile ‘/SID/oradata/data03/ard198.dbf’ autoextend off;


To find Non extendable
datafiles

select
file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files where
autoextensible=’NO’ order by file_name;

To enable autoextension

alter
database datafile ‘<datafile>’ autoextend on maxsize 1800m;

To backup the Control file
after making a change / adding datafiles

alter
database backup controlfile to trace;

Tablespaces with less than
25% of free space

set
linesize 100 pagesize 30 feedback 1 echo off wrap on
column
tblspace format a12 heading “Tablespace”
column
“Used Space(MB)” format 999999999
column
“allocated size(MB)” format 999999999
column
“maximum allowable (MB)” format 999999999
column
“effective free(MB)” format 999999999
column
“%FREE” format 999.99
select
a.tsdf
tblspace,
b.usedspace
“Used Space(MB)”,
nvl(a.filespace,0)
“allocated size(MB)”,
a.extnspace  as “maximum allowable (MB)”,
(a.extnspace-b.usedspace)
as “effective free(MB)”,
round((((a.extnspace-b.usedspace)/a.extnspace)*100),2)
as “%FREE”
from
(select
tablespace_name tsdf,sum(bytes)/1048576
filespace,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extnspace from
dba_data_files group by tablespace_name) a,(select tablespace_name
tsfs,(bytes/1048576) usedspace from sm$ts_used) b where a.tsdf = b.tsfs (+) and
a.tsdf like upper(‘%%’) and
round((((a.extnspace-b.usedspace)/a.extnspace)*100),2) <= 25 order by
a.tsdf;
and
(a.extnspace-b.usedspace)<=1843

To check space for all the
tablespaces

select
TABLESPACE_NAME, (BYTES/1024)/1024 “Used Space(MB)”,
total  “allocated size(MB)”,
maxi
“maximum allowable (MB)”,
maxi-(BYTES/1024)/1024
“effectivefree(MB)”,
–maxi-total
“free(MB)”,
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2)
“% FREE”
from
SM$TS_USED,(select
sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
dba_data_files
where tablespace_name =tablespace_name) where
tablespace_name
=tablespace_name;

To find size of Objects more
than 2GB in a specific tablespace

select
owner||’  ,  ‘||SEGMENT_NAME||’  , ‘||((BYTES/1024)/1024)/1024 from
dba_segments where tablespace_name=’&tablespace_name’ and (((BYTES/1024)/1024)/1024)>2;

select
owner||’  ,  ‘||SEGMENT_NAME||’  , ‘||((BYTES/1024)/1024)/1024 from
dba_segments where tablespace_name=’&tablespace_name’ and segment_name like
‘%DTEA_PA_REPORTING_AGT_HISTO%’;

To find LOBs

select
OWNER, TABLE_NAME, COLUMN_NAME from dba_lobs where segment_NAME in (select *
from
(select
SEGMENT_NAME from dba_segments where TABLESPACE_NAME=’SYSAUX’ and segment_type
=
‘LOB
PARTITION’ order by 1 desc) where rownum<45);


Find List of Files – for
Table spaces

select
file_name,status,FILE_ID,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from
dba_data_files where tablespace_name=upper(‘&tablespace_name’) order by
FILE_ID;

FIND FREE,UTILIZED,ALLOCATED

prompt
Checking tablespace free space
SELECT
d.tablespace_name,
(a.bytes
– NVL(f.bytes,0))/1024/1024 “Used Space(MB)”,
a.bytes/1024/1024
“allocated size(MB)”,
a.maxbytes/1024/1024
“maximum allowable (MB)”,
(NVL(f.bytes,0)
+ (a.maxbytes – a.bytes))/1024/1024 “effective free(MB)”,
100-round(((a.bytes
– NVL(f.bytes,0))*100/a.maxbytes),2) “%FREE”
FROM
sys.dba_tablespaces
d,
(select
tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes))
maxbytes
from
sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name,
sum(bytes)
bytes
from
sys.dba_free_space group by tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND
d.tablespace_name = f.tablespace_name(+)
AND
NOT (d.extent_management = ‘LOCAL’ AND d.contents = ‘TEMPORARY’)
AND
d.tablespace_name=upper(‘&TABLESPACE_NAME’);

Checking Mb required to add
tablespace

SELECT
d.tablespace_name,
a.maxbytes/1024/1024
“maximum allowable (MB)”,(a.maxbytes/1024/1024)*0.15 “15% of Max
Allowable”,
(NVL(f.bytes,0)
+ (a.maxbytes – a.bytes))/1024/1024 “effective free(MB)”,
(((a.maxbytes/1024/1024)*0.15)-((NVL(f.bytes,0)
+ (a.maxbytes – a.bytes))/1024/1024)) “Mb to add to maintain 15%
free”,
100-round(((a.bytes
– NVL(f.bytes,0))*100/a.maxbytes),2) “%FREE”
FROM
sys.dba_tablespaces
d,
(select
tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes))
maxbytes
from
sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name,
sum(bytes)
bytes
from
sys.dba_free_space group by tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND
d.tablespace_name = f.tablespace_name(+)
AND
NOT (d.extent_management = ‘LOCAL’ AND d.contents = ‘TEMPORARY’)
AND
d.tablespace_name=upper(‘&TABLESPACE_NAME’);

set
lines 152
col  file_name for a50
col
TBS_NAME for a15
col
“% FREE” FOR 999.99
select
tablespace_name TBS_NAME,
(BYTES/1024)/1024
“Used(MB)”,
total  “allocated size(MB)”,
maxi
“max allowable(MB)”,
maxi-(BYTES/1024)/1024
“effect_free(MB)”,
–maxi-total
“free(MB)”,
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2)
“% FREE”
from
SM$TS_USED,(select
sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
dba_data_files
where tablespace_name in UPPER(‘&tablespace’)) where
tablespace_name
in upper(‘&tablespace’); 

select
tablespace_name from dba_tablespaces where tablespace_name like
‘%&TABLESPACE_NAME%’;     

FIND 25% LESS FREE SPACE

prompt
Checking tablespace free space size less than 15% of total size
SELECT
d.tablespace_name,
(a.bytes
– NVL(f.bytes,0))/1024/1024 “Used Space(MB)”,
a.bytes/1024/1024
“allocated size(MB)”,
a.maxbytes/1024/1024
“maximum allowable (MB)”,
(NVL(f.bytes,0)
+ (a.maxbytes – a.bytes))/1024/1024 “effective free(MB)”,
100-round(((a.bytes
– NVL(f.bytes,0))*100/a.maxbytes),2) “%FREE”
FROM
sys.dba_tablespaces
d,
(select
tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes))
maxbytes
from
sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name,
sum(bytes)
bytes
from
sys.dba_free_space group by tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND
d.tablespace_name = f.tablespace_name(+)
and
(100-round(((a.bytes – NVL(f.bytes,0))*100/a.maxbytes),2)) <= 75
AND
NOT (d.extent_management = ‘LOCAL’ AND d.contents = ‘TEMPORARY’);

To check freespace in the tablespace

select
a.tsdf
tblspace,
a.file_space
totspace,
nvl(b.free_space,0)
tfsspace,
a.extn_space
extspace,
round(((a.extn_space
– (a.file_space – nvl(b.free_space,0)))/a.extn_space)*100,2) pctfrees
from
(select
tablespace_name tsdf,sum(bytes)/1048576
file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extn_space
from
dba_data_files group by tablespace_name) a,
(select
tablespace_name tsfs,sum(bytes)/1048576 free_space,max(bytes)/1048576 max_chunk
from dba_free_space group by
tablespace_name)
b
where
a.tsdf
= b.tsfs (+) and
a.tsdf
like upper(‘%&TableSpace%’)
and
round(((a.extn_space – (a.file_space –
nvl(b.free_space,0)))/a.extn_space)*100,2) < &PcFree
order
by a.tsdf;


set
linesize 100 pagesize 30 feedback 1 echo off wrap on
column
tblspace format a15 heading “Tablespace”
column
totspace format 999999999 heading “Allocated
MB”
column
tfsspace format 999999999 heading “Free
MB”
column
extspace format 999999999 heading “AutoExt
MB”
column
pctfrees format 999.99 heading “% Free”
select
a.tsdf
tblspace,
a.file_space
totspace,
nvl(b.free_space,0)
tfsspace,
a.extn_space
extspace,
round(((a.extn_space
– (a.file_space – nvl(b.free_space,0)))/a.extn_space)*100,2) pctfrees
from
(select
tablespace_name tsdf,sum(bytes)/1048576
file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576
extn_space
from
dba_data_files group by tablespace_name) a,
(select
tablespace_name tsfs,sum(bytes)/1048576 free_space,max(bytes)/1048576 max_chunk
from
dba_free_space
group by
tablespace_name)
b
where
a.tsdf
= b.tsfs (+) and
a.tsdf
like upper(‘%’)
and
round(((a.extn_space – (a.file_space –
nvl(b.free_space,0)))/a.extn_space)*100,2) < 25
order
by a.tsdf;


SELECT
d.tablespace_name,
(a.bytes
– NVL(f.bytes,0))/1024/1024 “Used Space(MB)”,
a.bytes/1024/1024
“allocated size(MB)”,
a.maxbytes/1024/1024
“maximum allowable (MB)”,
(NVL(f.bytes,0)
+ (a.maxbytes – a.bytes))/1024/1024 “effective free(MB)”,
100-round(((a.bytes
– NVL(f.bytes,0))*100/a.maxbytes),2) “%FREE”
FROM
sys.dba_tablespaces
d,
(select
tablespace_name, sum(bytes) bytes,
sum(greatest(maxbytes,bytes))
maxbytes
from
sys.dba_data_files group by tablespace_name) a,
(select
tablespace_name,
sum(bytes)
bytes
from
sys.dba_free_space group by tablespace_name) f
WHERE
d.tablespace_name = a.tablespace_name(+)
AND
d.tablespace_name = f.tablespace_name(+)
AND
NOT (d.extent_management = ‘LOCAL’ AND d.contents = ‘TEMPORARY’)
AND
round(((NVL(f.bytes,0) + (a.maxbytes – a.bytes))/1048576),2) <2000
AND
round(((a.bytes – NVL(f.bytes,0))*100/a.maxbytes),2) <75
order
by 6 desc;



select
TABLESPACE_NAME,sum(bytes)/1024/1024 from dba_free_space where
tablespace_name=’&TABLESPACE_NAME’;





To add DATAFILE in a
Tablespace
alter
tablespace &tablespace_name add datafile ‘&filefullpath’ size 200M
autoextend on next 20M maxsize 1800M;
alter
tablespace &tablespace_name add datafile ‘&filefullpath’ size 2200M;
alter
tablespace &tablespace_name add datafile ‘&filefullpath’ size 1000M;
alter
database datafile ‘&datafilename’ autoextend on  maxsize 1800M;
ALTER DATABASE DATAFILE
‘&datafile’ RESIZE 1000M;
alter
database datafile ‘&filefullpath’ autoextend on  maxsize 1800M;
alter
database datafile ‘&filefullpath’ autoextend on  maxsize 2000M;
alter
database datafile ‘&filefullpath’ autoextend on  maxsize 5000M;
alter
database datafile ‘&filefullpath’ autoextend on  maxsize 2048M;
alter
database datafile ‘&filefullpath’ resize 1800M;

To check the Count of
Datafiles in a tablespace
select
count(*) from dba_data_files  where
tablespace_name=’&tablespace_name’;

To check the Count of
datafiles which can still extend
select
count(*) from dba_data_files  where maxbytes>=bytes
and 
tablespace_name=’&tablespace_name’;

select
sum(maxbytes)/1024/1024 from dba_data_files 
where tablespace_name=’&tablespace_name’;


set
lines 152
col  file_name for a70 
select
(BYTES/1024)/1024
“Used Space(MB)”,
total  “allocated size(MB)”,
maxi
“maximum allowable (MB)”,
maxi-(BYTES/1024)/1024
“effectivefree(MB)”,
–maxi-total
“free(MB)”,
round(((maxi-(BYTES/1024)/1024)/maxi)*100,2)
“% FREE”
from
SM$TS_USED,(select
sum((BYTES/1024)/1024)
total,sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024)  maxi from
dba_data_files
where tablespace_name in UPPER(‘&&tbs’)) where
tablespace_name
in upper(‘&tbs’);     

select
file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from
dba_data_files where tablespace_name=upper(‘&tbs’) order by file_name;  

Changing maxsize for all the
files whose size is set more than maxsize

set
linesize 152
select
‘alter  database datafile ‘ || ””||
a.file_name||”” || ‘ autoextend on maxsize ‘ || a.maxsize || ‘M;’
from
(select
file_name,bytes/1024/1024 maxsize,maxbytes/1024/1024  ,autoextensible
from
dba_data_files where tablespace_name in (”)
and
maxbytes<bytes  and
autoextensible=’YES’ order by file_name) a ;

Changing bytes for all the
files which have size less than  20MB

set
linesize 152
select
‘alter  database datafile ‘ || ””||
file_name||”” || ‘ size 50M ;’
from
dba_data_files
where
bytes/1024/1024< 20 order by file_name ;




Percentage allocated in the
tablespace

select
(1-(sum(dfs.bytes)/sum(dbf.bytes)))*100 
perallocated
from   dba_data_files dbf,
dba_free_space
dfs
where  dbf.tablespace_name=dfs.tablespace_name
and    dbf.tablespace_name=’&tablespace_name’;






Freespace in tablespaces in
MB

set
lines 152
set
pages 1000
select
    utbs.tablespace_name,
round(utbs.mb)
“Allocated Used/Unused MB”,
round(Ftbs.mb)
“Allocated_Free MB”,
round((100/utbs.mb)*Ftbs.mb)
“%Allocated_Free MB”,            
round(utbs.Maxmb-utbs.mb)
“Space_AutoExtensible MB”
from
(select
ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from
dba_data_files ddf
group
by ddf.tablespace_name) Utbs,
(select
dfs.tablespace_name,sum(dfs.bytes)/1048576 MB
from
dba_free_space dfs
group
by dfs.tablespace_name) Ftbs
where
utbs.tablespace_name=ftbs.tablespace_name
and
(100*Ftbs.mb)/utbs.mb<25
order
by (100*Ftbs.mb)/utbs.mb desc
/

2)
set
linesize 100 pagesize 30 feedback 1 echo off wrap on
column
tblspace format a12 heading “Tablespace”
column
totspace format 999999999 heading “Allocated
MB”
column
tfsspace format 999999999 heading “Free
MB”
column
extspace format 999999999 heading “AutoExt
MB”
column
pctfrees format 999.99 heading “% Free”
select
a.tsdf
tblspace,
a.file_space
totspace,
nvl(b.free_space,0)
tfsspace,
a.extn_space
extspace,
round(((a.extn_space
– (a.file_space – nvl(b.free_space,0)))/a.extn_space)*100,2) pctfrees
from
(select
tablespace_name tsdf,sum(bytes)/1048576
file_space,sum(decode(maxbytes,0,bytes,maxbytes))/1048576 extn_space
from
dba_data_files group by tablespace_name) a,
(select
tablespace_name tsfs,sum(bytes)/1048576 free_space,max(bytes)/1048576 max_chunk
from dba_free_space group by
tablespace_name)
b
where
a.tsdf
= b.tsfs (+) and
a.tsdf
like upper(‘%&TableSpace%’)
and
round(((a.extn_space – (a.file_space –
nvl(b.free_space,0)))/a.extn_space)*100,2) < &PcFree
order
by a.tsdf;





 

Recent Posts