今天在论坛看到讨论 http://www.itpub.net/thread-2067562-1-1.html
对第一个问题很感兴趣,实验如下:尝试在sys用户下建立大表test,然后将表迁移到scott用户下
OS:Oracle Linux Server release 6.4 64
数据库:oracle10.2.0.1.0 64bit
1、非归档模式
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0/dbs/arch
Oldest online log sequence 246
Current log sequence 248
SQL>
2、创建大表
SQL> conn / as sysdba
Connected.
SQL> create table test as select * from dba_object;
并用insert into test select * from test将表test数据创建到将近1亿条
3、查看表test的大小
1)查看分配的物理空间
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
2)查看实际占用空间
analyze table test compute statistics;
select num_rows * avg_row_len
from user_tables
where table_name = 'TEST';
查出大约10G
4、将scott用户的表空间users的数据文件调整到合适大小
5、迁移
create table target_test
tablespace users
parallel (degree 8)
nologging
as select * from test ;
忘记统计时间,但是不会太长,十分钟左右。
对第一个问题很感兴趣,实验如下:尝试在sys用户下建立大表test,然后将表迁移到scott用户下
OS:Oracle Linux Server release 6.4 64
数据库:oracle10.2.0.1.0 64bit
1、非归档模式
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/10.2.0/dbs/arch
Oldest online log sequence 246
Current log sequence 248
SQL>
2、创建大表
SQL> conn / as sysdba
Connected.
SQL> create table test as select * from dba_object;
并用insert into test select * from test将表test数据创建到将近1亿条
3、查看表test的大小
1)查看分配的物理空间
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
2)查看实际占用空间
analyze table test compute statistics;
select num_rows * avg_row_len
from user_tables
where table_name = 'TEST';
查出大约10G
4、将scott用户的表空间users的数据文件调整到合适大小
5、迁移
create table target_test
tablespace users
parallel (degree 8)
nologging
as select * from test ;
忘记统计时间,但是不会太长,十分钟左右。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31414383/viewspace-2132825/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31414383/viewspace-2132825/