Deleting Duplicate Rows in Oracle

A quick guide to deleting duplicate rows in Oracle.

Test Setup:

CREATE TABLE dupTest (
col1 VARCHAR2(30),
col2 VARCHAR2(30)
);
/

INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('A','Ant');
INSERT INTO dupTest VALUES ('B','Bear');

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
A Ant
B Bear

To delete the duplicate rows, check that the rowid is in a select of the max rowids from the table where the count is greater than 1.

DELETE FROM dupTest
WHERE ROWID IN ( SELECT MAX(ROWID)
FROM dupTest
GROUP BY col1, col2
HAVING COUNT(*) > 1 );

SELECT * FROM dupTest;


COL1 COL2
------ ----
A Ant
B Bear

The duplicate row has been deleted.

And finally cleanup:

DROP TABLE dupTest;

De-duped

Console,Text and XML Logger Output with java.util.logging.Logger

Enhancing the previous blog "Getting Started with java.util.logging.Logger", this example looks at the next step, to output different message levels to the console than to file and shows how to output text or xml.

The logging levels are:

Level	Description
SEVERE	The highest value; intended for extremely important messages (e.g. fatal program errors).
WARNING	Intended for warning messages.
INFO	Informational runtime messages.
CONFIG	Informational messages about configuration settings/setup.
FINE	Used for greater detail, when debugging/diagnosing problems.
FINER	Even greater detail.
FINEST	The lowest value; greatest detail.

So an example would look something like this:

ackage com.wownow;

import java.io.IOException;
import java.util.logging.ConsoleHandler;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.logging.SimpleFormatter;
import java.util.logging.XMLFormatter;

public class MyLoggingTest2 {

public static void main(String[] args) throws IOException {

Level consoleLogLevel = Level.CONFIG;
Level defaultLogLevel = Level.FINER;

ConsoleHandler ch = new ConsoleHandler();
ch.setLevel(consoleLogLevel);

FileHandler txtfh = new FileHandler("MyLogFile.txt");
txtfh.setFormatter(new SimpleFormatter());
txtfh.setLevel(defaultLogLevel);

FileHandler xmlfh = new FileHandler("MyLogFile.xml");
xmlfh.setFormatter(new XMLFormatter());
xmlfh.setLevel(defaultLogLevel);

Logger logger = Logger.getLogger("MyLogger");
logger.setUseParentHandlers(false);
logger.setLevel(defaultLogLevel);

logger.addHandler(ch);
logger.addHandler(txtfh);
logger.addHandler(xmlfh);

logger.severe("My Severe Message");
logger.warning("My Warning Message");
logger.info("My Info Message");
logger.config("My Config Message");
logger.fine("My Fine Message");
logger.finer("My Finer Message");
logger.finest("My Finest Message");

txtfh.close();
xmlfh.close();

}

}

In this example we:

• Create two level variables,one for the console and one for the files


• Then create a console handler, and set the level of this handler, in the case to "CONFIG"


• We then create a handler for the text file, set its formatter to SimpleFormatter, so the output is in plain text, then set its level


• Then we create a third handler for the xml and set its formater to XMLFormatter (this could be ommitted as it's the default), and again set its level


• The logger object is then created, we use setUseParentHandlers to false to stop duplicated messages appearing on the console


• The logger level is set, this assumes the defaultLogLevel is lower than the consoleLogLevel, otherwise you could use Level.ALL


• Next the three handlers are added to the logger


• Next we output a message for each logging level. Only the messages above the level "CONFIG" will be output to the console or "FINER" written to the file.


• We then close the file handlers.

My Done Message

Getting Started with java.util.logging.Logger

The basic method of logging would be to open a file and write your data to it. Not with Java! The Logger has some niffty features and is even easier than writing to a file.

Loggers main offering is a level that can be set, anything at or above that level gets output, anything below gets silently ignored. So you can keep your debug messages in your code and only turn them on when needed.

The logging levels are:

Level	Description
SEVERE	The highest value; intended for extremely important messages (e.g. fatal program errors).
WARNING	Intended for warning messages.
INFO	Informational runtime messages.
CONFIG	Informational messages about configuration settings/setup.
FINE	Used for greater detail, when debugging/diagnosing problems.
FINER	Even greater detail.
FINEST	The lowest value; greatest detail.

A quick start example would look something like this:

package com.wownow;

import java.io.IOException;
import java.util.logging.FileHandler;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MyLoggingTest {

public static void main(String[] args) throws IOException {

Level logLevel = Level.FINE;

FileHandler fh = new FileHandler("MyLogFile.xml");

Logger logger = Logger.getLogger("MyLogger");
logger.setLevel(logLevel);
logger.addHandler(fh);

logger.severe("My Severe Message");
logger.warning("My Warning Message");
logger.info("My Info Message");
logger.config("My Config Message");
logger.fine("My Fine Message");
logger.finer("My Finer Message");
logger.finest("My Finest Message");

fh.close();

}

}

In this example we:

• first set the logLevel to "FINE",


• then create our log file "MyLogFile.xml", by default Logger outputs to file in xml format.


• We then create the logger object, set the level and add the file handler.


• Next we output a message for each logging level. Only the messages above the set level, "FINE", will be output. The logger also outputs to console but only to "INFO" level by default.


• We then close the file handler.

The xml output looks something like:

<?xml version="1.0" encoding="windows-1252" standalone="no"?>
<!DOCTYPE log SYSTEM "logger.dtd">
<log>
<record>
<date>2009-06-09T12:23:56</date>
<millis>1244546636747</millis>
<sequence>0</sequence>
<logger>MyLogger</logger>
<level>SEVERE</level>
<class>com.rrf.mypackage.MyLoggingTest</class>
<method>main</method>
<thread>10</thread>
<message>My Severe Message</message>
</record>
<record>
<date>2009-06-09T12:23:56</date>
<millis>1244546636763</millis>
<sequence>1</sequence>
<logger>MyLogger</logger>
<level>WARNING</level>
<class>com.rrf.mypackage.MyLoggingTest</class>
<method>main</method>
<thread>10</thread>
<message>My Warning Message</message>
</record>
...
</log>

There are also two special levels Level.ALL and Level.OFF

See Console,Text and XML Logger Output to see how to output different levels and formats to different destinations.

There is much more to Logger, see here.

Logged.