Category Archives: SQL

External Tables with ORACLE_DATAPUMP Access Driver

Posted on by
Reply

Today I rediscovered a feature of Oracle that is in since 10i. In 12c it was extended.

What is it all about? I am able to use dumps that where created with data pump as a file for external tables. But not enough I can also create an external table using CTAS (create table as select). Let’s see the simple steps to do so.

First I need a directory to write into. There are two options. First I grant the right as seen below to the user in need or I create the directory with the privileged user and grant the right to the directory to the user.

GRANT CREATE ANY DIRECTORY TO SCOTT;

As SCOTT I may now create the need directory. Oracle needs access to that directory.

CREATE OR REPLACE DIRECTORY ext_dir AS '/u01/userhome/oracle/ext_tables';

Directory EXT_DIR created.

Next I may create the external table with the data pump access driver. This is done with “TYPE ORACLE_DATAPUMP”.

CREATE TABLE ext_emp
  ORGANIZATION EXTERNAL
    (
      TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY ext_dir
      LOCATION ('ext_emp.dmp')
    )
    AS SELECT * FROM emp;

Table EXT_EMP created.

Now I can already access the external table

SELECT * FROM ext_emp;

 EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM     DEPTNO
------ ---------- --------- ------ --------- ------ ------ ----------
  7369 SMITH      CLERK       7902 17-DEC-80    800                20
  7499 ALLEN      SALESMAN    7698 20-FEB-81   1600    300         30
  7521 WARD       SALESMAN    7698 22-FEB-81   1250    500         30
  7566 JONES      MANAGER     7839 02-APR-81   2975                20
  7654 MARTIN     SALESMAN    7698 28-SEP-81   1250   1400         30
  7698 BLAKE      MANAGER     7839 01-MAY-81   2850                30
  7782 CLARK      MANAGER     7839 09-JUN-81   2450                10
  7788 SCOTT      ANALYST     7566 19-APR-87   3000                20
  7839 KING       PRESIDENT        17-NOV-81   5000                10
  7844 TURNER     SALESMAN    7698 08-SEP-81   1500      0         30
  7876 ADAMS      CLERK       7788 23-MAY-87   1100                20
  7900 JAMES      CLERK       7698 03-DEC-81    950                30
  7902 FORD       ANALYST     7566 03-DEC-81   3000                20
  7934 MILLER     CLERK       7782 23-JAN-82   1300                10

14 rows selected.

The definition is the same as the table was.

DESC ext_emp

Name     Null? Type         
-------- ----- ------------ 
EMPNO          NUMBER(4)    
ENAME          VARCHAR2(10) 
JOB            VARCHAR2(9)  
MGR            NUMBER(4)    
HIREDATE       DATE         
SAL            NUMBER(7,2)  
COMM           NUMBER(7,2)  
DEPTNO         NUMBER(2)

Some additional thoughts:

  • No change of data possible. Once created it is read only
  • Parallel is possible
  • Compression is possible but only with “Advanced Compression”

It is a simple way to use external tables. No need to think about the format. No need to think about conversions. It is all done for you. Isn’t this great.

For more information just visit:
Oracle External Table Documentation
Oracle External Table Documentation – Oracle Data Pump Access Driver

Rollback – show progress

Posted on by
Reply

Hi,

as always I like to know how things are progressing especially when they are running long. This is also the case with ROLLBACK.

Here is a statement that helped me:

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
 WHERE s.saddr = t.ses_addr
   AND t.xidusn = r.usn
   AND rs.segment_id = t.xidusn
 ORDER BY t.used_ublk DESC
/

I am interested if there are better options.

Have fun.

Data Warehouse Blueprints

Posted on by
Reply

Ein Kollege von mir hat ein tolles DWH Buch veröffentlicht. Schaut doch mal rein. Ich kann es nur empfehlen!

Data Warehousing with Oracle

Our new book “Data Warehouse Blueprints” is now available at Hanser Verlag as printed edition and e-book. Here a short overview of the book and some background information about the long history of this publication.

View original post 540 more words

Literals: Alternative to single quote to start/end a string

Posted on by
Reply

As all know I can use single quotes to start/end a string in SQL and PL/SQL.

SELECT 'my string' AS a
  FROM dual;

A
---------
my string

When we have single quotes in a string we need an escape character. In Oracle this is another single quote. That looks like this.

SELECT 'my string with two single quotes for one '' ' AS a
  FROM dual;

A
-------------------------------------------
my string with two single quotes for one '

Complex strings can be difficult to handle this way. But Oracle provides another option.

SELECT q'[my string]' AS a
  FROM dual;

A
---------
my string

This way you can have as many single quotes in the string as you wish.
There are other options besides [] – {}, ().

SELECT q'{my 1' 2'' 3''' 4'''' string}' AS a
  FROM dual;

A
---------------------------
my 1' 2'' 3''' 4'''' string

I hope this helps.

Oracle Documentation 12c – Literals