Dont Shut Down That Database Use Oracle 9

Don’t Shut Down That Database! Use Oracle 9 i Online Object Redefinition Instead Chris Lawson Performance Solutions Roger Schrag Database Specialists, Inc. IOUG Live! April, 2002 Session #524 9/17/2021 1

Session Topics § § § § What is “online object redefinition”? Why is this feature important? The dbms_redefinition package The online redefinition process Examples Restrictions Lessons learned 9/17/2021 2

Online Object Redefinition The ability to change the definition of a database object without restricting the ability of users to query and update data § Oracle 8 i provided some online capabilities for IOTs. § Oracle 9 i enables a wider range of online maintenance for almost all types of tables. 9/17/2021 3

Things You Can Do With Online Redefinition § Move a table or index to a new tablespace § Change a table’s organization (partitioning, index-organized, etc. ) § Add, remove, or rename columns in a table § Change the data type of a column in a table § Add new indexes to a table § Change constraint definitions on a table 9/17/2021 4

Why Do We Need This Feature? § Databases are getting larger and more complex. § DBAs are being given less down time to perform maintenance. § Companies often insist on no downtime. § This feature makes it possible to perform many important maintenance tasks online. 9/17/2021 5

The dbms_redefinition Package Use the five procedures in this package to redefine an object online. § § § 9/17/2021 CAN_REDEF_TABLE START_REDEF_TABLE FINISH_REDEF_TABLE ABORT_REDEF_TABLE SYNC_INTERIM_TABLE 6

Permissions Required To Redefine A Table Online § § § EXECUTE on the dbms_redefinition package CREATE ANY TABLE ALTER ANY TABLE DROP ANY TABLE LOCK ANY TABLE SELECT ANY TABLE Typically, you’ll want to use a DBA account to redefine objects online. 9/17/2021 7

Overview Of The Online Redefinition Process Step 1: Verify the table is eligible for online redefinition. Step 2: Create an interim table. Step 3: Associate the interim table with the table to be redefined. Step 4: Add constraints, indexes, triggers, and grants to the interim table. Step 5: Complete the online redefinition. 9/17/2021 8

Step 1: Verify The Table Is Eligible For Online Redefinition § Confirms table to be redefined meets requirements for online object redefinition § Call the CAN_REDEF_TABLE procedure in the dbms_redefinition package: EXECUTE dbms_redefinition. can_redef_table ('FINANCE', 'RATE'); § Table qualifies if no exception is raised § Exception will be raised if table does not qualify: ORA‑ 12089: Cannot online redefine table with no primary key 9/17/2021 9

Step 2: Create An Interim Table § Online redefinition uses an interim table to change the object definition without restricting user access § Create interim table using exact column names, data types, organization, tablespace assignment, and storage clause that the redefined table should have 9/17/2021 10

Step 2: Create An Interim Table § Declare primary key on interim table, but do not create any other indexes, constraints, or triggers § Oracle will copy rows from the original table into the interim table during the redefinition process § Oracle will swap the names of the two tables at the end so the interim table will become the production table and vice versa 9/17/2021 11

Step 3: Associate The Interim Table With The Table To Be Redefined § Call the START_REDEF_TABLE procedure: EXECUTE dbms_redefinition. start_redef_table ('FINANCE', 'RATE', 'STAGING'); § Oracle creates a materialized view and log in order to populate the interim table from the production table and track subsequent updates to the production table. All rows in the production table are copied to the interim table 9/17/2021 12

Step 3: Associate The Interim Table With The Table To Be Redefined § A column mapping must be specified when calling START_REDEF_TABLE if any columns are being added or removed, if any data types are being changed, or if any data is being modified § Could take a long time if production table has many rows § Users have full query and update capability 9/17/2021 13

Step 4: Add Constraints, Indexes, Triggers, And Grants To Interim Table § Create constraints, indexes, database triggers, and grants on interim table desired on the production table at the end of the online redefinition process § Features on the production table, but not the interim table, will disappear from the redefined table in the next step § Create foreign keys with the DISABLE keyword 9/17/2021 14

Step 5: Complete The Online Redefinition § Interim table has organization, column definitions, indexes, constraints, triggers, and grants desired on redefined table. Interim table also has all of the rows of the production table except for updates occurring since Step 3 began § Call the FINISH_REDEF_TABLE procedure: EXECUTE dbms_redefinition. finish_redef_table ('FINANCE', 'RATE', 'STAGING'); 9/17/2021 15

Step 5: Complete The Online Redefinition § Oracle uses the materialized view log to propagate pending updates to interim table § Oracle locks production and interim tables briefly and swaps their names in data dictionary § Oracle drops the materialized view and log and enables disabled foreign keys § Redefinition is complete. Interim table (which used to be the production table) may be dropped 9/17/2021 16

Step 5: Complete The Online Redefinition § Step usually runs quickly (unless users have updated a lot of rows in the production table between the time Step 3 began and the time this step began) § Tables are locked only briefly. At all other times during the entire process, users have complete query and update ability on the table undergoing online redefinition 9/17/2021 17

Aborting An Online Redefinition § You may cancel an online redefinition process at any time before calling the FINISH_REDEF_TABLE procedure. § Abort the online redefinition by calling the ABORT_TABLE_REDEF procedure. Oracle drops the materialized view and log definitions, if they had been created. § You may drop the interim table if you wish. 9/17/2021 18

Examples Of Online Object Redefinition 1. Move a table to a new tablespace and add two new indexes. 2. Massage values in one column of a table and change the data type of another column. 9/17/2021 19

Example #1: Move A Table To A New Tablespace And Add Two New Indexes Move the accts_payable table in the finance schema to a new tablespace. At the same time, add indexes on the vendor_name and vendor_po columns. § ALTER TABLE. . . MOVE locks the table. § Export / Import makes the table read-only during export and inaccessible during import. § CREATE INDEX locks the table. § Online redefinition allows full query and update access. 9/17/2021 20

Step 1: Verify The Table Is Eligible For Online Redefinition SQL> BEGIN 2 dbms_redefinition. can_redef_table 3 ('FINANCE', 'ACCTS_PAYABLE'); 4 END; 5 / PL/SQL procedure successfully completed SQL> 9/17/2021 21

Step 2: Create An Interim Table SQL> 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE accts_payable_stage ( account_id VARCHAR 2(20), vendor_name VARCHAR 2(50) NOT NULL, vendor_address VARCHAR 2(50), vendor_po VARCHAR 2(20), invoice_date DATE NOT NULL, invoice_amount NUMBER NOT NULL, CONSTRAINT accts_payable_pk PRIMARY KEY (account_id) USING INDEX TABLESPACE accts_pay_ind ) TABLESPACE accts_pay_tab; Table created. SQL> 9/17/2021 22

Step 2: Create An Interim Table § Same column names and data types as production table § Primary key declared, but no other indexes or constraints § Desired tablespaces, storage clauses, and organization specified 9/17/2021 23

Step 3: Associate The Interim Table With The Table To Be Redefined SQL> BEGIN 2 dbms_redefinition. start_redef_table 3 ('FINANCE', 'ACCTS_PAYABLE', 4 'ACCTS_PAYABLE_STAGE'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> 9/17/2021 24

Step 3: Associate The Interim Table With The Table To Be Redefined § No column mapping provided because no column information changed in this example § Step could take a long time if accts_payable table has many rows § Users will have full query and update ability 9/17/2021 25

Step 4: Add Constraints, Indexes, Triggers, And Grants To Interim Table SQL> CREATE INDEX accts_payable_n 1 ON accts_payable_stage (vendor_name) 2 TABLESPACE accts_pay_ind; Index created. SQL> CREATE INDEX accts_payable_n 2 ON accts_payable_stage (vendor_po) 2 TABLESPACE accts_pay_ind; Index created. SQL> CREATE INDEX accts_payable_n 3 ON accts_payable_stage (invoice_date) 2 TABLESPACE accts_pay_ind; Index created. SQL> 9/17/2021 26

Step 4: Add Constraints, Indexes, Triggers, And Grants To Interim Table § Add to interim table whatever features are desired on redefined table § Could be quite different from what exists on original table (new indexes, different constraints, etc. ) 9/17/2021 27

Step 5: Complete The Online Redefinition SQL> BEGIN 2 dbms_redefinition. finish_redef_table 3 ('FINANCE', 'ACCTS_PAYABLE', 4 'ACCTS_PAYABLE_STAGE'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> 9/17/2021 28

Step 5: Complete The Online Redefinition § Could take a long time if many updates occurred after Step 3 began § Production table locked briefly near end of step § Production and interim table names are swapped § Drop interim table (old production table) manually after online redefinition is complete 9/17/2021 29

Example #2: Massage Values In One Column Of A Table And Change The Data Type Of Another Column Strip trailing blanks from all values in the description column of the invoices table. Also, change the data type of the vendor_id column. SQL> DESCRIBE invoices Name --------INVOICE_ID LINE_ITEM DESCRIPTION VENDOR_ID INVOICE_AMT 9/17/2021 Null? -------NOT NULL Type ---VARCHAR 2(20) NUMBER VARCHAR 2(200) NOT NULL NUMBER 30

Step 1: Verify The Table Is Eligible For Online Redefinition SQL> BEGIN 2 dbms_redefinition. can_redef_table 3 ('FINANCE', 'INVOICES'); 4 END; 5 / PL/SQL procedure successfully completed SQL> 9/17/2021 31

Step 2: Create An Interim Table SQL> 2 3 4 5 6 7 8 9 10 11 CREATE TABLE invoices_stage ( invoice_id VARCHAR 2(20), line_item NUMBER NOT NULL, description VARCHAR 2(200), vendor_id VARCHAR 2(20) NOT NULL, invoice_amt NUMBER NOT NULL, CONSTRAINT invoices_pk PRIMARY KEY (invoice_id) USING INDEX TABLESPACE ind ) TABLESPACE tab; Table created. SQL> 9/17/2021 32

Step 2: Create An Interim Table § Data type of vendor_id column has changed from NUMBER to VARCHAR 2(20) § Tablespaces specified even though we are not moving table or index to new tablespace 9/17/2021 33

Step 3: Associate The Interim Table With The Table To Be Redefined SQL> BEGIN 2 dbms_redefinition. start_redef_table 3 ('FINANCE', 'INVOICES_STAGE', 4 'INVOICE_ID, LINE_ITEM, ' || 5 'RTRIM (DESCRIPTION) DESCRIPTION, ' || 6 'TO_CHAR (VENDOR_ID) VENDOR_ID, ' || 7 'INVOICE_AMT'); 8 END; 9 / PL/SQL procedure successfully completed. SQL> 9/17/2021 34

Step 3: Associate The Interim Table With The Table To Be Redefined § Column mapping provided due to data massaging and type conversion § RTRIM function to strip trailing blanks from description column § TO_CHAR function to convert numeric vendor_id to VARCHAR 2 § Invoice descriptions and vendor IDs are transformed as rows are copied to interim table 9/17/2021 35

Step 4: Add Constraints, Indexes, Triggers, And Grants To The Interim Table § For simplicity, the invoices table in this example does not have any constraints, indexes, triggers, or grants other than the primary key declared when the interim table was created. 9/17/2021 36

Step 5: Complete The Online Redefinition SQL> BEGIN 2 dbms_redefinition. finish_redef_table 3 ('FINANCE', 'INVOICES_STAGE'); 4 END; 5 / PL/SQL procedure successfully completed. SQL> 9/17/2021 37

Step 5: Complete The Online Redefinition § At the end of the online redefinition, the description column is free of trailing blanks and the data type of the vendor_id column has been changed: SQL> DESCRIBE invoices Name --------INVOICE_ID LINE_ITEM DESCRIPTION VENDOR_ID INVOICE_AMT Null? -------NOT NULL Type ---VARCHAR 2(20) NUMBER VARCHAR 2(200) NOT NULL VARCHAR 2(20) NOT NULL NUMBER SQL> 9/17/2021 38

Limitations Of The Online Object Redefinition Feature § Table eligibility requirements § Limitations of column mapping § Row selectivity 9/17/2021 39

Not Eligible For Online Redefinition § Tables without a declared primary key § Tables that are the basis of a materialized view § Tables belonging to a cluster § Temporary tables § Tables in the SYS or SYSTEM schema § Tables with FILE, LONG, or user-defined data types 9/17/2021 40

Column Mapping Limitations § Simple functions and expressions only – Allowed: Changing the case of a string of text or multiplying a numeric value by a constant – Not allowed: Subqueries or non-deterministic functions § Mandatory columns must get values derived from existing columns 9/17/2021 41

Row Selectivity Limitation § Cannot eliminate rows from a table during online redefinition § Redefined table will have all of the rows in the original table 9/17/2021 42

Lessons Learned § § § Testing with the interim table Constraint validation Handling foreign keys Column mapping Speeding up the FINISH_REDEF_TABLE call 9/17/2021 43

Testing With The Interim Table You can validate your object redefinition against the interim table before calling the FINISH_REDEF_TABLE procedure. § § Verify column mapping Validate data transformation Test new indexes Abort the redefinition if problems are discovered—without impacting production 9/17/2021 44

Constraint Validation § Foreign keys declared on the redefined table are enabled but not validated at end of redefinition § Existing rows not tested for compliance § Validating existing rows must be done manually if validation desired The Oracle 9 i documentation fails to mention this point! 9/17/2021 45

Handling Foreign Keys Redefining a table that is a parent to foreign keys declared on other tables presents a special problem. § Recall that at the end of an online object redefinition, the names of the original production table and the interim table are swapped. § This means that after an online redefinition, foreign keys on other tables that used to reference the production table will now reference the interim table. 9/17/2021 46

Redefining A Table That Is A Parent To Foreign Keys Declared On Other Tables § Declare new foreign keys in disabled state that reference the interim table. § When redefinition is complete, drop foreign keys that reference the interim table and enable foreign keys that reference the redefined table. § Plan for locks and resources required to enable the new constraints. 9/17/2021 47

Column Mapping § Specify column map in call to START_REDEF_TABLE if any column names, data types, or values are changed § Single string consisting of comma-delimited expression / name pairs – An expression is a formula to derive column values from the existing production table. – A name is the name of a column in the interim table to be loaded with the value. 9/17/2021 48

SQL Syntax Refresher § Strings are delimited by single quotes. § Include a single quote in a string by specifying two single quotes in a row. § Concatenate two strings with the || operator. 9/17/2021 49

Column Mapping Example Append an “A” to each invoice number while redefining the invoices table. SQL> BEGIN 2 dbms_redefinition. start_redef_table 3 ('FINANCE', 'INVOICES_INTERIM', 4 'INVOICE_ID, VENDOR_ID, ' || 5 'INVOICE_DATE, AMOUNT, ' || 6 'INVOICE_NUMBER || ''A'' INVOICE_NUMBER'); 7 END; 8 / PL/SQL procedure successfully completed. SQL> 9/17/2021 50

Speeding Up The FINISH_REDEF_TABLE Call § Materialized view log catches all updates to production table after START_REDEF_TABLE call § All pending changes are propagated to interim table during FINISH_REDEF_TABLE call § Can call SYNC_INTERIM_TABLE before FINISH_REDEF_TABLE to propagate all pending changes § Does not speed up the overall redefinition process 9/17/2021 51

Wrapping Up § Oracle Corporation recognizes the need for high availability. § By and large, the online redefinition feature of Oracle 9 i is pretty easy to use. § Tricky issues, such as foreign key handling, stem from the complexities of changing a table while users are querying and updating it. § As with any new Oracle feature, test thoroughly before using! 9/17/2021 52

Contact Information Chris Lawson Performance Solutions Tel: 925/829 -7496 Email: chris@oraclemagician. com Web: www. oraclemagician. com Roger Schrag Database Specialists, Inc. Tel: 415/344 -0500 Email: rschrag@dbspecialists. com Web: www. dbspecialists. com 9/17/2021 53