I am going to develop a reporting database by querying the production database once using a stored procedure.
The stored procedure will then write the result into it's own output tables.
Following is the schema for the output table:
Create table Output (
Customer_ID NUMBER(15) not null,
STD_HASH RAW(1000 BYTE) ,
VALID_PERIOD_START NOT NULL TIMESTAMP(6),
VALID_PERIOD_END NOT NULL TIMESTAMP(6),
Address VARCHAR2(30 CHAR),
period for valid_period(valid_period_start,valid_period_end),
Constraint Output_PK Primary Key ( Customer_ID, valid_period_start, valid_period_end )
)
As the stored procedure will perform a lot of update and delete statement on the output table, and those output table is very big. The largest one I have right now is 8GB. I am thinking to alter those output tables with the "SHRINK SPACE" option at the end of the stored procedure to reclaim some spaces.
Following is the statement that I am going to apply:
Alter table OUTPUT1 ENABLE ROW MOVEMENT; -- Temporary enable row movement for the table
Alter TABLE output2 SHRINK SPACE;
Alter table OUTPUT1 DISABLE ROW MOVEMENT;-- Disable row movement.
Those output tables are temporal table that is using the valid period and the ID from production as a primary key.
However, as I am very new with the Shrink Space function. Can anyone tell me what is the disadvantage of using this function?
The table, basically will not be updated from anywhere other than this stored procedure. The stored procedure will be scheduled to run in a daily base.
Thanks in advance!
yes, shrink has some restrictions: (1) it locks the table during the shrink operation (2) it change the rowid of the table. (3) it needs the tablespace with automatic segment-space management enabled.
we have other alternatives to claim the unused space: (1) export/import (2) dbms_redefinition