模拟行迁移试验
1、创建scott模式
[oracle@std dbs]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 17 14:59:02 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select user_id,username from dba_users; USER_ID USERNAME ---------- ------------------------------ 11 OUTLN 0 SYS 5 SYSTEM 24 DBSNMP 21 TSMSYS 19 DIP 6 rows selected. SQL> @?/rdbms/admin/utlsampl.sql Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
2、备份EMP表
[oracle@std dbs]$ sqlplus scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 17 15:02:50 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE BONUS TABLE SALGRADE TABLE SQL> select constraint_name,constraint_type,table_name from user_constraints; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ FK_DEPTNO R EMP PK_DEPT P DEPT PK_EMP P EMP SQL> alter table emp drop constraints pk_emp; Table altered.
3、分析行连接
SQL> @?/rdbms/admin/utlchain.sql Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLE EMP TABLE CHAINED_ROWS TABLE BONUS TABLE SALGRADE TABLE EMP2 TABLE 6 rows selected. SQL> analyze table emp list chained rows into chained_rows; Table analyzed. SQL> select count(*) from chained_rows where table_name='EMP'; COUNT(*) ---------- 0
4、模拟产生行连接
SQL> truncate table emp; Table truncated. SQL> alter table emp pctfree 0; Table altered. SQL> insert into emp select * from emp2; 14 rows created. SQL> insert into emp select * from emp; 14 rows created. SQL> / 28 rows created. SQL> / 56 rows created. SQL> / 112 rows created. SQL> / 224 rows created. SQL> / 448 rows created. SQL> / 896 rows created. SQL> / 1792 rows created. SQL> commit; Commit complete. SQL> desc emp Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> update emp set ename='AAAAAAAAAA' where empno=7844; 256 rows updated. SQL> commit; Commit complete. SQL> analyze table emp list chained rows into chained_rows; Table analyzed. SQL> select count(*) from chained_rows where table_name='EMP'; COUNT(*) ---------- 30
5、消除行迁移
SQL> create table emp_old as select * from emp where rowid in 2 (select head_rowid from chained_rows 3 where table_name='EMP'); Table created. SQL> delete from emp where rowid in 2 (select head_rowid from chained_rows 3 where table_name='EMP'); 30 rows deleted. SQL> insert into emp select * from emp_old; 30 rows created. SQL> drop table emp_old; Table dropped. SQL> delete from chained_rows where table_name='EMP'; 30 rows deleted. SQL> commit; Commit complete. SQL> analyze table emp list chained rows into chained_rows; Table analyzed. SQL> select count(*) from chained_rows where table_name='EMP'; COUNT(*) ---------- 0
