Oracle database table Space Capacity tuning script

(Table Space indent script)]

--1, get tablespace information that needs to be freed (contains Oracle database own table space)

--drop table System.tbs_detail;

CREATE TABLE System.tbs_detail as Select

A.tablespace_name,

a.bytes/1024/1024 "SUM_MB",

(a.bytes-b.bytes)/1024/1024 "USED_MB",

b.bytes/1024/1024 "FREE_MB",

Round (((a.bytes-b.bytes)/a.bytes) *100,2) "Percent_used"

From

(select tablespace_name,sum (bytes) bytes from Dba_data_files Group by Tablespace_name) A,

(select Tablespace_name,sum (bytes) Bytes,max (bytes) largest from Dba_free_space Group by Tablespace_name) b

where A.tablespace_name=b.tablespace_name

Order by ((a.bytes-b.bytes)/a.bytes) desc;

--select * from System.tbs_detail "SUM_MB" desc, "FREE_MB" DESC;

--2, obtaining application table space data file usage in need of free space

--drop table System.datafile_space;

CREATE TABLE System.datafile_space as

Select A.tablespace_name,

A.file_name,

a.bytes/1024/1024 Total,

b.sum_free/1024/1024 Free

From Dba_data_files A,

(select file_id, sum (bytes) sum_free

From Dba_free_space

Group BY file_id) b

where a.file_id = b.file_id

and A.tablespace_name in (select Tablespace_name

From System.tbs_detail

Where (Tablespace_name like '%cqlt% ' or

Tablespace_name like '%cqst% '

or tablespace_name like ' ts% ' or tablespace_name like ' idx% '

or tablespace_name like '%hx% ')

and "SUM_MB" > 100);

--select * from System.datafile_space;

--3, generate data file size reset script, add 100m space on the basis of the actual space size of each data file currently in use

Select ' ALTER DATABASE datafile ' ' | | file_name | | "' Resize ' | |

Round (To_number (total-free + 100), 0) | | ' M; '

From System.datafile_space;

--View ASM disk group usage

Sqlplus/as Sysdba <

Set Feed off

Set Linesize 200

Set PageSize 200

Set echo off

Spool/home/oracle/check_log/chktbs.log Append

Select Name,state,type,total_mb,free_mb from V$asm_diskgroup;

Spool off

Quit

Eof