ORA-39001 ORA-39000 ORA-31640 ORA-27041 OSD-04002 : Oracle IMPDP Error

impdp Scott/tiger full=Y directory=data_pump_dir dumpfile=scott.dmp TABLE_EXISTS_ACTION=SKIP SKIP_UNUSABLE_INDEXES=y EXCLUDE=STATISTICS DATA_OPTIONS=skip_constraint_errors  REMAP_SCHEMA=old_schedma_in_dmp:scott logfile=data_pump_dir:scott_full_094358_1.log EXCLUDE=GRANT

After executing above command received error :

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "D:\backups\scott_full.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.

As per error
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 5) Access is denied.
i was assuming that the files or folder do not have correct permission either for window user or oracle user (which is owner of directory)

Looked for many websites while googling, no one able to give the correct resolution for this error, every one is assuming for permission issue.


Problem :

- Using parameter log file wrongly "logfile=data_pump_dir:scott_full_094358_1.log", it should be without "data_pump_dir"
- For another Log File Name ( I am not sure about this), when i use logfile parameter "logfile=scott_full_094358_1.log" it was still giving issue.
Then i modified the log file name to "logfile=scott_full.log" then only it worked for me.

Working IMPDP command
impdp Scott/tiger full=Y directory=data_pump_dir dumpfile=scott.dmp TABLE_EXISTS_ACTION=SKIP SKIP_UNUSABLE_INDEXES=y EXCLUDE=STATISTICS DATA_OPTIONS=skip_constraint_errors  REMAP_SCHEMA=old_schedma_in_dmp:scott logfile=scott_full.log EXCLUDE=GRANT

Oracle Database IMPDP Error

Error:

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "*DatabaseName*"."SYS_IMPORT_FULL_01":  
*DatabaseName*/******** DIRECTORY=MYDIR DUMPFILE=BackupName.DMP LOGFILE=BackupName.log PARALLEL=8 remap_schema=OldDBName:NewDbName remap_tablespace=OldTbName:NewTbName,OldTb1Name:NewTb1Name, remap_Table=dbname.table_name:newdbName.tableName table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "*DatabaseName*"."table_name" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "*DatabaseName*"."table_name" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01403: no data found
ORA-04088: error during execution of trigger '*DatabaseName*.****'

 

Solution

change parameter = table_exists_action=TRUNCATE  to table_exists_action=REPLACE

Oracle | Find Week Start Date and Week End date

 If you want to find out the week start date and week end date from any date, it is possible in Oracle with different way, since their is no direct function available in oracle

SELECT TRUNC(sysdate, 'iw') - 1 AS week_start_date
	,(TRUNC(sysdate, 'iw') + 7 - 1 / 86400) - 1 AS week_end_date
	,CASE 
		WHEN TO_CHAR(sysdate, 'DY') = 'SUN'
			THEN (TRUNC(sysdate + 1, 'iw') + 7 - 1 / 86400) - 1
		ELSE (TRUNC(sysdate, 'iw') + 7 - 1 / 86400) - 1
		END
--If aby custom Week start date or end date, In my case i handled for Sunday as week start date
FROM dual;

SQL13 How to Fill/populate data until next value

Scenerio is Data is populate in table but in between some null occurance are available. Need to populate the in null starting from copy from 1 populate data and copy until next occurance of data and so on.

This can be done by various ways, few of the best way, solution writing in this post. 

Data in Table 

id	COL1	COL2
1	A	B
2
3
4
5
6	A	C
7
8
9
10	A	D
11
12
13
14
15
16
17

Expected Result

id	COL1	COL2
1	A	B
2		B
3		B
4		B
5		B
6	A	C
7		C
8		C
9		C
10	A	D
11		D
12		D
13		D
14		D
15		D
16		D
17		D

SELECT *
INTO SQL13
FROM (
	SELECT 1 ID, 'A' COL1, 'B' COL2
	
	UNION ALL
	
	SELECT 2, NULL, NULL
	
	UNION ALL
	
	SELECT 3, NULL, NULL
	
	UNION ALL
	
	SELECT 4, NULL, NULL
	
	UNION ALL
	
	SELECT 5, NULL, NULL
	
	UNION ALL
	
	SELECT 6 ID, 'A' COL1, 'C' COL2
	
	UNION ALL
	
	SELECT 7, NULL, NULL
	
	UNION ALL
	
	SELECT 8, NULL, NULL
	
	UNION ALL
	
	SELECT 9, NULL, NULL
	
	UNION ALL
	
	SELECT 10 ID, 'A' COL1, 'D' COL2
	
	UNION ALL
	
	SELECT 11, NULL, NULL
	
	UNION ALL
	
	SELECT 12, NULL, NULL
	
	UNION ALL
	
	SELECT 13, NULL, NULL
	
	UNION ALL
	
	SELECT 14, NULL, NULL
	
	UNION ALL
	
	SELECT 15, NULL, NULL
	
	UNION ALL
	
	SELECT 16, NULL, NULL
	
	UNION ALL
	
	SELECT 17, NULL, NULL
	) test

--Solution 1
SELECT *
	, CASE 
		WHEN COL2 IS NULL
			THEN (
				SELECT TOP 1 COL2
				FROM SQL13
				WHERE ID < T.ID
					AND COL2 IS NOT NULL
				ORDER BY ID DESC
		    	)
		ELSE COL2
		END AS COL3
FROM SQL13 T
--Solution 2
WITH CTE AS (
	SELECT ID
		, COL2
		, COALESCE(LEAD(ID) OVER (
				ORDER BY ID
				), (
				SELECT COUNT(*) + 1
				FROM SQL13
				)) NXT_SEQ
	FROM SQL13
	WHERE COL1 IS NOT NULL
	)

SELECT EP.ID
	, EP.COL1
	, EP.COL2
	--,EP.COL3
	, (
		SELECT CTE.COL2
		FROM CTE
		WHERE EP.ID >= CTE.ID
			AND EP.ID < CTE.NXT_SEQ
		) POPULATED
FROM SQL13 EP;

--Solution 3
SELECT c1.id AS id
	, coalesce(c1.col2, t.col2)
FROM SQL13 c1
OUTER APPLY (
	SELECT TOP 1 c2.col2
	FROM SQL13 c2
	WHERE c1.col2 IS NULL
		AND c2.col2 IS NOT NULL
		AND c2.id < c1.id
	ORDER BY c2.id DESC
	) t

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice

SQL12 Generate duplicate records as per the count of sequence

Want to generate dupkciate ID in sequence, 

For 1 , generate 1 record

For 2 , generate 2 records

For 3 , generate 3 records

For 4 , generate 4 records

and so on

these records will be generated all in sequence.

Output Needed

id
1
2
2
3
3
3
4
4
4
4

--Oracle

WITH SQL12
AS (
	SELECT LEVEL col
	FROM dual connect BY LEVEL < 5
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.col <= a.col;

--SQL Server
WITH SQL12 (N)
AS (
	SELECT 1
	
	UNION ALL
	
	SELECT N + 1
	FROM SQL12
	WHERE N < 4
	)
SELECT a.*
FROM SQL12 a
JOIN SQL12 b ON b.N <= a.N
ORDER BY 1;

ORA-12899: value too large for column ColumnName

Error  :

. . imported "DatabaseName"."TableName"                   171.3 KB    5789 rows
ORA-02374: conversion error loading table "DatabaseName"."TableName"
ORA-12899: value too large for column ColumnName (actual: 31, maximum: 30)
ORA-02372: data for row: ColumnName : 0X'5467ASH567UJGFRT4VXH567HFGHK5EWHD57GQ5'

You are trying to restore the database with IMPDP command, sometimes you will receive the error in logs, ORA-12899: value too large for column Column Name

If you faced this error too, then solve as below:

1. Check the table name and column name from the logs, for which this error occur
2. After restore complete alter the table and increase the size of column (using alter command)
3. Again execute the IMPDP command for specific table only and include the below 3 parameter in your IMPDP command

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type  {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

See full IMPDP command here (Click Here)

ORA-39151: Table "DatabaseName"."TableName" exists.

Error
ORA-39151: Table "DatabaseName"."TableName" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Problem :

1. you might be trying to restore database using IMPDP command and in IMPDP using existing database to restore and used "remap_Table" in your IMPDP command

2. You might try to restore single table using "remap_Table" paramter

Solution:

Use below 3 parameter in your IMPDP command, see below parameter and description

• remap_Table - Specify which table need to map with existing table
• tables - Specify, which particular table need to restore
• table_exists_action - Specify, what should be the action if table already exists, these are of 4 type. {SKIP | APPEND | TRUNCATE | REPLACE}, for more information you can read official website

use below parameter along with the usage, see below:

• remap_Table = OldTableName_InBackup:NewdatabasetableName
• tables = TypeYourTableNameHere
• table_exists_action=Truncate

Restore Table From Full Backup

Scenario is you have database/User already exists and received a new Database Backup and you want to restore only 1 table instead of full database restore. And you want to restore table in existing database/User. So you need to use 3 new parameters in your existing restore IMPDP command. These are as below:

• remap_Table
• tables
• table_exists_action

Full IMPDP command

impdp 
DIRECTORY=DirectoryName
DUMPFILE=Backupname.DMP 
LOGFILE=AnyFileName.log 
PARALLEL=8 
EXCLUDE=INDEX 
remap_schema=OldUser_Inbackup:NewUser
remap_tablespace=OldTablespace_Inbackup:NewTablespace
exclude=statistics
--New Paramaters
remap_Table = OldTableName_InBackup:NewdatabasetableName
tables = TypeYourTableNameHere
table_exists_action=Truncate

ORA-01940: cannot drop a user that is currently connected

Cause : One is attempt to drop a user and user is currently connect to database, 

Resolution : we need to drop existing session of user, using below command :

ALTER SYSTEM KILL SESSION '*SID*, *SERIAL*';

See Full Post here, to find SID and SERIAL number of connected user (CLICK HERE)

Now you will be able to drop user using command 

Drop user username cascade;

 

Kill Connection of Connect Users Oracle

This post is going to help you if you would like to drop session of connected users of Oracle.

Find the list of connected users using below query :

SELECT 

vs.sid

, vs.serial#

, vs.status

, vp.spid 

FROM 

v$session vs

, v$process vp 

WHERE 

vp.addr(+) = vs.paddr;

If you would like to find the connected sessions for 1 specific user use below where condition :

and vs.username = '*****'

Note * Oracle is case sensitive, use CAPITAL letters only

Use below query to kill session of users, use input of SID/SERIAL from above query.

ALTER SYSTEM KILL SESSION '*SID*, *SERIAL*';

Kill EXPDP Job -Oracle

impdp/expdp give you facility to kill the job if any thing you have commanded wrong or written wrong parameter in impdp/expdp command.

How :

1. Assume you have already started expdp command
2. Press ctrl + C to exit prompt
3. enter "KILL JOB" or 
4. STOP_JOB=IMMEDIATE

ORA-02304: Invalid Object Identifier Literal

Was doing the impdp (import) on oracle, restore of database and observed new error populate in logs

ORA-02304: Invalid Object Identifier Literal

CREATE TYPE ***.**** OID "someNUMBERS" AS OBJECT
(
)
ORA-39083: Object type TYPE:  failed to create with error:
ORA-02304: invalid object identifier literal 

SOLUTION:

Use Parameter transform=oid:n in impdp command, it will be looks like

impdp ******* *** ***** transform=oid:n

Error While Restore Oracle Database - ORA-28365: wallet is not open

ORA-39083: Object type TABLE:"DBNAME" failed to create with error:
ORA-28365: wallet is not open

SQL Error: ORA-28367: wallet does not exist
28367. 0000 -  "wallet does not exist"
*Cause:    The Oracle wallet has not been created or the wallet location
           parameters in sqlnet.ora specifies an invalid wallet path.
*Action:   Verify that the WALLET_LOCATION or the ENCRYPTION_WALLET_LOCATION
           parameter is correct and that a valid wallet exists in the path
           specified

Problem : I was trying to restore the database on Oracle machine. Then it is giving above mention error. Since i resolved the problem months ago and not remember accurately, the sequence of error i faced.

Solution :

I performed below steps while fixing above problem

1. Please add below in sqlnet.ora and restarted the services

-------------------------

SQLNET.AUTHENTICATION_SERVICES= (NONE) 

--which was NTS earlier

WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = C:\app\oracle\admin\<sid>\wallet)

     )

   )

SQLNET.WALLET_OVERRIDE = False

SSL_CLIENT_AUTHENTICATION = FALSE

SSL_VERSION = 0

-----------------------------

2.Confirm the follwing content should be present in tnsname.ora 

NEAORACLE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = **hostname**)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = <sid>)

    )

  )

  

3. Create folder wallet in this path (C:\app\oracle\admin\<sid>\)and give full access to ewallet file by right clicking on it properties>security

4. Execute following query in sys db

select * from v$encryption_wallet; --- here wallet_type is unknown and status  is not open

administer key management set keystore close; -- to make it wallet type as  password

administer key management set keystore open identified by admin_123;--it will create ewallet file

administer key management set key identified by admin_123 with backup;--change password then in import utility  use ENCRYPTION_PASSWORD=admin_123--use password which u set for key

5. open cmd ,set sid and execute following command to import table into existing db

   

   impdp sys/password@<sid> DIRECTORY=Source_dmp ENCRYPTION_PASSWORD=<pwd> DUMPFILE=<dumpname> TABLES=<tname> PARALLEL=8 EXCLUDE=INDEX remap_schema=<originalSchema>:<remapSchema> remap_tablespace=<originalTablespace>:<remapTablespace> exclude=statistics 

Interview Question 2 - Count of all Occurrence of status/color until it changes to another

 Reproduce table script

select * from (
select '1' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-03-01' reporting_month ,'GREEN' STATUS union all
select '1' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '1' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-04-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '2' id ,'2020-01-01' reporting_month ,'GREEN' STATUS union all
select '3' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '3' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '4' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '4' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-06-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-05-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-04-01' reporting_month ,'GREEN' STATUS union all
select '5' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-02-01' reporting_month ,'RED' STATUS union all
select '5' id ,'2020-01-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-06-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-03-01' reporting_month ,'RED' STATUS union all
select '6' id ,'2020-02-01' reporting_month ,'GREEN' STATUS union all
select '6' id ,'2020-01-01' reporting_month ,'RED' STATUS  ) COMMULATIVE_SUM

Need in output , count of Status for first all occurrence of RED color (Occurrence will be end by GREEN color)

--Interview Question (SQL Query)
"Solution 1 "

select 
id
, min(NewRank)-1  
from
(
select 
id
, reporting_month
, STATUS
, case when STATUS = 'GREEN' then TRank else 99 end NewRank
, TRank
from
(
select 
DENSE_RANK() over (partition by id order by id, reporting_month desc) as TRank
, id
, reporting_month
, STATUS
from
COMMULATIVE_SUM
) d
) d2 
where 
NewRank <>99
group 
by id

"Solution 2 "
select 
id
,count(*)
from
(
select 
id,
sum(case when status = 'Red' then 0 else 1 end)
over(partition by id order by reporting_month desc) as r
from 
COMMULATIVE_SUM
)as t
where 
r=0
group by id

ORA-28040: No matching authentication protocol

Detail

Oracle installed - 12 C, General install on Developer machine

Error

ORA-28040: No matching authentication protocol

Reason could be

No authentication protocol defined for either oracle client or oracle server.

Solution

I have identified 2 solution as during fix of this error

One is we can set the parameter in sqlnet.ora file "ALLOWED_LOGON_VERSION_SERVER", where we can specify the minimum number of authentication protocol can connect to oracle database. you can set below parameter as per the requirement.

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10

2nd thing i observed, If oracle server is 12C and you are creating connection in any of tool using OJDBC6.jar file then i suggest to update your jar file to OJDBC7.jar  which can also able to resolve the problem.

TNS-12560: TNS:protocol adapter error/ TNS-00530: Protocol adapter error

Scenerio: 

Installed new oracle instance on machine and Setup listener and TNS. After  that i was able to connect the users either from cmd sqlplus or SQL Developer.

I deleted the current listener and TNS and cleared the file tns. Observed that for me TNS services is no longer in system. Then i recreated the lsnrctl and TNS. Probable you will able to create the listener successfully but might be not able to start using cmd or services.

CMD > LSNRCTL > start

You might see some error. 

***********************************************************************************

at that point you will not able to connect any database as well, it may throw below error :

 Unable to OpenSCManager: err=5

TNS-12560: TNS:protocol adapter error/ 

 TNS-00530: Protocol adapter error

***********************************************************************************

 

In order to solve the error i just open CMD as administrator then i re-execute the command LSNRCTL was now working for me.

Coming Back to point, after resolving the error related to LSNRCTL/TNS, i tried to connect user or database from SQL Developer from remote machine (in network) it was working fine for me.

ERROR While EXPDP (Oracle Restore) : ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

Error :

ORA-39001 : invalid argument value

ORA-39000 : bad dump file specification

ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file

I faced this error while importing (impdp) oracle dump in database using impdp command, and there is no error in Command

impdp remap_schema=OLD:NEW exclude=statistics remap_tablespace=OLD:NEW DIRECTORY=MY_DIR_ABC DUMPFILE=expdpDumpFile.dmp LOGFILE=TodayLogs.log PARALLEL=8 EXCLUDE=INDEX

Cause :

We need to concentrate on error ORA-39143: dump file "c:\**********\.dmp" may be an original export dump file. Where we can able to find actual root cause of the issue. Here condition is we are not aware the the backup was taken using exp or expdp.

If above error is appear then the dump (backup) is taken using command "exp"

Solution:

Use IMP command to restore the database:

imp Sys as sysdba/Password FROMUSER=OLD TOUSER=NEW file=expdpDumpFile.dmp ignore=y indexes=n statistics=none constraints=n log=TodayLogs.log grants=n

Basic SQL interview Questions - Part 1

This post contain the very basic question of SQL, today onward i will write multiple post with interview questions. So that it will be help you guys the question i am going to post really crispy and tricky. If you would like to share the any interview question with me, comment on any post . I will definitely

share it on my blog. 

1. Difference Between Group by Clause and Having Clause 

2. Different type of Clause in SQL Query

3. I have below table and some sample data in it, create a query to show the department name and total number employees working in department.

ID	DepartmentName	NumberOfEmployees
1	Dept A	140
2	Dept A	100
3	Dept B	300
4	Dept B	400
5	Dept C	400
6	Dept C	700

Once you will be able to find the Department Name and Its total number of employees, then write query to populate the records for condition, "Whose number of employee count more than 700"

4. What is left join how it will work, write query of left join and what will be output, for below sample tables/data. (Below table are refer using TabA.CountryID = TabB.ID 

Tab-A

ID	Name	CountryID
1	A	21
2	B	31
3	C	31
4	D	11
5	E	11
6	F	31

Tab-B

ID	CountryName
11	India
21	United Status
31	Singapore

2- question - Find the total number of employee living in each country, Write down the syntax and query output.

SYS.DBMS_DATAPUMP' must be declared

impdp DIRECTORY=DIR DUMPFILE=dump_file.DMP LOGFILE=dump_file_log.log PARALLEL=8 EXCLUDE=INDEX remap_schema=Old_User:New_User remap_tablespace=Old_Tablespace:New_Tablespace exclude=statistics 

SYS.DBMS_DATAPUMP' must be declared

When This Error Came:

I tried to restore the Oracle Database and it throws the error. I Used correct credentials to connect oracle database.

My machine has multiple oracle Instances installed.

Both Instances are installed on separate Path.

Solution:

To solve this error i executed below command on CMD window. (make sure you are not entered in SQLPLUS)

SET ORACLE_SID=ORCL

using this command we need to tell the machine, which instance we are plan to use, if you wan to switch your your oracle instance re-run same query after exiting SQLPLUS cmd.

How to Find Duplicate Records in Table | Interview Question

When generally asked for duplicate, every time for most of person start thing about the group by, count clause. Which is correct, but these will not work every where. Count(*), Group by , having are the for beginner level. Student get learnt from basics.

On other hand, when we are working in IT industry then window function are usable in real time examples.

What i think Count(*), group by, having are usable for OLAP database, and window function are usable at OLTP level.

Lets see result with some example:

XYZ
ID	FirstName	LastName	MiddleName
1	Gurjeet	Singh
2	Harry	Potter
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

When i use the query 

SELECT count(*), FirstName, LastName, MiddleName
FROM XYZ
GROUP BY  FirstName, LastName, MiddleName

So result will be 

Count	FirstName	LastName	MiddleName
2	Gurjeet	Singh
2	Harry	Potter
1	Prem	Singh	L

I am agree the query return the correct result, but it is providing the Count and Name of Duplicate person. Means Gurjeet is the person exists by 2 times in table.

But for some cases we also need ID along with the duplicate person name, which is not possible with the help of GROUP by. So here we have to use window function.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    row_number() over(partition by Firstname, LastName, MiddleName order by ID) rowNumber
FROM XYZ
) t where rowNumber > 1

So how query will work, below table represent how the row_number will be assign to records

XYZ
ID	FirstName	LastName	MiddleName	rn
1	Gurjeet	Singh		1
2	Harry	Potter		1
3	Gurjeet	Singh		2
4	Prem	Singh	L	1
5	Harry	Potter		2

When we use the outer query, and apply the filter "where rowNumber > 1" below result set will be appear. Here main focus is column ID.

ID	FirstName	LastName	MiddleName
3	Gurjeet	Singh
4	Prem	Singh	L
5	Harry	Potter

Now in some cases we also need ID along with the all duplicate person name (only Duplicate), which is possible with the help of using Count(*) using Partition by clause.

select * from (
SELECT 
    ID, 
    FirstName, 
    LastName, 
    MiddleName,
    Count(*) over(partition by Firstname, LastName, MiddleName order by ID) TotalCount
FROM XYZ
) t where rowNumber > 1

XYZ
ID	FirstName	LastName	MiddleName	TotalCount
1	Gurjeet	Singh		2
2	Harry	Potter		2
3	Gurjeet	Singh		2
5	Harry	Potter		2