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

The http://blog.itpub.net/29487349/viewspace-2143418/ article here talks about how to shrink the tablespace, so sometimes there will be the following situation, that is, we have a lot of free query tablespace, but there is very little resize space to execute the query in this article. Why? OK, let's take a look next.

Instance, Oracle11.2.0.4 for redhat6.7 x64

Query tablespace usage

Click (here) to collapse or open

TABLESPACE_NAME TOTAL FREE Free

--

SYSAUX 520 27 5

SYSTEM 750 9 1

TEST 20011156-TEST tablespace remaining 111m (test, small data)

UNDOTBS1 75 1 1

USERS 76.25 3 4

The size of the resize that can be executed by the query

Click (here) to collapse or open

Smallest

Size Current Poss.

FILE_NAME Poss. Size Savings

/ backup/oradata/mytest/test01.dbf 161 200 39

-

Sum 39, that is, the data file can release 39m, so what's going on?

Let's look at it through the following sentence:

Click (here) to collapse or open

SQL > select owner ownr, segment_name name,PARTITION_NAME, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks

2 from dba_extents

3 where tablespace_name = 'TEST'

4 order by block_id

5 /

...

SCOTT OBJPART_ID INDEX 21 5 4352 128

SCOTT OBJPART_ID INDEX 22 5 4480 128

SCOTT OBJPART_ID INDEX 23 5 4608 128

SCOTT OBJPART_ID INDEX 24 5 4736 128

SCOTT OBJPART_ID INDEX 25 5 4864 128

SCOTT OBJPART_ID INDEX 26 5 5120 128

SCOTT OBJ_PART P20141231 TABLE PA 0 5 9600 1024

SCOTT OBJ_PART P20141231 TABLE PA 1 5 10624 1024

SCOTT OBJ_PART P20141231 TABLE PA 2 5 19584 432-find out that the partition table block_id is larger, that is, it occupies the end of the data file (relative to other data blocks)

Then we can lower the position of the partitioned table by move table, that is, move to the position of the pretest point. Here, please pay attention to the table and index related to the partitioned table. Please judge according to the business and database.

Execute the following command:

Click (here) to collapse or open

SQL > alter table scott.obj_part MODIFY PARTITION p20141231 shrink space; this command does not reduce the block_id size in the data file, literally, it is only responsible for shrinking and lowering the high water level of the partition table. This command does not need to rebuild the index

Table altered.

SQL > alter table scott.obj_part move partition p20141231 tablespace test;-- move is moved to the free block at the front of the table space, and the index needs to be rebuilt

Table altered.

Check the tablespace resize again

Click (here) to collapse or open

SCOTT OBJPART_ID INDEX 23 5 4608 128

SCOTT OBJPART_ID INDEX 24 5 4736 128

SCOTT OBJPART_ID INDEX 25 5 4864 128

SCOTT OBJPART_ID INDEX 26 5 5120 128

SCOTT OBJ_PART P20141231 TABLE PA 0 5 5248 1024

SCOTT OBJ_PART P20141231 TABLE PA 1 5 6272 1024

SCOTT OBJ_PART P20141231 TABLE PA 2 5 7296 1024-position moved forward

SQL > select file_name

2 ceil ((nvl (hwm,1) * & & blksize) / 1024ax 1024) smallest

3 ceil (blocks*&&blksize/1024/1024) currsize

4 ceil (blocks*&&blksize/1024/1024)-

5 ceil ((nvl (hwm,1) * & & blksize) / 1024ax 1024) savings

6 from dba_data_files a

7 (select file_id, max (block_id+blocks-1) hwm

8 from dba_extents where tablespace_name='TEST'

9 group by file_id) b

10 where a.file_id = b.file_id (+) and tablespace_name='TEST'

Smallest

Size Current Poss.

FILE_NAME Poss. Size Savings

/ backup/oradata/mytest/test01.dbf 65 200 135

-

Sum 135The remaining space becomes larger.

Execute the relevant alter again. Resize operation can be done. When executing move and rebuild related to tables and indexes, please understand the business and related structures. Any operation has risks. Pay attention to backup and operating rules.

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.