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.

6. September 2013

PL/SQL-Code in einer SQL-Abfrage? Ja, Oracle12c!

PL/SQL-Code within a SQL query? Yes, in Oracle12c!
Heute geht es um ein neues SQL-Feature in Oracle12c - der PL/SQL inline WITH-Klausel. Die seit Oracle9i verfügbare SQL WITH-Klausel zum sog. Subquery Factoring ist den meisten ja bekannt (dazu gab es auch mal ein Blog Posting). Neu in Oracle12c ist, dass man innerhalb dieser WITH-Klausel PL/SQL-Funktionen definieren kann, die dann nur für diese eine Abfrage gültig sind.
with
  function half_sal(p_sal in number) return number 
  is 
  begin 
    return p_sal/2; 
  end;
  function quarter_sal(p_sal in number) return number
  is 
  begin 
    return p_sal/4; 
  end;
select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
from emp
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
Die Funktionen sind tatsächlich nur zur Laufzeit der Abfrage gültig - man findet im Data Dictionary anschließend keine PL/SQL-Funktionen mit kryptischen Namen. Die Möglichkeiten, die man nun in der Datenbank mit einer SQL-Abfrage hat, vervielfachen sich damit.
Es stellt sich aber die Frage, ob damit nicht ein großes Sicherheitsloch entsteht. Ist eine Anwendung für SQL Injection Attacken verwundbar, so scheint es, als ließe sich nun ganz bequem auch noch PL/SQL Code "injizieren". Aber so einfach ist es nun auch wieder nicht. PL/SQL in der SQL WITH-Klausel ist nur in der "Top-Level"-Abfrage erlaubt - nicht dagegen in einer Subquery. Das lässt sich einfach nachvollziehen ...
select * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

SQL> @script.sql
  with
  *
FEHLER in Zeile 2:
ORA-32034: Nicht unterstützte Benutzung von WITH-Klausel
Das Injizieren von PL/SQL kann also sehr einfach verhindert werden, indem man die SQL-Abfrage einfach als Unterabfrage in ein SELECT * FROM (...) "einrahmt". Wer allerdings sicher ist, dass seine SQL-Abfragen keinerlei SQL-Injection-Schwachstellen aufweisen und PL/SQL WITH in einer Unterabfrage verwenden möchte, kann dies mit dem Hint WITH_PLSQL "freischalten".
select /*+ WITH_PLSQL*/ * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
Der WITH_PLSQL Hint ist immer erforderlich, wenn es kein SELECT, sondern ein DML Statement ist (UPDATE, INSERT, DELETE). Mehr Informationen zu "PLSQL in der WITH-Klausel" findet Ihr in der Dokumentation - SQL Language Reference.
This blog posting is about a new feature in the Oracle12c database: the new possibility to add PL/SQL to a SQL WITH Clause. Most readers know about the SQL WITH clause, which is available since Oracle9i - in 2008 I published a blog posting about Subquery Factoring). Now, with Oracle12c, PL/SQL functions can be defined within the WITH clause of a SQL Statement - and these functions are valid for this very query only.
with
  function half_sal(p_sal in number) return number 
  is 
  begin 
    return p_sal/2; 
  end;
  function quarter_sal(p_sal in number) return number
  is 
  begin 
    return p_sal/4; 
  end;
select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
from emp
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
There are no stored PL/SQL objects in the data dictionary after query execution - the PL/SQL code is being compiled and executed in memory only. So, with this new feature, SQL queries can be much more powerful.
Given this, I see all the security questions coming up. "In an application with SQL injection vulnerabilities, it seems that an attacker now can inject not only SQL fragments, but also PL/SQL-Code - this is much more dangerous". But the Developers thought about this: Adding PL/SQL to the WITH clause is only allowed in the top-level query. In a subquery, it is forbidden, as the following example shows.
select * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

SQL> @plsql-with.sql
  with
  *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause
We can therefore prevent the injection of PL/SQL code into a SQL query very effectivley by "framing" our query as subquery into a "SELECT * FROM (...)" expression. Developers, who though need PL/SQL within a subquery (and who are sure that there is no risk), can enable it by using the WITH_PLSQL hint in the top-level query - as follows ...
select /*+ WITH_PLSQL*/ * from (
  with
    function half_sal(p_sal in number) return number 
    is 
    begin 
      return p_sal/2; 
    end;
    function quarter_sal(p_sal in number) return number
    is 
    begin 
      return p_sal/4; 
    end;
  select empno, sal, half_sal(sal) half_sal, quarter_sal(sal) quarter_sal 
  from emp
)
/

     EMPNO        SAL  HALF_SAL QUARTER_SAL
---------- ---------- --------- -----------
      7369        800    400,00      200,00
      7499       1600    800,00      400,00
      7521       1250    625,00      312,50
         :          :         :           : 
The WITH_PLSQL hint is always necessary, when the SQL statement is a DML command (INSERT, UPDATE, DELETE). More information about this feature is contained in the documentation - SQL Language Reference.

Beliebte Postings