Oracle Big Data and Hadoop
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
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' referncd 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 2;

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;

Related Topics
External Tables
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved