注意：本实验为不含有lob对象用户
用户：lizi 原表空间：lizi 新表空间：lizi1
查看用户情况：
SQL> select * from dba_users where username='LIZI';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE
EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ---------- ------------------------------ -------------------------------- ---------
--------- ------------------------------ ------------------------------ --------- ------------------------------ --
----------------------------
EXTERNAL_NAME
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
LIZI 59 FA00A8A82502557E OPEN
LIZI TEMP 23-AUG-10 DEFAULT
DEFAULT_CONSUMER_GROUP
查看索引情况：
SQL> CONN LIZI/LIZI
Connected.
SQL> select INDEX_NAME,INDEX_TYPE from user_indexes where INDEX_NAME='INDEX_CODE';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
INDEX_CODE NORMAL
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where
INDEX_NAME='INDEX_CODE';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------ -------------------------
----- ------------------------------
INDEX_CODE NORMAL LIZI TESTA
LIZI
查看lizi表空间里所包含的对象及大小：
SQL> select SEGMENT_NAME,bytes/1024/1024 from user_segments where TABLESPACE_NAME='LIZI';
SEGMENT_NAME BYTES/1024/1024
--------------------------------------------------------------------------------- ---------------
TESTA 12
INDEX_CODE 12.125
BIN$j0IxSSwYRizgQM3KBLIWtg==$0 .0625
BIN$j0IxSSwYRizgQM3KBLIWtg==$0这个对象还不知道是什么
SQL> select sum(bytes)/1024/1024 from user_segments where Segment_Name='INDEX_CODE';
SUM(BYTES)/1024/1024
--------------------
12.125
创建lizi1表空间，移动表跟索引：
SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@XS-I620-01 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.1.0.3.0 - Production on Thu Sep 2 15:36:50 2010
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='LIZI';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
/home/O10G/O10G/lizi.dbf
5 LIZI 52428800 6400 AVAILABLE
5 YES 3.4360E+10 4194302 1280 52363264 6392
SQL> CREATE TABLESPACE lizi1
2 DATAFILE '/home/O10G/O10G/lizi1.dbf' SIZE 50M autoextend on next 10m
3 maxsize unlimited
4 EXTENT MANAGEMENT LOCAL;
Tablespace created.
SQL> alter table testa move tablespace lizi1;
alter table testa move tablespace lizi1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user;
USER is "SYS"
SQL> conn lizi/lizi;----------------------注意必须使用所要移动的用户来操作
Connected.
SQL> alter table testa move tablespace lizi1;
Table altered.
SQL> alter index INDEX_code rebuild tablespace lizi1;
Index altered.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select SEGMENT_NAME,bytes/1024/1024,TABLESPACE_NAME from user_segments where TABLESPACE_NAME='LIZI';
SEGMENT_NAME BYTES/1024/1024 TABLESPACE_NAME
--------------------------------------------------------------------------------- --------------- -----------------
-------------
BIN$j0IxSSwYRizgQM3KBLIWtg==$0 .0625 LIZI
SQL> select SEGMENT_NAME,bytes/1024/1024,TABLESPACE_NAME from user_segments where TABLESPACE_NAME='LIZI1';
SEGMENT_NAME BYTES/1024/1024 TABLESPACE_NAME
--------------------------------------------------------------------------------- --------------- -----------------
-------------
TESTA 12 LIZI1
INDEX_CODE 18 LIZI1
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where
INDEX_NAME='INDEX_CODE';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME
TABLESPACE_NAME
------------------------------ --------------------------- ------------------------------ -------------------------
----- ------------------------------
INDEX_CODE NORMAL LIZI TESTA
LIZI1
SQL> select * from dba_users where username='LIZI';
select * from dba_users where username='LIZI'
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select SEGMENT_NAME,bytes/1024/1024,TABLESPACE_NAME from user_segments where TABLESPACE_NAME='LIZI1';
SEGMENT_NAME BYTES/1024/1024 TABLESPACE_NAME
--------------------------------------------------------------------------------- --------------- -----------------
-------------
TESTA 12 LIZI1
INDEX_CODE 18 LIZI1
SQL> CONN SYS/Oracle10 as sysdba
Connected.
SQL> select * from dba_users where username='LIZI';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE
EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ---------- ------------------------------ -------------------------------- ---------
--------- ------------------------------ ------------------------------ --------- ------------------------------ --
----------------------------
EXTERNAL_NAME
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
LIZI 59 FA00A8A82502557E OPEN
LIZI TEMP 23-AUG-10 DEFAULT
DEFAULT_CONSUMER_GROUP
可以看到用户还没有移动到新的表空间
移动用户到新的表空间：
SQL> alter user lizi default tablespace lizi1;
User altered.
SQL> select * from dba_users where username='LIZI';
USERNAME USER_ID PASSWORD ACCOUNT_STATUS LOCK_DATE
EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE
INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ---------- ------------------------------ -------------------------------- ---------
--------- ------------------------------ ------------------------------ --------- ------------------------------ --
----------------------------
EXTERNAL_NAME
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
LIZI 59 FA00A8A82502557E OPEN
LIZI1 TEMP 23-AUG-10 DEFAULT
DEFAULT_CONSUMER_GROUP
向该用户下的表插入数据测试：
SQL> select
2 total.tablespace_name,
3 round(total.MB,2) as Total_MB,
4 round(total.MB-free.MB, 2) as Used_MB,
5 round((1-free.MB/total.MB)*100, 2) as Used_Pct
6 from
7 (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,
8 (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total
9 where
10 free.tablespace_name=total.tablespace_name;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
EDU2009 5000 .06 0
LIZI 50 .06 .13
LIZI1 50 30.06 60.13
SYSAUX 410 385.81 94.1
SYSTEM 460 455 98.91
UNDOTBS1 220 10.25 4.66
USERS 836.25 835.13 99.87
7 rows selected.
SQL> conn lizi/lizi
Connected.
SQL> select count(*) from testa;
COUNT(*)
----------
1000000
SQL> timing start;
SQL> declare var number :=1;
2 BEGIN
3 while (var<=1000000) LOOP
4 insert into testa(code) values(var);
5 var:=var+1;
6 END LOOP;
7 END;
8 /
timing stop;
PL/SQL procedure successfully completed.
SQL> Elapsed: 00:01:49.06
SQL> SQL>
SQL>
SQL> select count(*) from testa;
COUNT(*)
----------
2000000
SQL> conn sys/Oracle10 as sysdba;
Connected.
SQL> select
2 total.tablespace_name,
3 round(total.MB,2) as Total_MB,
4 round(total.MB-free.MB, 2) as Used_MB,
5 round((1-free.MB/total.MB)*100, 2) as Used_Pct
6 from
7 (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_free_space group by tablespace_name) free,
8 (select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name) total
9 where
10 free.tablespace_name=total.tablespace_name;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
EDU2009 5000 .06 0
LIZI 50 .06 .13
LIZI1 70 60.06 85.8
SYSAUX 410 385.81 94.1
SYSTEM 460 455 98.91
UNDOTBS1 220 196.63 89.38
USERS 836.25 835.13 99.87
7 rows selected.
至此可以发现新的表空间发生了明显变化，移动完成。
