0

I am compressing partitioned tables.

before partitioned tables, I tried normal tables with the follwoing steps:

DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM'
);




BEGIN
   DBMS_REDEFINITION.CAN_REDEF_TABLE('USER','ORIGINAL', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



BEGIN
   DBMS_REDEFINITION.START_REDEF_TABLE(
      uname => 'USER',
      orig_table => 'ORIGINAL',
      int_table => 'INTERIM',
      options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/



DECLARE
   error_count pls_integer := 0;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('USER', 'ORIGINAL', 'INTERIM', dbms_redefinition.cons_orig_params, TRUE,TRUE,TRUE,FALSE, error_count);
   DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/


DECLARE
   error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
    uname => 'USER',
    orig_table => 'ORIGINAL',
    int_table => 'INTERIM',
    copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    ignore_errors => FALSE,
    num_errors => 0
);
END;
/

ALTER TABLE ORIGINAL MOVE ROW STORE COMPRESS ADVANCED;

However, for partitioned tables, this is not possible.

can someone tell me the procedure to compress partitioned tables in Oracle 19c?

Improve this question

1 Answer 1

Reset to default
0

Fixed it.

The above steps will indeed be followed (DBMS_REDEFINITION).

However, if your table has partitioned indexes, you will need to drop them.

-- Check indexes
SELECT index_name
FROM all_indexes
WHERE table_name = 'table_name'
AND partitioned = 'YES';


-- Copy DDL of the index

SELECT DBMS_METADATA.GET_DDL('INDEX','index_name', 'schema') FROM DUAL;

-- Drop index(s)

DROP INDEX index_name;

BEGIN
   DBMS_REDEFINITION.sync_interim_table(
      uname => 'SCHEMA',
      orig_table => 'table_name',
      int_table => 'MY_INTERIM_TABLE'
   );
END;
/


BEGIN
   DBMS_REDEFINITION.finish_redef_table(
      uname => 'SCHEMA',
      orig_table => 'table_name',
      int_table => 'MY_INTERIM_TABLE'
   );
END;
/
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.