It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Modifying External Tables

Altering External Tables

You can use any of the ALTER TABLE clauses shown in Table 1 below to change the characteristics of an external table. No other clauses are permitted.

Table 1: ALTER TABLE Clauses for External Tables
ALTER TABLE ClauseDescriptionExample
REJECT LIMITChanges the reject limit
ALTER TABLE admin_ext_employees
   REJECT LIMIT 100;
PROJECT COLUMNDetermines how the access driver validates rows in subsequent queries:
  • PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. This is the default.
  • PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table.
ALTER TABLE admin_ext_employees
   PROJECT COLUMN REFERNCED;

ALTER TABLE admin_ext_employees
   PROJECT COLUMN ALL;
DEFAULT DIRECTORYChanges the default directory specification
ALTER TABLE admin_ext_employees 
    DEFAULT DIRECTORY admin_dat2_dir;
ACCESS PARAMETERSAllows access parameters to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
    ACCESS PARAMETERS
       (FIELDS TERMINATED BY ';');
LOCATIONAllows data sources to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees
   LOCATION ('empxt3.txt',
             'empxt4.txt');
PARALLELNo difference from regular tables. Allows degree of parallelism to be changed.No new syntax
ADD COLUMNNo difference from regular tables. Allows a column to be added to an external table. Virtual columns are not permitted.No new syntax
MODIFY COLUMNNo difference from regular tables. Allows an external table column to be modified. Virtual columns are not permitted.No new syntax
SET UNUSEDTransparently converted into an ALTER TABLE DROP COLUMN command. Because external tables consist of metadata only in the database, theDROP COLUMN command performs equivalently to the SET UNUSEDcommand.No new syntax
DROP COLUMNNo difference from regular tables. Allows an external table column to be dropped.No new syntax
RENAME TONo difference from regular tables. Allows external table to be renamed.No new syntax

0 comments:

You Might Also Like

Related Posts with Thumbnails

Pages