[Pin to top] Oracle Database tablespace capacity adjustment (tablespace contraction script) script

-- 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