Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the recycling method of database tables, indexes and tablespaces". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

Description: after the database has been running for a period of time, the database needs to be archived.

After the completion of database archiving, tables and indexes need to be shrunk in order to really improve the running efficiency of the whole system.

If you need to reclaim the tablespaces of the database, you also need to rsize the data files.

Detailed operation steps:

I. shrinking of tables and indexes

1. View the objects that need to be shrunk (look at tables under a tablespace that have recyclable space of more than 100m)

Script:

SELECT NUM_ROWS,AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 pinggu, BLOCKS*8/1024shiji, (BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9) MB,TABLE_NAME

FROM dba_tables

WHERE tablespace_name='' AND

BLOCKS*8/1024-AVG_ROW_LEN*NUM_ROWS/1024/1024/0.9 > 100 AND rownum0

Order by 5

2. The utilization rate of some data files is very small, but the Resize of data files does not reclaim much space. You can check the corresponding space by using the following statement

Segment allocation of files

Select * from dba_extents where tablespace_name=' 'AND FILE_ID='' ORDER BY BLOCK_ID DESC

Note: the size of the resize of the data file is determined by the largest BLOCK_ID, so you can operate through the above shrink or move command

3. Perform RESIZE operations on data files

ALTER DATABASE DATAFILE'/ oracle/ID1/112_64/dbs/oradataccdata.dbf' (location of the data file)

RESIZE 10000M .

This is the end of the content of "what is the method of recycling database tables, indexes and tablespaces". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.