oracle删除数据缩表空间,关于收缩表空间，求教

这是我从一个puber的 博客上  看到的 很经典 ，

相信你看了以后会有更深的感悟

The only thing that'll make a file "shrink" is to alter the database and shrink the

datafile - datafiles will NOT shrink on their own due to "offline/online" or anything

like that.

But in your case - if OEM cannot shrink it further, neither will my script.  A file

contains extents of segments.  You have an extent for some allocated object out there.

Say you have tables T1 and T2 in that tablespace.

T1 is represented by X

T2 is represented by Y

free space in the tablespace is represented by f

You created T1 and T2, your datafile in that tablespace might look like this:

XYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

you could shrink that file now and get rid of all of the f's.  But now table T1 grows and

we have:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXfff

Now, you can shrink that file and get rid of just three f's (rest of the file is full of

data).  Now, table T2 runs out of room and extends:

XYXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXYff

Now, if you shrank the file, you would lose two f's of space.  However, you drop table T1

- the datafile looks like this:

fYfffffffffffffffffffffffffffffffffffffffffffffffffffffffffffYff

You can STILL only shrink two f's away at the end - there is a Y extent way out there and

we cannot shrink over it.  What you can do is:

alter table t2 MOVE and hopefully the datafile will end up looking like this:

YfYffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff

and you can shrink the file (or just move t2 into another tablespace all together)

see http://www.oracle.com/technology ... p/o54asktom.htmlfor "What's at the end of a file"

Here is an example showing what I tried to draw above:

ops$tkyte@ORA9IR2> create tablespace shrink_me

datafile 'C:\oradata\ora10win\shrink_me.dbf' size 704k

segment space management manual

uniform size 64k;

Tablespace created.

we have exactly 10 64k extents we can use.  (the 11th 64k block of space is used by

Oracle to manage these locally managed tablespaces in the datafile)

ops$tkyte@ORA9IR2>

ops$tkyte@ORA9IR2> create table t1

( x int, a char(2000) default 'a', b char(2000) default 'b', c char(2000) default 'c')

tablespace shrink_me;

Table created.

ops$tkyte@ORA9IR2> create table t2

( x int, a char(2000) default 'a', b char(2000) default 'b', c char(2000) default 'c')

tablespace shrink_me;

Table created.

Each row in these tables will consume a block (8 rows/extent - but don't forget the

first block is borrowed by Oracle to manage space in the segment...)

ops$tkyte@ORA9IR2>

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block;

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 T1

13          17         24 T2

13          25         88 free

so, we have the starting scenario - T1 has an extent, T2 has one and the rest of the

file is "free space", now lets fill up t1:

ops$tkyte@ORA9IR2> insert into t1 (x) select rownum from all_objects where rownum <= 56;

56 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block, segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block;

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 T1

13          17         24 T2

13          25         32 T1

13          33         40 T1

13          41         48 T1

13          49         56 T1

13          57         64 T1

13          65         72 T1

13          73         80 T1

13          81         88 free

10 rows selected.

we have the middle scenario - if we dropped T1 now, all of the T1's would become free

space and we could shrink the file, however:

ops$tkyte@ORA9IR2>

ops$tkyte@ORA9IR2>

ops$tkyte@ORA9IR2> insert into t2 (x) select rownum from all_objects where rownum <= 8;

8 rows created.

ops$tkyte@ORA9IR2> commit;

Commit complete.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block;

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 T1

13          17         24 T2

13          25         32 T1

13          33         40 T1

13          41         48 T1

13          49         56 T1

13          57         64 T1

13          65         72 T1

13          73         80 T1

13          81         88 T2

10 rows selected.

Now the entire tablespace is full - no more free space - but we drop t1 and get LOTS of

free space:

ops$tkyte@ORA9IR2> drop table t1;

Table dropped.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block;

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 free

13          17         24 T2

13          25         80 free

13          81         88 T2

the tablespace is 80% empty - but we cannot shrink it - we cannot shrink it at all, let

alone to 300k - because the LAST EXTENT is taken by T2

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;

alter database datafile '/tmp/shrink_me.dbf' resize 300k

*

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

but lets move T2 around...

ops$tkyte@ORA9IR2> alter table t2 move;       (HR用户下执行)

Table altered.

ops$tkyte@ORA9IR2>

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block；

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 T2

13          17         24 free

13          25         32 T2

13          33         88 free

now we have lots of free space at the end of the file and we can resize:

ops$tkyte@ORA9IR2> alter database datafile '/tmp/shrink_me.dbf' resize 300k;

Database altered.

ops$tkyte@ORA9IR2> select file_id, block_id first_block, block_id+blocks-1 last_block,segment_name

from dba_extents

where tablespace_name = 'SHRINK_ME'

union all

select file_id, block_id, block_id+blocks-1, 'free'

from dba_free_space

where tablespace_name = 'SHRINK_ME'

order by file_id, first_block；

FILE_ID FIRST_BLOCK LAST_BLOCK SEGMENT_NAME

---------- ----------- ---------- ------------------------------

13           9         16 T2

13          17         24 free

13          25         32 T2

don't forget, if you move a table, you have to then rebuild the indexes