Scribd
Upload
16 views

Using Oracle Data Pump

Uploaded by

Brigitte DOUALLA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
16 views

Using Oracle Data Pump

Uploaded by

Brigitte DOUALLA
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

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).

You might also like