將表遷移至其他表空間

實驗環境：REDHAT 5.5 X64系統+ORACLE10.2.0.4

實驗要求：將表遷移至其他表空間

 

實驗過程

建立源表空間及使用者

create tablespace test1 datafile '/home/oracle/oradata/orcl/test101.dat' size 100m;

 

create tablespace test2 datafile '/home/oracle/oradata/orcl/test102.dat' size 100m;

 

create user test identified by test default tablespace test1 temporary tablespace temp;

grant resource,connect,dba to test;

create table test.testtp as (select * from dba_object_tables);

create table test.testtp1 as (select * from dba_object_tables);

create table test.testtp2 as (select * from dba_object_tables);

create table test.testtp3 as (select * from dba_object_tables);

conn test/test

create index testtpi on ttesttp(table_name) tablespace test1;

create index testtp1i on testtp1(table_name) tablespace test1;

create index testtp2i on testtp2(table_name) tablespace test1;

create index testtp3i on testtp3(table_name) tablespace test1;

 

1.查詢當前表空間所包含表

select table_name ,tablespace_name from dba_tables where tablespace_name = 'TEST1';

2.查詢當前使用者預設表空間

select username,default_tablespace from dba_users where username = 'TEST';

 

3.查詢TEST使用者下的需要表分析的表並生成表分析語句

注：可能出現回車符號，去掉使得語句為一行，即可執行

SQL> select 'exec dbms_stats.gather_table_stats(ownname=> '|| '''' || owner || ''''|| ',tabname=>' ||''''|| table_name ||        ''''||',cascade=>true);'  from all_all_tables  where owner in ('TEST');

 

'EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'||''''||OWNER||''''||',TABNAME=>'||

--------------------------------------------------------------------------------

exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP3',cascade=>true);

 

exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP2',cascade=>true);

 

exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP1',cascade=>true);

 

exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP',cascade=>true);

 

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP3',cascade=>true);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP2',cascade=>true);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP1',cascade=>true);

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TEST',tabname=>'TESTTP',cascade=>true);

 

PL/SQL procedure successfully completed.

 

3.檢視使用者下所有索引狀態

SQL> select index_name,tablespace_name,status from all_indexes where wner= 'TEST';

 

INDEX_NAME

------------------------------

TABLESPACE_NAME                                              STATUS

------------------------------------------------------------ --------

TESTTP3I

TEST1                                                        VALID

 

TESTTP2I

TEST1                                                        VALID

 

TESTTP1I

TEST1                                                        VALID

 

                                                                                   

4.將表遷移到新表空間中

conn test/test

alter table testtp move tablespace TEST2;

alter table testtp1 move tablespace TEST2;

alter table testtp2 move tablespace TEST2;

alter table testtp3 move tablespace TEST2;

 

5. 表遷移完成之後檢視當前表空間和原表空間的表

SQL> select table_name ,tablespace_name from dba_tables where tablespace_name = 'TEST1';

 

no rows selected

 

SQL> select table_name ,tablespace_name from dba_tables where tablespace_name = 'TEST2';

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TESTTP                         TEST2

TESTTP1                        TEST2

TESTTP2                        TEST2

TESTTP3                        TEST2

 

6.檢視使用者下所有索引狀態

SQL> select index_name,tablespace_name,status from all_indexes where wner= 'TEST';

 

INDEX_NAME

------------------------------

TABLESPACE_NAME                                              STATUS

------------------------------------------------------------ --------

TESTTP3I

TEST1                                                        UNUSABLE

 

TESTTP2I

TEST1                                                        UNUSABLE

 

TESTTP1I

TEST1                                                        UNUSABLE

 

7.重建索引

生成重建索引語句

SQL> Select  distinct  'alter index  '||b.owner||'.'||b.index_name||' rebuild online tablespace '||tablespace_name||';' from dba_ind_columns a, dba_indexes b where a.table_owner = b.owner and b.owner = 'TEST' and a.table_name in(select table_name from all_indexes where wner= 'TEST' and STATUS='UNUSABLE') And  a.index_name = b.index_name;

 

'ALTERINDEX'||B.OWNER||'.'||B.INDEX_NAME||'REBUILDONLINETABLESPACE'||TABLESPACE_

--------------------------------------------------------------------------------

alter index  TEST.TESTTP2I rebuild online tablespace TEST1;

alter index  TEST.TESTTP1I rebuild online tablespace TEST1;

alter index  TEST.TESTTP3I rebuild online tablespace TEST1;

 

8.檢視使用者下所有索引狀態

SQL> select index_name,tablespace_name,status from all_indexes where wner= 'TEST';

 

SQL> select index_name,tablespace_name,status from all_indexes where wner= 'TEST';

 

INDEX_NAME

------------------------------

TABLESPACE_NAME                                              STATUS

------------------------------------------------------------ --------

TESTTP3I

TEST1                                                        VALID

 

TESTTP1I

TEST1                                                        VALID

 

TESTTP2I

TEST1                                                        VALID

 

9.重新進行表分析

略