String sql = "SELECT u.id, u.name, r.region_name, u.status "                                      FROM TABLE(:id_list))                 hosts: db_server                                                 + "ORDER BY r.region_name, u.name";                              JOIN customers c ON c.customer_id = o.customer_id
           + "FROM users u "                                               ORDER BY o.created_at DESC;                                  become: true                                          try (Connection con = dataSource.getConnection();                          WHERE o.order_id IN (SELECT column_value
           + "JOIN regions r ON r.id = u.region_id "                                                                                    tasks:                                                     PreparedStatement ps = con.prepareStatement(sql)) {                                          FROM TABLE(:id_list))
           + "LEFT JOIN activity a ON a.user_id = u.id "                  BEGIN                                                           - name: Pakete installieren                             ps.setString(1, "ACTIVE");                                             ORDER BY o.created_at DESC;
           + "WHERE u.status = ? AND a.last_seen > ? "                      DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');                  ansible.builtin.dnf:                                  ps.setTimestamp(2, Timestamp.valueOf(since));
           + "ORDER BY r.region_name, u.name";                            END;                                                                name: [postgresql-server, postgresql-contrib]        try (ResultSet rs = ps.executeQuery()) {                              BEGIN
try (Connection con = dataSource.getConnection();                         /                                                                   state: present                                          while (rs.next()) {                                                 DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
     PreparedStatement ps = con.prepareStatement(sql)) {                  CREATE INDEX idx_activity_user_seen                             - name: Dienst starten                                          long sysNumber = rs.getLong("id");                            END;
    ps.setString(1, "ACTIVE");                                                ON activity (user_id, last_seen DESC);                        ansible.builtin.systemd:                                      if (sysNumber > 0 && rs.getString("name") != null) {          /
    ps.setTimestamp(2, Timestamp.valueOf(since));                                                                                             name: postgresql                                                result.add(new DataRow(sysNumber, rs.getString("name")));    CREATE INDEX idx_activity_user_seen
    try (ResultSet rs = ps.executeQuery()) {                              SELECT o.order_id, o.created_at, c.customer_name                    state: started                                              }                                                                 ON activity (user_id, last_seen DESC);
        while (rs.next()) {                                                 FROM orders o                                                     enabled: true                                           }
            long sysNumber = rs.getLong("id");                              JOIN customers c ON c.customer_id = o.customer_id         - name: PostgreSQL bereitstellen                            }                                                                     SELECT o.order_id, o.created_at, c.customer_name
            if (sysNumber > 0 && rs.getString("name") != null) {           WHERE o.order_id IN (SELECT column_value                     hosts: db_server                                      } catch (SQLException e) {                                                  FROM orders o
                result.add(new DataRow(sysNumber, rs.getString("name")));                            FROM TABLE(:id_list))                 become: true                                              log.error("Abfrage fehlgeschlagen: {}", e.getMessage());                JOIN customers c ON c.customer_id = o.customer_id
            }                                                              ORDER BY o.created_at DESC;                                  tasks:                                                    throw new DataAccessException(e);                                      WHERE o.order_id IN (SELECT column_value
        }                                                                                                                                 - name: Pakete installieren                         }                                                                                                 FROM TABLE(:id_list))
    }                                                                     BEGIN                                                             ansible.builtin.dnf:                              String sql = "SELECT u.id, u.name, r.region_name, u.status "               ORDER BY o.created_at DESC;
} catch (SQLException e) {                                                  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');                    name: [postgresql-server, postgresql-contrib]               + "FROM users u "
    log.error("Abfrage fehlgeschlagen: {}", e.getMessage());              END;                                                                state: present                                             + "JOIN regions r ON r.id = u.region_id "                      BEGIN
    throw new DataAccessException(e);                                     /                                                               - name: Dienst starten                                         + "LEFT JOIN activity a ON a.user_id = u.id "                    DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
}                                                                         CREATE INDEX idx_activity_user_seen                               ansible.builtin.systemd:                                     + "WHERE u.status = ? AND a.last_seen > ? "                    END;
String sql = "SELECT u.id, u.name, r.region_name, u.status "                  ON activity (user_id, last_seen DESC);                          name: postgresql                                           + "ORDER BY r.region_name, u.name";                            /
           + "FROM users u "                                                                                                                  state: started                                  try (Connection con = dataSource.getConnection();                         CREATE INDEX idx_activity_user_seen
           + "JOIN regions r ON r.id = u.region_id "                      SELECT o.order_id, o.created_at, c.customer_name                    enabled: true                                        PreparedStatement ps = con.prepareStatement(sql)) {                      ON activity (user_id, last_seen DESC);
           + "LEFT JOIN activity a ON a.user_id = u.id "                    FROM orders o                                             - name: PostgreSQL bereitstellen                            ps.setString(1, "ACTIVE");
           + "WHERE u.status = ? AND a.last_seen > ? "                      JOIN customers c ON c.customer_id = o.customer_id           hosts: db_server                                          ps.setTimestamp(2, Timestamp.valueOf(since));                         SELECT o.order_id, o.created_at, c.customer_name
           + "ORDER BY r.region_name, u.name";                             WHERE o.order_id IN (SELECT column_value                     become: true                                              try (ResultSet rs = ps.executeQuery()) {                                FROM orders o
try (Connection con = dataSource.getConnection();                                                 FROM TABLE(:id_list))                 tasks:                                                        while (rs.next()) {                                                 JOIN customers c ON c.customer_id = o.customer_id
     PreparedStatement ps = con.prepareStatement(sql)) {                   ORDER BY o.created_at DESC;                                    - name: Pakete installieren                                     long sysNumber = rs.getLong("id");                             WHERE o.order_id IN (SELECT column_value
    ps.setString(1, "ACTIVE");                                                                                                              ansible.builtin.dnf:                                          if (sysNumber > 0 && rs.getString("name") != null) {                                  FROM TABLE(:id_list))
    ps.setTimestamp(2, Timestamp.valueOf(since));                         BEGIN                                                               name: [postgresql-server, postgresql-contrib]                    result.add(new DataRow(sysNumber, rs.getString("name")));     ORDER BY o.created_at DESC;
    try (ResultSet rs = ps.executeQuery()) {                                DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');                    state: present                                              }
        while (rs.next()) {                                               END;                                                            - name: Dienst starten                                      }                                                                 BEGIN
            long sysNumber = rs.getLong("id");                            /                                                                 ansible.builtin.systemd:                              }                                                                       DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
            if (sysNumber > 0 && rs.getString("name") != null) {          CREATE INDEX idx_activity_user_seen                                 name: postgresql                                } catch (SQLException e) {                                                END;
                result.add(new DataRow(sysNumber, rs.getString("name")));        ON activity (user_id, last_seen DESC);                          state: started                                      log.error("Abfrage fehlgeschlagen: {}", e.getMessage());              /
            }                                                                                                                                 enabled: true                                       throw new DataAccessException(e);                                     CREATE INDEX idx_activity_user_seen
        }                                                                 SELECT o.order_id, o.created_at, c.customer_name            - name: PostgreSQL bereitstellen                        }                                                                             ON activity (user_id, last_seen DESC);
    }                                                                       FROM orders o                                               hosts: db_server                                      String sql = "SELECT u.id, u.name, r.region_name, u.status "
} catch (SQLException e) {                                                  JOIN customers c ON c.customer_id = o.customer_id           become: true                                                     + "FROM users u "                                              SELECT o.order_id, o.created_at, c.customer_name
    log.error("Abfrage fehlgeschlagen: {}", e.getMessage());               WHERE o.order_id IN (SELECT column_value                     tasks:                                                           + "JOIN regions r ON r.id = u.region_id "                        FROM orders o
    throw new DataAccessException(e);                                                             FROM TABLE(:id_list))                   - name: Pakete installieren                                    + "LEFT JOIN activity a ON a.user_id = u.id "                    JOIN customers c ON c.customer_id = o.customer_id
}                                                                          ORDER BY o.created_at DESC;                                      ansible.builtin.dnf:                                         + "WHERE u.status = ? AND a.last_seen > ? "                     WHERE o.order_id IN (SELECT column_value
String sql = "SELECT u.id, u.name, r.region_name, u.status "                                                                                  name: [postgresql-server, postgresql-contrib]               + "ORDER BY r.region_name, u.name";                                                    FROM TABLE(:id_list))
           + "FROM users u "                                              BEGIN                                                               state: present                                  try (Connection con = dataSource.getConnection();                          ORDER BY o.created_at DESC;
           + "JOIN regions r ON r.id = u.region_id "                        DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');                - name: Dienst starten                                   PreparedStatement ps = con.prepareStatement(sql)) {
           + "LEFT JOIN activity a ON a.user_id = u.id "                  END;                                                              ansible.builtin.systemd:                              ps.setString(1, "ACTIVE");                                            BEGIN
           + "WHERE u.status = ? AND a.last_seen > ? "                    /                                                                   name: postgresql                                    ps.setTimestamp(2, Timestamp.valueOf(since));                           DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
           + "ORDER BY r.region_name, u.name";                            CREATE INDEX idx_activity_user_seen                                 state: started                                      try (ResultSet rs = ps.executeQuery()) {                              END;
try (Connection con = dataSource.getConnection();                             ON activity (user_id, last_seen DESC);                          enabled: true                                           while (rs.next()) {                                               /
     PreparedStatement ps = con.prepareStatement(sql)) {                                                                              - name: PostgreSQL bereitstellen                                    long sysNumber = rs.getLong("id");                            CREATE INDEX idx_activity_user_seen
    ps.setString(1, "ACTIVE");                                            SELECT o.order_id, o.created_at, c.customer_name              hosts: db_server                                                  if (sysNumber > 0 && rs.getString("name") != null) {              ON activity (user_id, last_seen DESC);
    ps.setTimestamp(2, Timestamp.valueOf(since));                           FROM orders o                                               become: true                                                          result.add(new DataRow(sysNumber, rs.getString("name")));
    try (ResultSet rs = ps.executeQuery()) {                                JOIN customers c ON c.customer_id = o.customer_id           tasks:                                                            }                                                             SELECT o.order_id, o.created_at, c.customer_name
        while (rs.next()) {                                                WHERE o.order_id IN (SELECT column_value                       - name: Pakete installieren                                 }                                                                   FROM orders o

Frank BergerIT-Dienstleistungen

Datenbanknahe IT-Systeme // souverän, offen, resilient

2009-01-15

· DataPump, PL/SQL

Oracle DataPump via PL/SQL – DBMS_DATAPUMP

Oracle DataPump hat sich bei vielen Anwendern aufgrund seiner Vorteile als Import/Export-Werkzeug durchgesetzt. Neben den Werkzeugen impdp und expdp lässt sich die DataPump auch via PL/SQL steuern. Hierzu dient das Package DBMS_DATAPUMP. Die Verwendung für einen Schema-Export zeigt folgendes Beispiel:

DECLARE
  v_DPHandle_n      NUMBER;
  v_TimeStamp_vc    VARCHAR2(15);
  v_JobName_vc      VARCHAR2(30);
  v_DumpFilename_vc VARCHAR2(50);
  v_LogFilename_vc  VARCHAR2(50);
  v_JobStatus_vc    VARCHAR2(50);
  v_SchemaName_vc   VARCHAR2(8)  := 'SCOTT';
  
  -- dieses Oracle-Verzeichnis muss evtl. vorher angelegt werden
  --
  -- CREATE OR REPLACE DIRECTORY DPUMP_TEST_DIR AS '/tmp';
  --
  -- evtl. muessen auch Schreib-/Lese-Rechte gesetzt werden
  --
  -- GRANT READ, WRITE ON DIRECTORY DPUMP_TEST_DIR TO orauser;
  --
  v_DumpDir_vc      VARCHAR2(20) := 'DPUMP_TEST_DIR';
BEGIN
  v_TimeStamp_vc    := TO_CHAR(sysdate, 'YYYYMMDD-HH24MISS');
  v_JobName_vc      := v_TimeStamp_vc || '_' || v_SchemaName_vc;
  
  -- wichtig ist der Platzhalter %U, je nach Parallelitaet werden so
  -- mehrere Dump-Files erzeugt
  v_DumpFilename_vc := v_SchemaName_vc || '_' || v_TimeStamp_vc || '_%U.dmp';
  v_LogFilename_vc  :=  v_SchemaName_vc || '_' || v_TimeStamp_vc || '.log';
    
  -- Datapump-Job oeffnen  
  v_DPHandle_n := DBMS_DATAPUMP.open(
    operation   => 'EXPORT',
    job_mode    => 'SCHEMA',
    job_name    => v_JobName_vc
  );

  -- Dump-File fuer den Job definieren
  DBMS_DATAPUMP.add_file(
    handle    => v_DPHandle_n,
    filename  => v_DumpFilename_vc,
    directory => v_DumpDir_vc
  );

  -- Log-File definieren
  DBMS_DATAPUMP.add_file(
    handle    => v_DPHandle_n,
    filename  => v_LogFilename_vc,
    directory => v_DumpDir_vc,
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
  );

  -- welches Schema soll exportiert werden?
  DBMS_DATAPUMP.metadata_filter(
    handle => v_DPHandle_n,
    name   => 'SCHEMA_EXPR',
    value  => '= ''' || v_SchemaName_vc || ''''
  );
    
  -- Parallelitaet auf 4 setzen  
  DBMS_DATAPUMP.SET_PARALLEL(
    handle => v_DPHandle_n,
    degree => 4
  );
    
  -- eigene Eintraege im Log-File sind moeglich
  DBMS_DATAPUMP.LOG_ENTRY(
    handle => v_DPHandle_n,
    message => '---> Schema Export via PL/SQL'
  );

  -- Job starten
  DBMS_DATAPUMP.start_job(v_DPHandle_n);
 
  -- auf das Job Ende wird normalerweise nicht gewartet
  -- mit WAIT_FOR_JOB laesst sich dies aber bei Bedarf
  -- realisieren
  DBMS_DATAPUMP.WAIT_FOR_JOB(
    handle => v_DPHandle_n,
    job_state => v_JobStatus_vc
  );
  
END;
/

Ein allgemeiner Hinweis zu Oracle DataPump:
DataPump-Prozesse laufen immer auf dem Oracle-Server!
Dies hat in der Praxis folgende Auswirkungen:

Der Betriebssystem-Benutzer, dem die Oracle-Server Prozesse zugeordnet sind, braucht Schreib-Rechte auf dem gewünschten Export-Verzeichnis (läuft die Datenbank-Instanz als Windows-Dienst und dem Benutzer LocalSystem ist kein Schreiben auf Netzwerk-Shares möglich, da dieser Benutzer keine Netzwerk-Shares sieht)
Es gibt keinen Client-/Server-Modus (z.B. Export eines Schemas von einem entfernten Oracle Server über einen lokalen Oracle Client)