En Oracle los datos e índices se encuentran físicamente en tablespaces. En algunos modelos de almacenamiento tiene sentido tener tablespaces separados para índices y datos. Sin embargo con las cabinas tipo EVA5000, EVA8100, etc., debido al concepto de virtualización la separación no ofrece ventajas en cuanto a rendimiento. Formas de reorganizar las tablas:
Hay 3 formas de reorganizar las tablas:
- Export e Import del esquema Oracle. El inconveniente es que lo tiene que hacer el Administrador, que es un proceso pesado y que se interrumpe el servicio.
- Alter table move y alter index rebuild. Esta método produce bloqueos. Es más selectivo que el anterior por lo que el tiempo de interrupción es menor. El inconveniente es que los índices se quedan en mal estado y hay que reconstruirlos.
ALTER TABLE
ALTER INDEX
La pega es que una tabla puede tener 15 índices por ejemplo. Si hacemos el MOVE de la tabla, ésta no se encuentra disponible hasta que hagamos el REBUILD de los índices. Para facilitar el trabajo podemos hacer lo siguiente:
ALTER TABLE TABLA-UNO MOVE;
ALTER TABLE TABLA-DOS MOVE;
SELECT 'ALTER INDEX',index_name,'REBUILD;'
from user_indexes
where table_name in ('TABLA-UNO','TABLA-DOS');
- Utilización de DBMS_REDEFINITION. La ventaja es que no hay ningún bloqueo. La reorganización se hace on-line. Sin embargo no se puede utilizar para todo tipo de tablas. Hay una nota Oracle que explica su uso. Es la 177407.1.
NOTA 177407.1 DE ORACLE
PURPOSEPrior to Oracle9i, any table re-organizations and redefinitions forced the online. This note explains how to reorganize a table online. NOTE: This note does not cover the use of row ids. As of 9.2.0.5, additional parameters were added to the DBMS_REDEFINITION packages which allow redefinition to use a row id instead of a primary key. SCOPE & APPLICATION Oracle8, Oracle8i, and Oracle9i. How to Re-Organize a Table Online:================================== What can be Redefined ONLINE on a Table?---------------------------------------- => A non-partitioned table can be converted into a partitioned table, and vice versa => The organization of a table can be changed from a heap based to IOTs (Index Organized Tables), and vice versa => Non-primary key columns can be dropped => New columns can be added to a table => Existing columns can be renamed => Parallel support can be added or removed => Storage parameters can be modified Renaming columns is possible without using Online Redefinition in RDBMS version9.2 with the new RENAME COLUMN clause of the ALTER TABLE statement. Restrictions------------The table to be re-organized: * Must have a primary key (restriction lifted in 9.2.0.5) * Cannot have User-defined data types * Cannot have FILE or LONG columns * Cannot be clustered * Cannot be in the SYS or SYSTEM schema * Cannot have materialized view logs and/or materialized views defined on them * Cannot be an horizontal subsetting of data * Must be re-organized within the same schema * Looses its snapshot logs * Can get new columns as part of the re-organization, but the new columns must be declared NULL until the re-organization is complete Process------- ------ 5' Table INT_EMP becomes EMP ------- | and vice-versa | 1 2 Source Table 3 Create interim Table EMP ---> Start redefinition ---> INT_EMP 3' data Source to Target ^ ^ ^ / | | / | | / 4 | / Create constraints / Create indexes 3'' / Create triggersDML on source table /updates/deletes/inserts 5 /Finish redifinitionstored into MLOG$_EMP ------ Step 1: Verify that the source table is able to undergo an ONLINE redefinition using the dbms_redefinition.can_redef_table procedure. Step 2: Create an empty interim table reflecting the final structure Step 3: Start the redefinition of the source table using the dbms_redefinition.start_redef_table procedure, defining: -> the source table to be reorganized -> the interim table -> the columns mapping for each column undergoing a change The start_redef_table procedure automatically: -> inserts all rows from the source table into the interim table -> creates a snapshot table MLOG$_EMP and a snapshot log to store DML changes temporarily until the final step of the redefinition Step 4: Create the constraints, indexes and triggers on the interim table as they are defined on the source table. Any referential constraint involving the interim table should be created disabled. Step 5: Finish the redefinition of the source table using the dbms_redefinition.finish_redef_table procedure. The finish_redef_table procedure automatically: -> applies all DML changes stored in the snapshot table into the interim table -> exchanges the names of the 2 tables: the interim table becomes the source table and vice versa Be aware that the names of the constraints, indexes, and triggers do not have the names they had on the source table. Step 6: Drop the interim table Step 7: From RDBMS version 9.2, the constraints and indexes can be renamed with the ALTER TABLE ... RENAME CONSTRAINT ... statement. In RDBMS version 8.x, and 9.0.x, if you want the restructured table to have the initial names for the constraints and indexes, you have to rebuild them, and for the triggers, you have to recreate them. Other Useful Procedures----------------------- a. Intermediate synchronization ----------------------------It is recommended to periodically synchronize the interim table with the sourceone when a large amount of DML is executed on the table while the re-organization is taking place by calling the dbms_redefinition.sync_interim_table() procedure. Calling this procedure reduces the time taken by dbms_redefinition.finish_redef_table() to complete the re-organization process. The small amount of time that the original table is locked during dbms_redefinition.finish_redef_table() is independent of whether dbms_redefinition.sync_interim_table() has been called. b. Abort and cleanup after errors ------------------------------The user can call dbms_redefinition.abort_redef_table() in the event that anerror is raised during the re-organization process or the user wants to abort the re-organization process. This procedure allows to user to specify if the interim table should be dropped as part of the cleanup operations. Example------- The example below re-organizes the EMP table (assumes use of primary key): --> whose current structure is: SQL> desc emp Name Null? Type ------------------------------------- -------- --------------- EMPNO NOT NULL NUMBER NAME VARCHAR2(20) DEPTNO NUMBER --> with the following constraints: EMP_PK : a primary key with a UNIQUE index EMP_PK EMP_FK : a foreign key referencing the DEPT_PK (PK of the DEPT table) --> with the following indexes: EMP_PK on the primary key column EMPNO I_EMP_DEPTNO non-unique index on column DEPTNO --> with the following trigger: T_EMP that increments a counter in the AUDIT_EMP table by 1 each time an INSERT/UPDATE/DELETE occurs on a row in the EMP table --> The table contains 100 000 rows before the re-organization: SQL> select count(*) from emp; COUNT(*) ---------- 100000 --> The counter in the audit_emp table corresponds to the number of rows in the emp table: SQL> select * from audit_emp; C ---------- 100000 ----------------------------------------------------------------1. Determine if the table is a candidate for online re-organization ---------------------------------------------------------------- SQL> connect sys/x as sysdba Connected. SQL> grant execute on dbms_redefinition to test; Grant succeeded. SQL> connect test/test Connected. SQL> exec dbms_redefinition.can_redef_table('TEST', 'EMP'); BEGIN dbms_redefinition.can_redef_table('TEST', 'EMP'); END; * ERROR at line 1: ORA-12089: cannot online redefine table "TEST"."EMP" with no primary key ORA-06512: at "SYS.DBMS_REDEFINITION", line 8 ORA-06512: at "SYS.DBMS_REDEFINITION", line 247 ORA-06512: at line 1 A primary key is mandatory since materialized views and logs are created during the start of redefinition. SQL> alter table emp add constraint emp_pk primary key(empno); Table altered. SQL> execute DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','EMP'); PL/SQL procedure successfully completed. ------------------------------------------------------------2. Create an empty interim table reflecting the final structure ------------------------------------------------------------ For example, the EMP table undergoes the following transformations: * non partitioned --> partitioned * column NAME --> LAST_NAME * a new colum SAL is added SQL> create table int_emp(empno number, last_name varchar2(20), 2 deptno number, sal number) 3 partition by list (deptno)4 (partition p10 values (10),
5 partition p20 values (20), 6 partition p30 values (30), 7 partition p40 values (40)); SQL> select object_name,object_type,status,object_id,data_object_id
2 from user_objects; OBJECT_NAME OBJECT_TYPE STATUS OBJECT_ID DATA_OBJECT_ID ------------ ------------------ ------- ---------- -------------- AUDIT_EMP TABLE VALID 5806 5806 DEPT TABLE VALID 5793 5793 DEPT_PK INDEX VALID 5794 5794 EMP TABLE VALID 5803 5803 EMP_PK INDEX VALID 5808 5808 I_EMP_DEPTNO INDEX VALID 5805 5805 T_EMP TRIGGER VALID 5807 INT_EMP TABLE PARTITION VALID 5831 5831 INT_EMP TABLE PARTITION VALID 5832 5832 INT_EMP TABLE PARTITION VALID 5833 5833 INT_EMP TABLE PARTITION VALID 5834 5834 INT_EMP TABLE VALID 5830 ---------------------------------3. Start the re-organization process --------------------------------- SQL> execute SYS.DBMS_REDEFINITION.START_REDEF_TABLE('TEST', - > 'EMP', - > 'INT_EMP', - > 'name last_name'); BEGIN SYS.DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_REDEFINITION", line 8 ORA-06512: at "SYS.DBMS_REDEFINITION", line 146 ORA-06512: at line 1 The user performing the re-organization requires the following privileges: * CREATE ANY TABLE * ALTER ANY TABLE * DROP ANY TABLE * LOCK ANY TABLE * SELECT ANY TABLE * CREATE ANY INDEX * CREATE ANY TRIGGER SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', - > 'EMP', - > 'INT_EMP', - > 'empno empno, name last_name, deptno deptno'); PL/SQL procedure successfully completed. SQL> select sql_text from v$sqlarea where sql_text like '%INT_EMP%'; SQL_TEXT -------------------------------------------------------------------------------- truncate table "TEST"."INT_EMP" purge snapshot log DELETE FROM "TEST"."INT_EMP" SNAP$ WHERE "EMPNO" = :1 INSERT INTO "TEST"."INT_EMP" ("EMPNO","LAST_NAME","DEPTNO") VALUES (:1,:2,:3) UPDATE "TEST"."INT_EMP" SET "EMPNO" = :1,"LAST_NAME" = :2,"DEPTNO" = :3 WHERE "EMPNO" = :1 INSERT INTO "TEST"."INT_EMP"("EMPNO","LAST_NAME","DEPTNO") SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO" FROM "TEST"."EMP" "EMP" INSERT /*+ APPEND */ INTO "TEST"."INT_EMP" ("EMPNO","LAST_NAME","DEPTNO") SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO" FROM "TEST"."EMP" "EMP" BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST','EMP','INT_EMP', 'empno empno, name last_name, deptno deptno'); END; SQL> select object_name, object_type, status, object_id, data_object_id 2 from user_objects order by 4; OBJECT_NAME OBJECT_TYPE STATUS OBJECT_ID DATA_OBJECT_ID ------------ ------------------ ------- ---------- -------------- DEPT TABLE VALID 5793 5793 DEPT_PK INDEX VALID 5794 5794 EMP TABLE VALID 5803 5803 I_EMP_DEPTNO INDEX VALID 5805 5805 AUDIT_EMP TABLE VALID 5806 5806 T_EMP TRIGGER VALID 5807 EMP_PK INDEX VALID 5808 5808 INT_EMP TABLE VALID 5830INT_EMP TABLE PARTITION VALID 5831 5857
INT_EMP TABLE PARTITION VALID 5832 5858 INT_EMP TABLE PARTITION VALID 5833 5859 INT_EMP TABLE PARTITION VALID 5834 5860MLOG$_EMP TABLE VALID 5855 5855
RUPD$_EMP TABLE VALID 5856 14 rows selected. => 2 tables are created: --> a permanent table MLOG$_EMP which is a snapshot log on EMP to store all DML performed on EMP table once the START_REDEF_TABLE is launched --> a temporary table RUPD$_EMP (of SESSION duration) SQL> select count(*) from int_emp; COUNT(*) ---------- 100000 SQL> select count(*) from mlog$_emp; COUNT(*) ---------- 0 SQL> select count(*) from rupd$_emp; COUNT(*) ---------- 0 SQL> select count(*) from emp; COUNT(*) ---------- 100000 SQL> select count(*) from audit_emp; COUNT(*) ---------- 100000 SQL> select master,log_table from user_mview_logs; MASTER LOG_TABLE ------------------------------ ------------------------------ EMP MLOG$_EMP SQL> select mview_name,container_name, build_mode 2 from user_mviews; MVIEW_NAME CONTAINER_NAME BUILD_MOD --------------------- ------------------------ --------- INT_EMP INT_EMP PREBUILT SQL> select query from user_mviews; QUERY ------------------------------------------------------------------- select empno empno, name last_name, deptno deptno from "TEST"."EMP" ---------------------------------------4. Create constraints on the interim table --------------------------------------- SQL> alter table int_emp 2 add constraint int_emp_pk primary key(empno); Table altered. SQL> alter table int_emp add constraint 2 int_emp_fk foreign key(deptno) references dept(deptno); Table altered. SQL> alter table int_emp MODIFY CONSTRAINT int_emp_fk 2 DISABLE KEEP INDEX; SQL> select constraint_name,constraint_type, status 2 from user_constraints; CONSTRAINT_NAME C STATUS ------------------------------ - -------- DEPT_PK P ENABLED EMP_PK P ENABLED EMP_FK R ENABLED INT_EMP_PK P ENABLED INT_EMP_FK R DISABLED --------------------------------------- Create triggers on the interim table --------------------------------------- We deliberately create a trigger which increments the C column by 2 instead of 1 to show that after the re-organization completion the appropriate triggge is used. SQL> create or replace trigger t_int_emp 2 before insert or update or delete on int_emp 3 for each row 4 declare 5 PRAGMA AUTONOMOUS_TRANSACTION; 6 begin 7 update audit_emp set c=c+2; 8 commit; 9 end; 10 / Trigger created. SQL> select trigger_name, status from user_triggers; TRIGGER_NAME STATUS ------------------------------ -------- T_EMP ENABLED T_INT_EMP ENABLED -----------------------------------------5. Simulate DML activity on the source table ----------------------------------------- SQL> delete emp where empno=1; 1 row deleted. SQL> commit; Commit complete. SQL> select count(*) from emp; COUNT(*) ---------- 99999 SQL> select count(*) from int_emp; COUNT(*) ---------- 100000 SQL> select count(*) from mlog$_emp; COUNT(*) ---------- 1 SQL> select * from audit_emp; C ---------- 100001 ------------------------------------------------------6. You can resync the Interim table with the Source table ------------------------------------------------------ SQL> execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', - > 'EMP', 'INT_EMP'); PL/SQL procedure successfully completed. SQL> select count(*) from emp; COUNT(*) ---------- 99999 SQL> select count(*) from int_emp; COUNT(*) ---------- 99999 SQL> select count(*) from mlog$_emp; COUNT(*) ---------- 0 SQL> select * from audit_emp; C ---------- 100001 -------------------------------------7. Grant privileges on the interim table ------------------------------------- SQL> select * from USER_TAB_PRIVS_MADE; GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE ---------- ----------- --------- ---------- --- --- SCOTT EMP TEST SELECT NO NO SCOTT EMP TEST UPDATE NO NO SQL> grant select, update on INT_EMP to SCOTT; Grant succeeded. ----------------------------------8. Finish the re-organization process ---------------------------------- SQL> execute DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST',- > 'EMP', 'INT_EMP'); PL/SQL procedure successfully completed. SQL> delete from emp where empno=2; 1 row deleted. SQL> commit; Commit complete. SQL> select * from audit_emp; C ---------- 100003 The new trigger is used: 100001 is incremented by 2. SQL> desc emp Name Null? Type ------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER LAST_NAME VARCHAR2(20) DEPTNO NUMBER SAL NUMBER ----------------------------------9. Clean up ---------------------------------- SQL> drop table int_emp; Table dropped. -- If you receive an ORA-02449 error while trying to drop the interim table, then issue: SQL> drop table int_emp cascade constraints; Table dropped. ----------------------------------10. Rename the constraints ----------------------------------In RDBMS version 9.2, you can rename the constraints: SQL> alter table emp rename constraint INT_EMP_PK to EMP_PK ; Table altered. SQL> alter table emp rename constraint INT_EMP_FK to EMP_FK; Table altered. SQL> select constraint_name, index_name, status from user_constraints; CONSTRAINT_NAME INDEX_NAME STATUS ------------------------ --------------------------- -------- EMP_PK INT_EMP_PK ENABLED EMP_FK ENABLED Be aware that the index associated to the primary key keeps the former name. Before RDBMS version 9.2, if you want to rename the constraints, drop and recreate them: SQL> alter table emp drop constraint int_emp_pk; Table altered. SQL> alter table emp add constraint emp_pk primary key (empno); Table altered. SQL> alter table emp drop constraint int_emp_fk; Table altered. SQL> alter table emp add constraint emp_fk 2 foreign key (deptno) references dept (deptno); Table altered.
Note----Once the START_REDEF_TABLE procedure is started, you can interrumpt the process using the following procedure:SQL> execute dbms_redefinition.abort_redef_table('TEST','EMP','INT_EMP');PL/SQL procedure successfully completed. Related Documents-----------------Oracle9i Database Administrator's Guide Chapter Managing TablesNote 177408.1 ORA-12091: cannot online redefine table with materialized views
Note 139335.1 Drop View with Referencing Foreign Keys Requires CASCADE CONSTRAINTS Clause
1 comentario:
Muchas Gracias por este aporte!
Publicar un comentario