Online
Table Redefinition Primer
Trenutni
opis tabele admin_emp
Verifikacija
mogućnosti redefinicije
Konačna
struktura tabele ADMIN_EMP
SQL>
desc admin_emp
Name
Null? Type
-----------------------------------------
-------- ------------
EMPNO NOT NULL NUMBER(5)
ENAME NOT NULL VARCHAR2(15)
JOB
VARCHAR2(10)
DEPTNO NOT NULL NUMBER(3)
- dodavanje novih kolona mgr,
hiredate, sal, bonus
- vrednost podataka u koloni bonus
se inicijalizuje na vrednost 0 (0 bonus)
- vrednost podataka u koloni deptno
povećava se za 10 (deptno+10 deptno)
- nova tabela se particionira po
vrednosti za kolonu deptno
grant connect, resource to scott;
grant CREATE ANY TABLE to scott;
grant ALTER ANY TABLE to scott;
grant DROP ANY TABLE to scott;
grant LOCK ANY TABLE to scott;
grant SELECT ANY TABLE to scott;
grant execute on dbms_redefinition to scott;
grant CREATE ANY TRIGGER to scott;
grant CREATE ANY INDEX to scott;
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('scott','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/
PL/SQL procedure successfully completed.
CREATE TABLE int_admin_emp
(empno NUMBER(5)
PRIMARY KEY,
ename
VARCHAR2(15) NOT NULL,
job
VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE
DEFAULT (sysdate),
sal
NUMBER(7,2),
deptno NUMBER(3)
NOT NULL,
bonus NUMBER
(7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION
emp1000 VALUES LESS THAN (1000) TABLESPACE TEST,
PARTITION
emp2000 VALUES LESS THAN (2000) TABLESPACE TEST)
/
Table created.
SQL> BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('scott',
'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0
bonus',
dbms_redefinition.cons_use_pk);
END;
/
PL/SQL procedure successfully completed.
(Bug No.5054629, Filed 22-FEB-2006, Severity Minimal Loss of
Service, WORKAROUND: Grant SYSDBA or run the online redefinition as SYS?!)
SQL> show user
USER is "SYS"
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('scott',
'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
PL/SQL procedure successfully completed.
SQL> select
object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS
/
OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------------------------
------------------------------ ----------------------------------------------------------
ADMIN_EMP_PK ADMIN_EMP CREATE UNIQUE INDEX
"SCOTT"."TMP$$_ADMIN_EMP_PK0" ON "SCOT
SYS_C004628 ADMIN_EMP ALTER TABLE
"SCOTT"."INT_ADMIN_EMP" MODIFY ("ENAME" CONSTR
SYS_C004629 ADMIN_EMP ALTER TABLE
"SCOTT"."INT_ADMIN_EMP" MODIFY ("DEPTNO" CONST
ADMIN_EMP_PK ADMIN_EMP ALTER TABLE
"SCOTT"."INT_ADMIN_EMP" ADD CONSTRAINT "TMP$$_
Mogu se ignoriasati.
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('scott',
'admin_emp', 'int_admin_emp');
END;
/
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott',
'admin_emp', 'int_admin_emp');
END;
/
PL/SQL procedure successfully completed.
SQL> drop table INT_ADMIN_EMP;
CREATE TABLE
SCOTT.ADMIN_EMP
(
EMPNO
NUMBER(5),
ENAME
VARCHAR2(15 BYTE) NOT NULL,
JOB
VARCHAR2(10 BYTE),
MGR
NUMBER(5),
HIREDATE DATE DEFAULT
(sysdate),
SAL
NUMBER(7,2),
DEPTNO
NUMBER(3) NOT NULL,
BONUS
NUMBER(7,2) DEFAULT (1000)
)
TABLESPACE
TEST
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY
RANGE (EMPNO)
(
PARTITION EMP1000 VALUES LESS THAN (1000)
LOGGING
NOCOMPRESS
TABLESPACE TEST
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION EMP2000 VALUES LESS THAN (2000)
LOGGING
NOCOMPRESS
TABLESPACE TEST
PCTFREE
10
INITRANS
1
MAXTRANS
255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
ALTER TABLE
SCOTT.ADMIN_EMP ADD (
PRIMARY KEY
(EMPNO)
USING INDEX
TABLESPACE TEST
PCTFREE
10
INITRANS
2
MAXTRANS
255
STORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));