online_redef_11204

SQL> create user dorothy account unlock identified by oracle;

User created.

SQL> grant connect to dorothy;

Grant succeeded.

SQL> grant create session to dorothy;

Grant succeeded.

SQL> conn dorothy
Enter password:
Connected.
SQL> CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);  2    3    4    5

Table created.

SQL> insert into redef_tab values(123,'valami');

1 row created.

SQL> insert into redef_tab values(321,'lol');

1 row created.

SQL> CREATE VIEW redef_tab_v AS
SELECT * FROM redef_tab;  2

View created.

SQL> CREATE SEQUENCE redef_tab_seq;

Sequence created.

SQL> CREATE OR REPLACE PROCEDURE get_description (
  p_id          IN  redef_tab.id%TYPE,
  p_description OUT redef_tab.description%TYPE) AS
BEGIN
  SELECT description
  INTO   p_description
  FROM   redef_tab
  WHERE  id = p_id;
END;
/  2    3    4    5    6    7    8    9   10

Procedure created.

SQL> CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/  2    3    4    5    6    7    8

Trigger created.

SQL> COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;SQL>

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

6 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@localhost dbhome_2]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 5 12:07:31 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> EXEC DBMS_REDEFINITION.can_redef_table('DOROTHY', 'REDEF_TAB');

PL/SQL procedure successfully completed.

SQL> CREATE TABLE dorothy.redef_tab2 AS
SELECT *
FROM   dorothy.redef_tab WHERE 1=2;  2    3

Table created.

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

Session altered.

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

Session altered.

SQL> EXEC DBMS_REDEFINITION.start_redef_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> ALTER TABLE dorothy.redef_tab2 ADD (CONSTRAINT redef_tab2_pk PRIMARY KEY (id));

Table altered.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> DROP TABLE dorothy.redef_tab2;

Table dropped.

SQL> ALTER TABLE dorothy.redef_tab RENAME CONSTRAINT redef_tab2_pk TO redef_tab_pk;

Table altered.

SQL> disconn
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> conn dorothy
Enter password:
Connected.
SQL> COLUMN object_name FORMAT A20
SQL> SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

--Hogy ne vesszen el a trigger:

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  :new.id := redef_tab_seq.NEXTVAL;
END;
/  2    3    4    5    6    7    8

Trigger created.

SQL> disconn
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> EXEC DBMS_REDEFINITION.can_redef_table('DOROTHY', 'REDEF_TAB');

PL/SQL procedure successfully completed.

SQL> CREATE TABLE dorothy.redef_tab2 AS
SELECT *
FROM   dorothy.redef_tab WHERE 1=2;  2    3

Table created.

SQL> ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

Session altered.

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

Session altered.

SQL> EXEC DBMS_REDEFINITION.start_redef_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname               => 'DOROTHY',
    orig_table          => 'REDEF_TAB',
    int_table           => 'REDEF_TAB2',
    copy_indexes        => 1,             -- Default
    copy_triggers       => TRUE,          -- Default
    copy_constraints    => TRUE,          -- Default
    copy_privileges     => TRUE,          -- Default
    ignore_errors       => FALSE,         -- Default
    num_errors          => l_num_errors,
    copy_statistics     => FALSE,         -- Default
    copy_mvlog          => FALSE);        -- Default

  DBMS_OUTPUT.put_line('num_errors=' || l_num_errors);
END;
/SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
num_errors=0

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('DOROTHY', 'REDEF_TAB', 'REDEF_TAB2');

PL/SQL procedure successfully completed.

SQL> DROP TABLE dorothy.redef_tab2;

Table dropped.

SQL> disconn
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
SQL> conn dorothy
Enter password:
Connected.
SQL> COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;SQL>

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_BIR        TRIGGER             INVALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                INVALID

6 rows selected.

SQL> ALTER TRIGGER redef_tab_bir COMPILE;

Trigger altered.

SQL> ALTER VIEW redef_tab_v COMPILE;

View altered.

SQL> COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects ORDER BY object_name;SQL>

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
GET_DESCRIPTION      PROCEDURE           VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB2_PK        INDEX               VALID
REDEF_TAB_BIR        TRIGGER             VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_V          VIEW                VALID

6 rows selected.