Migrating from Access to Oracle

Two Options.  One provides a lot more detail if you need it (constraints, indexes, ddl for tables, control files for Sql Loader, etc.).  The other simply creates a delimited text file that can then be imported using SQL Loader.

Option A - Access Export.
Pretty simple strategy here.

1. Open the Access file.
2. Go to the Tables section.
3. Double click on the table to open it.
4. Right click on the table, then choose Export > Text File.
5. In the dialog box, choose a file name.  Make sure the 3 boxes are all unchecked.  Click OK.
6. Choose Delimited, then click Next.
7. Choose Comma delimited, with a Text Qualifier of a double quote.
8. Click on Next, then Finish.
9. In Oracle, create a table that matches the Access one. 
10. Create the control file for Sql Loader.
11. Now use Sql Loader to load the data into your new Oracle table.

Option B - Sql Loader.
This did not work as advertised for me.  Here are the steps I took to finally be able to migrate a single table from Access to Oracle, using the Sql Developer utility to do this.

1. Use SqlDeveloper 3, not the newest 4.  4 does not seem to map any data types, so it fails miserably.
2. Connect to the target instance.
3. Right click, Migration Repository, then Associate Migration Repository.
4. Tools, Migration, Microsoft Access Exporter, Run Microsoft Access 2007 Exporter....
5. This should bring up Access.  Pretty simple.  Pick the mdb to migrate, make sure the "migrate data" is checked.  I used c:\temp for the output directory.
6. Now, back in SqlDeveloper, Tools, Migration, Migrate.  Pick your target connection, give it a name, choose an output directory.  Whenever you have the choice, pick "Offline"!  This is key, as we are going to hack some files when we are done.
7. Once the migration finishes, it should bring up the script in a worksheet.  Just find the part with the create table ddl, and run that to create your target table.
8. Now that you have an empty table, shell out to a command prompt and drill down until you find a control file for Sql Loader.  Put in the correct location of the data file.  For me, I put this all under c:\temp, so after drilling around a bit it wasn't too hard to locate the data and ctl files.
9. Also, check your date formats.  For me, it was not really close.  The dates were in a mm/dd/yyyy hh24:mi:ss format, but the control file had them masked completely wrong.
10. Now, use Sql Loader to load the data into your newly created table.

Ugly, but it did finally work.

Mike

Drop db links and jobs in other schemas

A quick workaround to working in other schemas when you don't know the password.

############################################################################

if [ "${1}" = "" ] ; then
  echo "Usage ${0} sid,  exiting..."
  exit 1
fi
. /home/oracle/.bashrc.CLIENT

TARGET_SID=${1}
PRIVATE_FILE=/home/oracle/.private

MASTER_USER=system
MASTER_PASS=`cat $PRIVATE_FILE|grep -i "${TARGET_SID}:"|grep ":SYSTEM:"|head -1|awk -F: '{print $3}'`
echo $MASTER_USER
echo $MASTER_PASS

# Find 'em and Drop 'em
sqlplus -s "$MASTER_USER/$MASTER_PASS@$TARGET_SID" <<EOF
set pages 9999
set lines 300
set trim on
set trims on
set feedback off
set termout off
set verify off
set head off
set newpage none
set echo off
set term off
spool OUT/create_procedures.sql

select 'create or replace procedure '||owner||'.drop_dblink_'||rownum||' as '||chr(10)||'begin'||chr(10)||'execute immediate ''drop database link '||db_link||''';'||chr(10)||'end;'||chr(10)||'/'||chr(10)
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN')
order by owner, db_link;


spool off;

spool OUT/run_procedures.sql

select 'exec '||owner||'.drop_dblink_'||rownum||';'||chr(10)||'commit;'||chr(10)
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'STRMADMIN')
order by owner, db_link;

spool off;

spool OUT/drop_procedures.sql

select 'drop procedure  '||owner||'.drop_dblink_'||rownum||';'
from dba_db_links
where owner not in ('SYS', 'SYSTEM', 'SYSMAN', 'STRMADMIN')
order by owner, db_link;

spool off;

@ OUT/create_procedures.sql
@ OUT/run_procedures.sql
@ OUT/drop_procedures.sql
exit
EOF


########################################################################

if [ "${1}" = "" ] ; then
  echo "Usage ${0} sid,  exiting..."
  exit 1
fi
. /home/oracle/.bashrc.CLIENT

TARGET_SID=${1}
PRIVATE_FILE=/home/oracle/.private

MASTER_USER=system
MASTER_PASS=`cat $PRIVATE_FILE|grep -i "${TARGET_SID}:"|grep ":SYSTEM:"|head -1|awk -F: '{print $3}'`
echo $MASTER_USER
echo $MASTER_PASS

# Find 'em and Break 'em
sqlplus -s "$MASTER_USER/$MASTER_PASS@$TARGET_SID" <<EOF
set pages 9999
set lines 300
set trim on
set trims on
set feedback off
set termout off
set verify off
set head off
set newpage none
set echo off
set term off
spool OUT/create_procedures.sql

select 'create or replace procedure '||schema_user||'.drop_job_'||job||' as '||chr(10)||'begin'||chr(10)||'dbms_job.broken ('||job||', true);'||chr(10)||'end;'||chr(10)||'/'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';


spool off;

spool OUT/run_procedures.sql

select 'exec '||schema_user||'.drop_job_'||job||';'||chr(10)||'commit;'||chr(10)
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

spool OUT/drop_procedures.sql

select 'drop procedure  '||schema_user||'.drop_job_'||job||';'
from dba_jobs
where schema_user not in ('SYS', 'SYSTEM', 'SYSMAN')
and broken = 'N';

spool off;

@ OUT/create_procedures.sql
@ OUT/run_procedures.sql
@ OUT/drop_procedures.sql
exit
EOF

Desktop Linux

I've been running Ubuntu on my desktop, since leaving Novell back in 2012.  I was feeling a bit nostalgic the other day, and decided to build out a VM with the latest version of OpenSUSE.  Let me share a few bullet points with you about this experience.

1.  The install of OpenSUSE took twice as long.

2.  Boot time is incredibly slow with OpenSUSE.  Honestly, I can start booting it, go get a drink, startup my Ubuntu VM, reboot it a couple of times, get up and go to the bathroom, come back and OpenSUSE will still be booting.

3.  Response time is even slower.  It's painful actually.

4.  The update process is HORRIBLE.  When OpenSUSE first popped up and told me I had 174 updates, I thought, OK, no big deal, right?  Well the graphical update went into some sort of infinite loop.  I had to kill it after about the 6th time it restarted itself.  In the end, I opted for a simple "zypper patch" as root at the command line.  That  sucker has been running for over 2 hours.  By comparison, Ubuntu updates rarely take in excess of 5 minutes.

As much as I miss the good old Novell days, I think I am converted to running Ubuntu.

I must say, running OpenSUSE in seamless mode does  rock.  It is the one thing I've found it does better than Ubuntu.

Access SQL Server from Linux

Let's face it.  Even since back before they started labeling the sql server releases as 2000 whatever, there has been a need to access sql server from other operating systems.  I'm sure java would be a slick way to go, but for an  administrator used to living at a bash prompt, that just won't cut it.

Thankfully, as part of some consulting work, I stumbled upon FreeTDS.  It's pretty slick.
1.  Download FreeTDS from http://www.freetds.org/

2.  Install FreeTDS
 - copy the compressed file to the target server.
 - uncompress the software bundle.
 - cd into the FreeTDS source.
 - ./configure
 - make
 - make install
 - the default is to install to /usr/local

3.  Configure FreeTDS
 - cd /usr/local/etc
 - vi freetds.conf
 - add in a line for the target server
 # A typical Microsoft server
 [EvolveDB]
     host = 137.65.51.90
     port = 1433
     tds version = 7.0

4.  Query your server
 tsql -S <your name from the freetds.conf file> -U <username> -P <password>
 If my user was admin and my password was friday, server name EvolveDB
 tsql -S EvolveDB -U admin -P friday

5.  Use tsql in batch mode
 tsql is very limited.  Simply use redirects to either run scripts or capture output.
 For example, using the above connection, and running a script named mike.sql and capturing that to mike.out
 tsql -S EvolveDB -U admin -P friday < mike.sql > mike.out

I've found that tsql does some funky things with the output.  For example, you can pipe it directly to awk, but if you toss sed in there as well to just remove spaces, it blitzes the tabs as well.  I've had the best luck sticking a ~ in as a delimiter, then using that with awk to parse the columns back out.

Test writing to an Oracle directory

Let's say you want to make sure you can write to a directory you created.

1.  select * from dba_directories where directory_path = '/usr/tmp';

2.  In my case, I had three.  I decided to test the ODPDIR directory.

3.
DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'ODPDIR', 'ODPDIR.txt', 'W' );
  utl_file.put_line( l_file, 'Here is some text' );
  utl_file.fclose( l_file );
END;
/

4.  I ssh into my server, cd to /usr/tmp, and I see a file called ODPDIR.txt has been created.  Whoot!

Utility and Setup for Testing JDBC Connections

This is a Windows based solution.  Sorry Linux fans.  The utility is available in a Linux format, so I would assume this could easily be ported to Linux.

This was used for testing Oracle connections, but the utility should support any standard jdbc connection.

1.  Ensure java is installed and in your PATH.

2.  Download Jisql from http://www.xigole.com/software/jisql/jisql.jsp.  Note the names in the uncompressed LIB directory.

3.  Ensure you have the Oracle JDBC drivers installed and note the path to the jar files.

4.  Now, you should be ready for some simple testing.  A simple batch file like this will do the trick.
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using SID >> logfile.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
         -driveroraclethin -cstring jdbc:oracle:thin:@address-scan:1521:sidname-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using SERVICE_NAME >> z:\mike\runit.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
    -driver oraclethin -cstring jdbc:oracle:thin:@//address-scan:1521/servicname.yourcompany.com -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT

5.  You can alternatively use TNS entries for testing the connections.  This was the best way I found to test a more complicated setup, such as load balanced, fault tolerant connection.  Something like this: <em>(note - I made a copy of the tnsnames.ora file to c:\temp then trimmed it down to just have the two entries I wanted to test)</em>
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using TNSENTRY>> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@TNSENTRY-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using TNSENTRY >> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@WAREHOUSE3 -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT

Commit Strategy for Large Transactions

Let's face it.  Every DBA has come across a need to update a LOT of records.  The problem usually is that a mass update can take forever, blow out your redo logs, lock out other access, etc.  Here is the solution.  This was written and used in Oracle, but I'm thinking could easily be modified to work with any ANSI compliant relational database.

DECLARE

CURSOR cur IS
   select * from owner.table_temp;
                 
ct NUMBER(5) := 0;
group_size NUMBER(5) := 5000;

BEGIN
    FOR srmvt in cur LOOP
        INSERT INTO owner.table   (<columns>)
            VALUES  (srmvt.<columns>);
       ct := ct + 1;
       IF ct >= group_size THEN
           commit;
           ct := 0;
       END IF;
   END LOOP;
   commit;
END;

I have also used this for updates as well.

DECLARE
CURSOR cur2 IS  
   select * from yourcompany.yourtable;
                
ct2 NUMBER(5) := 0;
group_size NUMBER(5) := 5000;
BEGIN
   FOR srmvt2 in cur2 LOOP
        update yourcompany.yourtable set process_flag = 'SUCCESS'
        where transactionid = srmvt2.transactionid;
       ct2 := ct2 + 1;
       IF ct2 >= group_size THEN
           commit;
           ct2 := 0;
       END IF;
   END LOOP;
   commit;
END;
/