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

This article mainly shows you "how to reduce the high water level in the database". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "how to reduce the high water level in the database".

1. Movea.move can not only reset the water level line (HWM) and solve the IO waste caused by the loose table, but also solve the problem of row migration in the table.

B.move can move tables to other table spaces or in the original table space, which can solve table space fragmentation to some extent.

c. If there is a large table on the table space and the index is drop (or truncate), resulting in a large amount of free space in the first half of the table space, you can move the back table to the front free space through move, thus shrinking the data file.

Experiment:

Sys@ORCL > conn shall/shall

Connected.

Shall@ORCL > create table zhong (x int)

Table created.

Shall@ORCL > begin

2 for i in 1..100000 loop

3 insert into zhong values (I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

-collect statistics

Shall@ORCL > analyze table zhong compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 244 12

-delete table zhong

Shall@ORCL > delete zhong

100000 rows deleted.

Shall@ORCL > analyze table zhong compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 244 12

-move defragmenting

Shall@ORCL > alter table zhong move

Table altered.

Or alter table zhong move tablespace hct;-move to hct tablespace

/ *

If you move to the hct tablespace, you can see that the tablespace has changed, as follows

Shall@ORCL > select table_name,tablespace_name from user_tables

TABLE_NAME TABLESPACE_NAME

TTTT USERS

ZHONG HCT

, /

Shall@ORCL > analyze table zhong compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 0 8

-the high water level has come down. Move to the tablespace, you need to ensure that there is enough remaining space.

-rebuild the index

Shall@ORCL > alter index inx_t_x rebuild

Index altered.

Or alter index inx_t_x rebuild tablespace users

-View index status

SCOTT@test > set linesize 200

SCOTT@test > select index_name,table_name,tablespace_name,status from user_indexes

-Note:

-Rebuild index

After the move operation on the table, the rowid in the table has changed, which causes the index to be unable to locate the data in the original table, which triggers the index failure, so the command of alter index index_name rebuild [online] is needed to rebuild.

-Space allocation

For alter table move operation, you must give enough remaining space to the table space of move, otherwise an ORA-01652 alarm may occur.

-exclusive lock

The move operation is equivalent to moving all the data in the table, so during the move process, oracle places an exclusive lock lock on the table, and it can only be select at this time.

2. Shrink space this command is a new function for Oracle 10g. The shrink operation is to restructure the original loose data storage structure, migrate the back rows in the table to the front free block, release the completely free area after completion, and pre-HWM to the location of the last block in the table, thus realizing the loose table to be restructured and compact.

Conditions of use

Automatic segment management mode. Only ASSM managed tablespaces are supported. If not, ORA-10635: Invalid segment or tablespace type will be reported.

Open Row Mobile alter table table_name enable row movement

Parameters:

Alter table TABLE_NAME shrink space [compact | cascate]

Alter table TABLE_NAME shrink space; defragment and reclaim space

Alter table TABLE_NAME shrink space compact; only defragments and does not reclaim space.

Alter table TABLE_NAME shrink space cascate; defragmentation reclaims space and collates (an index, for example) along with cascading objects of the table.

Use steps

1. Alter table t1 enable ROW MOVEMENT

2. Shrink operation

3. Alter table t1 disable ROW MOVEMENT

Experiment:

-View tablespace segment management mode

Sys@ORCL > select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces order by segment_space_management

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN

SYSAUX 8192 LOCAL SYSTEM AUTO

HCT 8192 LOCAL SYSTEM AUTO

USERS 8192 LOCAL SYSTEM AUTO

EXAMPLE 8192 LOCAL SYSTEM AUTO

TEMP 8192 LOCAL UNIFORM MANUAL

UNDOTBS1 8192 LOCAL SYSTEM MANUAL

SYSTEM 8192 LOCAL SYSTEM MANUAL

-View the default tablespace used by shall users

Sys@ORCL > select username,default_tablespace,temporary_tablespace from dba_users where username='SHALL'

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

-

SHALL USERS TEMP

-create tables and insert data

Sys@ORCL > conn shall/shall

Connected.

Shall@ORCL > create table shall (ttt int)

Table created.

Sys@ORCL > begin

2 for i in 1..1000000 loop

3 insert into shall values (I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

Shall@ORCL > analyze table shall compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='SHALL'

TABLE_NAME BLOCKS EMPTY_BLOCKS

SHALL 1630 34

-delete table shall

Shall@ORCL > delete shall

1000000 rows deleted.

Shall@ORCL > analyze table shall compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='SHALL'

TABLE_NAME BLOCKS EMPTY_BLOCKS

SHALL 1630 34

-start shrink defragmentation

Shall@ORCL > alter table shall enable row movement

Table altered.

Shall@ORCL > alter table shall shrink space

Table altered.

Shall@ORCL > alter table shall disable row movement

Table altered.

-the high water level did not fall before refreshing the statistics.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='SHALL'

TABLE_NAME BLOCKS EMPTY_BLOCKS

SHALL 1630 34

Shall@ORCL > analyze table shall compute statistics

Table analyzed.

Shall@ORCL > select table_name,blocks,empty_blocks from user_tables where table_name='SHALL'

TABLE_NAME BLOCKS EMPTY_BLOCKS

SHALL 1 7

Advantages of using shrink to lower high water levels:

1) can be performed online without affecting the DML operations on the table. Of course, concurrent DML operations will have a brief block at the end of the shrink.

2) another advantage of shrink is that the relevant index on the table is still enable after defragmentation.

As for the second point, the rowid of the row has changed when shrink defragmented the table, so why can the relevant index still be enable? In fact, oracle will maintain the corresponding index during the shrink to ensure that the index is still valid when the index ends the shrink. This maintenance is different from the index rebuild, which does not organize the index space. Shrink has the cascede option, and if you add this option to the shrink, the corresponding index space on the table will be sorted. ALTER TABLE tablename SHRINK SPACE CASCADE

Shrink can also be carried out in two steps.

1) execute ALTER TABLE tablename SHRINK SPACE compact first. In this case, oracle will move row to the top of segment as far as possible below the high water mark, but will not shrink the high water mark, that is, no space will be released. This operation is useful for queries that are trying to read blocks that have been released.

2) then, when ALTER TABLE test SHRINK SPACE is executed, the results in the first step have been stored on disk and will not be defragmented again, but will only shrink the high water level and free up space. The second step should be done when the system is not busy.

How shrink works

Shrink's algorithm starts from the bottom of the segment and moves the row to the top of the segment. The moving process is equivalent to the combination of delete/insert operations, in which a large amount of undo and redo information will be generated.

In addition, for space requirements, shrink does not need extra space, move needs twice as much space.

3. Rename to copies the data you want to retain to the temporary table t drop the original table, and then the rename to temporary table t is the original table

Verify:

Begin

For i in 1..100000 loop

Insert into T2 values (I)

End loop

Commit

End

/

Analyze table t2 compute statistics

Select table_name,blocks,empty_blocks

From dba_tables

Where table_name='T2'

TABLE_NAME BLOCKS EMPTY_BLOCKS

T2 152 103

SQL > delete T2

100000 rows deleted.

SQL > create table T3 as select * from T2

SQL > analyze table T2 compute statistics

SQL > select table_name,blocks,empty_blocks

2 from dba_tables

3 where table_name='T2'

TABLE_NAME BLOCKS EMPTY_BLOCKS

T2 152 103

SQL > drop table T2

SQL > alter table T3 rename to T2

SQL > analyze table T2 compute statistics

SQL > select table_name,blocks,empty_blocks

2 from dba_tables

3 where table_name='T2'

TABLE_NAME BLOCKS EMPTY_BLOCKS

T2 1 6

4. After exp/imp is exported with EXP, the original table / tablespace is deleted, and then re-imported with IMP

Experiment:

Shall@ORCL > create table zhong (id int)

Table created.

Shall@ORCL > begin

2 for i in 1..1000000 loop

3 insert into zhong values (I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

Shall@ORCL > analyze table zhong compute statistics

Table analyzed.

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 1630 34

-delete and then export the table

Shall@ORCL > delete zhong where id > 50000

950000 rows deleted.

[oracle@zyx ~] $exp\'/ as sysdba\ 'tables=shall.zhong file=zhong.dmp log=zhong.log

Export: Release 11.2.0.4.0-Production on Sun May 1 18:34:39 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path...

Current user changed to SHALL

. . Exporting table ZHONG 50000 rows exported

Export terminated successfully without warnings.

[oracle@zyx ~] $

-drop original table

Shall@ORCL > drop table zhong

Table dropped.

-Import table

[oracle@zyx ~] $imp\'/ as sysdba\ 'tables=zhong file=zhong.dmp fromuser=shall touser=shall

Import: Release 11.2.0.4.0-Production on Sun May 1 18:37:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. Importing SHALL's objects into SHALL

. . Importing table "ZHONG" 50000 rows imported

Import terminated successfully without warnings.

[oracle@zyx ~] $

-when statistics are not refreshed

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 1630 0

-after refreshing statistics

Shall@ORCL > analyze table zhong compute statistics

Table analyzed.

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 110 1554

The BLOCKS column represents the number of database blocks that have been used in the table, that is, the waterline. EMPTY_BLOCKS stands for database blocks assigned to the table, but above the waterline, that is, blocks that have never been used

5. Deallocate unusedalter table table_name deallocate unused

Note: this proves that DEALLOCATE UNUSED frees up unused space above the HWM, but does not free up the free space under the HWM, nor does it move the location of the HWM.

After truncate table, it is possible that the tablespace has not been freed. You can use the following statement:

Alter table table name deallocate UNUSED KEEP 0

For example:

Alter table tablename deallocate UNUSED KEEP 0

Or:

Truncate table tablename DROP STORAGE; can free the tablespace

Note: tablespaces will not be freed without KEEP 0.

Experiment: import and export experiment above

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 110 1554

-start tidying up

Sys@ORCL > alter table shall.zhong deallocate unused keep 0

Table altered.

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 110 1554

Sys@ORCL > analyze table shall.zhong compute statistics

Table analyzed.

-after finishing

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 110 18

6. Use truncate as far as possible in truncate (e.g. truncate T1)

Experiment: connected to the above experiment

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 110 18

Sys@ORCL > truncate table shall.zhong

Table truncated.

Sys@ORCL > analyze table shall.zhong compute statistics

Table analyzed.

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 0 128

Sys@ORCL > alter table shall.zhong deallocate unused keep 0

Table altered.

Sys@ORCL > analyze table shall.zhong compute statistics

Table analyzed.

Sys@ORCL > select table_name,blocks,empty_blocks from dba_tables where table_name='ZHONG'

TABLE_NAME BLOCKS EMPTY_BLOCKS

ZHONG 0 24

The above is all the contents of the article "how to lower the high water level in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.