1

I don't really understand how to delete columns using the dbms_redefinition utility. All the time there are some strange errors and data overwriting. Disabled foreign keys appear in other tables and are not deleted. Indexes are duplicated... If anyone understands how to use this utility, then please help. I would also like to hear about the pitfalls, if there are any and an explanation of each step

I use the following sequence:

  1. First, I mark the columns in table A as unused (ALTER TABLE A SET UNUSED (...))

  2. Generating DDL to create a new table without unused columns (SELECT DBMS_METADATA.GET_DDL('TABLE', 'A') FROM dual) and create table A_temporary

  3. And finally I use dbms_redefinition:

    DECLARE p_owner varchar2(30) := 'user'; orig_table varchar2(30) := 'A'; int_table varchar2(30) := 'A_temporary'; BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(p_owner, orig_table, int_table, NULL, dbms_redefinition.cons_use_pk); END; / DECLARE p_owner varchar2(30) := 'user'; orig_table varchar2(30) := 'A'; int_table varchar2(30) := 'A_temporary'; num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(p_owner, orig_table, int_table, dbms_redefinition.cons_orig_params, TRUE, TRUE, TRUE, TRUE, num_errors); END; / SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS; DECLARE p_owner varchar2(30) := 'user'; orig_table varchar2(30) := 'A'; int_table varchar2(30) := 'A_temporary'; BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE(p_owner, orig_table, int_table); END; / DECLARE p_owner varchar2(30) := 'user'; orig_table varchar2(30) := 'A'; int_table varchar2(30) := 'A_temporary'; BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE(p_owner, orig_table, int_table); END; / DROP TABLE A_temporary CASCADE CONSTRAINTS PURGE /

I took this instruction from here. Is everything okay in it in the three steps that I take and I shouldn't have any problems, or am I using it incorrectly and you have any comments?

The purpose of the idea is to achieve fast physical deletion of columns

P.S. The table I'm doing this for has a lot of relationships with other tables and takes up a lot of disk space (about 200 GB)

Improve this question
7
  • stackoverflow.com/questions/78332874/… Commented Jun 1, 2024 at 11:49
  • Um, there is indeed a mention that you can use dbms_redefinition, but not a word about how to use it. What is this link for? Commented Jun 1, 2024 at 14:18
  • To explain how you don't need to do this at all. There's really no pressing benefit to getting the column out completely. Just reorg the table with alter table move to remove an unused col from the data blocks and free up the space. It's then gone both logically and physically. Commented Jun 1, 2024 at 14:21
  • Wouldn't that lock the table? Commented Jun 1, 2024 at 16:50
  • 1
    you can do an online table move alter table .. move parallel (degree 16) update indexes online that does not block DMLs, except that it requires a brief exclusive lock at the very end to swap the segments. Commented Jun 1, 2024 at 17:15

1 Answer 1

Reset to default
1

According to the tech note https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=534544425586275&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=1120704.1&_afrWindowMode=0&_adf.ctrl-state=13cxb22k6m_4

you are doing almost the same thing, but some differences:

they don't play with the UNUSED column, so once you have your new interim table, I would re-enable the column before the start_redef_table, in case it has some unexpected side effects,

they always do a DBMS_REDEFINITION.CAN_REDEF_TABLE to be sure

they use explicit column mapping (col_mapping argument) when calling DBMS_REDEFINITION.START_REDEF_TABLE and

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS is the latest thing they do before dbms_redefinition.finish_redef_table and thus AFTER START_REDEF_TABLE.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.