Oracle Database tablespace capacity adjustment (tablespace contraction script) script

Oracle database tablespace capacity adjustment (tablespace contraction script) script -- 1. Obtain the tablespace information to be released (including the tablespace owned by oracle database) -- drop table system. tbs_detail; create table system. tbs_detail as selecta. tablespace_name,. bytes/1024/1024 "Sum_MB", (. bytes-b.bytes)/1024/1024 "used_MB", B. bytes/1024/1024 "free_MB", round (. bytes-b.bytes)/. 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. tablespace_name = B. tablespace_name order by (. bytes-b.bytes)/. bytes) desc; -- select * from system. tbs_detail order by "Sum_MB" desc, "free_MB" desc; -- 2. Get the usage of the application tablespace data file to be released -- drop table system. datafile_space; create table system. datafile_space as select. TABLESPACE_NAME,. FILE_NAME,. 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. FILE_ID = B. file_id and. 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, add 100 MB space on the basis of the actual space used by each data file 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 <EOFset feed offset linesize 200 set pagesize 200 set echo offspool/home/oracle/check_log/chktbs. log appendselect name, state, type, total_mb, free_mb from v \ $ asm_diskgroup; spool offquitEOF