Using Oracle Data Pump (expdp/impdp) for reorganization involves exporting the database objects,
dropping the original objects, and then importing them back. This process can help reduce
fragmentation and reclaim space. Below are the steps and scripts to perform a database
reorganization using expdp and impdp.
Steps:
1. Prepare for Export:
o Ensure you have enough disk space for the dump files.
o Create a directory for Data Pump if it doesn't exist.
2. Export the Data:
o Use expdp to export the schema or specific tables.
3. Drop the Original Objects:
o Drop the original tables and indexes.
4. Import the Data:
o Use impdp to import the data back into the schema.
5. Gather Statistics:
o Collect statistics to help the optimizer.
6. Post-Reorganization Checks:
o Verify the space usage and performance.
Example Script:
Step 1: Create Directory for Data Pump
sql
Copier le code
CREATE OR REPLACE DIRECTORY data_pump_dir AS '/path/to/your/directory';
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO your_user;
Step 2: Export the Data
bash
Copier le code
expdp your_user/your_password@your_db schemas=your_schema directory=data_pump_dir
dumpfile=your_schema.dmp logfile=expdp_your_schema.log
Step 3: Drop Original Objects (Optional)
You may choose to drop the tables and indexes if you want to start fresh. Be cautious with this step as
it will permanently remove the data.
sql
Copier le code
BEGIN
FOR rec IN (SELECT table_name FROM all_tables WHERE owner = 'YOUR_SCHEMA') LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS';
END LOOP;
END;
BEGIN
FOR rec IN (SELECT index_name FROM all_indexes WHERE owner = 'YOUR_SCHEMA') LOOP
EXECUTE IMMEDIATE 'DROP INDEX ' || rec.index_name;
END LOOP;
END;
Step 4: Import the Data
bash
Copier le code
impdp your_user/your_password@your_db schemas=your_schema directory=data_pump_dir
dumpfile=your_schema.dmp logfile=impdp_your_schema.log
Step 5: Gather Statistics
sql
Copier le code
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'YOUR_SCHEMA',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE
);
END;
/
Step 6: Post-Reorganization Checks
Check the free space in tablespaces:
sql
Copier le code
SELECT tablespace_name,
total_blocks * block_size / 1024 / 1024 AS total_size_mb,
free_blocks * block_size / 1024 / 1024 AS free_size_mb
FROM dba_free_space
JOIN dba_tablespaces USING (tablespace_name)
JOIN dba_data_files USING (file_id)
WHERE tablespace_name = 'YOUR_TABLESPACE';
Key Considerations:
Backup: Always take a backup before performing any reorganization tasks.
Constraints and Indexes: Ensure all constraints and indexes are recreated after import.
Downtime: Plan for downtime as exporting and importing large datasets can be time-
consuming.
Testing: Perform the process on a test database to estimate the time required and to ensure
no data is lost.
By following these steps, you can effectively reorganize your Oracle 11g database to reduce free
space and improve performance using Data Pump (expdp/impdp).
