POUWIEL|COM

HOST echo %NLS_LANG% > Y:\temp\NLS_CHAR_SESSION.lst

DECLARE
  dbs_nls  varchar2(4000);
  ses_nls  varchar2(4000);
--
  file_contents VARCHAR2(32767) := '
@@Y:\temp\NLS_CHAR_SESSION.lst
';
--
BEGIN
--
  file_contents := TRIM(replace(replace(file_contents,CHR(13),''),CHR(10),' '));
  ses_nls := file_contents;
  SELECT a.value||'_'||b.value||'.'||c.value INTO dbs_nls
    FROM (select value from nls_database_parameters where parameter = 'NLS_LANGUAGE') a,
         (select value from nls_database_parameters where parameter = 'NLS_TERRITORY') b,
         (select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET') c;
--
  IF(NVL(dbs_nls,'X') != NVL(ses_nls,'Y')) THEN
    IF (UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252') OR (UPPER(ses_nls) = 'AMERICAN_AMERICA.WE8ISO8859P1' AND UPPER(dbs_nls) = 'AMERICAN_AMERICA.WE8MSWIN1252')
      dbms_output.put_line('INFO: ');
      dbms_output.put_line('  CHARACTERSET WE8MSWIN1252 is a binary superset of WE8ISO8859P1')
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    ELSE
      dbms_output.put_line('##### WARNING!!! ##############################################');
      dbms_output.put_line('  The two CHARACTERSET settings are not equal !!!');
      dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
    END IF;
  ELSE
    dbms_output.put_line('INFO: ');
    dbms_output.put_line('  The two CHARACTERSET settings are equal');
    dbms_output.put_line('  DATABASE:: '||dbs_nls|| CHR(10) || '  SESSION :: '||ses_nls);
  END IF;
--
END;
/

CREATE TABLE ballage2 ( X NUMBER(10), Y VARCHAR2(24 CHAR), Z DATE )
/

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

CREATE OR REPLACE PACKAGE PNL_BALLAGE2 IS 
  type cg$row_type is record
    ( x                      ballage2.x%type
    , y                      ballage2.y%type
    , z                      ballage2.z%type
    ) ;
--------------------------------------------------------------------------------
  procedure ins( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure upd( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure del( p_ballage2_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  procedure lck( p_ballage2_old_rec in cg$row_type ) ;
--------------------------------------------------------------------------------
  function slct( p_x	    	in ballage2.x%type )
  return cg$row_type ;
--
  cg$tmp_rec cg$row_type ;
--
  type t_ballage2_tabtype is table of cg$row_type index by binary_integer ;
end PNL_BALLAGE2 ;
/

CREATE OR REPLACE PACKAGE BODY PNL_BALLAGE2 IS
  PROCEDURE ins( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    INSERT INTO ballage2
      ( x
      , y
      , z
      )
      VALUES
      (       p_ballage2_rec.x
      , NVL ( p_ballage2_rec.y , 'Initiële actie' )
      ,       TO_DATE(p_ballage2_rec.z, 'YYYY/MM/DD HH24:MI:SS') )
      ;
  --
  END ins ;
  --------------------------------------------------------------------------------
  PROCEDURE upd( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    UPDATE ballage2
    SET    y						= p_ballage2_rec.y
    ,      z            = p_ballage2_rec.z
    WHERE  x						= p_ballage2_rec.x
    ;
  --
  END upd ;
  --------------------------------------------------------------------------------
  PROCEDURE del( p_ballage2_rec in cg$row_type )
  IS
  BEGIN
    DELETE FROM ballage2
    WHERE  x						= p_ballage2_rec.x
    AND    y						= p_ballage2_rec.y
    AND    z						= p_ballage2_rec.z
    ;
  --
  END del ;
  --------------------------------------------------------------------------------
  PROCEDURE lck( p_ballage2_old_rec in cg$row_type )
  IS
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   cg$tmp_rec.x
    ,      cg$tmp_rec.y
    ,      cg$tmp_rec.z
    FROM   ballage2
    WHERE  x						= p_ballage2_old_rec.x
    AND    y						= p_ballage2_old_rec.y
    AND    z						= p_ballage2_old_rec.z
    FOR UPDATE
    ;
  --
  END lck ;
  --------------------------------------------------------------------------------
  FUNCTION slct(p_x in ballage2.x%type)
  RETURN cg$row_type
  IS
    r_ballage2_rec cg$row_type DEFAULT NULL;
  BEGIN
    SELECT x
    ,      y
    ,      z
    INTO   r_ballage2_rec.x
    ,      r_ballage2_rec.y
    ,      r_ballage2_rec.z
    FROM   ballage2
    WHERE  x            = p_x
    ;
  --
    RETURN r_ballage2_rec ;
  --
  END slct;
END PNL_BALLAGE2;
/

DECLARE 
  my_data ballage2%rowtype;
BEGIN 
  my_data.x := 106; 
  my_data.y := 'JACKING';
  my_data.z := TO_DATE('2017/06/19 15:00:01', 'YYYY/MM/DD HH24:MI:SS');

  pnl_ballage2.ins( my_data ); 
END; 
/

DECLARE
  CURSOR my_data_cur
    IS
    SELECT x, y, z
      FROM ballage2
     WHERE x=102;

  my_data my_data_cur%ROWTYPE;
BEGIN
  OPEN my_data_cur;
  FETCH my_data_cur INTO my_data;
  my_data.y := 'altered via upd';
  CLOSE my_data_cur;
  commit;
  pnl_ballage2.upd( my_data );
END;
/

DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 102;

  my_data.y := 'altered again via upd';

  pnl_ballage2.upd( my_data );
END;
/


DECLARE
  my_data ballage2%ROWTYPE;
BEGIN
  SELECT *
   INTO my_data
   FROM ballage2
  WHERE x = 103;

  pnl_ballage2.del( my_data );
END;
/

DECLARE
my_data ballage2%ROWTYPE;
p1      varchar2(16);
BEGIN
SELECT *
    INTO my_data
    FROM ballage2
   WHERE x = 102;

pnl_ballage2.lck( my_data );
my_data.y := 'altered using lck';
dbms_lock.sleep(20);
pnl_ballage2.upd( my_data );
commit;
END;
/


DECLARE
  v_ret ballage2%ROWTYPE;
BEGIN
  v_ret:=pnl_ballage2.slct(104);
  DBMS_OUTPUT.PUT_LINE(v_ret.x||', '||v_ret.y||', '||v_ret.z);
END;
/

Just like this, sure it can be extended with some checks and other nifty things..:

DECLARE
  counter     number  := 1 ;
  -- declare a varchar variable to contain the
  -- TO_CHAR string, as DATE type is actually 
  -- a number
  v_date2char varchar2(50) ;
  --
  CURSOR c_data_ballage
    IS
    SELECT code
    ,      description
    ,      TO_CHAR(completion, 'YYYY/MM/DD HH24:MI:SS') AS completion
      FROM ballage ;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000) ;
	
  DBMS_OUTPUT.PUT_LINE('DECLARE') ;
  DBMS_OUTPUT.PUT_LINE('  lStart            number := 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('  CODE_ARRAY        dbms_sql.number_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;') ;
  DBMS_OUTPUT.PUT_LINE('  COMPLETION_ARRAY  dbms_sql.date_table ;') ;
  DBMS_OUTPUT.PUT_LINE('BEGIN') ;
  DBMS_OUTPUT.PUT_LINE(' ') ;
	
  FOR data_record IN c_data_ballage LOOP
    -- As moment of writing Oracle database considers NULL and empty string ('')
    -- as equal, as show in sample below:
    -- SQL> SELECT NVL(NULL,'This returns NULL') A,NVL('','This will too') B FROM DUAL;  
    -- A                 B  
    -- ----------------- -------------
    -- This returns NULL This will too
		
    IF data_record.completion IS NOT NULL THEN
      v_date2char := 'TO_DATE('''||data_record.completion||''', '''||'YYYY/MM/DD HH24:MI:SS'||''')' ;  
    ELSE
      v_date2char := ''''||data_record.completion||'''' ;
    END IF ;
		
    DBMS_OUTPUT.PUT_LINE('    CODE_ARRAY('||counter||') := '  || data_record.code ||' ;') ;
    DBMS_OUTPUT.PUT_LINE('    DESCRIPTION_ARRAY('||counter||') := '''|| data_record.description ||''' ;') ;
    DBMS_OUTPUT.PUT_LINE('    COMPLETION_ARRAY('||counter||') := '|| v_date2char ||' ;') ;
    counter     := counter + 1 ;
    v_date2char := NULL ;
  END LOOP ;
	
  DBMS_OUTPUT.PUT_LINE('    LOOP') ;
  DBMS_OUTPUT.PUT_LINE('    BEGIN') ;
  DBMS_OUTPUT.PUT_LINE('    	FORALL i IN lStart..CODE_ARRAY.COUNT') ;
  DBMS_OUTPUT.PUT_LINE('    		INSERT INTO ballage VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;') ;
  DBMS_OUTPUT.PUT_LINE('    	EXIT ;') ;
  DBMS_OUTPUT.PUT_LINE('    EXCEPTION WHEN OTHERS THEN') ;
  DBMS_OUTPUT.PUT_LINE('    	lStart := lStart + sql%ROWCOUNT + 1 ;') ;
  DBMS_OUTPUT.PUT_LINE('    END ;') ;
  DBMS_OUTPUT.PUT_LINE('  END LOOP ;') ;
  DBMS_OUTPUT.PUT_LINE('END ;') ;
  DBMS_OUTPUT.PUT_LINE('/') ;
END ;
/

This will generate the following output (for my “ballage” table that is):

DECLARE
  lStart            number := 1 ;
  CODE_ARRAY        dbms_sql.number_table ;
  DESCRIPTION_ARRAY dbms_sql.varchar2_table ;
  COMPLETION_ARRAY  dbms_sql.date_table ;
BEGIN

    CODE_ARRAY(1) := 101 ;
    DESCRIPTION_ARRAY(1) := 'WRITING' ;
    COMPLETION_ARRAY(1) := TO_DATE('2017/02/27 14:00:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(2) := 102 ;
    DESCRIPTION_ARRAY(2) := 'PROCESSING' ;
    COMPLETION_ARRAY(2) := TO_DATE('2017/03/14 15:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(3) := 103 ;
    DESCRIPTION_ARRAY(3) := 'SEEDING' ;
    COMPLETION_ARRAY(3) := TO_DATE('2017/05/08 11:28:01', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(4) := 104 ;
    DESCRIPTION_ARRAY(4) := 'TRANSFORMING' ;
    COMPLETION_ARRAY(4) := TO_DATE('2018/07/07 17:30:00', 'YYYY/MM/DD HH24:MI:SS') ;
    CODE_ARRAY(5) := 105 ;
    DESCRIPTION_ARRAY(5) := 'BAWLING' ;
    COMPLETION_ARRAY(5) := '' ;
    LOOP
    BEGIN
        FORALL i IN lStart..CODE_ARRAY.COUNT
                INSERT INTO ballage2 VALUES (CODE_ARRAY(i), DESCRIPTION_ARRAY(i), COMPLETION_ARRAY(i) ) ;
        EXIT ;
    EXCEPTION WHEN OTHERS THEN
        lStart := lStart + sql%ROWCOUNT + 1 ;
    END ;
  END LOOP ;
END ;
/

ascii dumps and converts to multilangual hexes.
US7ASCII is outdated, it slowely drives me insane.
Those are a few of my least favorite things…

Here we are, struggling again. *sigh…

The skinny:
Read through Oracle’s NLS_LANG faq. Again. Don’t argue, just do it. Yes, Again!
Check your codepage, I’ll assume your on Windows otherwise you wouldn’t be in this mess.
For arguments sake, let’s assume it’s 850.

Microsoft Windows [Version 6.0.6002]
Copyright (c) 2006 Microsoft Corporation.  All rights reserved.

c:\Windows>chcp
Active code page: 850

Set your NLS_LANG to: american_america.we8pc850

Start SQLPlus.

Note to the weary:
When working remotely via Citrix, it’s at all good possible you’ll check your codepage, determine it’s that 850, set your NLS_LANG to american_america.we8pc850, run your script that’s located on a mounted share and still have garbage.
That.. that I can’t help you with.. Still need to figure that one out. I suppose it’s due to the fact that the scripts are compiled on another system, placed on the share and both environments may have different codepages than 850. Maybe another time, eh!?

Update ::
According to RedGrittyBrick on superuser.com, it seems that Notepad uses ANSI as another word for WE8MSWIN1252. Set your NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and be golden again…

Running Windows Server 2012 R2 HYPER-V Core but what to do regarding backups..!?

Easy.

Install the Windows Server Backup feature. As I am using HYPER-V Core, I can install the feature using server manager on my laptop.
Than, create a shared folder on my HYPER-V server with:

net share BCK=C:\BCK /GRANT:Everyone,FULL

And finally, backup the virtual machine using wbadmin.exe, like so:

WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:<vm_name>

Because I’ve got Swiss cheese for memory, I created a little script to do this for me:

@ECHO OFF

:HOUSEKEEPING
ECHO.

:CHECKFORSWITCHES
IF -%1-==-- (
    ECHO.:: ##########!! WARNING !!##############################################################
    ECHO.:: ## No parameters were passed. Please look through the Usage section
    ECHO.:: ##  & GOTO DISPINFO
) ELSE IF '%1'=='/H' (
    GOTO DISPINFO
) ELSE IF '%1'=='/h' (
    GOTO DISPINFO
) ELSE IF '%1'=='/?' (
    GOTO DISPINFO
) ELSE IF EXIST "C:\VMS\%1" (
    GOTO MAIN
) ELSE (
    ECHO.:: ##########!! WARNING !!##############################################################
    ECHO.:: ## Provided parameter was not recognized/valid. Please look through the Usage section
    ECHO.:: ##  & GOTO DISPINFO    
)

:DISPINFO
ECHO.:: ##########~~ INFORMATION ~~##########################################################
ECHO.:: ## BACKUP
ECHO.::
ECHO.:: Description:
ECHO.::     Backs up a single Virtual Machine to the local shared folder BCK @ \\<servername>\BCK
ECHO.::     The command used is: "WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:{vm_name}"
ECHO.::
ECHO.:: Usage:
ECHO.::     c:\Users\Administrator\Documents\backup.bat {vm_name}
ECHO.::
ECHO.:: Parameter List:
ECHO.::     /?             Displays this help message.
ECHO.::     vm_name        When given a vm_name, it will be passed as argument to the wbadmin program
ECHO.::
ECHO.:: Resources:
ECHO.::     WBADMIN        @ https://blogs.msdn.microsoft.com/virtual_pc_guy/2013/02/25/backing-up-hyper-v-virtual-machines-from-the-command-line/
ECHO.::                    @ https://virtualizationreview.com/blogs/virtual-insider/2013/02/back-up-hyper-v-vms.aspx
ECHO.::
ECHO.::     BATCH script   @ http://stackoverflow.com/questions/26551/how-to-pass-command-line-parameters-to-a-batch-file
ECHO.::                    @ http://stackoverflow.com/questions/13684259/show-help-message-if-any-command-line-argument-equals
ECHO.
ECHO.
GOTO :END

:MAIN
set arg1=%1
echo starting "WBADMIN START BACKUP -backupTarget:\\<servername>1\BCK -hyperv:%arg1%"
WBADMIN START BACKUP -backupTarget:\\<servername>\BCK -hyperv:%arg1%
:END

May not be the most elegant of code or robust, but it gets the job done.

//JP

DECLARE
  var_mod number;
  CURSOR c1 IS
    SELECT tablespace_name, ROUND(SUM(bytes)/(1024*1024),0) AS mb
      FROM dba_data_files
     WHERE tablespace_name IN (SELECT DISTINCT tablespace_name
                                 FROM dba_segments
                                WHERE owner IN ('<schema_name>','<schema_name>','<schema_name>','<schema_name>'))
    GROUP BY tablespace_name
    ORDER BY 1;
BEGIN
   FOR tblspc IN c1
   LOOP
     var_mod := FLOOR(tblspc.mb/9216);
     IF tblspc.mb <= 9216 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE '||tblspc.mb||'M AUTOEXTEND ON MAXSIZE 9G;');
     ELSIF REMAINDER(tblspc.mb, 9216) = 0 THEN
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     ELSE
       DBMS_OUTPUT.PUT_LINE ('CREATE TABLESPACE '||tblspc.tablespace_name||' DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       FOR i IN 1..var_mod LOOP
         var_mod := var_mod - 1;
         DBMS_OUTPUT.PUT_LINE ('ALTER TABLESPACE '||tblspc.tablespace_name||' ADD DATAFILE SIZE 9G AUTOEXTEND ON MAXSIZE 9G;');
       END LOOP;
     END IF;
     var_mod :=0;
   END LOOP;
END;
/

This piece of code will gather all used tablespaces by given schema's and output create statements for those tablespaces broken down into separate files of max. 9G.
Use at your own leisure and discretion... As always: No support and no responsibility

source: tech-recipes
To remove the carriage-return character from a windows originating file in vi:

:%s/<Ctrl-V><Ctrl-M>/g

or
:%s/\r//g

In UNIX, you can escape a control character by preceeding it with a CONTROL-V. The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).

Fill/alter in the required parameters and Ctrl-C/Ctrl-V:

DECLARE
  h1   number;
BEGIN
  h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'SCHEMA', job_name => '<JOB_NAME__MAKE_THIS_UPPERCASE!!!>', version => 'COMPATIBLE', remote_link=>'<DB_LINK_NAME>');
  dbms_datapump.set_parallel(handle => h1, degree => 1);
  dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
  dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN(''<SCHEMA_NAME>'')');
  dbms_datapump.metadata_filter(handle => h1, name => 'NAME_EXPR', value => 'NOT IN(''<EXCLUDED_TABLE01>'',''<EXCLUDED_TABLE02>'',''<EXCLUDED_TABLE03>'')', object_type => 'TABLE');
  dbms_datapump.set_parameter(handle => h1, name => 'FLASHBACK_TIME', value => 'TO_TIMESTAMP (to_char(sysdate,''dd.mm.yyyy HH24:MI:SS''),''dd.mm.yyyy HH24:MI:SS'')');
  dbms_datapump.set_parameter(handle => h1, name => 'ESTIMATE', value => 'BLOCKS');
  dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
  dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
END;
/

Just a quick bite:
find and pre-/append for formatting datafiles to use in create controlfile script:

find /m*/oradata/${SID} -type f | sed "s/^/'/;s/$/'/"

Result:

'/m001/oradata/database01/database01pdtdc00101.dbf'
'/m001/oradata/database01/database01posdl01.dbf'
'/m001/oradata/database01/database01posdm03.dbf'
'/m001/oradata/database01/database01sysaux01.dbf'
'/m002/oradata/database01/database01pserrc00201.dbf'
'/m002/oradata/database01/database01phvcf00101.dbf'

On any schema, you’d need an any-privilege…
Which so happens to be: ANALYZE ANY DICTIONARY and ANALYZE ANY

So, give these to user you want to call the dbms_stats package and all will be fine…

In this case, I created this tiny script: Read the rest of this entry »

For this setup, I create a folder “./out” and a folder “./log” on the OS and the becessary pipes.
In the source database, I create as many users I need to, each of these user has at least the privilege to export the entire database.
If you add (and I did), FGA policies on tables of a specific user, you can simulate a parallel export in database version that do not support this natively. Read the rest of this entry »

Again, a query for retrieving the number of open cursors per SID for the copy/paste-minded dba’s out there: Read the rest of this entry »

You add a datafile, you backup your controlfile. It’s that simple…. Read the rest of this entry »