partition table update partition-key result in changing tablespace

create table pt1
partition by list (deptno)
(
partition p1 values ('10') tablespace ts_1,
partition p2 values ('20') tablespace ts_2,
partition p3 values ('30') tablespace ts_3,
partition p4 values ('40') tablespace ts_4
)
as select * from emp ;[@more@]

SQL> select * from pt1 partition (p1) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10

SQL> alter tablespace ts_1 offline ;

Tablespace altered

SQL> select * from pt1 partition (p1) ;

select * from pt1 partition (p1)

ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/app/oracle/oradata/BYMIS/datafile/o1_mf_ts_1_6197cj0l_.dbf'

SQL> select * from pt1 ;

select * from pt1

ORA-00376: file 10 cannot be read at this time
ORA-01110: data file 10: '/u01/app/oracle/oradata/BYMIS/datafile/o1_mf_ts_1_6197cj0l_.dbf'

SQL> select * from pt1 partition (p2) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 20

SQL> select * from pt1 partition (p3) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-03 950.00 30

6 rows selected

SQL> select * from pt1 partition (p4) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> alter tablespace ts_1 online ;

Tablespace altered

SQL> select * from pt1 partition (p1) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10

SQL> update pt1 set deptno=40 where deptno=10 ;

update pt1 set deptno=40 where deptno=10

ORA-14402: updating partition key column would cause a partition change

SQL> update pt1 set deptno=40 where deptno=10 ;

update pt1 set deptno=40 where deptno=10

ORA-14402: updating partition key column would cause a partition change

SQL>
SQL> alter table pt1 enable row movement ;

Table altered

SQL> update pt1 set deptno=40 where deptno=10 ;

3 rows updated

SQL> commit ;

Commit complete

SQL> select * from pt1 partition (p1) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL> select * from pt1 partition (p4) ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7782 CLARK MANAGER 7839 1981-06-09 2450.00 40
7934 MILLER CLERK 7782 1982-01-23 1300.00 40
7839 KING PRESIDENT 1981-11-17 5000.00 40

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/350519/viewspace-1034420/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/350519/viewspace-1034420/