11g New Feature - External table compression and encryption

HAPPY NEW YEAR... well, almost. I thought that I'd share an Oracle Database 11g New Feature with you now that the year is almost done. We will talk about external table encryption and compression! Both of these features require either a license for the compression option or a license for the advanced security option. Sorry, I don't determine what is licensed and what isn't, I just shell out information on the new features! :-)

External Table Compression

Oracle Data Pump supports compression of dumpfiles in Oracle Database 11g. As a side-effect, you can also compress external tables dump files when creating them. Simple add the compression enabled syntax to the access parameters section as seen in this example:

Drop table ext_new_emp;
host del c:\oracle\admin\orcl\dpdump\new_emp.dmp

create table ext_new_emp
organization external
(
type oracle_datapump
default directory DATA_PUMP_DIR
access parameters
(logfile DATA_PUMP_DIR compression enabled)
location ('new_emp.dmp')
)
as select * from new_emp;

External Table Encryption

Oracle Database 11g offers TDE, Transparent Data Encryption. The Oracle Data Pump driver supports encryption in Oracle Database 11g. Use the encryption enabled keyword when creating the external table to encrypt the resulting dump file as seen in this example:

Drop table ext_new_emp;
host del c:\oracle\admin\orcl\dpdump\new_emp.dmp
create table ext_new_emp
organization external
(
type oracle_datapump
default directory DATA_PUMP_DIR
access parameters
(logfile DATA_PUMP_DIR encryption enabled)
location ('new_emp.dmp')
)
as select * from new_emp;