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)))';