Oracle | Big Data and R

Oracle Big Data and Hadoop Version 20c

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Note This page is dedicated to hooks inside the Oracle Databaes that specifically support Big Data and the Apache Hadoop file system

Dependencies

ALL_EXTERNAL_LOCATIONS	CDB_EXTERNAL_TABLES	USER_EXTERNAL_LOCATIONS
ALL_EXTERNAL_TABLES	DBA_EXTERNAL_LOCATIONS	USER_EXTERNAL_TABLES
CDB_EXTERNAL_LOCATIONS	DBA_EXTERNAL_TABLES

Object Privileges

SELECT

SELECT ANY TABLE

System Privileges

ALTER TABLE	CREATE TABLE	DROP ANY TABLE
CREATE ANY TABLE

Actions As SYS

Create Directory and grant privileges CREATE OR REPLACE DIRECTORY <name> AS <operating_system_path_and_directory>;

conn sys@pdbdev as sysdba

      

      CREATE OR REPLACE DIRECTORY ext AS 'c:\external';

      

      GRANT read, write ON DIRECTORY ext TO uwclass;

External Table File

Create Text File Using a Text Editor This file should be placed into the operating system file system directory 'c:\external' referenced by the CREATE DIRECTORY command above

7369,KYTE,SME,20

      7499,MILLSAP,SALESMAN,30

Create Table

Create Internal Representation of the External Table CREATE TABLE <table_name> ( <column_definitions>) ORGANIZATION EXTERNAL (TYPE oracle_loader DEFAULT DIRECTORY <oracle_directory_object_name> ACCESS PARAMETERS ( RECORDS DELIMITED BY newline BADFILE <file_name> DISCARDFILE <file_name> LOGFILE <file_name> [READSIZE <bytes>] [SKIP <number_of_rows> FIELDS TERMINATED BY '<terminator>' OPTIONALLY ENCLOSED BY '<character>' REJECT ROWS WITH ALL NULL FIELDS MISSING FIELD VALUES ARE NULL (<column_name_list>))\ LOCATION ('<file_name>')) [PARALLEL] REJECT LIMIT <UNLIMITED | integer>;

Alter Table

Access Parameters ALTER TABLE <table_name> ACCESS PARAMETERS (FIELDS TERMINATED BY '<delimiter>');

SELECT table_name, access_parameters

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 
      ACCESS PARAMETERS (FIELDS TERMINATED BY '|');

      

      SELECT table_name, access_parameters

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 
      ACCESS PARAMETERS (FIELDS TERMINATED BY ',');

      

      SELECT table_name, access_parameters

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 ACCESS PARAMETERS (FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL (empno, ename, job, deptno));

      

      SELECT table_name, access_parameters

      FROM user_external_tables;

Default Directory ALTER TABLE <table_name> DEFAULT DIRECTORY <directory_name>;

SELECT table_name, default_directory_owner, default_directory_name

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 DEFAULT DIRECTORY ctemp;

      

      SELECT table_name, default_directory_owner, default_directory_name

      FROM user_external_tables;

Drop Column ALTER TABLE <table_name> DROP COLUMN (<column_name>);

See Add Column Demo Above

Modify Column ALTER TABLE <table_name> MODIFY (<column_name> <column_change>);

desc ext_tab2

      

      ALTER TABLE ext_tab2  MODIFY (deptno VARCHAR2(10));

      

      desc ext_tab2

Parallel Access ALTER TABLE <table_name> PARALLEL <integer>;

SELECT table_name, degree

      FROM user_tables;

      

      ALTER TABLE ext_tab2 PARALLEL 
      8;

      

      SELECT table_name, degree

      FROM user_tables;

Project Column ALTER TABLE <table_name> PROJECDT COLUMN <ALL | REFERENCED>;

SELECT table_name, property

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 PROJECT COLUMN REFERENCED;

      

      SELECT table_name, property

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 PROJECT COLUMN ALL;

      

      SELECT table_name, property

      FROM user_external_tables;

Reject Limit ALTER TABLE <table_name> REJECT LIMIT <integer>;

SELECT table_name, reject_limit

      FROM user_external_tables;

      

      ALTER TABLE ext_tab2 REJECT LIMIT 2;

      

      SELECT table_name, reject_limit

      FROM user_external_tables;

Rename To ALTER TABLE <current_table_name> RENAME TO <new_table_name>;

ALTER TABLE ext_tab2 RENAME TO ext_tab9;

Set Unused ALTER TABLE <table_name> SET UNUSED COLUMN <column_name>;

Do not use this syntax as it is misleading. It is translated directly into a DROP COLUMN command

Target File Name ALTER TABLE <table_name> LOCATION ('<file1>', '<file2>', ....);

SELECT * FROM ext_tab;

      

      ALTER TABLE ext_tab LOCATION ('demo1.dat');

      

      SELECT * FROM ext_tab;

      

      ALTER TABLE ext_tab LOCATION ('demo1.dat', 'demo2.dat');

      

      SELECT * FROM ext_tab;

      

      ALTER TABLE ext_tab LOCATION ('demo2.dat');

Drop Table

Drop an External Table DROP TABLE <table_name>;

DROP TABLE ext_tab;