Shulou(Shulou.com)06/01 Report--

Can be used to contract segments, and there are two ways to eliminate space debris:

1.alter table table_name move

It should be noted that:

1) the move operation locks the table. If it is a very small watch, you can make it online. If it is a large table, you must pay attention to it, it will lock the table for a long time and can only query, which will affect the normal business operation. )

2) the move operation will invalidate the index, so be sure to rebuild. Because the move operation changes the ROWID of some records, the index becomes invalid after MOVE, requiring REBUILD. )

two。 Use shrink space

Alter table table_name shrink space

prerequisite

1) Line record transfer (enable row movement) must be enabled

2) it is only applicable to heap tables and is located in the table space of automatic segment space management (heap tables include: standard tables, partition tables, materialized view containers, materialized view log tables)

Advantages:

Improve cache utilization and OLTP performance

Reduce disk I / O, improve access speed, and save disk space

Segment contraction is online, and indexes are maintained during segment contraction, requiring no additional disk space

Add parameters

Cascade: shrink the table and its indexes and move the high water mark to free up space

Compact: just shrink the tables and indexes, don't move the high water mark, don't free up space.

If you do compression when the business is busy

You can use alter table shrink space compact to defragment the table without adjusting the high water mark, and then call alter table table_name shrink space again to free up space.

You can also use alter table table_name shrink space cascade to shrink indexes at the same time, which is equivalent to performing alter index idxname shrink space at the same time.

Method 1: move contraction table

1) create a new table test and insert data

SQL > create table TEST (id int, name char (2000)) tablespace users; Table created. SQL > insert into TEST values (1, 'aa'); SQL > insert into TEST values (2,' bb')

SQL > insert into TEST values (3, 'cc')

-- View rowid in test table

SQL > select Dbms_Rowid.rowid_block_number (rowid) from TEST

2) Delete part of the data in the table and view the rowid in the table again

SQL > delete from TEST where mod (id,2) = 1

SQL > select Dbms_Rowid.rowid_block_number (rowid) from TEST

3) perform move operation on the table

SQL > alter table TEST move

4) check the rowid in the table again

SQL > select Dbms_Rowid.rowid_block_number (rowid) from TEST

Summary: 1

After the move operation, the rowid of the data has changed. We know that the index obtains the data rows through rowid, so the index on the table must be rebuild.

5) check the index in the table. The index is invalid at this time.

SQL > select index_name,status from user_indexes where index_name='TEST_INDEX'

INDEX_NAME STATUS

TEST_INDEX UNUSABLE

6) re-indexing online

SQL > alter index TEST_MOVE_INDEX rebuild online

Summary: 2

After the move operation, the index in the table becomes invalid

-- check the lock

SQL > SELECT b.session_id AS sid

NVL (b.oracle_username,'(oracle)') AS username

A.owner AS object_owner

A.object_name

Decode (b.locked_mode, 0, 'None'

1, 'Null (NULL)'

2, 'Row-S (SS)'

3, 'Row-X (SX)'

4, 'Share (S)'

5, 'S/Row-X (SSX)'

6, 'Exclusive (X)'

B.locked_mode) locked_mode

B.os_user_name

FROM dba_objects a

V$locked_object b

WHERE a.object_id = b.object_id

SID USERNAME OBJECT_OWNER OBJECT_NAME LOCKED_MODE OS_USER_NAME

33 YMM YMM TEST Exclusive (X) oracle

Summary: 3

-- Exclusive (X) is the No. 6 lock, exclusive lock.

This means that when table is doing move operations, we can only select it.

That is to say, when one of our session does the DML operation on the table and there is no commit, the table cannot be move in the other session.

Otherwise, oracle will return this error message: ORA-00054.

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NAME EXTENTS BLOCKS INIT

-

TEST 3 1280 10

-- the TEST table initially allocates 10m of space and 1280 BLOCKS.

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

-

TEST

The USER_TABLES view shows that there are 0 BLOCKS,1280 free BLOCKS in use.

-- insert data into the table

SQL > insert into TEST select * from information

SQL > analyze table TEST compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NAME EXTENTS BLOCKS INIT

TEST 3 1280 10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 1006 274

After inserting the data, the allocated space remains the same because 10m has not been used up. Shows that 1006 BLOCKS are used and 274 BLOCKS are idle. At this time, 1006 BLOCKS is the high water mark.

SQL > commit

SQL > select count (*) from test

COUNT (*)

-

122513

SQL > delete from test where rownum analyze table test compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NAME EXTENTS BLOCKS INIT

--

TEST 3 1280 10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 1006 274

SQL > select count (distinct dbms_rowid.rowid_block_number (rowid)) used_blocks from test

USED_BLOCKS

-

five hundred and seventy three

As you can see here, after deleting some of the data, it still shows that 1006 BLOCKS are used, and the high water level remains the same. But the actual number of BLOCK used by the query is only 573. So the DELETE operation does not change the HWM.

SQL > alter table TEST move

SQL > analyze table TEST compute statistics

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NAME EXTENTS BLOCKS INIT

TEST 3 1280 10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 592 688

Summary: 4

After MOVE, the HWM has been reduced and the free block has also gone up.

-- but the space allocated has not changed, still 1280 BLOCKS.

Method 2: shrink space contraction table

SQL > delete from test where rownum alter table TEST enable row movement

SQL > alter table TEST shrink space

SQL > analyze table TEST compute statistics;-- > EMPTY_BLOCKS gets data after updating statistics using analyze

SQL > select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST'

SEGMENT_NAME EXTENTS BLOCKS INIT

-

TEST 1 600 10

SQL > select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'

TABLE_NAME BLOCKS EMPTY_BLOCKS

TEST 592 8

-- SHRINK SPACE really compresses the segment, including the initial allocation, so it reclaims the high water mark.

Verify the difference between cascade and compact

-- delete some data

SQL > delete from test where rownum alter table test shrink space compact;-- > shrink the table segment in compact mode

SQL > exec show_space ('TEST','SCOTT')

Unformatted Blocks. 0

FS1 Blocks (0-25).. one

FS2 Blocks (25-50) two

FS3 Blocks (50-75). 0

FS4 Blocks (75-100). one hundred and three

Full Blocks.. 14214-the only change is 14318-142140104 blocks, that is, a reduction of 104blocks of fully filled data blocks

Total Blocks.. 14488-the total number of blocks and total size of the data have not been reduced, that is, the high water mark has not been moved

Total Bytes.. 118685696

Total MBytes.. one hundred and thirteen

Unused Blocks.. five

Unused Bytes.. 40960

Last Used Ext FileId. four

Last Used Ext BlockId. 16521

Last Used Block. one hundred and forty seven

PL/SQL procedure successfully completed.

SQL > alter table test shrink space cascade;-- > shrink in cascade mode

SQL > exec show_space ('TEST','SCOTT')

Unformatted Blocks. 0

FS1 Blocks (0-25).. one

FS2 Blocks (25-50) two

FS3 Blocks (50-75). 0

FS4 Blocks (75-100). 0

Full Blocks.. 14214

Total Blocks.. 14384-- > the total number and size of blocks have been reduced.

Total Bytes.. 117833728

Total MBytes.. one hundred and twelve

Unused Blocks.. four

Unused Bytes.. 32768

Last Used Ext FileId. four

Last Used Ext BlockId. 16521

Last Used Block. forty-four

PL/SQL procedure successfully completed.

-- > the index remains valid after contraction

SQL > select OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='TEST'

OWNER INDEX_NAME STATUS

SCOTT idx_test VALID

Summary:

Compact: just shrink the tables and indexes, don't move the high water mark, don't free up space.

Cascade: shrink the table and its indexes and move the high water mark to free up space

Grammar summary:

ALTER TABLE ENABLE ROW MOVEMENT-- > prerequisites

ALTER TABLE SHRINK SPACE [| COMPACT | CASCADE]

ALTER TABLE SHRINK SPACE COMPCAT;-- > shrink the table and index, do not move the high water mark, and do not free up space

ALTER TABLE SHRINK SPACE;-- > shrink meter, lower high water mark

ALTER TABLE SHRINK SPACE CASCADE;-- > shrink the table, lower the high water mark, and shrink the related indexes.

ALTER TABLE MODIFY LOB (lob_column) (SHRINK SPACE);-- > shrink LOB segment

ALTER INDEX IDXNAME SHRINK SPACE

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.