The external tables are often useful within the ETL process of knowledge warehouses because the info doesn’t got to be staged and may be queried in parallel.

Notice that you simply shouldn’t use external tables for frequently accessed tables.

Creating an Oracle external table steps

You follow these steps to make an external table:

First, create a directory which contains the file to be accessed by Oracle using the CREATE DIRECTORY statement.
Second, grant READ and WRITE access to users who access the external table using the GRANT statement.
Third, create the external table by using the CREATE TABLE ORGANIZATION EXTERNAL statement.

Creating an Oracle external table example

Here is CSV file that has two columns: language id and name.

Download the languages.csv file

We will create an external table that maps to the languages.csv file.

1) Create a directory object

First, place the language.csv enter the C:\loader directory.
Second, log in to the Oracle database using the sysdba user via the SQL*Plus program:
Enter user-name: sys@pdborcl as sysdba
Enter password:
Third, create a replacement directory object called lang_external that maps to the C:\loader directory:
SQL> create directory lang_external as ‘C:\loader’;
Directory created.

2) Grant READ and WRITE access on the directory object to users:

The following statement grant READ and WRITE privileges to the OT user:

SQL> grant read,write on directory lang_external to ot;

Grant succeeded.

3) Creating the external table

Use the CREATE TABLE ORGANIZATION EXTERNAL statement to make the external table called languages:

CREATE TABLE languages(

 language_id INT,

 language_name VARCHAR2(30)

)

ORGANIZATION EXTERNAL(

 TYPE oracle_loader

 DEFAULT DIRECTORY lang_external

 ACCESS PARAMETERS

 (FIELDS TERMINATED BY ',')

 LOCATION ('languages.csv')

);

When you create the external table using the CREATE TABLE ORGANIZATION EXTERNAL statement, you would like to specify the subsequent attributes:

TYPE

The TYPE determines the sort of the external table. Oracle provides two types: ORACLE_LOADER and ORACLE_DATADUMP:

The ORACLE_LOADER access driver is that the default that loads data from text data files. Technically speaking, the ORACLE_LOADER loads data from an external table to an indoor table. However, it cannot unload the info i.e., it cannot move data from the interior table to the external table.
The ORACLE_DATAPUMP access driver can perform both loads and unloads. It requires the info within the binary dump file format.

DEFAULT DIRECTORY

The DEFAULT DIRECTORY clause allows you to specify the default directory for storing all input and output files. It accepts a directory object, not a directory path.

ACCESS PARAMETERS

The ACCESS PARAMETERS clause allows you to explain the external data source. Note that every access driver has its own access parameters.

LOCATION

The LOCATION clause specifies the info files for the external table.

You specify the info enter the shape directory:file. If you omit the directory part, Oracle uses the DEFAULT DIRECTORY for the file.

Using an Oracle external table

Once the external table created, you’ll query it sort of a normal table:

SELECT

 language_id,

 language_name

FROM

 languages

ORDER BY

 language_name;

Here is that the partial output:

You can also create a view supported the external table:

CREATE VIEW language_a

SELECT language_name

FROM languages

WHERE language_name LIKE 'A%';

However, you can’t apply the INSERT, DELETE, and UPDATE to the external table:

DELETE FROM languages

WHERE language_id = 1;

Here is that the error message:

SQL Error: ORA-30657: operation not supported on external organized table

The same error will occur if you plan to insert a replacement row into the external table:

INSERT INTO languages(language_id, language_name)

VALUES(190,'Alien');

Oracle external table troubleshooting

If you forget to grant the directory permission to users who access the external table, you’ll receive the subsequent error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout

ORA-30653: reject limit reached

Oracle will issue the subsequent error if you plan to define a constraint like primary key and foreign key constraints on the external table.

ORA-30657: operation not supported on external organized table

In this blog, you’ve got learned about Oracle external tables and the way to use them to access data from flat files as they were in normal tables.

So, this brings us to the end of blog. This Tecklearn ‘Data Migration in Oracle’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

508