上篇文档练习了通过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;
然后索引就创建成功。
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博客 ” ,链接:http://blog.itpub.net/31414383/viewspace-2132835/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31414383/viewspace-2132835/