1.
Objectives of Moving Data
🔹
Introduction
In the context of PL/SQL and Oracle databases,
data movement refers to the processes and techniques involved in transferring
data from one location to another. This movement may be required within the
same database, across different databases, or even between different platforms.
Data movement is a critical task in database administration and data
integration projects, enabling systems to handle growing data volumes
efficiently and ensuring that data is available, consistent, and optimized for
performance.
🔹 Key
Objectives
- Data Integration:
Organizations often need to integrate data from multiple sources such as
customer relationship management (CRM), enterprise resource planning
(ERP), and other applications. By moving data into a centralized system,
decision-makers gain a holistic view of operations.
- Data Migration:
During software upgrades, server replacements, or transitions from one
database vendor to another, data must be moved seamlessly and accurately
without data loss or corruption.
- Data Archiving: Older
and less frequently accessed data can be moved to a different storage
medium to optimize primary database performance while still retaining
access to historical records.
- Backup and Recovery: Regularly
moving data as part of a backup strategy ensures that in the event of a
failure, the system can be restored to a previous state without loss.
- Data Distribution: In
distributed systems or large organizations, moving data between regional
databases and central systems ensures that information is synchronized and
up to date.
- Performance Tuning: By
relocating data to more appropriate storage or closer to the processing
unit, query performance can be significantly improved.
🔹 Example
Scenario
A retail company with branches in multiple
cities collects sales data locally. This data must be moved to the headquarters
database every night to perform consolidated reporting, trend analysis, and
forecasting.
2. Moving
Data: General Architecture
🔹 Overview
The architecture for moving data in Oracle
PL/SQL-based systems includes several layers and tools that ensure efficient
and reliable data transfer. The general architecture focuses on the source and
target systems, tools or utilities used for data movement, data formats, and
optional transformation processes that may occur during transit.
🔹 Components
- Source System: The
database or file system that holds the original data. It may be an Oracle
database, a flat file, or another format.
- Data Movement Utility: Tools
such as Oracle Data Pump, SQL*Loader, and external tables serve as the
middle layer for extracting and loading data.
- Intermediate Files: These
may include dump files, CSVs, or other formats used during the process.
- Target System: The
destination where the data is to be moved. It could be another database, a
cloud repository, or a data warehouse.
- Directory Objects:
Pointers to directories on the file system that Oracle uses to read or
write files securely.
🔹 Process
Flow
- Extract: Data is read from the source using a SELECT query or a
direct path.
- Transform: Optional transformations such as type casting,
filtering, or masking are applied.
- Load: The transformed or raw data is inserted into the target
system.
🔹 Diagram
[Source] →
[Extraction Tool] → [Intermediate File] → [Loading Tool] → [Target System]
3.
Directory Object: Overview
🔹 Definition
A Directory Object is a database object that
points to a specific directory on the server’s file system. Oracle uses it to
grant secure access to directories for reading from or writing to files during
data movement.
🔹 Importance
- Protects against unauthorized file access
- Centralizes control over file-based data operations
- Required by utilities like Data Pump, UTL_FILE, and external tables
4. Creating
Directory Objects
🔹 Syntax
CREATE OR
REPLACE DIRECTORY directory_name AS 'absolute_path';
🔹 Example
CREATE OR
REPLACE DIRECTORY data_dir AS '/u01/oracle/datafiles';
GRANT READ,
WRITE ON DIRECTORY data_dir TO scott;
🔹 Notes
- Only DBAs can create directory objects
- The specified path must exist on the database server
- Ensure proper OS-level permissions for Oracle access
5. Data
Pump: Overview
🔹 What is
Data Pump?
Oracle Data Pump is a powerful utility for
fast data and metadata export/import operations between Oracle databases. It
replaces the older EXP/IMP tools and provides greater performance, flexibility,
and manageability.
🔹 Features
- Parallel processing for faster operation
- Ability to resume jobs after failure
- Fine-grained object selection
- Support for network-based transfers
- Metadata filtering and transformation
6. Data
Pump: Benefits
🔹 High
Performance
Data Pump leverages direct path and parallel
execution to transfer large datasets quickly.
🔹 Flexible
Control
Users can filter specific tables, schemas, or
rows to export/import only relevant data.
🔹
Restartable Jobs
Interrupted jobs can resume from the point of
failure, saving time and effort.
🔹 Versatile
Interfaces
Data Pump jobs can be executed via
command-line tools, Oracle Enterprise Manager, or PL/SQL APIs.
🔹 Better
Monitoring
Detailed logs and job status reports help DBAs
track the process.
7. Data
Pump Export and Import: Overview
🔹 Export
(expdp)
Used to extract data from the source database
and write it to dump files.
🔹 Import
(impdp)
Used to load data from dump files into the
target database.
🔹 Basic
Syntax
expdp hr/hr
schemas=HR directory=data_dir dumpfile=hr.dmp logfile=exp.log
impdp hr/hr
schemas=HR directory=data_dir dumpfile=hr.dmp logfile=imp.log
8. Data
Pump Utility: Interfaces and Modes
🔹 Interfaces
- Command Line: Using
expdp and impdp
- Enterprise Manager (OEM): GUI
for job creation and monitoring
- PL/SQL API (DBMS_DATAPUMP): For
programmatic control
🔹 Modes
- Full Database Mode:
Transfers all objects
- Schema Mode:
Transfers all objects belonging to specified schemas
- Table Mode:
Transfers individual tables or partitions
- Tablespace Mode:
Transfers data from specific tablespaces
9.
Fine-Grained Object Selection
🔹 Concept
Oracle Data Pump allows selective
export/import of database objects using the INCLUDE and EXCLUDE
parameters.
🔹 Example
Export only EMPLOYEES and DEPARTMENTS tables:
expdp hr/hr
tables=employees,departments directory=data_dir dumpfile=hr_objs.dmp
🔹 Benefits
- Reduces file size
- Saves time by avoiding unnecessary objects
- Enables targeted data movement
10.
Advanced Feature: Sampling
🔹 Purpose
To export a representative subset of a table’s
data, typically used for testing, training, or analysis.
🔹 Syntax
expdp hr/hr
tables=employees sample=20 directory=data_dir dumpfile=emp_sample.dmp
🔹 Use Cases
- Testing ETL processes with smaller data sets
- Quick analysis without full table transfer
- Debugging data migration scripts
11. Export
Options: Files
🔹 Common
Parameters
- DUMPFILE: Names of output files
- LOGFILE: Specifies the log file for the job
- FILESIZE: Defines maximum size for each dump file
🔹 Example
expdp hr/hr
tables=employees directory=data_dir \
dumpfile=emp1.dmp,emp2.dmp
logfile=exp_emp.log filesize=50M
🔹 Notes
- Files are split when FILESIZE is reached
- Multiple dump files can be specified for large jobs
12. Data
Pump File Locations
🔹
Directory-Based Storage
All dump files and logs must be stored in a
server-side directory specified by a valid directory object.
🔹 Security
- Only users with READ and WRITE
privileges on the directory can use it
- Avoid using system directories for user data files
🔹 Best
Practices
- Always verify space availability
- Use dedicated directories for each operation
- Use meaningful names for files to track operations easily
Comments
Post a Comment