Author: kamusmail: kamus@itpub.netdate: 2004-4
Complete Resize DataFile via Move TableSpace. The concept of hwm is not explained here.
The test environment is the same as Oracle10g for Linux, the other versions.
We first create two tablespaces, which are T_TBS and T_TBS1, which have a data file, and the size is 5M and create a TEST_USER user to give this user's two tablespaces, and set the default table space is T_TBS. [zhangleyi @ as zhangley] $ sqlplus / as sysdba
SQL * Plus: Release 10.1.0.2.0 - Production on Tue Apr 13 21:01:25 2004
Copyright (C) 1982, 2004, Oracle. All Rights Reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Productionwith The Partitioning, OLAP AND DATA MINING OPTIONS
SYS AT ORCL10> ALTER USER TEST_USER Default TableSpace T_TBS;
User altered.
SYS AT ORCL10> ALTER USER TEST_USER Quota Unlimited on T_TBS;
User altered.
SYS AT ORCL10> ALTER USER TEST_USER Quota Unlimited on T_TBS1;
User altered
Log in with Test_User, create a table test_user at orcl10> create Table T_Obj As SELECT * FROM DBA_Objects WHERE ROWNUM <10000;
Table created.
Test_user at Orcl 10> INSERT INTO T_OBJ SELECT * from T_OBJ;
Rows created.
Test_user at OrCl10> /
19998 rows created.
TEST_USER AT ORCL10> / INSERT INTO T_OBJ SELECT * from T_Obj * Error At line 1: ORA-01653: Unable to extend TABLE TEST_USER.T_OBJ BY 128 in TableSpace T_TBS
TEST_USER AT ORCL10> CommIt;
COMMIT COMPLETE.
TEST_USER AT ORCL10> Select SUM (Blocks) "Total Blocks", SUM (Bytes) "Total Size" from DBA_EXTENTS WHERE OWNER = 'TEST_USER' AND segment_name = 't_obj';
Total Blocks Total Size ------------ ---------- 512 4194304
Ok, we have created a table and insert a lot of data. We can see the total number of BLOCKs and a total size through the DBA_EXTENTS view.
Below we use Delete to delete all data and insert a new 9999 Test_User At Orcl 10> Delete from T_OBJ;
39996 ROWS DELETED.TEST_USER AT ORCL10> INSERT INTO T_OBJ SELECT * FROM DBA_OBJECTS WHERE ROWNUM <10000;
Rows created.
TEST_USER AT ORCL10> CommIt;
COMMIT COMPLETE.
Test_user at Orcl 10> Select SUM (Blocks) "Total Blocks", SUM (Bytes) "Total Size" from DBA_EXTENTS 2 WHERE OWNER = 'test_user' and segment_name = 't_obj';
Total Blocks Total Size ------------ ---------- 512 4194304
The DBA_EXTENTS view is reviewed again, and the space that is occupied is not reduced.
We tried this data file, File # is 6 is the data file below the T_TBS table space Sys at orcl10> ALTER DATABASE DATAFILE 6 Resize 4M; ALTER DATABASE DATAFILE 6 Resize 4M * Error At line 1: ORA-03297: File Contains Used Data Beyond Requested Resize Value
SYS AT ORCL10> ALTER DATABASE DATAFILE 6 Resize 4500000;
Database altered.
We have found that Resize to 4M can not, but resize is 4500000, because Total Size is 4194304 above, this value is greater than 4m and less than 4500000.
Then we move this table to the T_TBS1 table space, this table space below the data file file # is 8
EST_USER AT ORCL10> ALTER TABLE T_OBJ MOVE TABLESPACE T_TBS1;
Table altered.
Test_user at Orcl 10> Select SUM (Blocks) "Total Blocks", SUM (Bytes) "Total Size" from DBA_EXTENTS 2 WHERE OWNER = 'test_user' and segment_name = 't_obj';
Total Blocks Total Size ------------ ---------- 128 1048576
We checked the DBA_EXTENTS view, found that Total size has changed, and it can be explained that the MOVE table is reset to the block, and HWM is also reset.
Below we resize this data file. SYS AT ORCL10> ALTER DATABASE DATAFILE 8 Resize 3M;
Database altered.
Sys at orcl10> host [zhangley @ askATA / ORCL10 / DATAFILE / [ZHANGLEYI @ as datafile] $ ls -l total dubes 1419076-rw-r ----- 1 zhangley DBA 20979712 April 13 21:17 Cattbs01.dbf-rw-r ----- 1 zhangleyi DBA 157294592 April 13 21:17 O1_MF_EXAMPLE_02P0GPOJ_.DBF-RW-R ------ 1 zhangley DBA 419438592 April 13 21:20 O1_MF_SYSAUX_02P09KNY_. DBF-RW-R ----- 1 zhangleyi DBA 555753472 April 13 21:17 O1_MF_SYSTEM_02P09KNO_.DBF-RW-R ----- 1 zhangley DBA 20979712 April 13 21:02 O1_MF_TEMP_02P0FZSD_.TMP-RW-R- ---- 1 ZHANGLEYI DBA 62922752 April 13 21:20 O1_MF_UNDOTBS1_02P09KOG_.DBF-RW-R ----- 1 zhangley DBA 209723392 April 13 21:17 O1_MF_USERS_02P09KQV_.dbf [color = red] -rw-r - --- 1 zhangleyi DBA 3153920 April 13 21:21 Test01.dbf [/ color] [color = red] -rw-r ---- 1 zhangleyi DBA 4513792 April 13 21:20 Test.dbf [/ color ] It can be seen that our purpose has been reached.
In real applications, we can put all Object in a table space into a new table space, then Drop drop the original table space, and then delete the data file in the original table space from the disk.
As for how to learn HWM, we can get the data dictionary after Analyze, then we can run the following script if you do not perform Analyze. This script can be used to check a total block number with an Object and the number of block on HWM, which of course knows where HWM is in.
DECLAREv_total_blocks NUMBER; v_total_bytes NUMBER; v_unused_blocks NUMBER; v_unused_bytes NUMBER; v_last_used_extent_file_id NUMBER; v_last_used_extent_block_id NUMBER; v_last_used_block NUMBER; BEGINdbms_space.unused_space ( 'SCOTT', 'BIGEMP', 'TABLE', v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block ); DBMS_OUTPUT.PUT_LINE ('Total Blocks:' || to_CHAR (V_TOTAL_BLOCKS)); DBMS_OUTPUT.PUT_LINE ('Blocks Above HWM:' || to_CHAR (V_UNUSED_BLOCKS); END; /
Total Blocks: 256Blocks Above HWM: 0PL / SQL Procedure SuccessFully Completed
Executed in 0.01 seconds
