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

-Analog data insertion

DECLARE

I NUMBER (10)

BEGIN

FOR I IN 1.. 50000 LOOP

INSERT INTO TEST_TAB VALUES (I, 'TESTSTRING')

END LOOP

COMMIT

END

/

PL/SQL procedure successfully completed.

TEST88@nopdb > select count (*) from TEST_TAB

COUNT (*)

-

50000

SYS@nopdb > select file_id, max (block_id) from dba_extents where file_id=7 group by file_id

FILE_ID MAX (BLOCK_ID)

--

7 155776

SYS@nopdb > select file_id,round (max (block_id) * 8 + 1024) HWMsize from dba_extents where file_id=7 group by file_id

FILE_ID HWMSIZE

--

7 1217

-- MOVE operation

TEST88@nopdb > alter table TEST_TAB move online

Table altered.

TEST88@nopdb > select count (*) from TEST_TAB

COUNT (*)

-

50000

SYS@nopdb > select file_id,round (max (block_id) * 8 + 1024) HWMsize from dba_extents where file_id=7 group by file_id

FILE_ID HWMSIZE

--

7 1290

SYS@nopdb > select file_id, max (block_id) from dba_extents where file_id=7 group by file_id

FILE_ID MAX (BLOCK_ID)

--

7 165120

-- indicates that the new block is used

-- View tablespace usage

TABLESPACE_NAME AUTOEXTENSIBLE CURRENT_SPACE_G CURRENT_USED_G MAX_SIZE_G TOTAL_USED_PCT

TEST88 NO 2. 01 2. 00

SYS@nopdb > alter database datafile 7 resize 1g

Alter database datafile 7 resize 1g

*

ERROR at line 1:

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

At this point, it is proved that empty blocks cannot be recycled, because the allocated table blocks are not recycled.

-truncate the table

TEST88@nopdb > truncate table TEST_TAB

TEST88@nopdb > select count (*) from TEST_TAB

COUNT (*)

-

0

SYS@nopdb > select file_id, max (block_id) from dba_extents where file_id=7 group by file_id

FILE_ID MAX (BLOCK_ID)

--

7 163984

SYS@nopdb > select file_id,round (max (block_id) * 8 + 1024) HWMsize from dba_extents where file_id=7 group by file_id

FILE_ID HWMSIZE

--

7 1281

Table truncated.

Tablespace usage:

TABLESPACE_NAME AUTOEXTENSIBLE CURRENT_SPACE_G CURRENT_USED_G MAX_SIZE_G TOTAL_USED_PCT

TEST88 NO 2 0 2. 00

-- move to another tablespace

TEST88@nopdb > alter table TEST_TAB move tablespace users online

Table altered.

-- moving back

TEST88@nopdb > alter table TEST_TAB move tablespace TEST88 online

Table altered.

SYS@nopdb > select file_id, max (block_id) from dba_extents where file_id=7 group by file_id

FILE_ID MAX (BLOCK_ID)

--

7 136

SYS@nopdb > select file_id,round (max (block_id) * 8 + 1024) HWMsize from dba_extents where file_id=7 group by file_id

FILE_ID HWMSIZE

--

7 1

SYS@nopdb > alter database datafile 7 resize 2m

Database altered.

Test results: only by moving other tablespaces can the requested blocks be released. DROP should be OK, too.

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.