Real-time HTML editor

Real-time HTML editor:

http://htmledit.squarefree.com/

Export import utility in Oracle

Exporting Setup Tables


STEP 1 :  Create a Directory Object in Sourced Oracle Database


CREATE DIRECTORY BKUP AS 'export directory path';

eg.,

a.  Login to the Source database using sysadm (if sysadm has directory creation rights) or system or sysdba and create the directory

CREATE DIRECTORY BKUP AS '/opt/oracle/expbkp';

b. Grant access to the directory to the sysadm user if you created the directory using non sysadm user


GRANT ALL ON DIRECTORY BKUP TO SYSADM;

EXIT


STEP 2 :  Prepare parameter file for exporting setup tables.  Change the values of the parameters userid, tables


a.  Edit the values of the parameters

USERID=SYSADM/SYSADM@DBNAME   --> change the logon credential
EXCLUDE=STATISTICS
COMPRESSION=ALL
DIRECTORY=BKUP  --> directory name created in the Step 1
TABLES=SETUP_TABLE_NAME1,   --> setup table names..
SETUP_TABLE_NAME2,
SETUP_TABLE_NAME3,
SETUP_TABLE_NAMEn

EG.,

USERID=SYSADM/SYSADM@HR91DMO  
EXCLUDE=STATISTICS
COMPRESSION=ALL
DIRECTORY=BKUP  
TABLES=PS_COUNTRY_TBL,
PS_REG_REGION_TBL,
PS_STATE_TBL



b. Login to the Server where the Source db is hosted and navigate to the backupfolder

c. create the parameter file setuptables.par

Open up any text editor and paste the above lines after changing the parameter values and save it as setuptables.par



STEP 3 :  Trigger the Oracle Export Utility

a.  Navigate to the the backup folder where you saved the parameter file and kick off the below script in the command line

expdp parfile=setuptables.par DUMPFILE=SETUPTABLES.dmp  LOGFILE=SETUPTABLES.log


STEP 4 : Monitor the execution of Step 3 for successful completion.  The output should be similar to the one given below..



Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
  /opt/oracle/oradata/expbkup/SETUPTABLES.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 09:35:54



Exporting Transactional Tables


STEP 1 :  Create a Directory Object in the Source Database

If you first exported the setup tables, you can ignore this step as the directory was created when you
exported setup tables

STEP 2 : Create the parameter file for exporting transactional tables


USERID=SYSADM/SYSADM@DBNAME   --> change the logon credential
EXCLUDE=STATISTICS
COMPRESSION=ALL
DIRECTORY=BKUP  --> directory name created in the Step 1
TABLES=TRANS_TABLE_NAME1,   --> setup table names..
TRANS_TABLE_NAME2,
TRANS_TABLE_NAME3,
TRANS_TABLE_NAMEn
QUERY=TRANS_TABLE_NAME1:"WHERE ",
      TRANS_TABLE_NAME2:"WHERE ",
      TRANS_TABLE_NAME3:"WHERE "
      TRANS_TABLE_NAMEn:"WHERE "


eg.,

1. COMMON FILTER

USERID=SYSADM/SYSADM@HR91DMO  
EXCLUDE=STATISTICS
COMPRESSION=ALL
DIRECTORY=BKUP  
TABLES=PS_PERSON,
PS_NAMES
PS_ADDRESSES
QUERY="WHERE EMPLID LIKE 'KCN%'"   --> WHEN ALL TABLES ARE HAVING COMMON FILTER


2. DIFFERENCE IN FILTER

USERID=SYSADM/SYSADM@HR91DMO  
EXCLUDE=STATISTICS
COMPRESSION=ALL
DIRECTORY=BKUP  
TABLES=PS_PERSON,
PS_NAMES
PS_ADDRESSES


QUERY=PS_PERSON:"WHERE EMPLID LIKE 'KCN%'",   --> WHEN ALL TABLES ARE HAVING COMMON FILTER
PS_NAMES:"WHERE NAME_TYPE='PRI'",
PS_ADDRESSES:"WHERE CITY='SGP'"


b. Login to the Server where the Source db is hosted and navigate to the backupfolder

c. create the parameter file transtables.par

Open up any text editor and paste the above lines after changing the parameter values and save it as transtables.par


STEP 3 :  Trigger the Oracle Export Utility

a.  Navigate to the the backup folder where you saved the parameter file and kick off the below script in the command line


 expdp parfile=transtables.par DUMPFILE=TRANS_TABLES1.dmp  LOGFILE=TRANS_TABLES1.log


STEP 4 : Monitor the execution of Step 3 for successful completion.  The output should be similar to the one given below..


******************************************************************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
  /opt/oracle/oradata/expbkup/TRANS_TABLES.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 09:46:47




Importing the data in to source

Step 1 :  create a directory object in the Target Database

Step 2 :  Copy the .dmp files generated for setup and transactional tables in to the folder for which the directory object is created

Step 3 :  Kick off the impdp to import the data with appropriate import flags


Eg.,

impdp sysadm/SYSADM@hr91dmo directory=bkup full=y IGNORE=Y dumpfile=TRANS_TABLES.dmp logfile=trans_tables_import.log 

impdp sysadm/SYSADM@hr91dmo directory=bkup full=y IGNORE=Y dumpfile=SETUP_TABLES.dmp logfile=setup_tables_import.log 


Note :  IGNORE=Y should be mentioned if the setup and transactional tables are already present in the target database

Locked Objects Delete

DELETE
   FROM PSCHGCTLLOCK
WHERE  OBJECTVALUE1 ='obj_name' AND OPRID = 'Oprid'

Singapore NRIC Number Generator

http://samliew.com/icgen/