ORA-39087: directory name DATA_PUMP_DIR is invalid

Oracle 12c introduced a new concept of a pluggable database. It comes with many interesting features as well as some nuances that we need to know to perform such tasks as data pump export and import.

The main surprise comes with an undocumented fact that Oracle’s default Data Pump directory object, DATA_PUMP_DIR, cannot be used for data pump export and import.

When you try to attempt running expdp or impdp utilities using DIRECTORY=DATA_PUMP_DIR, you should get the following error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

The solution is quite straightforward: do not use DATA_PUMP_DIR directory object for pluggable db.

You can create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Advertisements

An interview question that checks knowledge of Oracle SQL character functions.

Level: Beginner

Question: Find all employee names beginning with ‘S’ not using LIKE operator.

Method #1: INSTR function

SELECT ename
FROM emp
WHERE INSTR(ename, 'S')=1
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #2: Range Test

SELECT ename
FROM emp
WHERE ename>='S' AND ename<'T'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #3: SUBSTR function

SELECT ename
FROM emp
WHERE SUBSTR(ename, 1, 1) = 'S'
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #4: ASCII function

SELECT ename
FROM emp
WHERE ASCII(ename)=ASCII('S')
/

--Result:

ENAME
-------
SMITH
SCOTT

Workaround/Method #5: SUBSTR functions

SELECT ename
FROM emp
WHERE 'S' || SUBSTR(ename, 2) = ename
/

--Result:

ENAME
-------
SMITH
SCOTT

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to Simulate a Global Constant in Oracle PL/SQL?

Oracle PL/SQL does not support global constants, i.e. constants defined on the schema level.
You can define one in a package, which is typically used when you need to refer to a constant. However, such constant definitions look bulky as you always need to reference package name as a prefix to the constant name.

Deterministic functions allow you to mimic the constant use without sacrificing the performance.

Let’s see an example where we will define a “CONSTANT” that carried the date formatting string (for TO_DATE conversions):

CREATE OR REPLACE FUNCTION date_format 
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN 'yyyy-mm-dd hh24:mi:ss';
END date_format;
/

Now, you can use this function as if it were a globally defined constant:

SET SERVEROUTPUT ON

DECLARE
   v_date DATE;
BEGIN
   v_date:=TO_DATE('2015-11-14', date_format);
   DBMS_OUTPUT.PUT_LINE(v_date);
END;
/
14-NOV-15

PL/SQL procedure successfully completed.

According to Oracle documentation, DETERMINISTIC is a “hint that helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result.”

Since our function does not have any parameters, the optimizer will always return the previous (i.e. CONSTANT) result.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to mimic MINUS ALL and INTERSECT ALL in Oracle

Some databases, such as PostgreSQL and Teradata, natively support “ALL” keyword with SET operators MINUS (EXCEPT) and INTERSECT. As of current version, Oracle does not support these features; however, a little trick can help emulating the missing functionality.

Let’s see an example. We want to see job titles from department #20 that don’t exist in department #30.

SELECT job
FROM emp
WHERE deptno=20
MINUS
SELECT job
FROM emp
WHERE deptno=30;

--Result:

JOB
---------
ANALYST

If Oracle supported MINUS ALL the result of the following SQL would be quite different:

SELECT job
FROM emp
WHERE deptno=20
MINUS ALL
SELECT job
FROM emp
WHERE deptno=30;

--Result (possible):
JOB
---------
ANALYST
ANALYST
CLERK

The following trick with Analytical function ROW_NUMBER helps with Oracle SQL solution:

SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30;

JOB               RN
--------- ----------
ANALYST            1
ANALYST            2
CLERK              2

If we ignore (or hide by selecting from the above result) RN column, we would get exactly what we need:

WITH x AS (
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=20
MINUS
SELECT job, ROW_NUMBER()OVER(PARTITION BY job ORDER BY 1) rn
FROM emp
WHERE deptno=30
)
SELECT job
FROM x;

JOB
---------
ANALYST
ANALYST
CLERK

The same trick works fine with mimicking INTERSECT ALL statement.
The rule is simple:

SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...
INTERSECT
SELECT col1, col2, col3, ROW_NUMBER(PARTITION BY col1, col2, col3 ORDER BY 1) rn
FROM 
WHERE ...

Just make sure to list all selected columns in PARTITION BY clause.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

SQL puzzle: Find unique specialists in every department

Puzzle of the day.
This is a fairly simple problem but from time to time I am being approached by developers who need help with very similar problems.

Find all employees who has a unique job title in their respective department.

Solution #1: Using NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE NOT EXISTS(SELECT 1
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.job=b.job
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #1.1 – Generic substitution for NOT EXISTS

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job
           AND a.empno!=b.empno)
ORDER BY deptno, job

Solution #2: Using NOT IN

SELECT ename, deptno, job, sal
FROM emp a
WHERE job NOT IN(SELECT job
                 FROM emp b
                 WHERE a.deptno=b.deptno
                   AND a.empno!=b.empno)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #2.1: Using NOT IN with Multi-column subquery – it is pretty much the same as Solution #2.

SELECT ename, deptno, job, sal
FROM emp a
WHERE (job, deptno) NOT IN(SELECT job, deptno
                           FROM emp b
                           WHERE a.empno!=b.empno)
ORDER BY deptno, job

Solution #3.1: Using COUNT in subquery (very similar to Solution #1.1 but has different execution plan)

SELECT ename, deptno, job, sal
FROM emp a
WHERE 1=(SELECT COUNT(b.empno)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #3.2: A mixed version of Solutions #1.1 and #3.1:

SELECT ename, deptno, job, sal
FROM emp a
WHERE 0=(SELECT SUM(CASE WHEN a.empno=b.empno THEN 0 ELSE 1 END)
         FROM emp b
         WHERE a.deptno=b.deptno
           AND a.job=b.job)
ORDER BY deptno, job

Solution #4: Using Analytical function COUNT

WITH x AS (
SELECT ename, deptno, job, sal, COUNT(*) OVER(PARTITION BY deptno, job) cnt
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE cnt=1
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

Solution #4.1 – using MIN/MAX analytical functions – essentially, it is the same as solution #4

WITH x AS (
SELECT ename, deptno, job, sal, 
       MAX(empno) OVER(PARTITION BY deptno, job) max_no,
       MIN(empno) OVER(PARTITION BY deptno, job) min_no
FROM emp a
)
SELECT ename, deptno, job, sal
FROM x
WHERE max_no=min_no
ORDER BY deptno, job

Solution #5: Using In-Line view (WITH)

WITH x AS (
SELECT deptno, job
FROM emp
GROUP BY deptno, job
HAVING COUNT(*)=1
)
SELECT ename, deptno, job, sal
FROM emp JOIN x USING (deptno, job)
ORDER BY deptno, job

Result:

ENAME          DEPTNO JOB              SAL
---------- ---------- --------- ----------
MILLER             10 CLERK           1300
CLARK              10 MANAGER         2450
KING               10 PRESIDENT       5000
JONES              20 MANAGER         2975
JAMES              30 CLERK            950
BLAKE              30 MANAGER         2850

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to substitute ids with names in a delimited string with a single SELECT statement

Suppose that you have a comma delimited line of department numbers and you need to get a comma delimited line of department names. Apparently, you could write a PL/SQL block, loop through the list of departments and concatenate them in a single text string. However, with a new powerful built-in function listagg that was introduced in Oracle 11gR2 you can accomplish the task in a single SELECT statement.

The idea is to split the ids first using approach I presented in a previous post and then use LISTAGG function to assemble the result character string.

var department_numbers VARCHAR2(200)

exec :department_numbers:='10,20,40'

col dept_names for a80

WITH x AS (
SELECT REGEXP_SUBSTR(:department_numbers, '[^,]+', 1, LEVEL) deptno
FROM dual
CONNECT BY LEVEL <= LENGTH(:department_numbers)-LENGTH(REPLACE(:department_numbers,','))+1
)
SELECT listagg (dname, ', ') WITHIN GROUP (ORDER BY dname) AS dept_names
FROM dept d JOIN x ON d.deptno=x.deptno;

Result:

DEPT_NAMES
----------------------------------
ACCOUNTING, OPERATIONS, RESEARCH

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Analyze all objects in current schema

Since Oracle version 10, ANALYZE command has been deprecated for statistics gathering. The command stays in Oracle and is intended to be used to list chained rows and validate structure.

The ANALYZE command was superseded by procedures in dbms_stats package.

Specifically, if you need to collect statistics for a schema, you need to use dbms_stats.gather_schema_stats procedure.

If you want to run it for the current schema with all default parameters use this:

exec dbms_stats.gather_schema_stats(USER)

The full signature of this procedure:

DBMS_STATS.GATHER_SCHEMA_STATS ( 
   ownname          VARCHAR2, 
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE, 
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')), 
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')), 
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL, 
   options          VARCHAR2 DEFAULT 'GATHER', 
   objlist          OUT      ObjectTab,
   statown          VARCHAR2 DEFAULT NULL, 
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
  force             BOOLEAN DEFAULT FALSE);

Nuances when dropping an Oracle user, by Zahar Hilkevich

When you need to drop a user who is not currently connected to the database, the task is trivial (please do not try it if you don’t really need to drop your user):

DROP USER SCOTT CASCADE;

With actively used database user schemas,  the above command may fail due to active Oracle sessions opened by that user (ORA-01940: cannot drop a user that is currently connected). So you may want to drop all those sessions first before dropping the user:

DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    WHERE username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/

Seems like we are ready now to drop the user? Not so quick! While we were dropping existing sessions, the user might have opened more new sessions, so dropping a user may still fail with the same error:

ORA-01940: cannot drop a user that is currently connected

To work around this issue, we need to apply a simple trick: revoke a privilege to create a session before killing existing sessions! Here is a complete script (to be executed as SYS or SYSTEM):

REVOKE CONNECT FROM scott -- so new sessions will be blocked
/
--If CREATE SESSION privilege was granted to scott, we may want to revoke it as well
--Kill existing sessions:
DECLARE
    CURSOR c IS
    SELECT sid, serial#
    FROM v$session 
    where username='SCOTT';

    v_sql  VARCHAR2(200):='ALTER SYSTEM KILL SESSION ''{sid},{serial#}'' IMMEDIATE';
    v_kill VARCHAR2(200);
BEGIN
    FOR v IN c LOOP
        v_kill:=REPLACE(v_sql,'{sid}',v.SID);
        v_kill:=REPLACE(v_kill,'{serial#}',v.serial#);
        EXECUTE IMMEDIATE v_kill;
    END LOOP;
END;
/
--Drop the user
DROP USER SCOTT CASCADE
/

ORA-01578. Determine the name of the object with a corrupted data block.

If alert log file contains ORA-01578: ORACLE data block corrupted (file # x, block # xyz), you can determine the name of the object that has a corrupted data block:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &fileid
  AND &blockid BETWEEN block_id AND block_id + blocks - 1

Week_Start and Week_End custom functions in Oracle, by Zahar Hilkevich

Frequently, there is a need to obtain a start and end dates of a week for a given date. The following SQL can be used to get the first day of the current week:

SELECT TRUNC(SYSDATE, 'DAY') week_start
FROM dual

Result:

WEEK_STAR
---------
25-AUG-13

Note, that TRUNC(SYSDATE, ‘IW’) will always give us Monday as ISO year (and IW format points to it) always starts on Monday, so we cannot generically use this format.

Getting a week’s end is even simpler:

SELECT TRUNC(SYSDATE, 'DAY') week_start,
       TRUNC(SYSDATE, 'DAY') + 6 week_end
FROM dual

Result:

WEEK_STAR WEEK_END
--------- ---------
25-AUG-13 31-AUG-13

or if we want to see the day of the week:

SELECT TO_CHAR(TRUNC(SYSDATE, 'DAY'), 'MM/DD/YY DAY') week_start,
       TO_CHAR(TRUNC(SYSDATE, 'DAY') + 6, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ------------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s wrap it in PL/SQL functions:

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY');
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY')+6;
END week_end;
/
SELECT TO_CHAR(week_start, 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- -----------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s make things more complicated. What if a client wants a week to start with Saturday and end on Friday? For that matter, we will introduce a new function parameter: p_shift – it will control the shift from Sunday (or whatever your local setting is):

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE, 
                                      p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift;
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift+6;
END week_end;
/
SELECT TO_CHAR(week_start(p_shift=>1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/26/13 MONDAY      09/01/13 SUNDAY

The above functions can be created separately or together. If we always create both of them, we should eliminate redundant code and leverage existing:

CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    RETURN week_start(p_date, p_shift)+6;
END week_end;
SELECT TO_CHAR(week_start(p_shift=>-1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>-1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/24/13 SATURDAY    08/30/13 FRIDAY

Helpful reference:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm