Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 11 hours 52 min ago

ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

Wed, 2014-07-30 06:12

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.

Hopefully, the changes discussed here will help my code to become more readable ( and therefore maintainable) as well as looking a bit nicer.
William Robertson (from whom I plaigarised the title for this post) has some interesting things to say on PL/SQL coding style.

In this post, I’ve attempted to focus on stylistic topics that may affect code readability rather than just well established practices that I find annoying. Believe me, it was a bit of a struggle.

What I will be looking at is :

  • Uppercase Keywords
  • The use of camel case
  • The vexed question of ANSI Join syntax

Inevitably, what follows is likely to be highly subjective so feel free to disagree vehemently.

Uppercase Keywords

I started using SQL back in 1993, when the world was black and white.
My first experiences with Oracle was coding on a terminal emulator using vi.
Sure, you could change the screen colour if you were so minded, but syntax highlighting for PL/SQL was the stuff of science fiction.
The accepted way of distinguishing keywords was therefore, to type them in upper case.

--
-- Old style...
-- Keywords and built in packages are in uppercase.
-- brackets in the cursor for loop are not aligned, but the identation is consistent
--
DECLARE
    l_message VARCHAR2(50) := 'Happy New Millenium!';
BEGIN
    FOR r_emps IN (  
        SELECT first_name,  
        FROM hr.employees) 
    LOOP
        DBMS_OUTPUT.PUT_LINE('Message for '||r_emps.first_name||' - '||l_message);
    END LOOP;
END;
/

Needless to say, things have changed a bit since then. Not only do you have multiple Oracle IDEs to choose from, all but the most basic text editors will have syntax highlighting as standard. Of course many of them will have built-in highlighting for SQL rather than PL/SQL, but many such as Textpad and Gedit can easily be customised to suit.

One additional tweak to the next code example is that I’ve aligned the brackets in the way that you’d expect to see in other 3GLs. Apart from making bracket matching a bit easier, I think it looks a bit nicer…

--
-- All keywords and built-in packages are lowercase.
-- Syntax highlighting means that the keywords are still distinguishable...
--
declare
    l_message varchar2(50) := 'Happy New Millenium!';
begin
    for r_emps in
    (
        select first_name
        from hr.employees
    ) 
    loop
        dbms_output.put_line('Message for '||r_emps.first_name||' - '||l_message);
    end loop;
end;
/

…OK, WordPress doesn’t really do PL/SQL, so I’ll try to illustrate with a screenshot from Gedit :

My code in glorious technicolour

My code in glorious technicolour

We could make this code look even more funky and modern if, for example, we were to consider using…

Camel Case

Camel Case has never really gained wide acceptance in Oracle programming circles. I would suggest that the main reason for this is that Oracle stores object names, column names etc in the data dictionary in UPPERCASE.
Therefore, if you were to create a table with the following script…

Create Table Regions
(
    regionId Number,
    regionName Varchar2(25),
    longRegionDescription Varchar2(4000)
)
/

You may well be a bit surprised by the result of the following query…

select column_name, data_type
from user_tab_columns
where table_name = 'Regions'
/

no rows selected

Yes, in order to find the column details we need to specify the table name in upper case…

select column_name, data_type
from user_tab_columns
where table_name = 'REGIONS'
/

COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
REGIONID                       NUMBER
REGIONNAME                     VARCHAR2
LONGREGIONDESCRIPTION          VARCHAR2

Unless you want to go down the long and winding road of quoted identifiers, the underscore remains – in my opinion at least – the best way of separating words in identifiers.

Hang on a second, that’s probably fair enough as far as object names go, but what about variables ?

At this point, I have a confession to make. If I were to see something like this…

Declare
    lMessage varchar2(50) := 'Happy New Millenium!';
Begin
    For rEmps In
    (
        select first_name
        from hr.employees
    ) 
    Loop
        dbms_output.put_line('Message for '||rEmps.first_name||' - '||lMessage);
    End Loop;
End;
/

…my first reaction would be to assume that it was written by someone who normally codes in a language other than PL/SQL. Of course, this says rather more about me than the author of the code or, indeed, the code itself.

My justification for persisting with underscores in variable names can be reduced to two rather small fig leaves.

First of all, when declaring a variable for use to compare to a column value, I tend to keep to the name of the target column. For example :

create or replace function get_employee_name_fn
( 
    i_employee_id employees.employee_id%type
)
    return varchar2	
as
    l_last_name varchar2(100);
begin
    select last_name
    into l_last_name
    from employees
    where employee_id = i_employee_id;
    return l_last_name;
end;
/

The second is that, If I’m using underscores for some variables, it would look a bit odd if I then used Camel Case for others.

In an attempt to rid you of the unfortunate mental image you may have acquired with the fig leaves reference, I’ll move swiftly on to something that seems to ellicit strong opinions in the Oracle world…

ANSI Join syntax

In the vast majority of cases, if you’re writing PL/SQL then you will, at some point, need to write some SQL.

Back in Oracle 6, there was no Cost Based Optimizer. For the first several years of my using Oracle, tuning of SQL was done by understanding the 14 rules applied by the Rule Base Optimizer.
One key point was that a query was evaluated from the bottom up, so the ordering of the predicate was important.
For this reason, it became common practice to specify join conditions first, ahead of other, more restrictive conditions.
Of course, in those days, ANSI syntax was not available in Oracle, but it didn’t matter too much as you could be reasonably certain of where to look for join conditions in SQL. For example :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and reg.region_name = 'Europe'
order by coun.country_name, dept.department_name
/

In the period of time between the Cost Based Optimizer becoming de rigeur and the bugs in the ANSI syntax being ironed out in Oracle, a less structured coding style seems to have become prevalant.
Join conditions can now appear anywhere in the predicate list without any impact on performance…

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and reg.region_name = 'Europe'
and dept.location_id = loc.location_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and loc.country_id = coun.country_id
order by coun.country_name, dept.department_name
/
Inner Joins – ANSI Style

The ANSI syntax offers a solution to this by enforcing the separation of join conditions into a discreet clause in the query :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept
join employees emp
    on dept.manager_id = emp.employee_id
join locations loc
    on dept.location_id = loc.location_id
join countries coun
    on loc.country_id = coun.country_id
join regions reg
    on coun.region_id = reg.region_id
where reg.region_name = 'Europe'
and coun.country_name != 'Switzerland'
order by coun.country_name, dept.department_name
/

In terms of readability, unless you’re an old fogey like me, this would seem to be a significant improvement.

The ANSI syntax offers several variations on the theme of Inner Join. You can explicitly use the INNER keyword with your inner joins, although this would seem to be a bit superfluous as other join types would have to be stated explicitly in the code.

As well as the tradional…

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    on dept.department_id = emp.department_id
/

…you could specify a join between tables that have the same column name like this :

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    using(department_id)
/

Slightly less appealing is the NATURAL JOIN syntax, which assumes that you want to join on any and all matching column names between two tables.
If you run either of the previous two queries in the HR schema, they will return 106 rows.
However, the following query returns only 32 rows…

select dept.department_name, emp.last_name
from departments dept
natural join employees emp
/

As well as DEPARTMENT_ID, the tables also both contain a MANAGER_ID column. The above statement is therefore not, as you might think, the equivalent of the previous two, but is rather more …

select dept.department_name, emp.last_name
from departments dept
join employees emp
    using( department_id, manager_id)
/

With this in mind, I think I’ll steer clear of having my joins au naturel.

Outer Joins

This is one area where the ANSI syntax has a distinct advantage over it’s more venerable Oracle counterpart.
I’ve previously explored how it allows you to outer join a table multiple times in the same query.

Apart from this, it’s main distinguishing feature over the more traditional Oracle syntax is it’s sense of direction.

For a left outer join, tables are read left-to-right – i.e. the table you’re outer joining to is specified second :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept
left outer join employees emp
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

For a right outer join, the reverse is true, the table specified first is the one you’re outer joining to :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from employees emp
right outer join departments dept
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

Whilst having to know your left from right is something new to contend with, either of the above examples is more readable to someone familiar with SQL ( although not necessarily Oracle) than :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept, employees emp
where dept.department_id = emp.department_id(+)
group by dept.department_name
order by 2 desc, dept.department_name
/

In the past, there have been one or two issues with Oracle’s implementation of the ANSI join syntax. However, it now seems to be fairly stable and consistent.
Add this to it’s undoubted advantage in terms of readability and I’ve really rather run out of excuses not to use it.


Filed under: Oracle, PL/SQL, SQL Tagged: ansi join syntax, camel case, inner join, join using, LEFT OUTER JOIN, natural join, right outer join, uppercase keywords

DBMS_DATAPUMP – why quote delimiters are your friend

Mon, 2014-06-30 05:06

So, the World Cup is in full swing.
Now the lesser teams have fallen by the wayside ( England), we can get on with enjoying a feast of footie.
As well as a glut of goals, the current tournament has given us a salutory reminder of the importance of diet for elite athletes.
After predictably (and brilliantly) destroying England single-handedly, Luis Suaraz found himself a bit peckish and nipped out for an Italian. Now the whole world seems to be commenting on his eating habits.
Like Luis, you may find yourself thinking that you’ve bitten off more than you can chew when confronted by DBMS_DATAPUMP.

The documentation does offer some help…to an extent. However, the whole thing can seem a bit fiddly, especially if you’re used to the more traditional command-line interface for Datapump.

What follows is a tour through DBMS_DATAPUMP based on my own (sometimes painful) experience, broken down into bite-sized chunks.
Much of the functionality to filter object types and even data is common to both Exports and Imports.
So, the approach I’ve taken is to cover the Export first, with a view to finally producing a Full Database export.
I’ve then used the Import process against this to demonstrate some of the package’s filtering capabilities.
So, what’s on the menu today ?

  • Privileges required to run a DBMS_DATAPUMP job from your current schema and for the whole database
  • Running a consistent export
  • Running datapump jobs in the background
  • Monitoring running jobs
  • Importing from one schema to another
  • Specifying the types of objects to include in Exports and Imports
  • Specifying subsets of data
  • DDL only Jobs
  • How to Kill a Datapump Job

The full code examples have all been written and tested on Oracle XE 11gR2.
I’ve tried to maximise the use of in-line hard-coded values and minimise the number of variables in an attempt to make the code easier to follow.
Also, in these examples I’ve made use of the default DATA_PUMP_DIR directory object, but you can use any directory object to which you have the appropriate privileges.

For dessert, there are a couple of other DBMS_DATAPUMP features that I have found useful that are specific to Enterprise Edition ( in one case, with the Partitioning Option) ;

  • Including specific table partitions
  • Parallel processing

So, a fair bit to get through then. I hope you have an apetite…

Privileges required for using DBMS_DATAPUMP

Obviously, the first thing you need is execute privileges on DBMS_DATAPUMP. By default, this is granted to PUBLIC, although the more security conscious DBAs will have instituted more rigorous controls over just who gets access to this package.
The other thing you’ll need is READ and WRITE privileges on a database Directory Object.

DATA_PUMP_DIR

The DATA_PUMP_DIR directory object is created by default (but any old Directory Object will do)…

select directory_path
from dba_directories
where directory_name = 'DATA_PUMP_DIR'
/
DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/admin/XE/dpdump/

The HR user, for example, would need to be granted…

grant read, write on data_pump_dir to hr
/

That’s pretty-much it. With just these privileges( along with those for creating/altering the relevant object types), you can run DBMS_DATAPUMP to export/import objects and data in your current schema.

Once again, assuming we’re connected as HR :

set serveroutput on size unlimited
declare
--
-- Just export the current schema (HR)
--
    l_dph number; -- The datapump job handle
    l_status varchar2(4000); -- terminating status of the job
    
begin
    -- create the job...
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'SCHEMA', -- schema level export will use the current user by default
        job_name => 'HR_SCHEMA_EXPORT' -- appropriate job name
    );
    -- Specify the name and location of the export dump file we want to create...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_export.dmp',
        directory => 'DATA_PUMP_DIR', -- can use any database directory object
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1 -- if this file already exists, overwrite it
    );
    -- ...and a log file to track the progress of the export
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- Kick off the export
    dbms_datapump.start_job( handle => l_dph);

    -- ...and wait for the job to complete
    dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);

    dbms_output.put_line('Job done - status '||l_status);
end;
/

It’s probably worth noting that dbms_datapump jobs contain four main components as a rule.
First DBMS_DATAPUMP.OPEN creates a kind of container to hold the definition of the job.
Next, you specify any particular requirements for the job. This must always include reference to a dump file.
Then, use DBMS_DATAPUMP.START_JOB to kick-off the job you’ve defined.
Finally, you can either wait for the job to finish (as in this case), or leave it to run in the background. We’ll come onto background execution in a bit.

Roles required for Full Export/Import

If you want the God-Like powers to export/import the entire database then you will need the roles :

  • DATAPUMP_EXP_FULL_DATABASE
  • DATAPUMP_IMP_FULL_DATABASE

These roles are granted to the DBA role and the SYS user by default.

Incidentally, the fact that roles play quite a significant part in DBMS_DATAPUMP priviliges means that, if you do intend to wrap this functionality in a package, it would probably be wise to create it with Invoker Rights – e.g.

create or replace package my_datapump_package
    authid current_user as
...

Now that’s all sorted…

set serveroutput on size unlimited
declare
--
-- Do a no-nonsense full export 
--
    l_dph number; 
    l_status varchar2(4000); 
    
begin
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'FULL',
        job_name => 'MIKE_FULL_EXPORT' 
    );
    -- Specify the dump file we're going to create
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.dmp',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1
    );
    -- ... and a log file
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- Now press the big red shiny button...
    dbms_datapump.start_job( handle => l_dph);
    -- wait around for a bit...
    dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);
    dbms_output.put_line('Job done - status '||l_status);
end;
/

Pretty much the same as with the schema export we’ve already done. However, this time, we’ve specified the job_mode as FULL rather than SCHEMA.

With this script saved as full_exp_simple.sql…

SQL> @full_exp_simple.sql
Job done - status COMPLETED

PL/SQL procedure successfully completed.

SQL> 

Depending on the size of your database ( and the capacity of the server you’re running on), this may take a while.
When it’s all finished, you can wander over to the DATA_PUMP_DIR on the OS and you’ll see the fruit of your labours…

cd /u01/app/oracle/admin/XE/dpdump
ls -l my_full_export.*

-rw-r----- 1 oracle dba 321515520 Jun 18 19:55 my_full_export.dmp
-rw-r--r-- 1 oracle dba     84957 Jun 18 19:55 my_full_export.log

If we look at the logfile, we can see it starts like this :

Starting "MIKE"."MIKE_FULL_EXPORT":
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 349.9 MB
Processing object type DATABASE_EXPORT/TABLESPACE
...

… and ends with …

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "MIKE"."MIKE_FULL_EXPORT" successfully loaded/unloaded
******************************************************************************
Dump file set for MIKE.MIKE_FULL_EXPORT is:
  /u01/app/oracle/admin/XE/dpdump/my_full_export.dmp
Job "MIKE"."MIKE_FULL_EXPORT" successfully completed at 19:55:33

We now have an export. If, like me, you’ve just run this from a database where you know that there are no other active sessions, then you should have a nice consistent export. Unfortunately, this isn’t always the case…

Making your Export Read Consistent…and other tweaks

To adress this issue of consistency, we’ll need to change our script a bit. Whilst we’re at it, it would also be nice to have some record in the logfile of when the job started so we can work out how long it actually ran for.
Finally, we’d like to be able to run the export in the background so we don’t have to hang around waiting for it.

Consistent = Y – the DATAPUMP equivalent

Those of you old enough to remember the original export utility will recall that you could ensure that data in an export was referrentially consistent by the simple expedient of specifying consistent = Y in the exp command.
The equivalent in DBMS_DATAPUMP is to specify a value for FLASBACK_SCN.

NOTE – in order for this to work, your database must be running in ARCHIVELOG mode. This is especially relevant if you’re playing along on XE, which runs in NOARCHIVELOG by default.

To check the current status of archiving on the database :

select log_mode
from v$database
/

LOG_MODE
------------
ARCHIVELOG

If the query comes back NOARCHIVELOG then you need to enable archiving.
To do this, you need to connect as SYSDBA, shutdown and then mount the database, before starting archiving and then re-opening the database.
These steps can be achieved as follows once connected to the database as sysdba :

shutdown immediate;
startup mount;
alter database archivelog
/
alter database open
/

NOTE – these steps are intended if you are messing around in your own database ( e.g. XE on a laptop, like I am here). If you’re on a controlled environment, then you need to get your friendly neighbourhood DBA to do the honours.

Assuming the database is now in archivelog mode, the next thing we need to do is find SCN that we can pass to DBMS_DATAPUMP for it to use as a reference point for what we mean by consistent.
The SCN – System Change Number – is incremented every time a transaction completes in the database.
A quick demonstration is probably in order….

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774525

SQL> create table snacks( snack_name varchar2(30), description varchar2(4000));

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774562

SQL> insert into snacks( snack_name, description)
  2  values( 'CHIELLINI', 'Lite bite')
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774581

SQL> drop table snacks purge;

Table dropped.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    2774608

SQL> 

The fact that the SCN increments by more than one after each transaction completes in this session is explained by the transactions being run by the oracle background processes on my database.

Anyway, if we do want to find the current SCN, or even an SCN for a given time and date, we can simply use the
SQL TIMESTAMP_TO_SCN function :

select timestamp_to_scn( systimestamp)
from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
                       2647783

SQL> 

In order to pass this information to DBMS_DATAPUMP, we need to use the SET_PARAMETERS procedure.
This should do the trick :

...
dbms_datapump.set_parameter
(
    handle => the_datapump_job_handle,
    name => 'FLASHBACK_SCN',
    value => timestamp_to_scn( systimestamp)
);
...
Adding a message to the logfile

Next on our to-do list to improve our export is a message in the logfile recording when the export job started.
Oh look, there’s a package member called LOG_ENTRY. I wonder if that will help …

...
dbms_datapump.log_entry
(
    handle => the_datapump_job_handle,
    message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
...

Incidentally, we could include the SCN we’re specifying in the message as well ( although it would need to be captured in a variable before use in both the SET_PARAMETER call above and the LOG_ENTRY call).
One point to note – any entry in the logfile that results from a call to this procedure always starts with “;;;”.

Running the job in the background

Just in case you don’t fancy the idea of hanging around waiting for the job to finish, you can replace the call to WAIT_FOR_JOB with this…

...
dbms_datapump.detach( handle => the_datapump_job_handle);
...
The New, Improved Datapump Export Script

If we now apply all of these changes to our original script, it should look something like ….

set serveroutput on size unlimited
declare

    l_dph number; 
    l_scn number; -- The SCN from which the export will run    
begin
    -- setup the job
    l_dph := dbms_datapump.open
    (
        operation => 'EXPORT',
        job_mode => 'FULL',
        job_name => 'MIKE_FULL_EXPORT'
    );
    
    -- define the files
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.dmp',
        directory => 'DATA_PUMP_DIR', 
        filetype => dbms_datapump.ku$_file_type_dump_file,
        reusefile => 1 
    );
    
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'my_full_export.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    
    --
    -- Specify the SCN number to make sure that the export
    -- is a consistent copy
    --
    l_scn := timestamp_to_scn(systimestamp);
    dbms_datapump.set_parameter
    (
        handle => l_dph,
        name => 'FLASHBACK_SCN',
        value => l_scn
    );
        
    -- log the start time of the job
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')||' for SCN '||l_scn
    );
    
    -- start the job
    dbms_datapump.start_job( handle => l_dph);
   
    -- ...and leave it to run
    dbms_datapump.detach( handle => l_dph);
end;
/
Monitoring the job

The USER_DATAPUMP_JOBS view (there is also a DBA_ version available) contains details of any currently defined datapump jobs.

SQL> select job_name, state, attached_sessions
  2  from user_datapump_jobs;

JOB_NAME                       STATE                          ATTACHED_SESSIONS
------------------------------ ------------------------------ -----------------
MIKE_FULL_EXPORT               EXECUTING                                      1

SQL> 

Additionally, during the export, DATAPUMP will create a temporary table with the same name as the job.
In this table, you can see which object (if any) it’s currently working on…

select object_schema, object_name,
    work_item, 
    to_char(last_update, 'hh24:mi:ss') last_updated
from mike_full_export
where state = 'EXECUTING'
and object_schema is not null
and object_name is not null
/

There is an example of how to get “real-time” updates in the Oracle Documentation.
Unfortunately, it relies on DBMS_OUTPUT so the messages don’t get echoed to the screen until after the job is completed.

The simplest way to find out what’s happening right now is to check the logfile.
On Linux, for example, you could simply do this :

cd /u01/app/oracle/admin/XE/dpdump
tail -f my_full_export.log

If you haven’t got command line access to the database server, or simply prefer to keep everything in the database, then you could just create an external table based on the logfile. After all, you already have the required privileges on the directory …

create table datapump_log_xt
(
    line number,
    text varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory data_pump_dir
        access parameters
        (
            records delimited by newline
            nologfile
            fields terminated by whitespace
            (
                line recnum,
                text position(1:4000)
            )
        )
        location('')
    )
    reject limit unlimited
/

If we want to check progress, we can simply “tail” the file like this :

alter table datapump_log_xt location ('my_full_export.log')
/

select text
from datapump_log_xt
where line > ( select max(line) - 10 from datapump_log_xt)
order by line;

TEXT
--------------------------------------------------------------------------------
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

10 rows selected.

SQL> 

So far, we’ve looked exclusively at Exporting everything either from a given schema or an entire database.
Datapump also allows you to filter the data. Generally speaking, these filters apply whether you are importing or exporting.

Importing data from one schema to another

One of the many reasons that you may want to take a DataPump export may be to refresh a development environment.
It’s possible that the schema you’re refreshing on the Development database has a different name from the one on your Production environment. Let’s say, for the sake of argument, that we have a HR_DEV user in our development environment…

create user hr_dev identified by pwd
/

grant connect, resource, unlimited tablespace,
    create view, create sequence, create session,
    alter session, create synonym, create table, 
    create procedure
    to hr_dev
/

alter user hr_dev default tablespace users temporary tablespace temp
/

Fortunately, importing the HR objects in the export file into the HR_DEV user is fairly simple :

...
dbms_datapump.metadata_remap
(
    handle => the_datapump_job_handle,
    name => 'REMAP_SCHEMA'
    old_value => 'HR'
    value => 'HR_DEV'
);
...
Specifying schemas and object types

At this point, let’s say that we only want to import the HR schema. The rest of the objects in the export file aren’t really relevant to us.
Furthermore, let’s assume we only want to import Tables and Sequences as we’ll re-create all of our PL/SQL stored program units, views etc from source code files.

Filtering by schema

Allow me to introduce the METADATA_FILTER procedure :

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'SCHEMA_EXPR',
    value => q'[= 'HR']'
);
...

Once again, not as painful as it might appear. However, it’s here where we begin to see the benefits of quote delimiters.

Filtering by object types

Here’s where things get a bit more interesting.
Remember the export logfile. At the beginning, there were a number of entries like :

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
...

These paths are the basis for how datapump determines what to export.
Fortunately they are stored in some publicly available views :

  • DATABASE_EXPORT_OBJECTS
  • SCHEMA_EXPORT_OBJECTS
  • TABLE_EXPORT_OBJECTS

We’re doing a SCHEMA import so we can check to see that the relevant object_path is available to us by :

select object_path, comments
from schema_export_objects
where object_path in ('TABLE', 'SEQUENCE')
/

OBJECT_PATH	     COMMENTS
-------------------- --------------------------------------------------------------------------------
SEQUENCE	     Sequences in the selected schemas and their dependent grants and audits
TABLE		     Tables in the selected schemas and their dependent objects

The Path Map looks to be a flattened hierarchy ( possibly an XML representation). The point here is that, by specifying a node in this hierarchy, you can persuade DBMS_DATAPUMP to do your bidding.

As both TABLE and SEQUENCE are nodes in the object_path, we should be able to use that here…

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'INCLUDE_PATH_EXPR',
    value => q'[IN ('TABLE', 'SEQUENCE')]'
);
...

This will give us everything at the level at and below the TABLE and SEQUENCE nodes. Therefore, you will also get INDEXES for the tables…as well as Triggers.

select object_path, comments
from schema_export_objects
where object_path like '%TABLE%TRIGGER%'
/

OBJECT_PATH                    COMMENTS
------------------------------ ------------------------------
SCHEMA_EXPORT/TABLE/TRIGGER    Triggers
TABLE/TRIGGER                  Triggers

Remember, we don’t want any PL/SQL program units, so we need to filter these out. Fortunately, calls to the METADATA_FILTER procedure seem to be addative. As TRIGGER appears below TABLE in the hierarchy, we can filter them out with an additional call to the procedure :

...
dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'EXCLUDE_PATH_EXPR',
    value => q'[= 'TRIGGER']'  
);  
...
The finished HR Import Script

Here it is then, a script to Import Tables and Sequences from HR to HR_DEV :

set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- Only importing Tables and Sequences
--
    l_dph number;
    l_predicate varchar2(4000);
begin
    -- Setup the job "context" as usual
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_HR_DEV'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- and then only Sequences and Tables
	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'INCLUDE_PATH_EXPR',
	    value => q'[IN ('TABLE', 'SEQUENCE')]'
    );
    -- ...but no triggers...
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'EXCLUDE_PATH_EXPR',
        value => q'[= 'TRIGGER']'  
    );  
    -- and we want to import these objects into the HR_DEV schema
    dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    --
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
    --
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	-- Now point to the export dump file to take this from
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    -- ...and make sure we log what's going on...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    -- start the job...
    dbms_datapump.start_job( handle => l_dph);
    -- and detach...
    dbms_datapump.detach(handle => l_dph);
end;
/

Now to give it a whirl ( saved as hr_dev_imp.sql)….

SQL> @hr_dev_imp.sql

PL/SQL procedure successfully completed.

SQL> 

Looking at the logfile, all appears well…

;;; Job starting at 18:15:29
Master table "MIKE"."IMP_HR_DEV" successfully loaded/unloaded
Starting "MIKE"."IMP_HR_DEV":  
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HR_DEV"."COUNTRIES"                        6.367 KB      25 rows
. . imported "HR_DEV"."DEPARTMENTS"                      7.007 KB      27 rows
. . imported "HR_DEV"."EMPLOYEES"                        16.80 KB     107 rows
. . imported "HR_DEV"."JOBS"                             6.992 KB      19 rows
. . imported "HR_DEV"."JOB_HISTORY"                      7.054 KB      10 rows
. . imported "HR_DEV"."LOCATIONS"                        8.273 KB      23 rows
. . imported "HR_DEV"."REGIONS"                          5.476 KB       4 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "MIKE"."IMP_HR_DEV" successfully completed at 18:16:15

If we now connect as HR_DEV and check the objects we have in our schema :

select object_name, object_type
from user_objects
order by object_type, object_name
/

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
COUNTRY_C_ID_PK                INDEX
DEPT_ID_PK                     INDEX
DEPT_LOCATION_IX               INDEX
EMP_DEPARTMENT_IX              INDEX
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
JHIST_DEPARTMENT_IX            INDEX
JHIST_EMPLOYEE_IX              INDEX
JHIST_EMP_ID_ST_DATE_PK        INDEX
JHIST_JOB_IX                   INDEX
JOB_ID_PK                      INDEX
LOC_CITY_IX                    INDEX
LOC_COUNTRY_IX                 INDEX
LOC_ID_PK                      INDEX
LOC_STATE_PROVINCE_IX          INDEX
REG_ID_PK                      INDEX
DEPARTMENTS_SEQ                SEQUENCE
EMPLOYEES_SEQ                  SEQUENCE
LOCATIONS_SEQ                  SEQUENCE
COUNTRIES                      TABLE
DEPARTMENTS                    TABLE
EMPLOYEES                      TABLE
JOBS                           TABLE
JOB_HISTORY                    TABLE
LOCATIONS                      TABLE
REGIONS                        TABLE

29 rows selected.

SQL> 
Sequences are special

There is one thing to be aware of with importing sequences.
Most database objects ( procedure, packages, triggers, views etc), can be overwritten by using CREATE OR REPLACE.
Like tables, you cannot do this with Sequences.
Unlike tables, DBMS_DATAPUMP does not have an option to re-create existing sequences.
This means that, if we were to refresh the HR_DEV schema again using the same script, we’d be likely to run into a bit of a problem, as you can see from this log file entry :

...
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"HR_DEV"."LOCATIONS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR_DEV"."DEPARTMENTS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"HR_DEV"."EMPLOYEES_SEQ" already exists
...

With this in mind, it’s probably a good idea to drop any sequences prior to refreshing with an import…

set serveroutput on size unlimited
begin
    for r_seq in
    (
        select sequence_name
        from dba_sequences
        where sequence_owner = 'HR_DEV'
    )
    loop
        --
        -- check the sequence name is "clean"
        --
        if regexp_instr( replace( r_seq.sequence_name, '_'), '[[:punct:]]|[[:space:]]') > 0
        then
            raise_application_error( -20000, 'Sequence name contains dodgy characters.');
        end if;
        -- drop the sequence
        execute immediate 'drop sequence hr_dev.'||r_seq.sequence_name;
        dbms_output.put_line('Sequence '||r_seq.sequence_name||' dropped.');
    end loop;
end;
/

If we run this we’ll get…

SQL> @drop_sequences.sql
Sequence DEPARTMENTS_SEQ dropped.
Sequence EMPLOYEES_SEQ dropped.
Sequence LOCATIONS_SEQ dropped.

PL/SQL procedure successfully completed.

SQL> 
Specifying a subset of data

Sometimes you don’t want to export/import everything. You might just want a few tables with a subset of data.
In our HR_DEV environment we want to focus on the EMPLOYEES table. We don’t want all of the rows – let’s just have one department…

Specifying which tables to use

Once again, METADATA_FILTER can be used here :

dbms_datapump.metadata_filter
(
    handle => the_datapump_job_handle,
    name => 'NAME_LIST',
    value => q'['EMPLOYEES']',
    object_path => 'TABLE'
);

NAME_LIST tells datapump to look for object names rather than in object type paths.
Specifying the object type path as TABLE means that datapump will only look for a table called EMPLOYEES and not any other type of object with the same name.

Getting a subset of data

Here, we need to use the DATA_FILTER procedure. Unlike it’s METADATA counterpart, you need to provide a syntactically correct predicate for it to work…

...
dbms_datapump.data_filter
(
    handle => the_datapump_job_handle,
    name => 'SUBQUERY',
    value => 'where department_id = 20',
    table_name => 'EMPLOYEES',
    schema_name => 'HR'
);
...
The Data Sub-set Import Script

Before running this, I’ve taken the precaution of dropping all of the objects from the HR_DEV schema as I don’t want to run into any pesky constraint errors…

set serveroutput on size unlimited
declare
--
-- import Subset of employees into HR_DEV ( having cleared down the schema first)
--
    l_dph number;
begin
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_EMPLOYEES'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- only TABLES...
	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'INCLUDE_PATH_EXPR',
	    value => q'[= 'TABLE']'
    );
    -- and then only the EMPLOYEES table
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'NAME_LIST',
        value => q'['EMPLOYEES']',
        object_path => 'TABLE'
    );
    -- without any triggers or ref constraints
    	dbms_datapump.metadata_filter
	(
	    handle => l_dph,
	    name => 'EXCLUDE_PATH_EXPR',
	    value => q'[IN ('TRIGGER', 'REF_CONSTRAINT')]'
    );

    -- subset of EMPLOYEES
    dbms_datapump.data_filter
    (
        handle => l_dph,
        name => 'SUBQUERY',
        value => 'where department_id = 20',
        table_name => 'EMPLOYEES',
        schema_name => 'HR'
    );
        dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    --
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
    --
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	--
	-- Now point to the export dump file to take this from
	--
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    --
    -- ...and make sure we log what's going on...
    --
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_employees_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    --
    -- start the job...
    --
    dbms_datapump.start_job( handle => l_dph);
    --
    -- and detach...
    --
    dbms_datapump.detach(handle => l_dph);
end;
/	

Notice that I’ve also specifically excluded the REF_CONSTRAINTS and TRIGGERS from the import.
Run this and we get the following output in the logfile :

;;; Job starting at 18:35:49
Master table "MIKE"."IMP_EMPLOYEES" successfully loaded/unloaded
Starting "MIKE"."IMP_EMPLOYEES":  
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HR_DEV"."EMPLOYEES"                        16.80 KB       2 out of 107 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "MIKE"."IMP_EMPLOYEES" successfully completed at 18:36:33

We can see that only the EMPLOYEES table and it’s associated indexes have been imported :

select object_name, object_type
from user_objects
order by object_type, object_name
  4  /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
EMP_DEPARTMENT_IX              INDEX
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
EMPLOYEES                      TABLE

7 rows selected.

SQL> 

…and no Foreign Key constraints :

SQL> select table_name, constraint_name, constraint_type
  2  from user_constraints;

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
EMPLOYEES                      EMP_LAST_NAME_NN               C
EMPLOYEES                      EMP_EMAIL_NN                   C
EMPLOYEES                      EMP_HIRE_DATE_NN               C
EMPLOYEES                      EMP_JOB_NN                     C
EMPLOYEES                      EMP_EMP_ID_PK                  P
EMPLOYEES                      EMP_EMAIL_UK                   U
EMPLOYEES                      EMP_SALARY_MIN                 C

7 rows selected.

SQL> 

and only those EMPLOYEES in DEPARTMENT_ID 20…

select last_name, department_id
from employees
/

LAST_NAME                 DEPARTMENT_ID
------------------------- -------------
Hartstein                            20
Fay                                  20

SQL> 
DDL_ONLY Datapump operations

Once again the DATA_FILTER procedure comes in here. However, this time, the call is a bit different :

...
dbms_datapump.data_filter
(
	handle => the_datapump_job_handle,
	name => 'INCLUDE_ROWS',
	value => 0
);
...

Here the value is effectively a boolean – 0 = false i.e. don’t include rows. The default is 1 – do include rows.
Incidentally, this time I’ve dropped the user HR_DEV altogether before importing as it will be re-created by the Import if it does not already exist.
This next script will import all of the DDL from HR to HR_DEV, but will not import any data…

set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- This time all DDL, but no data
--
    l_dph number;
begin
    l_dph := dbms_datapump.open
    (
        operation => 'IMPORT',
        job_mode => 'SCHEMA',
        job_name => 'IMP_DDL_HR_DEV'
    );
    -- We only want objects in the HR schema
    dbms_datapump.metadata_filter
    (
        handle => l_dph,
        name => 'SCHEMA_EXPR',
        value => q'[= 'HR']'
    );
    -- but no data
    dbms_datapump.data_filter
    (
	    handle => l_dph,
	    name => 'INCLUDE_ROWS',
	    value => 0
    );
    --
    -- and we want to import these objects into the HR_DEV schema
    --
    dbms_datapump.metadata_remap
    (
        handle => l_dph,
        name => 'REMAP_SCHEMA',
        old_value => 'HR',
        value => 'HR_DEV'
    );
    -- If we find a table that already exists in the target
    -- schema then overwrite it..
	dbms_datapump.set_parameter
	(
		handle => l_dph,
		name => 'TABLE_EXISTS_ACTION',
		value => 'REPLACE'
	);
	-- Now point to the export dump file to take this from
	dbms_datapump.add_file
	(
	    handle => l_dph,
	    filename => 'my_full_export.dmp',
	    directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_dump_file
    );
    -- ...and make sure we log what's going on...
    dbms_datapump.add_file
    (
        handle => l_dph,
        filename => 'hr_dev_ddl_imp.log',
        directory => 'DATA_PUMP_DIR',
        filetype => dbms_datapump.ku$_file_type_log_file,
        reusefile => 1
    );
    -- log the start time...
    dbms_datapump.log_entry
    (
        handle => l_dph,
        message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
    );
    -- start the job...
    dbms_datapump.start_job( handle => l_dph);
    -- and detach...
    dbms_datapump.detach(handle => l_dph);
end;
/

After running this we find that the HR_DEV user has been created. However, you will need to connect using the password of the HR user included in the export.

We can see now that all of the HR objects have been imported into HR_DEV :

select object_name, object_type
from user_objects
order by object_type, object_name
/

OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------
ADD_JOB_HISTORY                PROCEDURE
COUNTRIES                      TABLE
COUNTRY_C_ID_PK                INDEX
DEPARTMENTS                    TABLE
DEPARTMENTS_SEQ                SEQUENCE
DEPT_ID_PK                     INDEX
DEPT_LOCATION_IX               INDEX
EMPLOYEES                      TABLE
EMPLOYEES_SEQ                  SEQUENCE
EMP_DEPARTMENT_IX              INDEX
EMP_DETAILS_VIEW               VIEW
EMP_EMAIL_UK                   INDEX
EMP_EMP_ID_PK                  INDEX
EMP_JOB_IX                     INDEX
EMP_MANAGER_IX                 INDEX
EMP_NAME_IX                    INDEX
JHIST_DEPARTMENT_IX            INDEX
JHIST_EMPLOYEE_IX              INDEX
JHIST_EMP_ID_ST_DATE_PK        INDEX
JHIST_JOB_IX                   INDEX
JOBS                           TABLE
JOB_HISTORY                    TABLE
JOB_ID_PK                      INDEX
LOCATIONS                      TABLE
LOCATIONS_SEQ                  SEQUENCE
LOC_CITY_IX                    INDEX
LOC_COUNTRY_IX                 INDEX
LOC_ID_PK                      INDEX
LOC_STATE_PROVINCE_IX          INDEX
REGIONS                        TABLE
REG_ID_PK                      INDEX
SECURE_DML                     PROCEDURE
SECURE_EMPLOYEES               TRIGGER
UPDATE_JOB_HISTORY             TRIGGER
USER_FK_TREE_VW                VIEW

35 rows selected.

SQL> 

…and just to prove that we’ve imported no data at all…

SQL> select count(*) from regions;

  COUNT(*)
----------
         0

SQL> select count(*) from locations;

  COUNT(*)
----------
         0

SQL> select count(*) from departments;

  COUNT(*)
----------
         0

SQL> select count(*) from jobs;

  COUNT(*)
----------
         0

SQL> select count(*) from job_history;

  COUNT(*)
----------
         0

SQL> select count(*) from employees;

  COUNT(*)
----------
         0

SQL> select count(*) from countries;

  COUNT(*)
----------
         0

SQL> 
Killing a runaway job

There will be times when things don’t quite work as expected. This is especially true if you’re playing around with Datapump parameter settings.
Just occasionally when testing the code in this post I’ve come across some variation on the theme of …

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5283
ORA-06512: at line 11

Yep, I’v fixed my runtime error, re-executed the scrpt and got this insistent little message.
The first thing to do when you get this ( especially if you’re running in the same session that you started the original job) is to logout and log back in again.

The second thing to do is to check to see if the job is still hanging about…

user
select job_name, state
from user_datapump_jobs
/

If so, then you can attach to the job and stop it.
If I’m connected as the job owner and I’m only running the one job, the following script should do the trick…

set serveroutput on size unlimited
declare
      l_dph number;
      l_job_name user_datapump_jobs.job_name%type;
begin
    select job_name into l_job_name from user_datapump_jobs;
    dbms_output.put_line('Killing '||l_job_name);
    l_dph := dbms_datapump.attach( job_name => l_job_name, job_owner => user);
    dbms_datapump.stop_job( l_dph);
    dbms_output.put_line('Job '||l_job_name||' terminated.');
exception
    when no_data_found then
        dbms_output.put_line('Job has already terminated.');
end;
/

This should work almost every time. On the off-chance it doesn’t, and the job does not appear in the USER_DATAPUMP_JOBS view anymore, then it’s possible that the temporary table created as part of the job has not been tidied up.
Remember, the temporary table is created in the job owner’s schema with the same name as the datapump job itself.
So, if I were to hit this problem whilst running the job IMP_HR_DEV, I could check :

select 1
from user_tables
where table_name = 'IMP_HR_DEV'
/

If the table does still exist and there is no entry in the _DATAPUMP_JOB view, we can simply drop it :

drop table imp_hr_dev
/

This should finally persuade datapump that the job is not in fact running.

A couple of Enterprise Edition Features

For those of you on a richer diet, there are a couple of further morsels which may well be worthy of consideration.

If you want to speed up your Datapump job by making use of the CPU cores available you could try :

...
dbms_datapump.set_parallel
(
	handle => the_datapump_job_handle,
	degree => 32
);
...

If you’ve really pushed the boat out and have the partitioning option, you can tell datapump to take only specific partitions for tables.

For example, say you have a number of tables that are partitioned by date ranges.
The tables may be partitioned by quarter-year, with the partitions following a consistend naming convention (e.g. Q12014, Q22014 etc).
If we only want to export/import the latest partition for each of these tables( say Q22014), we can do something like this :

...
for r_part_tabs in
(
	select table_name
	from user_tab_partitions
	where partition_name = 'Q22014'
)
loop
	dbms_datapump.data_filter
	(
		handle => the_datapump_job_handle,
		name => 'PARTITION_EXPR',
		value => q'[= 'Q22014']',
		table_name => r_part_tabs.table_name
	);
end loop
...

Despite having gorged ourselves on this feast of DBMS_DATAPUMP delights, it’s fair to say that I’ve by no means covered everything.
Doubtless you will want to do things with this API that I’ve not covered here. Indeed, you’ll probably also find better ways of implementing some of the same functionality.
In the meantime, I’m off for a digestif.
Bon Apetite.


Filed under: Oracle, PL/SQL Tagged: archivelog mode, database_export_objects, dba_datapump_jobs, dbms_datapump examples, dbms_datapump.add_file, dbms_datapump.data_filter, dbms_datapump.detach, dbms_datapump.log_entry, dbms_datapump.metadata_filter, dbms_datapump.metadata_remap, dbms_datapump.open, dbms_datapump.set_parallel, dbms_datapump.set_parameter, dbms_datapump.start_job, dbms_datapump.stop_job, dbms_datapump.wait_for_job, DDL Only import, drop sequence, EXCLUDE_PATH_EXPR, external table, flashback_scn, INCLUDE_PATH_EXPR, INCLUDE_ROWS, NAME_LIST, ora-31634, PARTITION_EXPR, REF_CONSTRAINTS, remap_schema, Running a Datapump job in the background, schema_export_objects, SCHEMA_EXPR, scn, SUBQUERY, TABLE_EXISTS_ACTION, table_export_objects, tail a logfile from sql, timestamp_to_scn, user_datapump_jobs, v$database.current_scn, v$database.log_mode

Oracle’s hidden documentation – Commenting the Data Model in Oracle

Sun, 2014-06-08 06:55

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain.

OMG, WTF does GD Mean ?

Whilst the English may have begun to wonder whether God wasn’t actually Scottish ( or even Welsh for that matter), the Brazilians swept all before them in the final round robin.
In the last match of the tournament, at the new footballing cathederal of the Maracana, over 200,000 people turned up to watch what the whole nation assumed would be their coronation of the new World Champions.
Apparently, no-one bothered to mention this to Uruguay.
Coming from a goal down, Uruguay won the match 2-1 and with it, the World Cup.
In Brazil this game is still known as Maracanaço – The Maracana Blow.
All of which brings me to the matter at hand.
Abbreviations in both Oracle table names and column names are fairly common. After all, you only get 30 characters to play with for each ( unless you want to go down the long and winding road of quoted identifiers). An example of this might be :

create table final_pool_standings
(
    team varchar2(30) constraint fps_pk primary key,
    pld number(1),
    w number(1),
    d number(1),
    l number(1),
    gf number(2),
    ga number(2),
    pts number(1),
    gd as (gf - ga)
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'URUGUAY', 3, 2, 1, 
    0, 7, 5, 5
)
/
   
insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'BRAZIL', 3, 2, 0,
    1, 14, 4, 4
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SWEDEN', 3, 1, 0,
    2, 6, 11, 2
)
/

insert into final_pool_standings
(
    team, pld, w, d,
    l, gf, ga, pts
)
values
(
    'SPAIN', 3, 0, 1,
    2, 4, 11, 1
)
/

commit;

Now, whilst for a footie fan( well, an English-speaking footie fan), the column names here are pretty much self explainatory, someone who is not quite so familiar with common abbreviations and acronyms associated with football may do a bit of head-scratching when presented with this table.

To be fair, it’s the same when looking at any data structure using abbreviations and acronyms.
In baseball you have GB (Games Back). In cricket, even more confusingly, you have Strike Rate, which means different things in the context of batting averages and bowling averages.
Fortunately, Oracle provides a mechanism to overcome this apparent failure in the QA processes for this particular data model.


Comment on Table

I think the first thing we need to do is to explain what the FINAL_POOL_STANDINGS table is all about :

comment on table final_pool_standings is
    'The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS'
/

Note that the comment itself is a string-literal so it’s not possible to build a concatenated string. On the plus side however, you do get up to 4000 characters to begin to de-mistify your data model.

The _TAB_COMMENTS views provide a means of interrogating table comments :

select comments
from user_tab_comments
where table_name = 'FINAL_POOL_STANDINGS'
/

COMMENTS
------------------------------------------------------------------------------------------
The 1950 World Cup round-robin group to decide the title. Table Short Name : FPS

Unravelling the GD mystery- Column Comments

Just as useful, if not more so, is the ability to comment on individual columns :

comment on column final_pool_standings.team is
    'The national team. Primary Key'
/

comment on column final_pool_standings.pld is
    'Games played in the final pool stage'
/

comment on column final_pool_standings.w is
    'Games won'
/

comment on column final_pool_standings.d is
    'Games drawn'
/

comment on column final_pool_standings.l is
    'Games lost'
/

comment on column final_pool_standings.gf is
    'Goals For - number of goals scored in final pool matches'
/

comment on column final_pool_standings.ga is
    'Goals Against - number of goals conceded in final pool matches'
/

comment on column final_pool_standings.pts is
    'Points gained in final pool matches - two for a win, one for a draw, nothing for a loss'
/

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column'
/

Most IDE’s actually display column comments in-line with the column definitions so they are easy to spot.
You can also retrieve them from the data dictionary directly :

select column_name, comments
from user_col_comments
where table_name = 'FINAL_POOL_STANDINGS'
/
COLUMN_NAME		       COMMENTS
------------------------------ ------------------------------------------------------------------------------------------
TEAM			       The national team. Primary Key
PLD			       Games played in the final pool stage
W			       Games won
D			       Games drawn
L			       Games lost
GF			       Goals For - number of goals scored in final pool matches
GA			       Goals Against - number of goals conceded in final pool matches
PTS			       Points gained in final pool matches - two for a win, one for a draw, nothing for a loss
GD			       Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column

This sort of information may well be held in whichever modelling tool you may or may not be using. However, there’s no substitute for having these sorts of comments in the database itself, especially if you happen to be the one trying to resolve an urgent support call on a job that’s accessing a data model with which you are unfamiliar.

Amending and deleting comments

If you want to edit a comment, you simply replace it with another string literal.
For example, if I wanted to expand on the comment for the GD column :

comment on column final_pool_standings.gd is
    'Goal Difference - Goals scored (GF) minus goals conceded (GA). Virtual column. In truth this is slightly anachronistic as Goal Difference was not used to rank teams on the same number of points in this tournament.'
/

If you want to remove a comment altogether, you simply need to set it to a NULL string :

comment on column final_pool_standings.w is 
    ''
/

Even if a table or column has no comment associated with it, it does still have an entry in the _COMMENTS views.

Hidden documentation – Comments on Data Dictionary Views

Oracle is way ahead of you here. It’s been using the comments functionality for tables and views for years.
Not sure what USER_TAB_PRIVS is for (or even, whether or not it’s a table) ?…

select table_type, comments
from dba_tab_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

TABLE_TYPE  COMMENTS
----------- --------------------------------------------------------------------------------
VIEW        Grants on objects for which the user is the owner, grantor or grantee

SQL> 

You can also find out more about the differences between the USER, ALL, and DBA versions of a view :

select table_name, comments
from dba_tab_comments
where table_name like '%\_TAB_PRIVS' escape '\'
/

TABLE_NAME	     COMMENTS
-------------------- ------------------------------------------------------------------------------------------
USER_TAB_PRIVS	     Grants on objects for which the user is the owner, grantor or grantee
ALL_TAB_PRIVS	     Grants on objects for which the user is the grantor, grantee, owner,
		      or an enabled role or PUBLIC is the grantee

DBA_TAB_PRIVS	     All grants on objects in the database
ROLE_TAB_PRIVS	     Table privileges granted to roles

Column comments are also included :

select column_name, comments
from dba_col_comments
where owner = 'SYS'
and table_name = 'USER_TAB_PRIVS'
/

COLUMN_NAME                    COMMENTS
------------------------------ --------------------------------------------------------------------------------
GRANTEE                        Name of the user to whom access was granted
OWNER                          Owner of the object
TABLE_NAME                     Name of the object
GRANTOR                        Name of the user who performed the grant
PRIVILEGE                      Table Privilege
GRANTABLE                      Privilege is grantable
HIERARCHY                      Privilege is with hierarchy option

7 rows selected.

SQL> 

There are hundreds of data dictionary objects that are documented in this way.
These do seem to be mainly the USER, ALL and DBA views – V$ views are noticable by their absence. However, the ones that are there do offer a significant amount of useful documentation.

OK. Time to review my World Cup Essentials checklist …

  • World Cup Wall Chart and TV Guide – check
  • Realistic Expectations – check
  • England Penalty shoot-out blind-fold ( just in case) – I’m not sure I’ll be needing that

Filed under: Oracle, SQL Tagged: COMMENT ON COLUMN, comment on table, dba_col_comments, dba_tab_comments, editing table and column comments, escape, escaping a string in a query predicate, user_col_comments, user_tab_comments

Putting the Plus into SQL*Plus – describing a Package member (with added kittens)

Sat, 2014-05-31 10:05

Deb’s been on about getting another cat recently.
“Absolutely not !”, I said, putting my foot down very firmly.
Oh yes, we all know who wears the trousers in this house thank you very much.
So…Cleo the Kitten is now making herself at home.

In fact, she’s on the desk now, sniffing at the key..$#%3″!Q…make that running across the keyboard.
Obviously, she’s not quite mastered the basics of shell scripting yet ( although she does evidently know that it employs most of the keys on the top row of the keyboard).
Maybe we need to start her off with something a little less complicated.
Not however, the sanctity of those cute and fluffy Oracle IDEs, I’ve had quite enough cute and fluffy for one day.
Instead we’re going to have a look at the grizzled old moggy that is SQL*Plus.

In my defence, I will say that the use of SQL*Plus is a something of a necessity for me as I spend a lot of time on the train and so write much of my ramblings on a small, elderly netbook.
I’ve got XE running on it so firing up an IDE will slow things down considerably.
And yes, OK, I did start programming before the World Wide Web and GUI desktops…but no, not before the invention of the internal combustion engine, Deborah. Honestly, haven’t you got a kitten you should be playing with ?

My starting point is simply this – wouldn’t it be great if you could use the DESCRIBE function to lookup a single member of a package in SQL*Plus.

So, what I’m going to cover here is :

  • How to get argument details for package members from the Data Dictionary
  • Building a pipelined function ( using a cursor for loop)
  • Mimicing the DESCRIBE functionality using SQL*Plus structures and formatting

Right, stop sharpening your claws on that sofa and pay attention…

Describing Packages

Say that I want to check the signature of DBMS_METADATA.GET_DDL.
If I’m in an IDE, I can simply type the package name, press the appropriate short-cut key to bring up the package signature and then search for the member of interest ( in this case the GET_DDL function).
In SQL*Plus however, it’s that last bit that’s somewhat problematic…

desc dbms_metadata

Run this and you will get reams of data spooled to the screen. This is not surprising. DBMS_METADATA contains a lot of members :

select count(procedure_name)
from dba_procedures
where owner = 'SYS'
and object_name = 'DBMS_METADATA'
/

COUNT(PROCEDURE_NAME)
---------------------
		   86

It’s by no means the largest package in terms of members ( DBMS_BACKUP_RESTORE has over 300, for example).
If we just want to examine the signature for the GET_DDL function, we’ll need to write some SQL. Fortunately, all of the information we need is contained in DBA_ARGUMENTS :

select argument_name, data_type, in_out, defaulted
from dba_arguments
where package_name = 'DBMS_METADATA'
and object_name = 'GET_DDL'
and data_level = 0
order by sequence
/

ARGUMENT_NAME		       DATA_TYPE		      IN_OUT	D
------------------------------ ------------------------------ --------- -
			       CLOB			      OUT	N
OBJECT_TYPE		       VARCHAR2 		      IN	N
NAME			       VARCHAR2 		      IN	N
SCHEMA			       VARCHAR2 		      IN	Y
VERSION 		       VARCHAR2 		      IN	Y
MODEL			       VARCHAR2 		      IN	Y
TRANSFORM		       VARCHAR2 		      IN	Y

The first argument has a NULL argument_name, but we can see that it’s an OUT parameter. This is, in fact, the return value of the function.

Incidentally, if you have a look at DBA_ARGUMENTS, you’ll notice that there is a DEFAULTED_VALUE column, Unfortunately, up to and including 11gR2, this is always blank. According to the Oracle Documentation, this column is “Reserved for future use”.

Anyway, we now have a relatively simple query that we can use to get the information we want. Now we need to make it a bit more convenient to access. After all, we don’t want to have to type the query out every time if we can help it.

The Pipelined function

In order to make this information a bit easier to get at (i.e. avoid the need to type out a query such as the one above every time), let’s create a pipelined function.

First of all, we’ll define some types :

create or replace type proc_desc_typ as object
(
    object_type varchar2(20),
    object_name varchar2(30),
    overload varchar2(40),
    argument_name varchar2(30),
    data_type varchar2(30),
    in_out varchar2(9),
    defaulted varchar2(1)
)
/

create or replace type proc_desc_tab_typ is table of proc_desc_typ
/

…and then the function itself.
Incidentally, I did write something a while ago on the subject of pipelined functions.
In the course of my reasearch, I noticed that all of the examples of pipelined functions use explicit cursors.
So, just for variety, I’m going to use a cursor for loop here, just to prove that it works.

create or replace function desc_plus_proc
(
    i_package all_arguments.package_name%type,
    i_member all_arguments.object_name%type default null,
    i_owner all_arguments.owner%type default null
)
    return proc_desc_tab_typ pipelined
    authid current_user
is
    l_owner all_arguments.owner%type;
    l_package all_arguments.package_name%type;
    
    cursor c_syns( cp_package all_synonyms.synonym_name%type) is    
        select table_owner, table_name
        from all_synonyms
        where owner in ('PUBLIC', user)
        and synonym_name = cp_package;
    
    l_is_synonym boolean := false;
    
    l_current_member all_arguments.object_name%type := null;
    l_current_overload all_arguments.overload%type := null;
    l_current_type varchar2(20) := null;
    
    l_rtn proc_desc_typ := proc_desc_typ(null, null, null, null, null, null, null);
    
begin
    --
    -- i_package is the only mandatory parameter
    --
    if i_package is null then
        raise_application_error(-20000, 'Must provide a package name ');
    end if;
    --
    -- Work out if we've been passed a synonym
    --
    if i_owner is null then
        open c_syns( upper( i_package));
        fetch c_syns into l_owner, l_package;
        l_is_synonym := c_syns%found;
        close c_syns;
    end if;
    if not l_is_synonym then
        --
        -- make sure the l_owner and l_package variables are
        -- set
        --
        l_owner := i_owner;
        l_package := i_package;
    end if;
        
    for r_args in
    (
        select object_name, argument_name, data_type,
            in_out, defaulted, overload, sequence
        from all_arguments
        where owner = upper(nvl(l_owner, owner))
        and package_name = upper(i_package)
        and object_name = upper(nvl(i_member, object_name))
        and data_level = 0
        order by object_name, subprogram_id, sequence
    )
    loop
        --
        -- We may well be returning details of multiple package members
        -- as either the member specified is overloaded or no member is 
        -- specified. So, we need to see if we're starting to describe a new object.
        --
        if r_args.sequence < 2 
        then
            --
            -- Work out if this is member is :
            -- 
            -- 1) A procedure that takes no arguments - 
            --  - first argument_name is null
            --  - data_type is null
            -- 2) A function 
            --  - first argument_name is null
            --  - data_type is return type
            -- 3) An ordinary procedure
            --
            l_current_member := r_args.object_name;
            if r_args.sequence = 0 then
                --
                -- Procedure with no arguments
                --
                r_args.argument_name := '<NONE>';
                r_args.in_out := null;
                r_args.defaulted := null;
                l_current_type := 'PROCEDURE';
            elsif r_args.sequence = 1 and r_args.argument_name is null
                and r_args.data_type is not null 
            then
                r_args.argument_name := '<RETURN>';
                l_current_type := 'FUNCTION';
            else
                l_current_type := 'PROCEDURE';
            end if;
        end if;
        l_rtn.object_type := l_current_type;
        l_rtn.object_name := r_args.object_name;
        l_rtn.overload := r_args.overload;
        l_rtn.argument_name := r_args.argument_name;
        l_rtn.data_type := r_args.data_type;
        l_rtn.in_out := r_args.in_out;
        l_rtn.defaulted := r_args.defaulted;
        pipe row( l_rtn);
    end loop;
end desc_plus_proc;
/

The functionality is fairly similar to that of DESCRIBE itself in that we first evaluate whether we’ve been passed a synonym and, if so, then resolve it to the underlying object.
We then loop through our query ( using the funky for loop).
As well as functions, procedures that take no arguments can also have null argument_names so we need a bit of logic to determine which is which.
Finally we pipe back the results.
A point to note here is that you can’t pipe the cursor record variable itself. You need to use a variable of the appropriate return type.

In addition, we’re using the ALL_ARGUMENTS view rather than it’s DBA counterpart. This is because we want to make the function available to users who may not have privileges to the DBA views.
We’ve also created the function with Invoker’s Rights (AUTHID CURRENT_USER). This ensures that we don’t override any grants to users and make available database objects that would not otherwise be visible to them.

As the function is potentially going to be used by anyone connecting to the database, we can issue the following grant :

grant execute on desc_plus_proc to public
/

…and possibly even…

create or replace public synonym desc_plus_proc for mike.desc_plus_proc
/

…or possibly not. Public grants and synonyms tend to illicit a fair amount of discussion in database circles. So, maybe you’d want to grant execute explicitly to individual users (or even a role). The same may apply to the synonym – you may prefer to dispense with it altogether ( and call referencing the owner) or create private synonyms as appropriate.
For the purposes of this particular post, I’m going to sit cat-like on the fence and feign complete disinterest.

Anyway, time for a quick test :

SQL> select object_type||' - '||object_name as member, argument_name, data_type, in_out, defaulted
  2  from table( desc_plus_proc('dbms_metadata', 'get_ddl'))
  3  /

MEMBER						      ARGUMENT_NAME		     DATA_TYPE			    IN_OUT    D
----------------------------------------------------- ------------------------------ ------------------------------ --------- -
FUNCTION - GET_DDL				      <RETURN>			     CLOB			    OUT       N
FUNCTION - GET_DDL				      OBJECT_TYPE		     VARCHAR2			    IN	      N
FUNCTION - GET_DDL				      NAME			     VARCHAR2			    IN	      N
FUNCTION - GET_DDL				      SCHEMA			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      VERSION			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      MODEL			     VARCHAR2			    IN	      Y
FUNCTION - GET_DDL				      TRANSFORM 		     VARCHAR2			    IN	      Y

7 rows selected.

SQL> 

Well, it seems to return the required results. However, the formatting could do with a bit of cleaning up for SQL*Plus.
Now, we could go and make some changes to the function itself. However, as it stands, it can be useful in any Oracle client environment, not just SQL*Plus. So, a better solution would be to ….

Unleash the power of SQL*Plus

You can do a fair bit to customise your SQL*Plus environment. As well as getting in on the fluffy kitten theme, William Robertson has a great article on customising your SQL*Plus environment…although his cat looks a bit older and I’m a bit disappointed that it doesn’t seem to have mastered the art of touch-typing. Have a look at his home page and you’ll see what I mean.
If, like William, you want to have your own personal settings (for SQL*Plus that is, not cats), you can create a file called login.sql.

Making yourself at home with login.sql

In order to get this file to run every time you start SQL*Plus, you can define the SQLPATH environment variable on your OS to point to the directory that login.sql is located.

For example, on my Linux machine :

cd $HOME
mkdir sqlplus_scripts

Now to set the SQLPATH environment variable. I’ll do this by editing the .bashrc file in my home directory to add the line :

export SQLPATH=$HOME/sqlplus_scripts

Finally, I can put my login.sql script in the sqlplus_scripts directory and it will always run when I start SQL*Plus.

My login.sql looks like this :

def _editor=/usr/bin/gedit

You may be wondering what exactly this has to do with our pipelined function. Well, now we have the SQLPATH set, SQL*Plus will look in here to find a file that we’re trying to execute.

With this in mind, we can make our describe package member function just that little bit more user-friendly.
In the new sqlplus_scripts directory, I’ve created the file desc_plus_proc.sql, which does several things.

  1. saves the existing SQL*Plus session settings
  2. sets the environment up to display the query output
  3. uses an anonymous PL/SQL block evaluates the argument string passed in
  4. executes the query using variables declared in SQL*Plus and populated in the block
  5. tidies up after itself

…all of which looks something like :

rem
rem Record the current session settings before we start mucking about
rem
store set $SQLPATH/default_settings.sql replace
rem
rem Set the page width and size (no of lines)
rem
set lines 130
set pages 24
rem
rem format the columns that will be output from the query
rem
column member format a40
column ovrld format a5
column argument_name format a30
column data_type format a30
column io format a3
column defaulted format a9

rem
rem declare the SQL*Plus variables to be populated for the call to the function
rem
var sp_owner varchar2(30)
var sp_package varchar2(30)
var sp_member varchar2(30)

rem 
rem supress verify output for the parameter replacement and feedback for the pl/sql block completion
rem
set verify off
set feedback off

declare
    --
    -- sort out the argument we've been passed.
    -- If it's got '.' in it then we need to determine what the
    -- individual elements are.
    -- Once we've established what we've been passed in, initialise
    -- the SQL*Plus variables we've just declared.
    --
    l_argstr varchar2(100):= '&1';
    
    l_count pls_integer;
    
    l_first_end pls_integer;
    l_second_end pls_integer;
    
    l_arg1 varchar2(30);
    l_arg2 varchar2(30);
    
    l_owner varchar2(30);
    l_package varchar2(30);
    l_member varchar2(30);
    
    l_dummy pls_integer;
    cursor c_is_user( cp_user varchar2) is
        select 1
        from all_users
        where username = upper( cp_user);    
begin
    --
    -- '.' + 1 = the number of elements in the argument we've been passed
    --
    l_count := regexp_count(l_argstr, '\.',1) + 1;
    if l_count = 1 then
        l_package := l_argstr;
    elsif l_count = 3 then
        l_first_end := instr( l_argstr, '.',1,1);
        l_second_end := instr( l_argstr, '.',1,2);
        l_owner := substr( l_argstr, 1, l_first_end -1);
        l_package := substr( l_argstr, l_first_end + 1, l_second_end - l_first_end -1);
        l_member := substr( l_argstr, l_second_end + 1);
    elsif l_count = 2 then
        -- this is either owner.package or package.member...
        l_first_end := instr(l_argstr, '.',1,1);
        l_arg1 := substr( l_argstr, 1, l_first_end -1);
        l_arg2 := substr( l_argstr, l_first_end + 1);
        open c_is_user( l_arg1);
        fetch c_is_user into l_dummy;
        if c_is_user%found then
            l_owner := l_arg1;
            l_package := l_arg2;
        else
            l_package := l_arg1;
            l_member := l_arg2;
        end if;    
    end if;
    :sp_owner := l_owner;
    :sp_package := l_package;
    :sp_member := l_member;
end;    
/

break on member on ovrld
select case when object_type is not null then object_type||' - '||object_name end as member,
    overload as ovrld,
    argument_name,
    data_type,
    in_out as io,
    defaulted
from table( desc_plus_proc( i_package => :sp_package, i_member => :sp_member, i_owner => :sp_owner))
/

rem
rem and now to tidy-up...
rem
undefine sp_owner
undefine sp_package
undefine sp_member
clear column
clear break

@$SQLPATH/default_settings.sql

The upshot of all this ? Well, from the SQL*Plus prompt my call to this function is now rather closer to the DESCRIBE syntax :

@desc_plus_proc dbms_metadata.get_ddl;
@desc_plus_proc dbms_output;

The output looks a bit more elegant too :

Output to have you purring like a kitten

Output to have you purring like a kitten

Of course, if you simply must have this functionality back in the comfort of your IDE, you’re free to use the pipelined function and customise the output accordingly.
In the meantime, the command line becomes just that little bit more…well, cute and fluffy.


Filed under: Oracle, PL/SQL, SQL Tagged: all_arguments, authid current_user, break on column, clear break, clear column, column format, create type as object, create type is table of, dba_arguments, dba_arguments.defaulted_value, dba_procedures, describe, login.sql, select from table, set feedback, set lines, set pagesize, set verify, SQL*Plus, sql*plus variable, SQLPATH, store SQL*Plus settings, undefine variable

Long to CLOB conversion – Constraint Conditions and Database D.I.Y

Tue, 2014-05-20 11:50

DIY on a public holiday is something akin to a Great British tradition. Unfortunately, my DIY skills can best be summed up as being able to move heavy stuff about and do a bit of light destruction.
Fortunately, Deb is great at that sort of thing. As a result, I seem to have spent rather a lot of time lately playing Igor to her mad scientist visionary genius.
On an unusually sunny Bank Holiday Monday recently, The Lady of the House sent me packing out into the back garden with instructions to “tidy it up a bit”.
There followed an afternoon of intensive Chemical Warefare on the weeds ( the one thing I do seem to have a talent for growing) followed by judicious application of mower and strimmer.
Needless to say, the end result resembled less the restoration of verdant sward that she was secretly hoping for and more an example of slash-and-burn agriculture.
Fortunately, the next Bank Holiday isn’t until the end of May.
This gives me some time to continue in my attempts to persuade my better half to move into the 21st century garden-wise.
My argument is that, most people will only ever see our back garden on Google Maps. Provided it looks OK from the satellite view then that’s really what matters.
So, all we need to do is concrete the lawn over then paint the light and dark green stripes in.
This way, rather than constantly cutting the grass, we just need to repaint it once a year or so.
I’ve even offered to paint in some flowerbeds.
Ironically, this argument appears to have fallen on stony ground.

On top of this, the Oracle database has also required me to do a bit of DIY lately. This takes the form of trying to search the text in a column defined as LONG.
You may be wondering what on earth I’m doing with a LONG column. After all, Oracle introduced the CLOB way back in 8i, before the turn of the century. Surely no-one uses LONGs any more ?
Well, almost no-one…except Oracle.

Long columns in Data Dictionary Views

First of all, let’s see just how widely used LONG columns still are in the Data Dictionary :

select count(*)
from dba_tab_columns
where owner = 'SYS'
and data_type = 'LONG'
/

This comes back with the grand total of 229 on 11gXE. Obviously, this will include multiple views based on a single table. However, it still represents a significant presence for the venerable LONG datatype.
This presence does become particularly apparent when you want to – for example – have a look at the constraints in a given schema.

The Test Schema

Taking inspiration from some of Deb’s shopping lists for our planned trips to the garden centre…

create user igor identified by handy
    default tablespace users
/
 
grant create session, create table to igor
/

alter user igor quota unlimited on users
/

Now to create the tables in the new schema :

create table garden_checklists
(
    gc_name varchar2(50) constraint gc_pk primary key
)
/
create table garden_checklist_items
(
    item_name varchar2(50) constraint gci_pk primary key,
    gc_name varchar2(50) not null constraint gci_gc_fk references garden_checklists( gc_name),
    description varchar2(4000),
    safe_for_mike varchar2(1) not null,
    cost number(9,2) constraint gci_cost_ck check (cost < 50.0)
)
/

So, connected as Igor, we can see that we have the following constraints in place :

select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'GARDEN_CHECKLIST_ITEMS'
/

CONSTRAINT_NAME      CONSTRAINT_TYPE SEARCH_CONDITION
-------------------- --------------- -------------------------------------------------------
SYS_C0010851	     C		     "GC_NAME" IS NOT NULL
SYS_C0010852	     C		     "SAFE_FOR_MIKE" IS NOT NULL
GCI_COST_CK	     C		     cost < 50.0
GCI_PK		     P
GCI_GC_FK	     R

Now, say we want to just list the Check constraints that other than NOT NULL constraints.
Simple enough you might think …

select constraint_name
from user_constraints
where constraint_type = 'C'
and search_condition not like '%IS NOT NULL'
/

However, when you run this you’ll get :

and search_condition not like '%IS NOT NULL'
    *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

That’s right, the SEARCH_CONDITION column is defined as a long. It looks like we’re going to need a different approach.

TO_LOB or not TO_LOB ? That is the question

The TO_LOB function has been around…well, as long as the LOB datatypes. That should do the job…

select constraint_name
from user_constraints
where constraint_type = 'C'
and to_lob(search_condition) not like '%IS NOT NULL'
/

and to_lob(search_condition) not like '%IS NOT NULL'
    *
ERROR at line 4:
ORA-00932: inconsistent datatypes: expected - got LONG

…or not, as the case may be.

In fact, TO_LOB does work, but only in a CREATE TABLE statement :

create table my_constraints as
    select constraint_name, 
        constraint_type, 
        to_lob( search_condition) as search_condition,
        r_owner, 
        r_constraint_name
    from user_constraints
/

Table created.

SQL> desc my_constraints
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 CONSTRAINT_NAME			   NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE				    VARCHAR2(1)
 SEARCH_CONDITION				    CLOB
 R_OWNER					    VARCHAR2(120)
 R_CONSTRAINT_NAME				    VARCHAR2(30)

Going back to our problem – how to get a list of Check Constraints other than NOT NULL constraints, things are now a whole lot easier :

select constraint_name
from my_constraints
where constraint_type = 'C'
and search_condition not like '%IS NOT NULL'
/

CONSTRAINT_NAME
------------------------------
GCI_COST_CK

OK, so we do have some kind of solution to our problem. However, creating a table every time we want to check the contents of a long column isn’t ideal.
Fortunately, there is another way of getting DDL for a constraint…

DBMS_METADATA.GET_DDL – Does Exactly What it Says On the Tin ?

Of course, DBMS_METADATA.GET_DDL will return DDL for a given object in the form of a CLOB. As it’s a DDL statement that we’re looking at, we will have to amend our predicate slightly, but the following should be fine :

select constraint_name
from user_constraints
where constraint_type = 'C'
and dbms_metadata.get_ddl
( 
    object_type => 'CONSTRAINT', 
    name => constraint_name, 
    schema => user
) not like '%NOT NULL%'
/

This is where things start getting a little bit silly. Running this query will give you :

and dbms_metadata.get_ddl
    *
ERROR at line 4:
ORA-31603: object "SYS_C003" of type CONSTRAINT not found in schema "IGOR"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

Now, it is true to say that SYS_C003 does not exist in the IGOR schema, it is valid to ask why on earth the function is even attempting to look it up, seeing as it’s being passed in a constraint name from USER_CONSTRAINTS.
If you’re interested, you can lookup this constraint ( as a user with sufficient privileges) :

select owner, table_name, constraint_name, constraint_type, search_condition
from dba_constraints
where constraint_name = 'SYS_C003'
/

You will find that this is a Check Constraint on the FILE# column of SYS.TAB$.

Interestingly, USER_CONSTRAINTS has an OWNER column. If we use this in the predicate, we hit a slightly different issue…

select constraint_name
from user_constraints
where constraint_type = 'C'
and owner = user
and dbms_metadata.get_ddl
( 
    object_type => 'CONSTRAINT', 
    name => constraint_name, 
    schema => user
) not like '%NOT NULL%'
/

and dbms_metadata.get_ddl
    *
ERROR at line 5:
ORA-31603: object "GCI_GC_FK" of type CONSTRAINT not found in schema "IGOR"
ORA-06512: at "SYS.DBMS_METADATA", line 4018
ORA-06512: at "SYS.DBMS_METADATA", line 5843
ORA-06512: at line 1

GCI_GC_FK does, in fact, exist in the current schema. The issue here is that retrieving DDL for a Foreign Key constraint requires a slightly different call to the function :

select dbms_metadata.get_ddl( 'REF_CONSTRAINT', 'GCI_GC_FK', user) from dual;

Perhaps a more pertinent question to ask would be, why is the function once again ignoring the query predicate and looking up something that is not a check constraint ?

Now, whilst I do accept that I could just be being as dumb as a bag of hammers ( to continue the D.I.Y theme), I can assure you dear reader, that I have not been drinking. This eliminates at least one of Flavio Casetta’s hypotheses as to what is actually going on here :)

I think we can conclude from this that using the DBMS_METADATA.GET_DDL function in-line in a query is possibly not entirely reliable. However, it seems to behave itself if we enclose it in the straight-jacket of a correlated sub-query :

select cons.constraint_name
from user_constraints cons
where cons.owner = user
and cons.constraint_type = 'C'
and 
(
    select dbms_metadata.get_ddl( 'CONSTRAINT', cons1.constraint_name, user)
    from user_constraints cons1
    where cons1.owner = cons.owner
    and cons1.constraint_type = cons.constraint_type
    and cons1.constraint_name = cons.constraint_name
) not like '%NOT NULL%'
/

CONSTRAINT_NAME
------------------------------
GCI_COST_CK

Once again, we have a solution to our problem of finding all of the Check constraints in the current schema that are not simple NOT NULL constraints.
Once again, the solution we have doesn’t feel entirely satisfactory…

If you can do it in PL/SQL…

Coming toward the end of our LONG and winding road, let’s see what PL/SQL would make of all this…

set serveroutput on size unlimited
begin
    for r_cons in 
    (
        select constraint_name, table_name, search_condition
        from user_constraints
        where constraint_type = 'C'
    )
    loop
        if r_cons.search_condition not like '%IS NOT NULL' then
            dbms_output.put_line(r_cons.constraint_name||' on '||r_cons.table_name||' - '||r_cons.search_condition);
        end if;
    end loop;
end;
/

Now, you might expect to hit the same problem as we started with here, namely ORA-00932. However, running this returns :

GCI_COST_CK on GARDEN_CHECKLIST_ITEMS - cost < 50.0

PL/SQL procedure successfully completed.

So, it seems that PL/SQL happily performs an implicit conversion from LONG to VARCHAR2 or CLOB to allow the string comparison to work without error.

This works even if you declare a scalar variable of type long :

set serveroutput on size unlimited
declare
    cursor c_constraints is
    select constraint_name, table_name, search_condition
    from user_constraints
    where constraint_type = 'C';
    
    l_constraint_name user_constraints.constraint_name%type;
    l_table_name user_constraints.table_name%type;
    l_search_condition user_constraints.search_condition%type;    
begin
    open c_constraints;
    loop
        fetch c_constraints into l_constraint_name, l_table_name, l_search_condition;
        if l_search_condition not like '%IS NOT NULL' then
            dbms_output.put_line(l_constraint_name||' on '||l_table_name||' - '||l_search_condition);
        end if;
        exit when c_constraints%notfound;
    end loop;
    close c_constraints;
end;
/
Other possible solutions

There are other ways of tackling the long problem.

The ever-excellent Adrian Billington has a solution which employs DBMS_XMLGEN, as well as a custom utility, which you may well find useful.
Alternatively, you could take a look at the DBMS_METADATA_UTIL package which contains both a long to varchar2 and long to clob conversion function…although they require you to pass in the name of the base table rather than a view, and supply a rowid…all a bit of a faff for something as simple as the specific problem I’ve looked at here.

Of course, there’s always the option of an open letter to Oracle….

“Dear Larry,

I know you’re probably busy this weekend, sorting out the shed, mowing the lawn, or possibly learning your lines for the next Iron Man movie, but do you think you could take a few moments to get this LONG columns in the Data Dictionary issue sorted ?

I know it’s probably on your “To Do” list and has been for the last 15 years or so.
If it’s going to be too much trouble to solve the problem once and for all, then maybe you might consider an interim solution such as :

  • tweaking the TO_LOB function so that it works against LONG columns in a straight select statement
  • persuading DBMS_METADATA.GET_DDL to behave itself

Any assistance you could offer would be much appreciated.

Love and Kisses,

Mike “


Filed under: Oracle, PL/SQL, SQL Tagged: DBMS_METADATA.GET_DDL, DBMS_METADATA.GET_DDL and Foreign Keys, long datatype in data dictionary, ORA-00932, ORA-31603, PL/SQL implicit conversion of LONG columns, TO_LOB, user_constraints

Tracing for fun and (tk)profit

Tue, 2014-05-06 14:55

If you ever wanted proof that time is relative, just consider The Good Old Days.
Depending on your age, nationality, personal preferences etc, that time could be when rationing finally ended; or when Trevor Brooking won the Cup for West Ham with a “bullet” header; or possibly when Joe Carter hit a three-run homer to seal back-to-back World Series for the Blue Jays.
Alternatively, it could be when you were able to get on to the database server and use tkprof to analyse those tricky database performance issues.

In these days of siloed IT Departments, Oracle trace files, nevermind the tkprof utility are out of the reach of many developers.
The database server itself is the preserve of Unix Admins and DBAs, groups which, with good reason, are a bit reluctant to allow anyone else access to the Server at the OS level.

Which is a pity. Sometimes there is just no substitute for getting into the nitty gritty of exactly what is happening inside a given session.

For those of you who miss The Good Old Days of tkprof, what follows is an exploration of how to access both trace files and even the tkprof utility itself without leaving the comfort of your database.
I’ll go through a quick recap of :

  • how to generate a trace file for a session
  • using tkprof to make sense of it all

Then, coming bang up to date :

  • viewing a trace file using an external table – and why you might want to
  • Using a preprocessor to generate tkprof output
  • implementing a multi-user solution for tkprof

Before I go any further, I think it’s only right to acknowledge this excellent article by Adrian Billington on the same theme.

Generating a trace file just like Mother used to make

First of all, we need to know where oracle will put any trace files that we generate.
This is simple enough, we can just look at the USER_DUMP_DEST parameter :

select value
from v$parameter
where name = 'user_dump_dest'
/

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xe/XE/trace

This directory may well already be choc full of trace files generated by background processes so it might be an idea to make sure that we can easily identify the trace file that we’re interested in.

We can specify a string to use in the file name by doing the following in the session we want to trace:

alter session set_tracefile_identifier='are_policeman_getting_younger';

Now, we’re ready to initiate the trace in the session :

alter session set timed_statistics=true
/
alter session set events '10046 trace name context forever, level 12'
/

Now let’s run a couple of queries to trace…

select * from hr.departments
/
...
select emp.first_name||' '||emp.last_name
from employees emp, departments dept
where emp.department_id = dept.department_id
and dept.department_name = 'IT Helpdesk'
/
...

Once we’re done, we can exit the session and then see if we can find the trace file :

ls -l /u01/app/oracle/diag/rdbms/xe/XE/trace/*are_policemen_getting_younger.trc
XE_ora_2995_are_policemen_getting_younger.trc

I think that’s our trace file.

If we now look at this, we’ll see something like :

Trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2995_are_policemen_getting_younger.trc
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe
System name:    Linux
Node name:      netbook-eM350
Release:        3.5.0-17-generic
Version:        #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012
Machine:        x86_64
Instance name: XE
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 2995, image: oracle@netbook-eM350 (TNS V1-V3)


*** 2014-04-22 13:04:58.945
*** SESSION ID:(12.3) 2014-04-22 13:04:58.945
*** CLIENT ID:() 2014-04-22 13:04:58.945
*** SERVICE NAME:(SYS$USERS) 2014-04-22 13:04:58.945
*** MODULE NAME:(SQL*Plus) 2014-04-22 13:04:58.945
*** ACTION NAME:() 2014-04-22 13:04:58.945

WAIT #140472211929432: nam='SQL*Net message to client' ela= 13 driver id=1650815232 #bytes=1 p3=0 obj#=13246 tim=1398168298928274

*** 2014-04-22 13:05:06.782
WAIT #140472211929432: nam='SQL*Net message from client' ela= 7836353 driver id=1650815232 #bytes=1 p3=0 obj#=13246 tim=1398168306782358
CLOSE #140472211929432:c=0,e=73,dep=0,type=1,tim=1398168306782754
=====================
...

In it’s raw form, the tracefile isn’t the easiest thing in the world to read. Fortunately, we can make things a little clearer by using tkprof :

tkprof XE_ora_2995_are_policemen_getting_younger.trc young_police.txt explain=uid/pwd@db sys=no

Run this replacing the uid/pwd@db with a valid database connect string for a user with an Explain table setup and you’ll get something a bit more readable generated in the young_police.txt file :


TKPROF: Release 11.2.0.2.0 - Development on Tue Apr 22 13:18:03 2014

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

Trace file: XE_ora_2995_are_policemen_getting_younger.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 1ac6kt76qnwsm Plan Hash: 4167016233

select * 
from
 hr.departments


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.01          6          8          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.04       0.11          6          8          0          27

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48  (MIKE)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        27         27         27  TABLE ACCESS FULL DEPARTMENTS (cr=8 pr=6 pw=0 time=11263 us cost=3 size=567 card=27)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
     27   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'DEPARTMENTS' (TABLE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         1        0.00          0.00
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     3       81.55         81.57
********************************************************************************

select emp.first_name||' '||emp.last_name
from employees emp, departments dept
where emp.department_id = dept.department_id
and dept.department_name = 'IT Helpdesk'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.02          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.03          6         12          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.01       0.06          6         12          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48  (MIKE)
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  HASH JOIN  (cr=6 pr=3 pw=0 time=17220 us cost=7 size=62 card=2)
         0          0          0   TABLE ACCESS FULL DEPARTMENTS (cr=6 pr=3 pw=0 time=17018 us cost=3 size=13 card=1)
         0          0          0   TABLE ACCESS FULL EMPLOYEES (cr=0 pr=0 pw=0 time=0 us cost=3 size=36 card=2)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   HASH JOIN
      0    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'DEPARTMENTS' (TABLE)
      0    TABLE ACCESS   MODE: ANALYZED (FULL) OF 'EMPLOYEES' (TABLE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  db file sequential read                         1        0.02          0.02
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     3      109.59        120.58
  SQL*Net break/reset to client                   1        0.01          0.01
********************************************************************************

...
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.05       0.13          0          0          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.04         12         20          0          27
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.05       0.18         12         20          0          27

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     8      109.59        228.41
  db file sequential read                         2        0.02          0.03
  Disk file operations I/O                        1        0.00          0.00
  db file scattered read                          2        0.00          0.00
  SQL*Net break/reset to client                   3        0.01          0.01
  log file sync                                   1        0.01          0.01

...
    4  user  SQL statements in session.
   28  internal SQL statements in session.
   32  SQL statements in session.
    2  statements EXPLAINed in this session.
********************************************************************************
Trace file: XE_ora_2995_are_policemen_getting_younger.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       4  user  SQL statements in trace file.
      28  internal SQL statements in trace file.
      32  SQL statements in trace file.
      19  unique SQL statements in trace file.
       2  SQL statements EXPLAINed using schema:
           MIKE.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
    2111  lines in trace file.
     221  elapsed seconds in trace file.

As they say in those smartphone ads, some sequences have been shortened.

New fangled tracing syntax

If you think the syntax to start tracing in a session is somewhat verbose, you’ll be pleased to know that there is a modern alternative.
Additionally, you don’t have to give your trace file a distinctive name to be able to work out which one it is that you’ve generated. You can instead simply issue the following query :

select value
from v$diag_info
where name = 'Default Trace File';

We can now start tracing the current session by simply doing the following :

exec  DBMS_MONITOR.SESSION_TRACE_ENABLE(null, null, true, true);

… and once we’re done, stop tracing even more easily…

exec dbms_monitor.session_trace_disable;

DBMS_MONITOR.SESSION_TRACE_ENABLE takes 4 parameters. The first two are the sid and serial# of the session you want to trace. If you specify null for these two parameters, it will simply start tracing in the current session.
The final two boolean arguments are to include binds and waits.
Specifying TRUE for both of these is the equivalent to a 10046 level 12 trace.

Viewing a Tracefile

In the here and now, there are a number of extremely useful IDEs for Oracle that do all sorts of wierd and wonderful things. SQLDeveloper, for example, will enable you to open a raw trace file and present the information in a more easily digestible fashion.

If you wanted to make use of this functionality ( or the equivalent in TOAD/PL/SQL Developer or whatever your favourite is), you may well find yourself faced with one of the ongoing issues in organisations of all sizes – i.e. which users should have access to the database server at the OS level.
In many organisations, the answer to this is Sysadmins and DBAs. Developers don’t get a look in.
Whilst the reasons behind these restrictions are, by-and-large completely valid, it does mean that the developers ( i.e. the people usually required to do the tuning) are denied a key part of Oracle functionality that enables diagnostics and tuning.

One solution to this problem is, of course, an External Table…

Trace file in an External Table

First of all, we need to create a directory object in the database on the trace file directory in the OS :

create or replace directory trace_dir for
    '/u01/app/oracle/diag/rdbms/xe/XE/trace'
/

The next step is to create a simple external table…with a twist…

create table trace_file_xt
( 
    line  number,
    text varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory trace_dir
        access parameters
        (
            records delimited by newline
            nologfile
            fields terminated by whitespace
            (
                line recnum,
                text position(1:4000)
            )
        )
        location ('')
   )
   reject limit unlimited
/

Yep, we’ve not specified a location for the external table.
So, whilst the table is created without error, it’s not currently pointing to a trace file.

Let’s trace another session and this time, we can have a look at the trace file generated without leaving the comfort of the SQL prompt…

select value
from v$diag_info
where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2007.trc

…now start tracing…

begin
    dbms_monitor.session_trace_enable(null, null);
end;
/

PL/SQL procedure successfully completed.

SQL> 

Now run a couple of statements…

select * from hr.departments;
select * from hr.employees;

…and now stop tracing the session :

begin
    dbms_monitor.session_trace_disable;
end;
/

PL/SQL procedure successfully completed.

SQL> 

Finally, in order to see our trace file in all it’s glory, simply point the external table at it…

alter table trace_file_xt location('XE_ora_2007.trc')
/

Table altered.

SQL> 

…and select from it…

select text
from trace_file_xt
order by line
/

TEXT
----------------------------------------------------------------------------------------------------------------------------------
Trace file /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2007.trc
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe
System name:	Linux
Node name:	netbook-eM350
Release:	3.5.0-17-generic
Version:	#28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012
Machine:	x86_64
Instance name: XE
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 2007, image: oracle@netbook-eM350 (TNS V1-V3)


*** 2014-04-22 18:39:17.619
*** SESSION ID:(97.15) 2014-04-22 18:39:17.619
*** CLIENT ID:() 2014-04-22 18:39:17.619
*** SERVICE NAME:(SYS$USERS) 2014-04-22 18:39:17.619
*** MODULE NAME:(SQL*Plus) 2014-04-22 18:39:17.619
*** ACTION NAME:() 2014-04-22 18:39:17.619
...
Getting tkprof output

If you’re happy with your IDE’s interpretation of the trace file, or even just prefer looking directly at the trace file itself, this should be enough to be getting on with. For those of us who like tkprof to take some of the strain out of trace file interpretation, a bit more is required.

Yep, we’re going to need one of those external table preprocessor thingys.
I know what you’re thinking, when you’ve got a hammer, everything looks like a nail.

First, we’ll need a Pre-process directory :

cd /u01/app/oracle
mkdir pre_proc_dir

Next, we need to create the shell script in our new directory :

#!/bin/sh
#
# Script to check if a prf file already exists for this trace file.
# If not then run tkprof.
# Final step is to send the contents of the file to stdout
# which in this case means the external table
#
inFile=`/usr/bin/basename $1 .trc`

if [ ! -f $inFile.prf ]
then
   /u01/app/oracle/product/11.2.0/xe/bin/tkprof $1 $inFile.prf
fi
/bin/cat $inFile.prf

A quick note on the permissions for this file. If it’s not owned by oracle – which may well be regarded as a sensible precaution, you do still need to make sure that oracle can execute it.
The best way to ensure this would seem to be to make sure that the group is set to dba, which has execute permissions on the file :

chgrp dba run_tkprof.sh
chmod 755 run_tkprof.sh
ls -l run_tkprof.sh
-rwxr-xr-x 1 mike dba 366 May  4 14:33 run_tkprof.sh

And finally, the table …

create table tkprof_xt
( 
    line  number,
    text varchar2(4000)
)
    organization external
    (
        type oracle_loader
        default directory trace_dir
        access parameters
        (
            records delimited by newline
            nologfile
            preprocessor pre_proc_dir: 'run_tkprof.sh'
            fields terminated by whitespace
            (
                line recnum,
                text position(1:4000)
            )
        )
        location ('')
   )
   reject limit unlimited
/

Once again, we haven’t specified the table’s location as this will be the trace file that we want to look at.
However, for this External table, the output is a bit different :

alter table tkprof_xt location('XE_ora_2007.trc')
/

select text
from tkprof_xt
order by line;

Run this and we get :

TKPROF: Release 11.2.0.2.0 - Development on Thu Apr 24 20:03:27 2014

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

Trace file: /u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_2007.trc
Sort options: default

********************************************************************************
count	 = number of times OCI procedure was executed
cpu	 = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk	 = number of physical reads of buffers from disk
query	 = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows	 = number of rows processed by the fetch or execute call
********************************************************************************

...

SQL ID: 7jk33n4f4mpy9 Plan Hash: 1445457117

select *
from
 hr.employees


call	 count	     cpu    elapsed	  disk	    query    current	    rows
------- ------	-------- ---------- ---------- ---------- ----------  ----------
Parse	     1	    0.04       0.05	     0		0	   0	       0
Execute      1	    0.00       0.00	     0		0	   0	       0
Fetch	     9	    0.00       0.01	     6	       14	   0	     107
------- ------	-------- ---------- ---------- ---------- ----------  ----------
total	    11	    0.05       0.06	     6	       14	   0	     107

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ----------------------------------------------
-----

       107	  107	     107  TABLE ACCESS FULL EMPLOYEES (cr=14 pr=6 pw=0 t
ime=12195 us cost=3 size=7383 card=107)



Elapsed times include waiting on following events:
  Event waited on			      Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client			  9	   0.00 	 0.00
  db file sequential read			  1	   0.01 	 0.01
  db file scattered read			  1	   0.00 	 0.00
  SQL*Net message from client			  9	  59.74 	59.85
********************************************************************************
...

We’re now looking at the output of tkprof, rather than at the raw trace file.

As for the .prf file we’ve generated, that has somehow ended up at /u01/app/oracle/product/11.2.0/xe/dbs/XE_ora_2007.prf.
Hmmm, not quite sure why that is.
Checking by re-running the script confirms that the file is simply read if it already exists.

Rather than worrying about how to ensure that the output files are all stored somewhere sensible, we could simply remove them once we’re finished.
Remember, the table only consumes the output of the shell script ( in this case, the cat command). Therefore, we can safely remove the file once we’ve used cat to echo it’s contents to stdout. More of that shortly.

Accessing this application when there’s more than one user

Whilst this functionality is never going to be accessed by a huge number of concurrent users, you are likely to have more than one developer in your team who may want to utilise tkprof in this way.
The problem here is that we can only process one trace file at one time.
Well, we could create a separate table for each developer. Alternatively, we could wrap access to the table in a package and then write out the result to a conventional table for the developers to look at in their own time.

Let’s start with the table we want to hold the tkprof output :

create table traced_sessions
(
    tracefile_name varchar2(512), 
    line_no number,
    text varchar2(4000),
    created_by varchar2(30),
    creation_date date,
    constraint tc_pk primary key (tracefile_name, line_no)
)
/

For the package, we just need two public procedures…

create or replace package tkprof_pkg as
    
    procedure start_trace_pr(i_identifier varchar2 default null);
    procedure stop_trace_pr;
end tkprof_pkg;
/

…which look like this…

create or replace package body tkprof_pkg as

procedure start_trace_pr( i_identifier varchar2 default null) 
is
--------------------------------------------------------------------------------
-- Start tracing in the current session 
--------------------------------------------------------------------------------
    l_location user_external_locations.location%type;
    l_tf_stmnt varchar2(4000);
    
    pragma autonomous_transaction;
begin
    --
    -- If we've been passed a tracefile identifier then check it's not an injection
    -- attempt. 
    -- Once we're happy then set it as the tracefile identifier.
    --
    if i_identifier is not null then
        if regexp_instr( replace( i_identifier, '_'), '[[:punct:]]|[[:space:]]') > 0
        then
            raise_application_error( -20000,
                'Identifier can contain only letters, numbers and underscores');
        end if;
        execute immediate 'alter session set tracefile_identifier = '||i_identifier;
    end if;
    --
    -- Make sure that the xt is not currently pointing to anything
    --
    select location
    into l_location
    from user_external_locations
    where table_name = 'TKPROF_XT';
    if l_location is not null then
        raise_application_error(-20001, 'External Table is currently in use.');
    end if;
    --
    -- start tracing in the current session
    -- we want wait and bind information (equivalent to level 12)
    --
    dbms_monitor.session_trace_enable
    (
        session_id => null,
        serial_num => null, 
        waits => true, 
        binds => true, 
        plan_stat => 'FIRST_EXECUTION'
    );
end start_trace_pr;

procedure stop_trace_pr
is
--------------------------------------------------------------------------------
-- Stop tracing in the current session
-- Then generate the tkprof output by setting the location of the
-- external table and selecting the results into a permanent table
-- NOTE - reference to v$parameter requires SELECT ANY DICTIONARY for the
-- package owner.
--
--------------------------------------------------------------------------------
    l_tracefile varchar2(512);
    l_stmnt VARCHAR2(4000);

    pragma autonomous_transaction;
begin
    dbms_monitor.session_trace_disable;
    --
    -- Get the tracefile basename (i.e. strip the path)
    --
    with dir as
    (
        select value||'/' as dirpath
        from v$parameter
        where name = 'user_dump_dest'
    )
    select replace( di.value, dirpath)
    into l_tracefile
    from v$diag_info di, dir
    where di.name = 'Default Trace File';
    
    l_stmnt := 'alter table tkprof_xt location ('
        ||chr(39)||l_tracefile||chr(39)||')';
    --
    -- Point the External Table at the tracefile
    --
    execute immediate l_stmnt;


    insert into traced_sessions
    (
        tracefile_name, line_no, text, created_by, creation_date
    )
        select l_tracefile, line, text, user, sysdate
        from tkprof_xt;
    --
    -- Now reset the xt location to null ready for the next run.
    -- As this is a DDL statement it'll also issue an implicit commit
    -- for the insert we've just done.
    --
    execute immediate q'[alter table tkprof_xt location('')]';
end stop_trace_pr;
end tkprof_pkg;
/

Of course, as we’re now persisting the output in the database, we don’t need to store the .prf file…

#!/bin/sh
#
# Run tkprof and then delete the .prf file generated.
#
inFile=`/usr/bin/basename $1 .trc`
/u01/app/oracle/product/11.2.0/xe/bin/tkprof $1 $inFile.prf
/bin/cat $inFile.prf
/bin/rm $inFile.prf

OK, it’s a bit rough and ready. We’re relying on the External Table’s location being re-set after every run. Provided the table is accessed only via the package, this should be the case.
As we’re accessing an External Table, where there is no concept of DML locking, we’re relying on the current location definition for the table to determine whether or not it’s in use.

Also, it’s all a bit serial. If the table is in use, then you just have to wait. Having said that, this isn’t going to be a massively multi-user application. It’s only developers/dbas that will be using it.
Additionally, the external table is only “in-use” for a very short time – i.e. the time it takes for the tkprof output to be generated and inserted into our TRACED_SESSIONS table.

Anyway, I suppose we’d better test it…

exec tkprof_pkg.start_trace_pr('dept_locations')

select dept.department_name
from hr.departments dept, hr.locations loc, hr.countries coun
where dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.country_name = 'United Kingdom';

DEPARTMENT_NAME
------------------------------
Human Resources
Sales


select dept.department_name
from hr.departments dept
inner join hr.locations loc on loc.location_id = dept.location_id
inner join hr.countries coun on coun.country_id = loc.country_id
where coun.country_name = 'United Kingdom';

DEPARTMENT_NAME
------------------------------
Human Resources
Sales

exec tkprof_pkg.stop_trace_pr;

select value
from v$diag_info
where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/xe/XE/trace/XE_ora_5010_DEPT_LOCATIONS.trc

Once we’re done, we should now be able to view the tkprof output with the following query (using good old SQL*Plus):

set linesize 130
set pages 5000
set heading off
select text
from traced_sessions
where tracefile_name = 'XE_ora_5010_DEPT_LOCATIONS.trc'
order by line_no
/

If we want to home in on the first of the two queries that we’ve run…

select line_no 
from traced_sessions
where tracefile_name = 'XE_ora_5010_DEPT_LOCATIONS.trc'
and text like '%select dept.department_name%' -- first line of the each query
/

LINE_NO
-------

548
586

Looks like the following query might be what we’re after …

select text
from traced_sessions
where tracefile_name = 'XE_ora_5010_DEPT_LOCATIONS.trc'
and line_no between 548 and 585
order by line_no
/

Sure enough, the output looks something like :

select dept.department_name
from hr.departments dept, hr.locations loc, hr.countries coun
where dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.country_name = 'United Kingdom'

call	 count	     cpu    elapsed	  disk	    query    current	    rows
------- ------	-------- ---------- ---------- ---------- ----------  ----------
Parse	     1	    0.00       0.00	     0		0	   0	       0
Execute      1	    0.00       0.00	     0		0	   0	       0
Fetch	     1	    0.00       0.00	     0		6	   0	       2
------- ------	-------- ---------- ---------- ---------- ----------  ----------
total	     3	    0.00       0.00	     0		6	   0	       2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 49
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
	 2	    2	       2  NESTED LOOPS	(cr=6 pr=0 pw=0 time=101 us)
	 2	    2	       2   NESTED LOOPS  (cr=5 pr=0 pw=0 time=89 us cost=3 size=66 card=2)
	 3	    3	       3    NESTED LOOPS  (cr=3 pr=0 pw=0 time=74 us cost=2 size=36 card=2)
	 1	    1	       1     INDEX FULL SCAN COUNTRY_C_ID_PK (cr=1 pr=0 pw=0 time=39 us cost=1 size=12 card=1)(object id 1
6394)

	 3	    3	       3     TABLE ACCESS BY INDEX ROWID LOCATIONS (cr=2 pr=0 pw=0 time=28 us cost=1 size=12 card=2)
	 3	    3	       3      INDEX RANGE SCAN LOC_COUNTRY_IX (cr=1 pr=0 pw=0 time=10 us cost=0 size=0 card=2)(object id 1
6420)

	 2	    2	       2    INDEX RANGE SCAN DEPT_LOCATION_IX (cr=2 pr=0 pw=0 time=18 us cost=0 size=0 card=4)(object id 1
6414)

	 2	    2	       2   TABLE ACCESS BY INDEX ROWID DEPARTMENTS (cr=1 pr=0 pw=0 time=5 us cost=1 size=15 card=1)


Elapsed times include waiting on following events:
  Event waited on			      Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client			  1	   0.00 	 0.00
  SQL*Net message from client			  1	   0.20 	 0.20
********************************************************************************

It’s nice to know that you can use these new-fangled external tables to relive the Good Old Days of performance tuning.
Now, if only they’d add a feature to allow me to relive the Good Old Days when I could have a lie-in on a Saturday, rather than having to get up and mow the lawn.


Filed under: Oracle, PL/SQL, Shell Scripting, SQL Tagged: alter session set events, alter session set timed_statistics, alter session set tracefile_identifier, alter table location, dbms_monitor, dbms_monitor.session_trace_disable, dbms_monitor.session_trace_enable, external table preprocessor, tkprof, user_dump_dest, v$diag_info, v$parameter