Clone Schema using Datapump API

There is no single command for cloning a schema/user. But you can use datapump expdp and impdp utility with remap_schema option to clone a schema. However, i ran into a situation where the developers did not have access to O.S . The limitation of Datapump is that it runs as a background process and not a client tool.  This means that the dumpfile is generated in the DB server where the data is being exported in.  If the developer does not have access to the O.S, then he cannot access the dumpfile generated..

 There is a workaround to this situation.  There is a feature of datapump utility where you can directly import data across a DB Link without exporting. Now you use this feature along with using the Datapump API instead of command prompt and we have a way to clone schema.

You can look at Oracle document to write better scripts. But, below is one i used. I have highlighted places where you will need to change value according to your environment.



set serveroutput on
DECLARE
  ind NUMBER;              -- Loop index
  spos NUMBER;             -- String starting position
  slen NUMBER;             -- String length for output
  h1 NUMBER;               -- Data Pump job handle
  percent_done NUMBER;     -- Percentage of job complete
  job_state VARCHAR2(30);  -- To keep track of job state
  le ku$_LogEntry;         -- For WIP and error messages
  js ku$_JobStatus;        -- The job status from get_status
  jd ku$_JobDesc;          -- The job description from get_status
  sts ku$_Status;          -- The status object returned by get_status
  --v_psw dba_users.password%type;
  v_psw varchar2(30) ;  -- password new user
  v_row varchar2(8) ;   -- mode rows=N si '1'
  cur_scn number ;      -- source
BEGIN
-- mode rows=N si '1'
-- v_row :=1;
--cur_scn:=6030373057 ;
--select password into v_psw from dba_users where username = '&user_c';
  h1 := Dbms_DataPump.Open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => 'impdp_vkumar3', version => 'COMPATIBLE', remote_link => 'DBLINKNAME_VK' );
    dbms_datapump.set_parallel(handle => h1, degree => 2);
    -- uncomment if you want the logfile written
    -- Dbms_DataPump.Add_File(handle => h1, filename => 'vkumar_imp1', directory => 'DATA_PUMP_DIR', filetype => 3);
    -- enter value for source schame
    dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''VIJAY_PROD'')'); 
-- cause bug Bug 5071931 DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW , exclure le calcul des stats
  DBMS_DATAPUMP.METADATA_FILTER(handle=> h1, name => 'EXCLUDE_PATH_EXPR', value => '=''TABLE_STATISTICS''');
  DBMS_DATAPUMP.METADATA_FILTER(handle=> h1, name => 'EXCLUDE_PATH_EXPR', value => '=''INDEX_STATISTICS''');
  -- remap source to destination
  Dbms_DataPump.METADATA_REMAP( h1 , 'REMAP_SCHEMA', 'VIJAY_PROD' , 'VIJAY_DEV' );
  -- USE SKIP or REPLACE option if file exists.
  Dbms_DataPump.Set_Parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value => 'SKIP');
  --Dbms_DataPump.Set_Parameter(handle => h1, name => 'FLASHBACK_SCN', value => cur_scn);
-- Initial extent clause omit
  dbms_datapump.metadata_transform ( h1, 'STORAGE' , 0 , null ) ;
--  dbms_datapump.metadata_transform ( handle => h1, name => 'SEGMENT_ATTRIBUTES' , value => 'n' ) ;
-- test equivalent to ROWS=N
-- mode rows=N si '1'
  IF v_row = '1' THEN
        dbms_datapump.data_filter(handle=> h1, name=> 'INCLUDE_ROWS' , value=>0);
  END IF;
-- Start the job. An exception will be returned if something is not set up
-- properly.One possible exception that will be handled differently is the
-- success_with_info exception. success_with_info means the job started
-- successfully, but more information is available through get_status about
-- conditions around the start_job that the user might want to be aware of.
    begin
    dbms_datapump.start_job(h1);
    dbms_output.put_line('Data Pump job started successfully');
    exception
      when others then
        if sqlcode = dbms_datapump.success_with_info_num
        then
          dbms_output.put_line('Data Pump job started with info available:');
          dbms_datapump.get_status(h1,
                                   dbms_datapump.ku$_status_job_error,0,
                                   job_state,sts);
          if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
          then
            le := sts.error;
            if le is not null
            then
              ind := le.FIRST;
              while ind is not null loop
                dbms_output.put_line(le(ind).LogText);
                ind := le.NEXT(ind);
              end loop;
            end if;
          end if;
        else
          raise;
        end if;
  end;
-- The export job should now be running. In the following loop, we will monitor
-- the job until it completes. In the meantime, progress information is
-- displayed.
 percent_done := 0;
  job_state := 'UNDEFINED';
  while (job_state != 'COMPLETED') and (job_state != 'STOPPED') loop
    dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip,-1,job_state,sts);
    js := sts.job_status;
-- If the percentage done changed, display the new value.
     if js.percent_done != percent_done
    then
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      percent_done := js.percent_done;
    end if;
-- Display any work-in-progress (WIP) or error messages that were received for
-- the job.
      if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
    then
      le := sts.wip;
    else
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
      else
        le := null;
      end if;
    end if;
    if le is not null
    then
      ind := le.FIRST;
      while ind is not null loop
        dbms_output.put_line(le(ind).LogText);
        ind := le.NEXT(ind);
      end loop;
    end if;
  end loop;
-- Indicate that the job finished and detach from it.
  dbms_output.put_line('Job has completed');
  dbms_output.put_line('Final job state = ' || job_state);
  dbms_datapump.detach(h1);
-- test if v_psw != 'EXIST' and != 'idem' , then set this psw , else password would be the original one from target user
 -- IF v_psw != 'EXIST' and v_psw != 'idem' THEN
   --     execute immediate 'alter user &user_c identified by '||v_psw||' ' ;
--  END IF;
-- Any exceptions that propagated to this point will be captured. The
-- details will be retrieved from get_status and displayed.
  exception
    when others then
      dbms_output.put_line('Exception in Data Pump job');
      dbms_datapump.get_status(h1,dbms_datapump.ku$_status_job_error,0,
                               job_state,sts);
      if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
      then
        le := sts.error;
        if le is not null
        then
          ind := le.FIRST;
          while ind is not null loop
            spos := 1;
            slen := length(le(ind).LogText);
            if slen > 255
            then
              slen := 255;
            end if;
            while slen > 0 loop
              dbms_output.put_line(substr(le(ind).LogText,spos,slen));
              spos := spos + 255;
              slen := length(le(ind).LogText) + 1 - spos;
            end loop;
            ind := le.NEXT(ind);
          end loop;
        end if;
      end if;
END;
/
-- end
-- compile & stats
 exec dbms_output.put_line('** Compilation and Statistics gathering ...') ;
 EXEC DBMS_UTILITY.compile_schema(schema => 'VIJAY_DEV1');
 exec DBMS_STATS.GATHER_SCHEMA_STATS ( 'VIJAY_DEV',DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

Parse xml documents directly from filesystem without loading into Oracle database

First, create a database directory object and grant it read,write privilages.
Create or replace directory xmlload as '/tmp/xmlload';
grant read,write on XMLLOAD to <username>;

-- to avoid some ora-600 errors in 11.2.0.2 version of DB.
alter session set events='31156 trace name context forever, level 0x400';


If a DBA is tasked to read a xml file, he will like to see the output as rows and columns after parsing base on xquery condition...

E.g

WITH vendxml_col AS
(select XMLTYPE(bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8'))vend_doc from dual)
-- Above WITH used to simulate your table/data read from file.
SELECT  a.name,a.operation,a.searchable
  FROM vendxml_col,
       XMLTABLE('for $i in /import_data/template/template_attribute
                                    where $i/@name eq "UPN"
                                    or $i/@searchable eq "n"
                                   return $i'
                PASSING vendxml_col.vend_doc
                COLUMNS
                name varchar2(300) PATH '@name',
operation varchar2(500) PATH '@operation',
searchable varchar2(1) PATH '@searchable') a;

However, if you  want a xml output  as a result of your parsing you can simply read xmltype instead of xmltable.

select xmlquery(
'for $i in /import_data/template/template_attribute
                                    where $i/@name eq "VIJAY"
                                    or $i/@operation_flag=fn:true()
                                   return $i'
PASSING d.xml_doc  returning content) the_result
from
(select xmltype (bfilename('XMLLOAD','template.xml'),NLS_CHARSET_ID('AL32UTF8')) xml_doc from dual) d;

Oracle database link with easy connect, RAC, bequeath connection.

The old way of creating a database link in oracle database is to use the syntax
create database link linkname connect to <username> identified by <password> using 'connect_string';
where connect string maps to a entry in tnsnames.ora.

If you did not want to depend on any files in Oracle local directries, then you had the workaround of  replacing the connect string with the complelete description as follows.
create database  link linkname connect to <username> identified by <password> using
'(DESCRIPTION =   
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.101)(PORT = 1521))   
(CONNECT_DATA =     
(SERVICE_NAME = VIJAY)))';

Now, you can use the same concept and connect using the easy connect syntax as follows.

 create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay';

If this is a RAC environment and you need to access one specific instance, then the syntax would be 

create  database link abc1 connect to <username> identified by <password> using '//10.0.10.101:1521/vijay/vijay1';


If for some reason, you need to create a database link to read data from the same host (loopback ) you could use a bequeath connection in the database as follows.


create   database link beq_link connect to <username> identified by <password>
USING
  '(description=(address=(protocol=beq)(program=/u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle))
    (CONNECT_DATA = (SERVICE = orclprd)))';

oracle 11g DB in a Virtualbox ( vbox)

For those who want to play with Oracle Database 11.2.x in a Linux environment, check this out.

 The only changes i did was to use NAT network adapter to be able to connect to internet from within the VM.

HV enqueue – contention during parallel inserts.

While using insert /*+ append */ to insert into a 11gr2 database, I found 80% of the waits being enq: HV - contention . 

 
     HV enqueue –  From the description of event which says Lock used to broker the high watermark during parallel inserts, you would assume that we have to tweak the insert statement .

Solution:
After few tests, the source of the problem was found to be the underlying datafile extending, The files were extending by only 4M. I resized the datafile to a size i expect the data to grow.
  alter database datafile '/filename'  resize xxG;
The wait event completely disappeared and  the insert was 3 times faster.

identity column in oracle

I was involved in a DB2 to Oracle 11gr2 migration.
There were couple of table in db2 that had identity columns.  These columns get incremented during inserts.
In oracle, the equivalent to this is to use sequence objects in the database.

As always, oracle gives you more control than any other database. I can think of two methods that could be used to implement identity columns.

•  Sequence only
• Sequence + trigger.

 Sequence only : In oracle you could simply create a sequence object and call  sequenceName.nextvalue in the insert statement. this is most efficient. This is the  method mostly implemented by Oracle DBAs/applications.
e.g. insert into test_tab values ( sequence.nextvalue,' testvalue'); 

Sequence + Trigger : This method is slightly more code than the first option. One advantage of this method is that your sql code could run on both oracle and DBs that have identity columns. You still have to ensure that you list all the columns you are inserting  and skip the identity column.
eg. insert into test-tab(ename) values ('testvalue');
 

In the below test, i could insert 100000 rows with no trigger in 10 seconds and 26 seconds with triggers.
The difference may be very insignificant for most of the cases.
I also found no difference in performance with the IF condition in the trigger. So it is a good to have the IF condition in place so that they do not get fired with we try to load data into the table including the column having the trigger.


SQL> @trig
SQL>
SQL> --create table trig_tab nologging parallel as select *     from testvijay;
SQL> --create sequence trig_tab_seq START WITH 1 INCREMENT BY 1 NOCYCLE cache 1000;
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5      IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7      END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.12
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.02
SQL> set timing on
SQL> set echo on
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.26
SQL> commit;

Commit complete.

Elapsed: 00:00:00.95
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5   --   IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7    --  END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.13
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.03
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.23
SQL> commit;

Commit complete.

Elapsed: 00:00:04.80
SQL> alter trigger trig_tab_tr disable;

Trigger altered.

Elapsed: 00:00:00.09
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,object_id,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,trig_tab_seq.nextval,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:10.08
SQL> commit;

Commit complete.

Elapsed time or exeution time for Oracle sqls in 11g

Oracle is perhaps the best database available out there when it comes to diagnostics and performance monitoring tools. The captured data can tell us the number of times events were called and the time waited for each event.

To find the elapsed time of a sql in 10g timeframe, we usually look at v$sqlstats or dba_hist_sqlstats. The average elapsed time of a sql is equal to sum( elspsed_time) /sum(executions) from v$sqlstats or
sum(elapsed_time_delta)/sum(executions_delta) from dba_hist_sqlstat.
When a sql rans in parallel, there was a problem with the accuracy of elapsed_time.

The elapsed time adds the time spent by the individual parallel slaves for a sql. So for simple sql quries, where all the parallel slaves were busy until the end of the sql, we could still get the elapsed time by dividing the total_elapsed_time by number of parallel slaves. i.e sum (elapsed_time_delta)/sum(px_servers_executions). But what if not all slaves were busy , then the above formulate would not work.

The solution to this problem is in v$active_session_history and dba_hist_active_sess_history and the introduction of a new column SQL_EXEC_START in 11g.

Oracle background process looks at v$session information and updates v$active_session_history every second. A new column introduced in 11g called sql_exe_start now logs the time the sql started its execution.

So now you can calculate the elapsed time by looking for the maximum sampled time the sql was running with the same start time ( sql_exec_start). Sample sql script below.

Since the precision of v$active_session_history is in seconds, The precisions of the result is one second. And for sql queries taking less than 1 second, the traditional way of looking at v$sqlstat should be good enough as in most cases, those queries may not run in parallel.


I have observer that dba_hist_active_sess_history records sample data from v$active_session_history every 10 seconds. Now that would mean that the precision of elapsed_time for sqls  would be 10 seconds from dba_hist_active_sess_history table.

select sql_id,round(avg(exe_sec1),3) ela_per_exe from (

select sql_exec_start, sql_id,

  to_char(max(sample_time),'SSSSS')

- to_char(sql_exec_start,'SSSSS') exe_sec1
from v$active_session_history
where
sql_id is not null and
sql_exec_start is not null
group by sql_exec_start, sql_id
having

  to_char(max(sample_time),'SSSSS')

  -to_char(min(sample_time),'SSSSS') > 1 )
group by sql_id;