oracle external table excel,External Table Example01 create a external table

1)external table example01

1@@@@create a external table.

guide:

External table are read-only. No DML operations are allowed.

No create indexes on an external table. No backup the external using RMAN.

Create an Externall Table:

~The metadata,which describes how the data looks to Oracle, including the

external table name, the column names, and Oracle data types. These are

the names you will use in the SQL statements to access the external table.

The metadata is stored in the Oracle data dictionary.

~The access parameters, which describe how the data is stored in the external

file(i.e, where it's located, its format, and how to identify the fields and

records). The access driver use access parameters.

@@@

@@@<1>create directory object for log and data.

@@@

SYS@ocp> !mkdir -p /home/oracle/datafiles

SYS@ocp> !mkdir -p /home/oracle/logfiles

SYS@ocp> CREATE OR REPLACE DIRECTORY data_file_dir AS '/home/oracle/datafiles';

Directory created.

SYS@ocp> CREATE OR REPLACE DIRECTORY log_file_dir AS '/home/oracle/logfiles';

Directory created.

SYS@ocp> GRANT READ, WRITE ON DIRECTORY data_file_dir TO sh;

Grant succeeded.

SYS@ocp> GRANT READ, WRITE ON DIRECTORY log_file_dir TO sh;

Grant succeeded.

@@@

@@@<2>create sql script for creating a external table.

@@@

@@@create external table like this:

[oracle@station78 datafiles]$ cat external_table.sql

CREATE TABLE sh.external_table

(product_id VARCHAR2(8),

product_name  VARCHAR2(30),

category   VARCHAR2(4),

cost_price  NUMBER(6,2),

sell_price   NUMBER(6,2),

weight    NUMBER(4,2),

shipping_charge  VARCHAR2(20),

manufacturer  VARCHAR2(10))

ORGANIZATION EXTERNAL

(TYPE ORACLE_LOADER

DEFAULT DIRECTORY data_file_dir

ACCESS PARAMETERS

(RECORDS DELIMITED BY NEWLINE

CHARACTERSET US7ASCII

BADFILE log_file_dir:'external_table.bad'

LOGFILE log_file_dir:'external_table.log'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY "'")

LOCATION ('external_data.dat')

)

REJECT LIMIT UNLIMITED PARALLEL;

@@@

@@@<3>create the data for external table. It is like sqlldr.

@@@

[oracle@station78 datafiles]$ cat external_data.dat

'SP1000', 'Digital Camera', 'ELEC', 45.67, 67.23, 15.00, 4.50, 'Ricoh', 'Ricoh'

'SP1001', 'APS Camera', 'ELEC', 24.67, 36.23, 5.00, 4.50, 'Ricoh', 'Ricoh'

'SP1010', 'Camera', 'ELEC', 35.67, 47.89, 5.00, 4.50, 'Agfa', 'Agfa'

@@@

@@@<4>create external table, then inspect the result.

@@@

[oracle@station78 ~]$ sqlplus sh/sh

SH@ocp> @datafiles/external_table.sql

Table created.

@@@

SH@ocp> select * from external_table;

PRODUCT_ PRODUCT_NAME       CATE COST_PRICE SELL_PRICE     WEIGHT SHIPPING_CHARGE       MANUFACTUR

-------- ------------------------- ---- ---------- ---------- ---------- --------------------

SP1000     Digital Camera     ELEC      45.67      67.23       15 4.50           Ricoh

SP1001     APS Camera        ELEC      24.67      36.23        5 4.50           Ricoh

SP1010     Camera         ELEC      35.67      47.89        5 4.50           Agfa

@@@

@@@check the metadata in data dictionary.

SYS@ocp> desc sh.external_table

Name                       Null?    Type

----------------------------------------- -------- ----------------------------

PRODUCT_ID                        VARCHAR2(8)

PRODUCT_NAME                        VARCHAR2(30)

CATEGORY                        VARCHAR2(4)

COST_PRICE                        NUMBER(6,2)

SELL_PRICE                        NUMBER(6,2)

WEIGHT                         NUMBER(4,2)

SHIPPING_CHARGE                    VARCHAR2(20)

MANUFACTURER                        VARCHAR2(10)

@@@

@@@check the discription of external table.

SH@ocp> select * from user_external_tables where table_name='EXTERNAL_TABLE';

TABLE_NAME               TYP TYPE_NAME              DEF DEFAULT_DIRECTORY_NAME

------------------------------ --- ------------------------------ --- ------------------------------

REJECT_LIMIT                 ACCESS_

---------------------------------------- -------

ACCESS_PARAMETERS

----------------------------------------------------------------------------------------------------

PROPERTY

----------

EXTERNAL_TABLE               SYS ORACLE_LOADER          SYS DATA_FILE_DIR

UNLIMITED                 CLOB

RECORDS DELIMITED BY NEWLINE

CHARACTERSET US7ASCII

BADFILE log_file_dir:'external_table.bad'

LOGFILE log_file_dir:'external_table.log'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY "'"

ALL

SH@ocp> select * from user_external_locations where table_name='EXTERNAL_TABLE';

TABLE_NAME

------------------------------

LOCATION

----------------------------------------------------------------------------------------------------

DIR DIRECTORY_NAME

--- ------------------------------

EXTERNAL_TABLE

external_data.dat

SYS DATA_FILE_DIR

@@@

@@@<4>copy the external table data to assigned table.

@@@

@@@This is the one method:

SYS@ocp> create table dw.product as select * from sh.external_table;

Table created.

@@@

@@@If you already had the table, you could do like this:

SYS@ocp> ed

1  INSERT INTO dw.product

2  (product_id, product_name, category,

3   cost_price, sell_price, weight,

4   shipping_charge, manufacturer)

5  SELECT product_id, product_name, category,

6        cost_price, sell_price, weight,

7       (shipping_charge * 1.10),     @@@this method is more flexible.

8        manufacturer

9* FROM sh.external_table

SYS@ocp> /

3 rows created.

SYS@ocp> commit;

Commit complete.

@@@

@@@<5>check the explain plan for external table.

@@@

@@@only explain, do not execute.

SYS@ocp> !find /u01/app/oracle/product/ -name utlxplp.sql

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplp.sql

SYS@ocp> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------

Plan hash value: 2113680126

-----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    TQ    |IN-OUT| PQ Distrib |

-----------------------------------------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT          |            |  8168 |   678K|     4     (0)| 00:00:01 |    |      |        |

|   1 |  PX COORDINATOR           |            |       |       |        |           |    |      |        |

|   2 |   PX SEND QC (RANDOM)          | :TQ10000       |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR          |            |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | PCWC |        |

|   4 |     EXTERNAL TABLE ACCESS FULL| EXTERNAL_TABLE |  8168 |   678K|     4     (0)| 00:00:01 |  Q1,00 | PCWP |        |

-----------------------------------------------------------------------------------------------------------------------------

@@@summary: we could see PX, it is parallel