Oracle - Copy data from A to B while rejecting duplicates

Copying data from table A to B while rejecting duplicates

 

An easy way to copy data from one table to another while ignoring duplicate records is by using the DBMS_ERRLOG package.

The DBMS_ERRLOG package provides a procedure that enables you to create 
an error logging table so that DML operations can continue after encountering 
errors rather than abort and roll back.

To start off you need to create a logging table.

The syntax for creating the table is as follows:

DBMS_ERRLOG.CREATE_ERROR_LOG (
   dml_table_name            IN VARCHAR2,
   err_log_table_name        IN VARCHAR2 := NULL,
   err_log_table_owner       IN VARCHAR2 := NULL,
   err_log_table_space       IN VARCHAR2 := NULL,
   skip_unsupported          IN BOOLEAN := FALSE);

To creates a logging table, only the table name is required.

 exec dbms_errlog.create_error_log('TableA');

This create a table called ERR$_TableA in the current schema based on the table TableA.

You can then copy data from the source table to the target. Duplicate records are rejected and logged but the statement will not fail.

INSERT INTO my_schema.TableB
SELECT *
FROM my_schema.TableA
LOG ERRORS INTO my_schema.ERR$_TableA ('already exists') REJECT LIMIT UNLIMITED;

 

ORA-32028: Syslog facility or level not recognized

Recently I had the opportunity to update the audit settings on an Oracle database. It seemed fairly straight forward: alter the system parameters then recycle the database. Easy!!

Upon issuing the startup command the below error was displayed. 

 ORA-32028: Syslog facility or level not recognized

The issue was because I didn't use single quotes in the alter system command

alter system set audit_trail=NONE

Instead of:

alter system set audit_trail=’NONE’

To fix this issue the value in the database parameter file needed to be amended.

With the database offline, create a copy of the pfile.

create pfile='/tmp/initDB1.ora' from spfile;

Then edit the value.

Edit the pfile

vi /tmp/initDB1.ora

......

*.audit_sys_operations=FALSE

*.audit_syslog_level=' '

*.audit_trail='NONE'

.....

After changing the parameter, start the database using the amended pfile.

sqlplus / as sysdba

startup pfile = /tmp/initDB1.ora

Then create an spfile from the good pfile.

create spfile from pfile='/tmp/initDB1.ora';

This saves an spfile to $ORACLE_HOME/dbs which has the parameters that are in the pfile.

ls -lrt $ORACLE_HOME/DBS

rw-r-----    1 oracle   dba            3584 11 Nov 12:33 spfileDB1.ora

Finally restart the database to check that it now starts successfully when it uses the spfile.

shutdown immediate

startup

Oracle – DB Temp flle has been deleted!

Actions to take when the temp file has been accidentally deleted

If the temp file is removed / deleted when the database is shut down then the DB is clever enough to realize that its missing and should create a new file when it’s brought back online.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/temp01.dbf

[oracle@localhost orcl]$ ls

afiedt.buf                                       APEX_2614203650434107.dbf     redo03.log

APEX_1930613455248703.dbf     control01.ctl                                  sysaux01.dbf

APEX_2041602962184952.dbf     example01.dbf                               system01.dbf

APEX_2610402357158758.dbf     redo01.log                                     undotbs01.dbf

APEX_2611417663389985.dbf     redo02.log                                     users01.dbf

SQL> startup

[oracle@localhost orcl]$ ls

afiedt.buf                                        control01.ctl                   system01.dbf

APEX_1930613455248703.dbf      example01.dbf                temp01.dbf

APEX_2041602962184952.dbf      redo01.log                      undotbs01.dbf

APEX_2610402357158758.dbf      redo02.log                      users01.dbf

APEX_2611417663389985.dbf      redo03.log

APEX_2614203650434107.dbf      sysaux01.dbf

* Temp01 file has been magically re-created

If the database is up when the file is removed then you can manually re-create it on the fly as follows:

-- create a new temp file

ALTER TABLESPACE TEMP ADD TEMPFILE

'/home/oracle/app/oracle/oradata/orcl/temp02.dbf' SIZE 50M;

-- Drop the old file

ALTER TABLESPACE TEMP DROP TEMPFILE

'/home/oracle/app/oracle/oradata/orcl/temp01.dbf';

SQL> select file_name, tablespace_name from dba_temp_files;

FILE_NAME                                                                 TABLESPACE_NAME

/home/oracle/app/oracle/oradata/orcl/temp02.dbf       TEMP

Or if you are able to restart the database, then you can let the database re-create it for you.

[oracle@localhost ~]$ rm /home/oracle/app/oracle/oradata/orcl/temp02.dbf

SQL> shutdown immediate

SQL> startup

[oracle@localhost orcl]$ ls

afiedt.buf                                       control01.ctl                    system01.dbf

APEX_1930613455248703.dbf     example01.dbf                 temp02.dbf

APEX_2041602962184952.dbf     redo01.log                       undotbs01.dbf

APEX_2610402357158758.dbf     redo02.log                       users01.dbf

APEX_2611417663389985.dbf     redo03.log

APEX_2614203650434107.dbf     sysaux01.dbf

Lookup Oracle Error Codes From The Server

OERR

Oerr is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. 
This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual. 
Oerr is installed with the Oracle Database software and is located in the ORACLE_HOME/bin directory. 

Oracle doesn't ship an "oerr" utility for Windows platforms (a perl script is available to simulate oerr behaviour on Windows).

This is a shell script which works by finding the associated ORA message from a flat file in the rdbms dir.

You run the command from the server like this: 

oerr abc nnnnn  where oerr is the command, "abc" is the Oracle error code prefix (such as "ORA" for all Oracle error codes)  and the "nnnnn" represents the numeric portion of the Oracle error code (i.e. 01555).

So, for the famous snapshot to old error you would type:

oerr ORA 01555

This returns information about the possible cause of the issue as well as actions  required to resolve the issue.

/home/oracle $ oerr ORA 01555
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
//         overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
//          setting. Otherwise, use larger rollback segments

This can be a useful tool and can be quicker than firing up a browser to search for details of the error code on-line.

Links:

Oracle FAQ - Oerr

Java World - Oracle Error Tool

Renaming Data Files

We had an issue recently were someone's fat fingers (not guilty, honest) had accidently created a data file with an extension of .bdf as opposed to .dbf.

/u03/oradata/PRD/datafile/my_data_file.004.bdf

This is not really an issue - as far as the DB is concerned the database engine does not really care about the extension.

However in this instance the incorrectly named file was slipping past the “Database Exclude” filter in the sys admins file system backup utility.

Now Oracle 12c includes the ALTER DATABASE MOVE DATAFILE command, which performs an online move of a datafile.

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf';

Unfortunately this was 11g.

There are two ways to re-name a datafile, however both methods will require some downtime.

Method 1. - Take the associated tablespace offline and then rename the datafile.

This requires an outage to rename the datafile as we have to take tablespace offline to rename it.

 -- Take Table Space off-line

ALTER TABLESPACE PRD_PRIMARY_DATA OFFLINE NORMAL;

-- Rename Data File

ALTER TABLESPACE MY_DATA

RENAME DATAFILE '/u03/oradata/PRD/datafile/my_data_file.004.bdf'

TO '/u03/oradata/PRD/datafile/my_data_file.004.dbf';

-- Put Data File back on-line

ALTER TABLESPACE MY_DATA ONLINE;

Method 2. - Shutdown database and rename datafile

This could also be performed by taking the shuting down the database

Rename datafile by taking down the database.

sqlplus / as sysdba

SQL> SHUTDOWN IMMEDIATE

SQL> HOST mv /u03/oradata/PRD/datafile/my_data_file.004.bdf /u03/oradata/PRD/datafile/my_data_file.004.dbf

SQL> STARTUP MOUNT

SQL> ALTER DATABASE RENAME FILE '/u03/oradata/PRD/datafile/my_data_file.004.bdf' TO '/u03/oradata/PRD/datafile/my_data_file.004.dbf';

SQL> ALTER DATABASE OPEN;

Oracle - Renaming and Relocating Datafiles

Oracle Base - Renaming or Moving Oracle Files

Oracle SQL Loader - Insert filename into table

 How to insert the name of your data file into the load table

I was tasked with a problem today on a project where one of the requirements was to load data from a flat file into a DB table, but at the same time also load the name of the file. The file name is unique and contains information about country of origin, sequence number and date.
My first thought was does SQL Loader accept parameters! It doesn't. After a little web searching I came across this eloquent solution for this very problem.

To allow SQL Loader to insert the input file name into output table you first have to update your control file (myControlFile.ctl) with a placeholder for the file name (:FILE).


Load data
truncate
into table MY_TABLE
fields terminated by ',' optionally enclosed by '"'
TRAILING NULLCOLS
(
UNIQUEREF,
ID,
QTY,
ETA DATE"DD/MM/YYYY",
FILENAME CONSTANT ":FILE"
)


Then update shell script with the following.  This will Loop through files in current directory and load each one. The cat / sed command will create a new control file and replace the placeholder (:FILE) with the name of your csv file.


FILES=`ls *.csv`
CTL=myControlFile.ctl
for f in $FILES
do
   cat $CTL| sed "s/:FILE/$f/g" > $f.ctl
   sqlldr usr/pass control=$f.ctl data=$f bad=mybad.bad discard=mydsc.dsc log=mylog.log ERRORS=1000000
done 

The file name should now be successfully loaded into the MY_TABLE.