partition in a partitioned table truncated by mistake

Import partition data
A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump.

However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using partition exchange.

Steps -

1. Create a temporary user.
2. Import partition data only into table in the temporary user
3. Ratify data.
4. Move segments in temporary table into new non-partitioned table.
5. Move this non-partitioned table into the original users tablespace.
6. Exchange partition between temporary and original partition and clean up.

NOTE -

ORIGINAL OWNER is PART_OWNER
TABLESPACE_NAME IS TEST_TBS
TABLE_NAME is TEST
PARTITION IS TEST_PART_DEC

1. Create temporary user with separate tablespace -

Temporary user called PART_RESTORE.

2. Import partition data only into table in the temporary user

imp userid/password file=test_export.dmp log=test_import.log fromuser=PART_OWNER touser=PART_RESTORE tables=TEST:TEST_PART_DEC feedback=10000 buffer = 64000 ignore=y &

3. RATIFY DATA IN PART_RESTORE SCHEMA.

4. Move segments fron temporary table into new non-partitioned table.

CREATE TABLE PART_RESTORE.TEST_TEMP
AS SELECT * FROM PART_RESTORE.TEST
WHERE ROWNUM<1;

5. Move this non-partitioned table into the original users tablespace.

ALTER TABLE PART_RESTORE.TEST_TEMP MOVE TABLESPACE TEST_TBS;

6. Exchange partition between temporary and original partition and clean up.

– set it as a nologging table

ALTER TABLE PART_RESTORE.TEST_TEMP NOLOGGING;

– move the data into the temp table.

INSERT /*+ APPEND */INTO PART_RESTORE.TEST_TEMP
SELECT *
FROM PART_RESTORE.TEST;
COMMIT;

— exchange the partition into the final PART_OWNER TABLE

ALTER TABLE PART_OWNER.TEST
EXCHANGE PARTITION TEST_PART_DEC
WITH TABLE PART_RESTORE.TEST_TEMP
UPDATE GLOBAL INDEXES;

– rebuild any unusable local indexes
ALTER TABLE PART_OWNER.TEST
MODIFY PARTITION TEST_PART_DEC
REBUILD UNUSABLE LOCAL INDEXES;

– gather stats on the new partition…
begin
DBMS_STATS.GATHER_TABLE_STATS (ownname => PART_OWNER, tabname => ‘TEST’, partname => ‘TEST_PART_DEC’, estimate_percent => 5, degree => 1, granularity => ‘ALL’, cascade => FALSE);
end;
/

– drop the ofsa_restored temp table…

DROP TABLE PART_RESTORE.TEST_TEMP ;