Pro Oracle Database 11g Administration (60 page)
BARTS EXADATA_ET EXA_DIR records delimited by newline Additionally, you can select from the DBA_EXTERNAL_LOCATIONS table for information regarding any flat files referenced in an external table:
select
owner
,table_name
,location
from dba_external_locations;
Here’s some sample output:
OWNER TABLE_NAME LOCATION
--------------- -------------------- ---------------
BARTS EXADATA_ET ex.csv
Loading a Regular Table from the External Table
Now you can load data contained in the external table into a regular database table. When you do this, you can take advantage of Oracle’s direct-path loading and parallel features. This example creates a regular database table that will be loaded with data from the external table: create table exa_info(
exa_id NUMBER
,machine_count NUMBER
,hide_flag NUMBER
349
CHAPTER 14 ■ EXTERNAL TABLES
,oracle NUMBER
,ship_date DATE
,rack_type VARCHAR2(32)
) nologging parallel 2;
You can direct-path load this regular table (via the APPEND hint) from the contents of the external table as follows:
SQL> insert /*+ APPEND */ into exa_info select * from exadata_et;
You can verify that the table was direct-path loaded by attempting to select from it before you commit the data:
SQL> select * from exa_info;
Here’s the expected error:
ORA-12838: cannot read/modify an object after modifying it in parallel After you commit the data, you can select from the table:
SQL> commit;
SQL> select * from exa_info;
The other way to direct-path load a table is to use the CREATE TABLE AS SELECT (CTAS) statement. A CTAS statement automatically attempts to do a direct-path load. In this example, the EXA_INFO table is created and loaded in one statement:
SQL> create table exa_info nologging parallel 2 as select * from exadata_et; By using direct-path loading and parallelism, you can achieve loading performance similar to that from using SQL*Loader. The advantage of using SQL to create a table from an external table is that you can now perform complex data transformations using standard SQL*Plus features when building your regular database table (EXA_INFO, in this example).
Any CTAS statements automatically process with the degree of parallelism that has been defined for the underlying table. However, when you use INSERT AS SELECT statements, you need to enable parallelism for the session:
SQL> alter session enable parallel dml;
As a last step, you should generate statistics for any table that has been loaded with a large amount of data. Here’s an example:
exec dbms_stats.gather_table_stats(-
ownname=>'BARTS',-
tabname=>'EXA_INFO',-
estimate_percent => 20, -
cascade=>true);
Performing Advanced Transformations
Oracle provides sophisticated techniques for transforming data. This section details how to use a pipelined function to transform data in an external table. Listed next are the steps for doing this: 1. Create an external table.
2. Create a record type that maps to the columns in the external table.
350
CHAPTER 14 ■ EXTERNAL TABLES
3. Create a table based on the record type created in step 2
4. Create a piplelined function that is used to inspect each row as it’s loaded and transform data based on business requirements.
5. Use an INSERT statement that selects from the external table and uses the pipelined function to transform data as it’s loaded.
This example uses the same external table and CSV file created in the previous section on loading data from CSV files. Recall that the external table name is EXADATA_ET and the CSV file name is ex.csv.
After you create the external table, next create a record type that maps to the column names in the external table:
create or replace type rec_exa_type is object
(
exa_id number
,machine_count number
,hide_flag number
,oracle number
,ship_date date
,rack_type varchar2(32)
);
/
Next, create a table based on the previous record type:
create or replace type table_exa_type is table of rec_exa_type;
/
Oracle PL/SQL allows you to use functions as a row source for SQL operations. This feature is known as
pipelining
. It lets you use complex transformation logic combined with the power of SQL*Plus. For this example, you create a pipelined function to transform selected column data as it’s loaded.
Specifically, this function adds 30 days to the SHIP_DATE when the ORACLE column has a 0 value: create or replace function exa_trans
return table_exa_type pipelined is
begin
for r1 in
(select rec_exa_type(
exa_id, machine_count, hide_flag
,oracle, ship_date, rack_type
) exa_rec
from exadata_et) loop
if (r1.exa_rec.oracle = 0) then
r1.exa_rec.ship_date := r1.exa_rec.ship_date + 30;
end if;
pipe row (r1.exa_rec);
end loop;
return;
end;
/
Now you can use this function to load data into a regular database table. For reference, here’s the CREATE TABLE statement that instantiates the table to be loaded:
351
CHAPTER 14 ■ EXTERNAL TABLES
create table exa_info(
exa_id NUMBER
,machine_count NUMBER
,hide_flag NUMBER
,oracle NUMBER
,ship_date DATE
,rack_type VARCHAR2(32)
) nologging parallel 2;
Next, use the pipelined function to transform data selected from the external table and insert it into the regular database table in one step:
SQL> insert into exa_info select * from table(exa_trans);
Here’s the data that was loaded into the EXA_INFO table for this example: SQL> select * from exa_info;
EXA_ID MACHINE_COUNT HIDE_FLAG ORACLE SHIP_DATE RACK_TYPE
---------- ------------- ---------- ---------- --------- ------------
5 2 0 0 03-JAN-10 Half
6 1 0 1 31-MAR-10 Quarter
7 4 0 1 07-MAY-10 Full
8 1 1 0 30-MAY-10 Quarter
Although the example in this section is simple, you can use the technique to apply any degree of sophisticated transformational logic. This technique allows you to embed the transformation requirements in a pipelined PL/SQL function that modifies data as each row is loaded.
Viewing Text Files from SQL
External tables allow you to use SQL SELECT statements to retrieve information from operating-system flat files. For example, say you want to report on the contents of the alert log file. First, create a directory object that points to the location of the alert log:
SQL> select value from v$parameter where name ='background_dump_dest'; Here’s the output for this example:
/oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace
Next, create a directory object that points at the location of the background dump destination: SQL> create directory t_loc as '/oracle/app/oracle/diag/rdbms/o11r2/O11R2/trace'; Now, create an external table that maps to the database alert log operating-system file. In this example, the database name is O11R2, and thus the alert log file name is alert_O11R2.log: create table alert_log_file(
alert_text varchar2(4000))
organization external
( type oracle_loader
default directory t_loc
access parameters (
records delimited by newline
nobadfile
nologfile
352
CHAPTER 14 ■ EXTERNAL TABLES
nodiscardfile
fields terminated by '#$~=ui$X'
missing field values are null
(alert_text)
)
location ('alert_O11R2.log')
)
reject limit unlimited;
You can query the table via SQL queries. For example:
SQL> select * from alert_log_file where alert_text like 'ORA-%';
This allows you to use SQL to view and report on the contents of the alert log. You may find this a convenient way to provide SQL access to otherwise inaccessible operating-system files.
The ACCESS PARAMETERS clause of an external table’s ORACLE_LOADER access driver may look familiar if you’ve previously worked with SQL*Loader. Table 14–1 describes some of the more commonly used access parameters. For a full list of access parameters, see Oracle’s
Database Utilities
guide (available on OTN).
Table 14–1.
Selected Access Parameters for the ORACLE_LOADER Driver
Access Parameter
Description
DELIMITED BY
Indicates which character delimits the fields
TERMINATED BY
Indicates how a field is terminated
FIXED
Specifies the size of records having a fixed length
BADFILE
Name of the file that stores records that can’t be loaded due to an error NOBADFILE
Specifies that a file shouldn’t be created to hold records that can’t be loaded due to errors
LOGFILE
Name of the file in which general messages are recorded when creating an external table
NOLOGFILE
Specifies that no log file should be created
DISCARDFILE
Names the file to which records are written that fail the LOAD WHEN clause NODISCARDFILE
Specifies that no discard file should be created
SKIP
Skips the specified number of records in the file before loading
PREPROCESSOR
Specifies the user-named program that runs and modifies the contents
of the file before Oracle loads the data
MISSING FIELD VALUES ARE
Loads fields that have no data as NULL values
NULL
353
CHAPTER 14 ■ EXTERNAL TABLES
2. Use the CREATE TABLE...ORGANIZATION EXTERNAL...AS SELECT statement to unload data from the database into the dump file.
First, create a directory object. The next bit of code creates a directory object named DP that points at the /oradump directory:
SQL> create directory dp as '/oradump';
If you’re not using a user with DBA privileges, then explicitly grant access to the directory object to the required user:
SQL> grant read, write on directory dp to larry;
To create a dump file, use the ORACLE_DATAPUMP access driver of the CREATE TABLE...ORGANIZATION
EXTERNAL statement. This example unloads the INV table’s contents into the inv.dmp file: CREATE TABLE inv_et
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv.dmp')
)
AS SELECT * FROM inv;
The previous command does two things:
• Creates an external table named INV_ET based on the INV table
• Creates a platform-independent dump file named inv.dmp
Now you can copy the inv.dmp file to a separate database server and base an external table on this dump file. The remote server (to which you copy the dump file) can be a different platform than the server where you created the file. For example, you can create a dump file on a Windows box, copy to a Solaris server, and select from the dump file via an external table. In this example, the external table is named INV_DW:
CREATE TABLE inv_dw
(inv_id number
,inv_desc varchar2(30))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv.dmp')
);
After it’s created, you can access the external table data from SQL*Plus: SQL> select * from inv_dw;
You can also create and load data into regular tables using the dump file: SQL> create table inv as select * from inv_dw;
This provides a simple and efficient mechanism for transporting data from one platform to another.
355
CHAPTER 14 ■ EXTERNAL TABLES
Using Parallelism to Reduce Elapsed Time
To maximize the unload performance when you create a dump file via an external table, use the PARALLEL clause. This example creates two dump files in parallel:
CREATE TABLE inv_et
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv1.dmp','inv2.dmp')
)
PARALLEL 2
AS SELECT * FROM inv;
To access the data in the dump files, create a different external table that references the two dump files:
CREATE TABLE inv_dw
(inv_id number
,inv_desc varchar2(30))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dp
LOCATION ('inv1.dmp','inv2.dmp')
);
You can now use this external table to select data from the dump files: SQL> select * from inv_dw;
Compressing a Dump File
You can create a compressed dump file via an external table. For example, use the COMPRESS option of the ACCESS PARAMETERS clause: