19. September 2013

Oracle12c Data Pump: Views 'als Tabellen' exportieren ...

Oracle12c Data Pump: Export views 'as tables'
Die Data Pump in Oracle12c bringt ein sehr nützliches neues Feature mit: Views können nun auch "als Tabellen" exportiert werden. Wurde bislang eine View auch nur als solche exportiert - nach dem Import stand also wiederum eine View zur Verfügung. Wollte man bislang die Daten einer View per Export / Import auf eine andere Tabelle übertragen, so musste man zunächst ein CREATE TABLE AS SELECT machen. Das fällt nun weg: Im Kommandozeilentool impdp kann der Parameter VIEWS_AS_TABLES verwendet werden - das sieht dann so aus:
$ expdp userid=scott/tiger@sccloud029:1521/pdb1 
        directory=data_pump_dir 
        dumpfile=view.dmp 
        views_as_tables=VIEW_ON_EMP

Export: Release 12.1.0.1.0 - Production on Thu Sep 19 10:50:14 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_02":  userid=scott/********@sccloud029:1521/pdb1 
directory=data_pump_dir dumpfile=view.dmp views_as_tables=VIEW_ON_EMP
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 "SCOTT"."VIEW_ON_EMP"                       7.843 KB       3 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_02 is:
  /home/oracle/view.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_02" successfully completed at Thu Sep 19 10:50:59 2013 
elapsed 0 00:00:35
Importiert man dieses File nun (bspw. in ein anderes Schema) ...
$ impdp userid=scott2/scott2@sccloud029:1521/pdb1 
        directory=data_pump_dir 
        dumpfile=view.dmp 
        remap_schema=scott:scott2

Import: Release 12.1.0.1.0 - Production on Thu Sep 19 10:56:08 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT2"."SYS_IMPORT_FULL_01":  userid=scott2/********@sccloud029:1521/pdb1 
directory=data_pump_dir dumpfile=view.dmp remap_schema=scott:scott2
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT2"."VIEW_ON_EMP"                      7.843 KB       3 rows
Job "SCOTT2"."SYS_IMPORT_FULL_01" successfully completed at Thu Sep 19 10:56:30 2013 
elapsed 0 00:00:14
... so findet man danach eine Tabelle vor und keine View mehr.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
VIEW_ON_EMP                    TABLE

1 Zeile wurde ausgewählt.
Aufmerksamen Beobachtern dürfte auffallen, dass während des Exports eine Tabelle im Quellschema erzeugt wird, deren Namen mit KU$VAT_ beginnt. Diese Tabelle legt die Data Pump als "Metadaten-Template" an. Sie enthält alle Spalten der View, aber keine Daten. Nach dem Export wird sie gelöscht. Verwendet man beim Aufruf von expdp zusätzlich den Parameter TABLE_NAME, dann kann man eine eigene Tabelle, die man selbst erstellt hat, als Metadaten-Template verwenden. Das ist recht hilfreich, wenn aus einer Read Only Datenbank exportiert wird.
Eine der schönen Eigenschaften der Data Pump (im Vergleich zum alten Export/Import) ist ja, dass man es auch per PL/SQL mit DBMS_DATAPUMP ansprechen kann. Darüber habe ich in einem früheren Blog-Posting schon mal berichtet. Auch DBMS_DATAPUMP wurde um die neue Möglichkeit, Views als Tabellen zu exportieren, erweitert. Eine PL/SQL-Prozedur zum Export einer View als Tabelle sieht dann so aus ...
create or replace procedure exp_view_as_table(
  p_table_name in varchar2
) authid current_user is
  v_dp_handle number;
  v_tname     varchar2(30) := p_table_name;
  pragma AUTONOMOUS_TRANSACTION; 
begin
  -- Create Data Pump Handle - "TABLE EXPORT" in this case
  v_dp_handle := dbms_datapump.open(
    operation      => 'EXPORT',
    job_mode       => 'TABLE'
  );
  -- Specify target file - make it unique with a timestamp
  dbms_datapump.add_file(
    handle         => v_dp_handle,
    filename       => v_tname || '_'||to_char(sysdate, 'YYYYMMDD-HH24MISS')||'.dmp',
    directory      => 'DATA_PUMP_DIR'
  );
  -- Export only those tables specified in the procedure's parameter
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'NAME_EXPR',
    value          => '='''||v_tname||''''
  );
  -- Export Views as tables
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'VIEWS_AS_TABLES',
    value          => v_tname
  );
  -- Do it!
  dbms_datapump.start_job(
    handle        => v_dp_handle
  );
  commit;
end;
/
sho err
... und wird wie folgt aufgerufen. Beachtet bitte, dass diese PL/SQL Prozedur den Export als Data Pump Job startet - der Export wird also in fast allen Fällen noch laufen, wenn Ihr den Prompt "zurückbekommt". Den Status der Export-Operation könnt Ihr in der View USER_DATAPUMP_JOBS nachsehen. Wenn Ihr "synchron" arbeiten wollt, könnt Ihr nach dem START_JOB noch WAIT_FOR_JOB verwenden - die Prozedur endet so erst dann, wenn der Job abgeschlossen ist.
Ein kleiner Hinweis am Rande: Die Data Pump schreibt will die Details der Export-Operation in eine Tabelle schreiben; der User braucht also entsprechende Privilegien (und ab Oracle12c ist UNLIMITED TABLESPACE nicht mehr Bestandteil der RESOURCE-Rolle).
begin
  exp_view_as_table('{View-Name}');
end;
/
sho err
Wie im früheren Blog-Posting schon beschrieben, lässt sich das sehr schön mit DBMS_SCHEDULER kombinieren, um die Inhalte einer View in regelmäßigen Abständen zu exportieren. Mit dem Paket DBMS_FILE_TRANSFER lassen sich die Dumpfiles dann auf einen anderen Datenbankserver übertragen; und dort kann der Import ebenfalls mit DBMS_DATAPUMP erfolgen.
Data Pump in Oracle12c has a very interesting new feature: It allows to "export views as tables". Up to Oracle11g a view was always exported as a view - so the export dumpfile contains the CREATE VIEW statement - and after importing the target schema also contains the view. Exporting view data as a table required to first materialize the data with a CREATE TABLE AS SELECT. In Oracle12c we can directly export a view as a table - as follows:
$ expdp userid=scott/tiger@sccloud029:1521/pdb1 
        directory=data_pump_dir 
        dumpfile=view.dmp 
        views_as_tables=VIEW_ON_EMP

Export: Release 12.1.0.1.0 - Production on Thu Sep 19 10:50:14 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_02":  userid=scott/********@sccloud029:1521/pdb1 
directory=data_pump_dir dumpfile=view.dmp views_as_tables=VIEW_ON_EMP
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 "SCOTT"."VIEW_ON_EMP"                       7.843 KB       3 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_02 is:
  /home/oracle/view.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_02" successfully completed at Thu Sep 19 10:50:59 2013 
elapsed 0 00:00:35
After importing this file (into another schema, for instance) ...
$ impdp userid=scott2/scott2@sccloud029:1521/pdb1 
        directory=data_pump_dir 
        dumpfile=view.dmp 
        remap_schema=scott:scott2

Import: Release 12.1.0.1.0 - Production on Thu Sep 19 10:56:08 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SCOTT2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT2"."SYS_IMPORT_FULL_01":  userid=scott2/********@sccloud029:1521/pdb1 
directory=data_pump_dir dumpfile=view.dmp remap_schema=scott:scott2
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SCOTT2"."VIEW_ON_EMP"                      7.843 KB       3 rows
Job "SCOTT2"."SYS_IMPORT_FULL_01" successfully completed at Thu Sep 19 10:56:30 2013 
elapsed 0 00:00:14
... we now find a table containing the views' data.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
VIEW_ON_EMP                    TABLE

1 row selected.
Some readers might notice that Data Pump, indeed, creates a table during export - it's name starts with KU$VAT_. This table serves as a "metadata template" and contains all columns of the view, but no data. After the export operation has finished, the template table is being deleted. The table_name parameter of expdp allows to specify an own table as the metadata template - this is useful for read only databases.
One of the advantages of data pump over "classic" import/export is, that we have a PL/SQL API for using it - in the past, there was already a blog posting about DBMS_DATAPUMP. Based on this, we can create a PL/SQL procedure EXP_VIEW_AS_TABLE, which does the same job as the above expdp command - but now it's a stored procedure within the database. And also in PL/SQL, we can use the VIEWS_AS_TABLES feature.
create or replace procedure exp_view_as_table(
  p_table_name in varchar2
) authid current_user is
  v_dp_handle number;
  v_tname     varchar2(30) := p_table_name;
  pragma AUTONOMOUS_TRANSACTION; 
begin
  -- Create Data Pump Handle - "TABLE EXPORT" in this case
  v_dp_handle := dbms_datapump.open(
    operation      => 'EXPORT',
    job_mode       => 'TABLE'
  );
  -- Specify target file - make it unique with a timestamp
  dbms_datapump.add_file(
    handle         => v_dp_handle,
    filename       => v_tname || '_'||to_char(sysdate, 'YYYYMMDD-HH24MISS')||'.dmp',
    directory      => 'DATA_PUMP_DIR'
  );
  -- Export only those tables specified in the procedure's parameter
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'NAME_EXPR',
    value          => '='''||v_tname||''''
  );
  -- Export Views as tables
  dbms_datapump.metadata_filter(
    handle         => v_dp_handle,
    name           => 'VIEWS_AS_TABLES',
    value          => v_tname
  );
  -- Do it!
  dbms_datapump.start_job(
    handle        => v_dp_handle
  );
  commit;
end;
/
sho err
The procedure can be called like any other PL/SQL procedure. Note that the procedure just starts the Data Pump Job - so the export is not necessarily finished when you get the prompt back. If you need the procedure to operate synchronously (t.m. to wait until the job finished), you need to call WAIT_FOR_JOB after calling START_JOB. If not, you can use USER_DATAPUMP_JOBS in order to check the job's status. Also note, that Data Pump creates a table to store the details of the export operation - so your database user needs appropriate privileges (BTW: in Oracle12c, UNLIMITED TABLESPACE is no longer opart of the RESOURCE role).
begin
  exp_view_as_table('{View-Name}');
end;
/
sho err
And as described in the earlier blog posting about DBMS_DATAPUMP, this can be combined very nicely with the Scheduler API in the database. So a contents of a view can be exported regularly as a table. Then, we can utilize DBMS_FILE_TRANSFER to transfert the dumpfile to another server - and there it is finally being imported - again - with DBMS_DATAPUMP.

Keine Kommentare:

Beliebte Postings