How to release space from database( in other words: resize datafile ) 【zt】
How to release space from database
(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)
當然就是resize datafile 了,下面稍微寫了一下步驟
[@more@]How to release space from database
(Step 3-5 only applies to development/testing DB or in an offline production DB, because move table would destroy index.)
1. Find which tablespace have much free space.
Select sum(bytes)/1024/1024 , tablespace_name from dba_free_space
Group by tablespace_name order by 1 desc
SUM(BYTES)/1024/1024 TABLESPACE_NAME
721.0625 USERS
327.25 UNDOTBS1
2. For the tablespace that has much free space, try to resize it to the highest blocks.
SELECT 'alter database datafile '||d.file_id ||' resize '||
NVL(ROUND(MAX(e.block_id+e.blocks -1)* t.block_size /1024/1024+1),11)||'M;'
FROM dba_extents e, dba_data_files d , dba_tablespaces t
WHERE d.tablespace_name = t. tablespace_name
AND d.file_id = e.file_id(+)
AND t. tablespace_name = :tbsname
AND e. tablespace_name(+) = :tbsname
GROUP BY d.file_id , t.block_size
SCRIPT
alter database datafile 5 resize 237M;
alter database datafile 6 resize 41M;
3. Check if there is still much free space. If not, continue to the next tablespace, Else, continue to the next step.
4. Find the free space of the tablespace.
SELECT file_id , block_id, block_id+blocks , blocks
FROM dba_free_space
WHERE TABLEspace_name = :tbsname
ORDER BY 1, 2 DESC
FILE_ID | First_block | Last_block | Total Block |
5 | 30217 | 30337 | 120 |
5 | 28937 | 29833 | 896 |
5 | 28553 | 28681 | 128 |
5 | 28185 | 28297 | 112 |
5 | 28129 | 28169 | 40 |
5 | 28081 | 28121 | 40 |
5 | 13129 | 28041 | 14912 |
5 | 7601 | 13097 | 5496 |
5 | 2657 | 7585 | 4928 |
5 | 2585 | 2625 | 40 |
5 | 137 | 2569 | 2432 |
6 | 5105 | 5249 | 144 |
6 | 5081 | 5097 | 16 |
6 | 5001 | 5073 | 72 |
6 | 4745 | 4849 | 104 |
6 | 3625 | 4705 | 1080 |
6 | 97 | 3593 | 3496 |
5. For each file, try to move the segment down the big free chunk.
a. Move tables above the point
b. Rebuild affected indexes
c. Rebuild indexes above the point
a. Move tables above the point
Select distinct ‘alter table ‘||owner||’.’||segment_name||’ move ;’
From dba_extents
Where file_id = :file_id
And segment_type = ‘TABLE’
And tablespace_name = :tbs_name
And block_id > :block_id --(for file_id 6, should be 3593)
b. Rebuild affected indexes
Select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’
From all_indexes
where status = ‘UNUSED’
c. Rebuild indexes above the point
Select distinct ‘alter table ‘||owner||’.’||segment_name||’ move ;’
From dba_extents
Where file_id = :file_id
And segment_type = ‘INDEX’
And tablespace_name = :tbs_name
And block_id > :block_id --(for file_id 6, should be 3593)
6. Continue step 2
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-999742/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How to release space from database( in other words: resize datafile ) (zt)Database
- [轉]How to release space from databaseDatabase
- Alter database datafile resize ORA-03297 原因解析Database
- How to copy a datafile from ASM to a file system not using RMANASM
- Unable To Open Database After ASM Upgrade From Release 11.1 To Release 11.2DatabaseASM
- How to free space from an ASM diskgroup? (Doc ID 1553744.1)ASM
- How to restore and recover a database from an RMAN backup_881395.1RESTDatabase
- How to move ASM database files from one diskgroup to anotherASMDatabase
- how to remove datafile pathREM
- [ZT]MapReduce explained in 41 wordsAI
- oracle實用sql(6)--tablespace/datafile resizeOracleSQL
- alter database create datafileDatabase
- alter database create datafile '' as ''Database
- 轉貼roger大師_resize datafile小記
- How to Choose Size of Datafile on Raw Devicedev
- Move datafile:From File System to ASMASM
- Move datafile:From ASM to File SystemASM
- Renaming a Datafile in the Primary DatabaseDatabase
- How to Find Out How Much Space an Index is UsingIndex
- How to Convert Decimal Numbers to Words with PythonDecimalPython
- How to Quiesce a DatabaseUIDatabase
- MySQL :Ignoring query to other databaseMySqlDatabase
- ALTER DATABASE DATAFILE OFFLINEDatabase
- Major Database Release Number (22)Database
- Allocate More Space for a Database (37)Database
- How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11 on Linux [轉帖]SQLOracleDatabaseGatewayServerLinux
- How to Perform a Healthcheck on the DatabaseORMDatabase
- How to enable the flashback database:Database
- How a Database Is Mounted (293)Database
- AC040--Postings to CO from Other Components
- Database Testing: How to Regression Test a Relational DatabaseDatabase
- How to build your custom release bazel version?UI
- How to recover from USB pipe errorsError
- alter database datafile offline drop 與 alter tablespace drop datafile 區別Database
- How to Perform a Health Check on the DatabaseORMDatabase
- [原創] How to Quiesce a DatabaseUIDatabase
- How a Standby Database Is Mounted (295)Database
- How a Clone Database Is Mounted (296)Database