Home » SQL & PL/SQL » SQL & PL/SQL » How to eliminate duplicate rows from a table (oracle 11g)
How to eliminate duplicate rows from a table [message #621594] Thu, 14 August 2014 13:40 Go to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I am trying to delete duplicate rows from my classmate.employee table.

I am trying to follow this steps that were given in the ORAFAQ knowledgebase titled How does one eliminate duplicates rows from a table?

method 2 suggest the following which seems the easiest and cleanest way to do it.

SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;


Well I ran into problems with the third step...here is my code for step3.
rename classmate.employee2 to classmate.employee;


The error I'm getting is the following.

error starting at line 1 in command:
rename classmate.employee2 to employee
Error report:
SQL Error: ORA-01765: specifying owner's name of the table is not allowed
01765. 00000 - "specifying table's owner name is not allowed"
*Cause:
*Action:


I tried removing Classmate from either side it still doesn't work.
What am I doing wrong?
Re: How to eliminate duplicate rows from a table [message #621595 is a reply to message #621594] Thu, 14 August 2014 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you remove CLASSMATE. from both sides?
SQL> create table t_dept as select * from dept;

Table created.

SQL> rename scott.t_dept to scott.brisime;
rename scott.t_dept to scott.brisime
       *
ERROR at line 1:
ORA-01765: specifying table's owner name is not allowed


SQL> rename t_dept to brisime;

Table renamed.

SQL>
Re: How to eliminate duplicate rows from a table [message #621596 is a reply to message #621595] Thu, 14 August 2014 13:52 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Yes I got this error.
Error starting at line 1 in command:
rename employee2 to employee
Error report:
SQL Error: ORA-04043: object EMPLOYEE2 does not exist
04043. 00000 - "object %s does not exist"
*Cause: An object name was specified that was not recognized by the system.
There are several possible causes:
- An invalid name for a table, view, sequence, procedure, function,
package, or package body was entered. Since the system could not
recognize the invalid name, it responded with the message that the
named object does not exist.
- An attempt was made to rename an index or a cluster, or some
other object that cannot be renamed.
*Action: Check the spelling of the named object and rerun the code. (Valid
names of tables, views, functions, etc. can be listed by querying
the data dictionary.)
Re: How to eliminate duplicate rows from a table [message #621597 is a reply to message #621596] Thu, 14 August 2014 13:53 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
And yes Employee2 does exist..
Re: How to eliminate duplicate rows from a table [message #621598 is a reply to message #621597] Thu, 14 August 2014 14:11 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you connected as EMPLOYEE2's owner? Run
select owner, table_name from all_tables where upper(table_name) = 'EMPLOYEE2';
What is the result?
Re: How to eliminate duplicate rows from a table [message #621599 is a reply to message #621594] Thu, 14 August 2014 14:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2089
Registered: January 2010
Senior Member
Question is how big is the table and what percenatge of duplicate it has. Method you are using will force you to recreate indexes, triggers, grants,... and recompile invalid objects caused by table drop.

SY.
Re: How to eliminate duplicate rows from a table [message #621600 is a reply to message #621599] Thu, 14 August 2014 14:25 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
This is the result...

OWNER TABLE_NAME
------------------------------ ------------------------------
CLASSMATE EMPLOYEE2

1 rows selected
Re: How to eliminate duplicate rows from a table [message #621601 is a reply to message #621600] Thu, 14 August 2014 14:33 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I also ran this query to see if their are any constraints..
SELECT *
  FROM user_cons_columns
 WHERE table_name = 'classmate.employee2';


got this result

OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------------------

0 rows selected

Re: How to eliminate duplicate rows from a table [message #621602 is a reply to message #621601] Thu, 14 August 2014 14:37 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You still didn't answerQuote:

Are you connected as EMPLOYEE2's owner?

Run
SQL> show user
What is the result?
Re: How to eliminate duplicate rows from a table [message #621604 is a reply to message #621601] Thu, 14 August 2014 15:09 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
dariusd7 wrote on Thu, 14 August 2014 15:33
I also ran this query to see if their are any constraints..
SELECT *
  FROM user_cons_columns
 WHERE table_name = 'classmate.employee2';



You are not going to get any results from that.
First, you don't give schema names for the TABLE_NAME column.
Second, values in TALBE_NAME are in upper case, unless you created them with double quotes around them, which would be a very bad idea.

Sit back and think for a minute. You should be running your commands from the schema where they reside, otherwise you are going to get errors like the valid error that you reported about the object not existing. Sure, it didn't exist in the schema you were connected as.
Re: How to eliminate duplicate rows from a table [message #621605 is a reply to message #621604] Thu, 14 August 2014 15:31 Go to previous message
BlackSwan
Messages: 23189
Registered: January 2009
Senior Member
http://www.orafaq.com/wiki/Scripts
Previous Topic: Spool into excel file with different tabs
Next Topic: multiple currency value
Goto Forum:
  


Current Time: Sun Dec 28 08:33:34 CST 2014

Total time taken to generate the page: 0.04319 seconds