Oracle Database Cloning using Backup sets

Development environment required copy of production database, below are the steps for cloning

High Level Steps

A. Target Side
     1. Prepare Target Environment for Oracle Database cloning
     2. Install Oracle binaries same as source environment
     3. Create necessary directory structure for target database

B. Source Side
     1. Create pfile from source database and transfer to target system
     2. Full backup of database and transfer to target system

C. Target Side
     1. Verify all backup pieces copied to target
     2. Modify pfile as per the target environment
     3. Start the target database in NMOUNT using modified pfile
     4. Connect to RMAN utility
                clone database using “duplicate database to <CloneDB Name>  backup location”.
     5. Check the database status, datafile, redologs, tempfile

Create necessary directory structure at target

mkdir -p /u01/app/oracle/oradata/orclcln
mkdir -p /home/oracle/DILIP/oradata/orclcln/ARCHIVELOG
mkdir -p /u01/app/oracle/admin/orclcln/adump
mkdir -p /u01/app/oracle/admin/orclcln/adump

Take Backup of source database

RMAN_FULL_TEST.sh

#RMAN Full backup Scripts

#!/bin/ksh

ORACLE_HOSTNAME=server1.example.com; export ORACLE_HOSTNAME

ORACLE_UNQNAME=oracle; export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

ORACLE_SID=oracle; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

mkdir /home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"

test=/home/oracle/TEST_2016/ORACLE_"$(date +"%d-%m-%Y")"

rman target=/ << EOF

RUN {

  ALLOCATE CHANNEL ch1 TYPE

    DISK FORMAT '$test/%d_DB_%u_%s_%p';

  BACKUP DATABASE CURRENT CONTROLFILE SPFILE PLUS ARCHIVELOG;

  DELETE FORCE NOPROMPT EXPIRED BACKUP DEVICE TYPE DISK;

  DELETE FORCE NOPROMPT OBSOLETE;

  RELEASE CHANNEL ch1;

}

EXIT;

EOF

Check the backup files created

ls -lrt /home/oracle/TEST_2016/ORACLE_06-02-2020

total 1096672

-rw-r----- 1 oracle oinstall   2963456 Feb  6 20:14 ORACLE_DB_tqunskku_17338_1

-rw-r----- 1 oracle oinstall 559357952 Feb  6 20:15 ORACLE_DB_trunskl0_17339_1

-rw-r----- 1 oracle oinstall   1523712 Feb  6 20:16 ORACLE_DB_tsunsknv_17340_1

-rw-r----- 1 oracle oinstall     98304 Feb  6 20:16 ORACLE_DB_ttunsko1_17341_1

-rw-r----- 1 oracle oinstall     14848 Feb  6 20:16 ORACLE_DB_tuunsko2_17342_1

Now transfer the backup files from source to target system using scp command

scp /home/oracle/TEST_2016/ORACLE_06-02-2020/* oracle@192.168.5.10:/home/oracle/TEST_2016/ORACLE_06-02-2020

oracle@192.168.5.10's password:

ORACLE_DB_tqunskku_17338_1                           100% 2894KB   2.8MB/s   00:00

ORACLE_DB_trunskl0_17339_1                             100%  533MB 133.4MB/s   00:04

ORACLE_DB_tsunsknv_17340_1                            100% 1488KB   1.5MB/s   00:00

ORACLE_DB_ttunsko1_17341_1                            100%   96KB  96.0KB/s   00:00

ORACLE_DB_tuunsko2_17342_1                           100%   15KB  14.5KB/s   00:00

Modify pfile as per the target environment

cat /home/oracle/DILIP/oradata/orclcln/PFILE_ORCLCLN_NEW.ora'

orclcln.__db_cache_size=301989888

orclcln.__java_pool_size=33554432

orclcln.__large_pool_size=16777216

orclcln.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orclcln.__pga_aggregate_target=553648128

orclcln.__sga_target=1056964608

orclcln.__shared_io_pool_size=0

orclcln.__shared_pool_size=654311424

orclcln.__streams_pool_size=33554432

*.audit_file_dest='/u01/app/oracle/admin/orclcln/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/orclcln/control01.ctl','/u01/app/oracle/oradata/orclcln/control02.ctl'#Restore Controlfile

*.db_file_name_convert='/u01/app/oracle/oradata/oracle/','/u01/app/oracle/oradata/orclcln/'

*.log_file_name_convert='/u01/app/oracle/oradata/oracle/','/u01/app/oracle/oradata/orclcln/'

*.db_block_size=8192

*.db_domain=''

*.db_name='orclcln'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCLCLN'

*.db_recovery_file_dest_size=8589934592

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclclnXDB)'

*.log_archive_dest_1='LOCATION=/home/oracle/DILIP/oradata/orclcln/ARCHIVELOG'

*.log_archive_dest_10=''

*.memory_target=1607467008

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS'

set environment variable and start the database in nomount mode

export ORACLE_SID orclcln
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
sqlplus / as sysdba
startup nomount pfile='/home/oracle/DILIP/oradata/orclcln/PFILE_ORCLCLN_NEW.ora';

connect to rman utility and clone the database using backup location

$ORACLE_HOME/bin/rman auxiliary '/'

run
{
SET NEWNAME FOR DATABASE  TO '/u01/app/oracle/oradata/orclcln/%b';
duplicate database to orclcln  backup location '/home/oracle/TEST_2016/ORACLE_06-02-2020' nofilenamecheck;
}

Send tablespace status report

Send TABLESPACE status report mail in HTML Format

Step 1 : Create SQL script file 

             To Generate HTML file for database status and tablespace report

Step 2 : Spool to shell script file

Step 3 : execute shell script to get tablespace report via mail in HTML format

Step 1 and Step 2

[oracle]$ cat report.sql

 SET head OFF;
 SET echo OFF;
 SET termout OFF;
 SET verify OFF;
 set colsep ,
 set pagesize 0
 set feedback off
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh
select 'sendmail sample_dbmonitoring@xyz.com <<EOF' from dual;
select 'To: dbmonitoring_group@xyz.com ' from dual;
select 'Subject: Text message' from dual;
select 'Content-Type: text/html; charset="us-ascii"' from dual;
select '<html>' from dual;
select '<body>' from dual;
select '<p>' from dual;
select '<font size="5" face="Courier" color="blue">' from dual;
select 'Tablespace Alert' from dual;
select '</font>' from dual;
select '<font size="2" face="screen" color="green">' from dual;
spool off
SET head on;
set heading on;
set verify on;
set termout on
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>DATABASE NAME and STATUS </TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: white} --> -
</STYLE>" -
BODY "TEXT=blue" -
TABLE "WIDTH='50%' BORDER='5'"
set pagesize 200
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
prompt 1.DATABASE STATUS
select name as DBNAME,open_mode,database_role,(select startup_time from v$instance) as starttime from v$database;
prompt 2.TABLESPACE STATUS
select a.tablespace_name as  "TSNAME",
        round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
        round(a.freespace,1) "FREE_SPACE_MB",
        round(b.totalspace) "TOTAL_SPACE_MB",
        round(100 * (a.freespace / b.totalspace),5) "%_FREE"
 from
  ( select tablespace_name,
        sum(bytes)/1024/1024 TotalSpace
   from dba_data_files
   group by tablespace_name) b,
 ( select tablespace_name,
    sum(bytes)/1024/1024 FreeSpace
   from dba_free_space
   group by tablespace_name) a
  where b.tablespace_name = a.tablespace_name(+)
  order by 5;
spool off
set markup html off
set heading off
set heading off
SET head OFF;
SET echo OFF;
SET termout OFF;
SET verify OFF;
set colsep ,
set pagesize 0
spool /home/oracle/Desktop/dilip/MAIL_SCRIPT/test_report.sh append;
select '</font>' from dual;
select '</p>' from dual;
select '</body>' from dual;
select '</html>' from dual;
prompt EOF
spool off
exit

--Generated shell script file which will send mail in HTML format of tablespace status

Verify the generated file

[oracle]$ cat test_report.sh
sendmail sample_dbmonitoring@xyz.com <<EOF
To: dbmonitoring_group@xyz.com

Subject: Text message
Content-Type: text/html; charset="us-ascii"
<html>
<body>
<p>
<font size="5" face="Courier" color="blue">
Tablespace Alert
</font>
<font size="2" face="screen" color="green">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=US-ASCII">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>DATABASE NAME and STATUS </TITLE>  <STYLE type='text/css'>  <!-- BODY {background: white} -->  </STYLE>
</head>
<body TEXT=blue>
1.DATABASE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
DBNAME
</th>
<th scope="col">
OPEN_MODE
</th>
<th scope="col">
DATABASE_ROLE
</th>
<th scope="col">
STARTTIME
</th>
</tr>
<tr>
<td>
ORACLE
</td>
<td>
READ WRITE
</td>
<td>
PRIMARY
</td>
<td>
25-MAR-21
</td>
</tr>
</table>
<p>
2.TABLESPACE STATUS
<br>
<p>
<table WIDTH='50%' BORDER='5'>
<tr>
<th scope="col">
TSNAME
</th>
<th scope="col">
USED_SPACE_MB
</th>
<th scope="col">
FREE_SPACE_MB
</th>
<th scope="col">
TOTAL_SPACE_MB
</th>
<th scope="col">
%_FREE
</th>
</tr>
<tr>
<td>
SYSTEM
</td>
<td align="right">
     694.6
</td>
<td align="right">
       5.4
</td>
<td align="right">
       700
</td>
<td align="right">
    .76786
</td>
</tr>
<tr>
<td>
SYSAUX
</td>
<td align="right">
     706.4
</td>
<td align="right">
      53.6
</td>
<td align="right">
       760
</td>
<td align="right">
    7.0477
</td>
</tr>
<tr>
<td>
USERS
</td>
<td align="right">
       2.4
</td>
<td align="right">
       2.6
</td>
<td align="right">
         5
</td>
<td align="right">
     51.25
</td>
</tr>
<tr>
<td>
UNDOTBS1
</td>
<td align="right">
      11.3
</td>
<td align="right">
     303.8
</td>
<td align="right">
       315
</td>
<td align="right">
  96.42857
</td>
</tr>
<tr>
<td>
TRAING
</td>
<td align="right">
       2.2
</td>
<td align="right">
    5122.8
</td>
<td align="right">
      5125
</td>
<td align="right">
  99.95732
</td>
</tr>
</table>
<p>
</body>
</html>
</font>
</p>
</body>
</html>
EOF

Step 3 : Execute the shell script

[oracle]$./test_report.sh

Export import Oracle Table statistics

 
Export import Oracle Table statistics 
 
--SOURCE DB

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--SOURCE DB

SQL> BEGIN
     DBMS_STATS.CREATE_STAT_TABLE (ownname => 'SCOTT',stattab => 'STATTAB_EMPLOYEE');
     END;
     /  2    3    4
 
PL/SQL procedure successfully completed. 

--SOURCE DB

SQL> BEGIN  dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  2    3    4
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> --SOURCE DB

SQL> select owner,table_name from dba_tables where table_name='STATTAB_EMPLOYEE';
 
OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                         STATTAB_EMPLOYEE

 --SOURCE DB
SQL> select count(1) from SCOTT.STATTAB_EMPLOYEE;
 
  COUNT(1)
----------
        99
 
--SOURCE DB : Export statistics of table EMPLOYEE
[oracle@rac02 ~]$ expdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_EMPLOYEE_EXPORT_DIR
 
Export: Release 10.2.0.3.0 - Production on Monday, 27 September, 2021 23:52:28
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Username: / as sysdba
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=SRC_SCOTT_EXPORT_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STATTAB_EMPLOYEE"               30.07 KB      99 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u03/EXPORT_BACKUP/STATTAB_EMPLOYEE_28SEPT2021.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:52:35
  
SQL> set pages 200 lines 192
SQL> col DIRECTORY_NAME format a30
SQL> col DIRECTORY_PATH format a40
select directory_name,directory_path from dba_directories;
 
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
TRINITYDUMP                    /backup/expdp
XML_DIR                        /home/oracle/xml
SRC_SCOTT_EXPORT_DIR           /u03/EXPORT_BACKUP
WORK_DIR                       /opt/oracle/product/10.2.0/db_1/work
ADMIN_DIR                      /opt/oracle/product/10.2.0/db_1/md/admin
DATA_PUMP_DIR                  /opt/oracle/product/10.2.0/db_1/rdbms/log
 
 
7 rows selected.
 
SQL> !ls -lrt /u03/EXPORT_BACKUP
total 112
-rw-r--r-- 1 oracle oinstall   1132 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.log
-rw-r----- 1 oracle oinstall 106496 Sep 27 23:52 STATTAB_EMPLOYEE_28SEPT2021.dmp
 
--SCP  STATTAB_EMPLOYEE_28SEPT2021.dmp to target DB Server
--TARGET DB 
--import STATTABB_EMPLOYEE_28SEPT2021.dmp in the target DB

--impdp dumpfile=STATTAB_EMPLOYEE_28SEPT2021.dmp logfile=impdp_STATTAB_EMPLOYEE_28SEPT2021.log tables=SCOTT.STATTAB_EMPLOYEE directory=TGT_EMPLOYEE_IMPORT_DIR

--SOURCE DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       23.04.19
 
--TARGET DB 

SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                          EMPLOYEE                       28.09.21
--TARGET DB import the  
SQL> BEGIN  dbms_stats.import_table_stats(ownname=>'SCOTT',tabname=>'EMPLOYEE',stattab=>'STATTAB_EMPLOYEE',cascade=>true);
END;
/  
 
PL/SQL procedure successfully completed.
--TARGET DB  
SQL> select owner,table_name,last_analyzed from dba_tables where owner='SCOTT' and table_name='EMPLOYEE';
 
OWNER                          TABLE_NAME                     LAST_ANA
------------------------------ ------------------------------ --------
SCOTT                           EMPLOYEE                      23.04.19   -- statistics imported successfully
 

Expdp selected table using INCLUDE parameter in Oracle

Expdp selected table when the table list is big

If table list is big  then There may be  two option  which can  help us to  expdp selected tables
   1. we can use select query in the INCLUDE parameter in expdp with filter condition
   2. Manual Approach we can use par file and pass those files which has to take expdp backup

Let suppose if we want to export only those table which has records, then we would have to specify INCLUDE parameter in the EXPDP and specify NUM_ROW>0

include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"

Below are the demonstration example

C:\Users>expdp Dilip/PasswD123#@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:\"in \(select table_name from user_tables where NUM_ROWS >0 \)\"

Export: Release 12.2.0.1.0 - Production on Mon Jun 12 17:19:28 2023
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "DILIP"."SYS_EXPORT_SCHEMA_01":  Dilip/********@PDB1 DIRECTORY=PDB1_DUMP_DIR DUMPFILE=TESTDUMP12JUNE2023_ROWGT0.dmp include=table:"in \(select table_name from user_tables where NUM_ROWS >0 \)"

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows
. . exported "DILIP"."TESTING_TBL_02" 21.78 KB       1 rows
. . exported "DILIP"."TESTING_TBL_03" 20.64 KB     165 rows
. . exported "DILIP"."TESTING_TBL_04" 19.60 KB     291 rows
. . exported "DILIP"."TESTING_TBL_05" 17.38 KB     164 rows
Master table "DILIP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DILIP.SYS_EXPORT_SCHEMA_01 is:
  D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT0.DMP
Job "DILIP"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 17:20:36 2023 elapsed 0 00:01:06

2 Second option is Manual Approach

I create parfile and save it as expdp_table_12062023.par

with below contents I will use this parfile in expdp parameter

We can get table list which has more than 0 rows using below query

select table_name||',' from user_tables where NUM_ROWS>0;

DIRECTORY=PDB1_DUMP_DIR

DUMPFILE=TESTDUMP12JUNE2023_ROWGT01.DMP

TABLES=

TESTING_TBL_01,

TESTING_TBL_02,

TESTING_TBL_03,

TESTING_TBL_04,

TESTING_TBL_05

LOGFILE=expdp_TESTDUMP12JUNE2023_ROWGT01.log

C:\Users>expdp Dilip/PasswD123#@PDB1 parfile=expdp_table_12062023.par

Export: Release 12.2.0.1.0 - Production on Mon Jun 12 18:13:09 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "DILIP"."SYS_EXPORT_TABLE_01":  Dilip/********@PDB1 parfile=expdp_table_12062023.par

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/TRIGGER

. . exported "DILIP"."TESTING_TBL_01" 1.781 GB 34023780 rows

. . exported "DILIP"."TESTING_TBL_02" 403.8 KB    3493 rows

. . exported "DILIP"."TESTING_TBL_03" 347.5 KB    2695 rows

. . exported "DILIP"."TESTING_TBL_04" 170.7 KB    1677 rows

. . exported "DILIP"."TESTING_TBL_05" 174.4 KB    3103 rows

Master table "DILIP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

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

Dump file set for DILIP.SYS_EXPORT_TABLE_01 is:

  D:\BACKUP1\PDB_DUMP_DIR\TESTDUMP12JUNE2023_ROWGT01.DMP

Job "DILIP"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 12 18:13:44 2023 elapsed 0 00:00:34

Command

#expdp \"sys/Oracle_4U@PDB1 as sysdba \" directory=PDB1_DUMP_DIR schemas=LOCAL_SCH1,LOCAL_SCH2 dumpfile=LOCAL_SCH_21JULY2023.dmp logfile=Expdp_LOCAL_SCH_21JULY2023.log

expdp directory=DATA_PUMP_DIR dumpfile=gokhan.dmp include=table:\"in \(select table_name from user_tables where table_name like \'INVOICE%\' or table_name like \'PRODUCT%\' \)\”

expdp APP_SCHEMA/Oracle_4U@PDB1 directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_DULICATED_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where DUPLICATED = \'Y\' \)\"

expdp APP_SCHEMA/Oracle_4U@PDB directory=EXPORT_BACKUP_DIR dumpfile=APP_SCHEMA_sharded_TABLES_12MAY2023.dmp include=table:\"in \(select table_name from user_tables where sharded = \'Y\' \)\"

--Exclude big table

expdp directory=DATA_EXPORT_DIR schemas=SCOTT dumpfile=TESTSCHEMA_07102024.dmp logfile=TESTSCHEMA_07102024.log exclude=TABLE:"IN('BIG_TABLE_01')"

  

Delete Duplicate Data from Table

create table Student_test(ROLLNO VARCHAR2(10) NOT NULL
                         ,STNAME VARCHAR2(30) NOT NULL
,PROGRAM VARCHAR2(10) NOT NULL
 ,FEES NUMBER NOT NULL);

alter table Student_test modify ROLLNO VARCHAR2(20) NOT NULL;
 
insert into Student_test values('10020220314','DILIP BAKSHI','MCA',42000);
insert into Student_test values('10020220314','DILIP BAKSHI','MCA',42000);
insert into Student_test values('10120220314','SANJAY SINHA','BCA',22000);
insert into Student_test values('10120220314','SANJAY SINHA','BCA',22000);

select ROLLNO,STNAME,PROGRAM,count(1) from Student_test
GROUP BY ROLLNO,STNAME,PROGRAM 
HAVING COUNT(1) >1
ORDER BY ROLLNO,STNAME,PROGRAM;

SQL> alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM);
alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM)
                                        *
ERROR at line 1:
ORA-02437: cannot validate (SYS.PK_STUDENT_TEST) - primary key violated
SQL>
SQL>  select * from student_test;
ROLLNO               STNAME                         PROGRAM          FEES
-------------------- ------------------------------ ---------- ----------
10020220314          DILIP BAKSHI                   MCA             42000
10020220314          DILIP BAKSHI                   MCA             42000
10120220314          SANJAY SINHA                   BCA             22000
10120220314          SANJAY SINHA                   BCA             22000

SQL> select ROLLNO,STNAME,PROGRAM,count(1) from Student_test
     GROUP BY ROLLNO,STNAME,PROGRAM
     HAVING COUNT(1) >1
     ORDER BY ROLLNO,STNAME,PROGRAM;  

ROLLNO               STNAME                         PROGRAM      COUNT(1)
-------------------- ------------------------------ ---------- ----------
10020220314          DILIP BAKSHI                   MCA                 2
10120220314          SANJAY SINHA                   BCA                 2

SQL> select * from student_test
     where rowid NOT IN (select max(rowid)  from student_test group by ROLLNO,STNAME,PROGRAM); 

ROLLNO               STNAME                         PROGRAM          FEES
-------------------- ------------------------------ ---------- ----------
10120220314          SANJAY SINHA                   BCA             22000
10020220314          DILIP BAKSHI                   MCA             42000

SQL> DELETE FROM Student_test WHERE rowid IN (
     SELECT MAX(rowid) FROM Student_test GROUP BY ROLLNO,STNAME,PROGRAM); 
2 rows deleted.
SQL>
SQL> alter table student_test add constraint pk_student_test primary key(ROLLNO,STNAME,PROGRAM);
Table altered.

SQL>

SQL SERVER

==================
delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From Student_test) T
Where T.RowNumber > 1

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