ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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');
