4.3 advanced features (3) -- Filter

1. Filter tables

(1) Use wildcard * to copy all tables under the user

In the extract parameter file

Table HR.*;

In the remote replicate parameter file

Map HR.*, Target HR.* ; 

(2) filter tables

Extract parameter file

Table HR.EMPLOYEES; Table HR.JOBS;

Replicate parameter file

Map HR.EMPLOYEES, Target HR.EMPLOYEES ; Map HR.JOBS, Target HR.JOBS  

2. Filter Columns

Extract parameter file

Table HR.EMPLOYEES COLS (employee_Id, first_name, last_name); 

Only the columns specified by Cols are copied above. The target table has no restrictions as long as these columns exist and does not violate the constraints.

If the filter condition is written in the parameter file of the shipping process, passthru mode is no longer used.

Or the following code

Table HR.EMPLOYEES COLSEXCEPT (email); 

All columns other than the email address in the copy Area

3. Filter rows

(1) Use the WHERE clause

Extract parameters:

Table HR.EMPLOYEES, WHERE (EMPLOYEE_ID < 100);

Only data with the value of employee_id less than 100 is copied.

Add the replicate Parameter

Map hr. Employees, target HR. Employees, where (job_id = "it_prog"); ---- Note: Double quotation marks

When replicat is used, only data of job_id = "it_prog" is loaded.

(2) Use the filter parameter

Add the following to ext or rep parameters:

Table HR.EMPLOYEES, FILTER (SALARY / 12 < 1000); 

Or

Map HR.EMPLOYEES, Target HR.EMPLOYEES, FILTER (SALARY / 12 > 1000);

Only data with a monthly salary of more than 1000 is extracted or loaded above.

(3) When performing the delete operation, only records with a monthly salary of less than 1000 are deleted.

Table HR.EMPLOYEES, FILTER (ON DELETE, SALARY / 12 < 1000); 

(4) If the table names of the source and target fields are different

Add the replicate Parameter

Map HR.EMPLOYEES, Target HR.STAFF, COLMAP (USEDEFAULTS, WAGES = SALARY); 

Note: In the replicate parameter, use assumetargetdefs to indicate that the source and target tables are consistent. If the table name of the source segment is: staff.

The target table structure is as follows:

Table staff name null? Type verification -------- ---------------------------- employee_id (PK) not null number (6) first_name varchar2 (20) last_name not null varchar2 (25) full_name not null varchar2 (46) Email not null varchar2 (25) phone_number varchar2 (20) hire_date not null date job_id not null varchar2 (10)WagesNumber () ---- source segment field name: salarycommission_pct number () manager_id number (6) department_id number (4)

Next, because the tables at source and target are different, you need to generate a data definition file.

Source segment editing

GGSCI (db11) 2> edit param defgendefsfile ./dirdef/RHREMD1.defsUSERID ogg, PASSWORD oggTABLE HR.*; 

Run the following command:

[email protected]  /home/oracle/ogg$ ./defgen paramfile dirprm/defgen.prm ***********************************************************************        Oracle GoldenGate Table Definition Generator for Oracle      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.                    Starting at 2014-10-06 22:08:47***********************************************************************Operating System Version:LinuxVersion #1 SMP Fri Jan 27 17:17:51 EST 2012, Release 2.6.18-308.el5Node: db11Machine: x86_64                         soft limit   hard limitAddress Space Size   :    unlimited    unlimitedHeap Size            :    unlimited    unlimitedFile Size            :    unlimited    unlimitedCPU Time             :    unlimited    unlimitedProcess id: 27974*************************************************************************            Running with the following parameters                  *************************************************************************defsfile ./dirdef/RHREMD1.defsUSERID ogg, PASSWORD ***TABLE HR.*;Expanding wildcard HR.*:Retrieving definition for HR.COUNTRIESRetrieving definition for HR.DEPARTMENTSRetrieving definition for HR.EMPLOYEESRetrieving definition for HR.JOBSRetrieving definition for HR.JOB_HISTORYRetrieving definition for HR.LOCATIONSRetrieving definition for HR.REGIONSRetrieving definition for HR.T2014-10-06 22:09:11  WARNING OGG-00869  No unique key is defined for table ‘T‘. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.Definitions generated for 8 tables in ./dirdef/RHREMD1.defs

Send the def file to the dirdef directory of the target end.

[email protected]  /home/oracle/ogg$ scp ./dirdef/RHREMD1.defs [email protected]192.168.1.3:/home/oracle/ogg/dirdef

Finally, tell Ogg uses the defgen file, and adds

SourceDefs ./dirdef/RHREMD1.defs

Comment out

--AssumeTargetDefs

Start the process!

4.3 advanced features (3) -- Filter