How the oracle table is fragmented and sorted

Oracle table fragmentation and finishing is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Determine the degree of fragmentation

SELECT table_name

ROUND ((blocks * 8), 2) "high water space k"

ROUND ((num_rows * avg_row_len / 1024), 2) "Real use Space k"

ROUND ((blocks * 10 / 100) * 8,2) "reserved space (pctfree) k"

ROUND (

(blocks * 8

-(num_rows * avg_row_len / 1024)

-blocks * 8 * 10 / 100)

2)

"waste of space k"

FROM user_tables

WHERE temporary ='N'

ORDER BY 5 DESC

View the table when statistics were last collected

Select table_name,last_analyzed from dba_tables where owner = 'SCHEMA_NAME'

Collect statistics for objects throughout the Schema

SQL > exec dbms_stats.gather_schema_stats (ownname= > 'SCHEMA_NAME')

-- organize the table without affecting DML operation

SQL > alter table TABLE_NAME enable ROW MOVEMENT;-- starts the line movement function

SQL > alter table TABLE_NAME shrink space compact;-only defragment without reclaiming space

-- reset the high water level. There can be no DML operation at this time.

SQL > alter table TABLE_NAME shrink space;-defragment and reclaim space and adjust the watermark. Execute when there is little business.

SQL > alter table TABLE_NAME disable ROW MOVEMENT;-- turn off line movement

Advantages of shrink:

There is no need to rebuild the index.

It can be operated online.

If the system business is busy

You can first execute shrink space compact to reorganize data, and then execute shrink space to reduce HWM to release free data blocks when the business is not busy.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.