oracle把表移到另一个表空间,将表迁移至其他表空间

实验环境：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 = 'TE