| How to eliminate duplicate rows from a table [message #621594] |
Thu, 14 August 2014 13:40  |
 |
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 #621596 is a reply to message #621595] |
Thu, 14 August 2014 13:52  |
|
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 #621604 is a reply to message #621601] |
Thu, 14 August 2014 15:09 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
dariusd7 wrote on Thu, 14 August 2014 15:33I 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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
