上篇文档练习了通过ctas+parrel+nologging快速迁移大表target_test到其它schema，今天练习通过创建索引等操作，减少查询等的等待时间。
OS:Oracle Linux Server release 6.4 64
数据库：oracle10.2.0.1.0 64bit
1、表的情况
SQL> show user
USER is "SCOTT"
SQL> desc target_test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from target_test order by owner;
COUNT(*)
----------
92037120

2、创建索引
SQL>create index v_target_test on target_test(object_name,owner);
报错：ORA-01652:unable to extend temp segment by num in scott_tbs
经过查询，发现scott用户的默认表空间是scott_tbs,而scott用户下所有表的表空间为users，users的空间足够
所以决定将scott的默认表空间设置为users
alter user scott default tablespace users;
然后索引就创建成功。

ITPub博客

ORA-01652:

最新文章