Oracle Data Pump é uma alternativa nas operações de
exportação/importação dos dados, introduzido na versão 10G do banco de dados
Oracle, mais rápida e flexível aos tradicionais EXP e IMP, que foram muito utilizados
nas versões anteriores do Oracle Database.
Assim como o banco de dados evoluiu com a implementação de diversas
melhorias, esta ferramenta também foi aperfeiçoada e segue abaixo a descrição
de duas melhorias introduzidas na versão 12c:
Transport view as table
O Data Pump irá exportar uma tabela com os dados obtidos através da
view.
Para usar este recurso, você deverá utilizar o parâmetro
VIEWS_AS_TABLES.
View do esquema HR que será utilizada para os testes:
CREATE OR
REPLACE FORCE VIEW HR.EMP_DETAILS_VIEW
AS
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.country_id,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.job_title,
l.city,
l.state_province,
c.country_name,
r.region_name
FROM employees e,
departments d,
jobs j,
locations l,
countries c,
regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id
Criação do diretório que será utilizado para gerar o arquivo:
create
directory dpdir as '/home/oracle/data_pump';
Export utilizando o parâmetro novo:
[oracle@oracle01
admin]$ expdp alex_zaballa@pdb1 views_as_tables=HR.EMP_DETAILS_VIEW
directory=dpdir dumpfile=emp_view.dmp logfile=emp_view_export.log
Export:
Release 12.1.0.1.0 - Production on Mon Oct 7 14:39:18 2013
Copyright
(c) 1982, 2013, Oracle and/or its affiliates.
All rights reserved.
Password:
Connected
to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production
With
the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and
Real Application Testing options
Starting
"ALEX_ZABALLA"."SYS_EXPORT_TABLE_01": alex_zaballa/********@pdb1
views_as_tables=HR.EMP_DETAILS_VIEW directory=dpdir dumpfile=emp_view.dmp
logfile=emp_view_export.log
Estimate
in progress using BLOCKS method...
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total
estimation using BLOCKS method: 16 KB
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
.
. exported "HR"."EMP_DETAILS_VIEW" 24.56 KB 106 rows
Master
table "ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for ALEX_ZABALLA.SYS_EXPORT_TABLE_01 is:
/home/oracle/data_pump/emp_view.dmp
Job
"ALEX_ZABALLA"."SYS_EXPORT_TABLE_01" successfully completed
at Mon Oct 7 14:40:02 2013 elapsed 0 00:00:33
Como é possível observar, foram exportadas 106 linhas.
Agora vamos importar a tabela gerada para outro owner:
[oracle@oracle01
admin]$ impdp alex_zaballa@pdb1 remap_schema=hr:alex_zaballa directory=dpdir
dumpfile=emp_view.dmp logfile=emp_view_import.log
Import:
Release 12.1.0.1.0 - Production on Mon Oct 7 14:46:44 2013
Copyright
(c) 1982, 2013, Oracle and/or its affiliates.
All rights reserved.
Password:
Connected
to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production
With
the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and
Real Application Testing options
Master
table "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully
loaded/unloaded
Starting
"ALEX_ZABALLA"."SYS_IMPORT_FULL_01": alex_zaballa/********@pdb1
remap_schema=hr:alex_zaballa directory=dpdir dumpfile=emp_view.dmp
logfile=emp_view_import.log
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
.
. imported "ALEX_ZABALLA"."EMP_DETAILS_VIEW" 24.56 KB 106 rows
Job
"ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully completed
at Mon Oct 7 14:47:05 2013 elapsed 0 00:00:11
Verificando os dados:
SQL>
show user
USER
is "ALEX_ZABALLA"
SQL>
select * from tab;
TNAME TABTYPE
CLUSTERID
------------------------------
------- ----------
EMP_DETAILS_VIEW TABLE
SQL>
select count(*) from emp_details_view;
COUNT(*)
----------
106
Turn off redo log
generation
No IMPDP, foi introduzida a opção de reduzir a geração de redo durante
uma importação através da opção DISABLE_ARCHIVE_LOGGING da
cláusula TRANSFORM.
Este parâmetro pode ser setado de forma global, para tabelas ou para
índices:
transform=disable_archive_logging:Y
transform=disable_archive_logging:Y:table
transform=disable_archive_logging:Y:index
Obviamente que este parâmetro não surtirá efeito caso o banco esteja no
modo FORCE LOGGING.
[oracle@oracle01
admin]$ impdp alex_zaballa@pdb1 remap_schema=hr:alex_zaballa directory=dpdir
dumpfile=emp_view.dmp logfile=emp_view_import2.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Import:
Release 12.1.0.1.0 - Production on Mon Oct 7 15:16:46 2013
Copyright
(c) 1982, 2013, Oracle and/or its affiliates.
All rights reserved.
Password:
Connected
to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With
the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and
Real Application Testing options
Master
table "ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully
loaded/unloaded
Starting
"ALEX_ZABALLA"."SYS_IMPORT_FULL_01": alex_zaballa/********@pdb1
remap_schema=hr:alex_zaballa directory=dpdir dumpfile=emp_view.dmp
logfile=emp_view_import2.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing
object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
.
. imported "ALEX_ZABALLA"."EMP_DETAILS_VIEW" 24.56 KB 106 rows
Job
"ALEX_ZABALLA"."SYS_IMPORT_FULL_01" successfully completed
at Mon Oct 7 15:17:05 2013 elapsed 0 00:00:12
Abraço
Alex Zaballa
0 comentários:
Postar um comentário