移动用户及其对象到新的表空间试验
时间：2010-09-03 来源：lizisor
注意：本实验为不含有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. 至此可以发现新的表空间发生了明显变化，移动完成。
查看用户情况：
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. 至此可以发现新的表空间发生了明显变化，移动完成。
相关阅读 更多 +
