移动用户及其对象到新的表空间试验

注意：本实验为不含有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

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.

至此可以发现新的表空间发生了明显变化，移动完成。

移动用户及其对象到新的表空间试验

热门频道