-- 1. obtain information about the tablespace to be released (including the tablespace owned by Oracle database)
-- 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) *) "percent_used"
From
(Select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(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 order by "sum_mb" DESC, "free_mb" DESC;
-- 2. Obtain the usage of the application tablespace data file to be released
-- Drop table system. datafile_space;
Create Table System. datafile_space
Select a. tablespace_name,
A. file_name,
A. Bytes/1024/1024 total,
B. sum_free/1024/1024 free
From dba_data_files,
(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 a data file size reset script to increase the space usage of each data file by 100 MB.
Select 'alter database datafile' | file_name | '''resize' |
Round (to_number (total-free + 100), 0) |'m ;'
From system. datafile_space;
-- View the usage of the ASM disk group
Sqlplus/As sysdba <EOF
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
For reprinting, please indicate the author's source and original article links; otherwise, you will be held legally responsible:
Author: xiangsir
Link: http://blog.csdn.net/xiangsir/article/details/9002201
QQ: 444367417
MSN: xiangsir@hotmail.com