将表迁移至其他表空间

实验环境：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);

 

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

 

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.重新进行表分析

略

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26739940/viewspace-766943/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26739940/viewspace-766943/