1. User's Guide
  2. Using Liquibase with SQLcl

2 Using Liquibase with SQLcl

This chapter explains the Liquibase feature in SQLcl. It has the following topics:

2.1 About Liquibase

Liquibase is an open-source database-independent library for tracking, managing and applying database schema changes. For an understanding of the major concepts in Liquibase, see Major Concepts.

In SQLcl, you can now execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs). You can process these objects manually using SQLcl or through any of the traditional Liquibase interfaces.

With the Liquibase feature in SQLcl, you can:

  • Generate and execute single object changelogs
  • Generate and execute schema changesets with object dependencies
  • Automatically sort a changeset during creation based on object dependencies
  • Record all SQL statements for changeset or changelog execution, as it is generated
  • Provide full rollback support for changesets and changelogs automatically

2.2 Requirements for Using Liquibase

The two important aspects for using Liquibase are capturing and deploying objects in an Oracle database.

Capture Objects

To capture an object or a schema, you must have SQLcl 19.2 or later installed.

In this release, you can only capture objects from the schema you are connected to in SQLcl. You also need write permission on the directory in which you save the files.

If you are capturing an entire schema, the user you are connected to must have the privilege to create a table. The DATABASECHANGELOG_EXPORT table is created internally to gather object details and sort them correctly. The created object is automatically excluded from the capture process and destroyed upon capture completion.

Deploy Objects

Liquibase uses the DATABASECHANGELOG table to track the changesets that have been run. The DATABASECHANGELOGLOCK table ensures that only one instance of Liquibase is running at a time. The DATABASECHANGELOG_EXPORT table tracks the object state and the SQL statements executed during deployment.

  • SQLcl

    Deploying changes to any database through SQLcl requires the 19.2 release or later and the privilege to create a table. You must have necessary permissions to create any object type through the change that you are deploying.

  • Liquibase

    If you use Liquibase directly to deploy changesets, you need to have:

    • the extension installed in your Liquibase environment. Add the oracle-liquibase.jar file in liquibase/lib/ext.
    • the privilege to create a table.

2.3 Supported Types

DDL types use create or replace syntax, however, a snapshot of the object is taken before applying the change so automatic rollback to the last known state is supported. SXML types support automatic alter generation with automatic rollback support.

DDL types have their own change type.

  • CONSTRAINT
  • DIMENSION
  • FUNCTION
  • OBJECT_GRANT
  • PACKAGE_BODY
  • PACKAGE_SPEC
  • PROCEDURE
  • PUBLIC_SYNONYM
  • REF_CONSTRAINT
  • SYNONYM
  • TYPE BODY
  • TYPE SPEC

SXML types share the SXML change type.

  • AQ_QUEUE
  • AQ_QUEUE_TABLE
  • AQ_TRANSFORM MATERIALIZED_VIEW
  • ASSOCIATION
  • AUDIT
  • AUDIT_OBJ
  • CLUSTER
  • CONTEXT
  • DB_LINK
  • DEFAULT_ROLE
  • FGA_POLICY
  • JOB
  • LIBRARY
  • MATERIALIZED_VIEW_LOG
  • OPERATOR
  • PROFILE
  • PROXY
  • REFRESH_GROUP
  • RESOURCE_COST
  • RLS_CONTEXT
  • RLS_GROUP
  • RMGR_CONSUMER_GROUP
  • RMGR_INTITIAL_CONSUMER_GROUP
  • RMGR_PLAN
  • RMGR_PLAN_DIRECTIVE
  • ROLE
  • ROLLBACK_SEGMENT
  • SEQUENCE
  • TABLE
  • TABLESPACE
  • TRIGGER XS_ACL
  • TRUSTED_DB_LINK
  • USER
  • VIEW
  • XMLSCHEMA
  • XS_ACL_PARAM INDEX
  • XS_DATA_SECURITY
  • XS_ROLE
  • XS_ROLESET
  • XS_ROLE_GRANT
  • XS_SECURITY_CLASS
  • XS_USER

2.4 Supported Liquibase Commands in SQLcl

You can invoke the Liquibase commands in SQLcl with liquibase or lb. To display a list of all available commands, execute liquibase or lb with no arguments.

  • liquibase(lb) genobject <object_type> <object_name>

    Captures a single object using the current connection in SQLcl. It creates an xml file in the current working directory named <object name>_<object type>.xml.

    Available Parameters:

    Name Description Required
    Object type Type of object (this is an Oracle type) Yes
    Object name Name of object Yes

    Example:

    SQL> lb genobject table employees 
lb genobject table employees 
Action successfully completed please review created file EMPLOYEES_TABLE.xml

  • liquibase(lb) genschema

    Captures the entire schema that the user is connected to in SQLcl. It creates an xml file in the current working directory for each object in the schema, and a controller.xml file. The controller file is a change log that includes all files in the proper order to allow the schema to be deployed correctly.

    Available Parameters:

    Name Description Required
    Public synonym Capture public synonyms. Default is false. No
    Grants Capture grants. Default is false. No

    Example:

    SQL> lb genschema
lb genschema
[Type - TYPE_SPEC]:                          153 ms
[Type - TYPE_BODY]:                          29 ms
[Type - SEQUENCE]:                           48 ms
[Type - CLUSTER]:                            27 ms
[Type - TABLE]:                              36 ms
[Type - MATERIALIZED_VIEW_LOG]:              19 ms
[Type - MATERIALIZED_VIEW]:                  6 ms
[Type - VIEW]:                               148 ms
[Type - REF_CONSTRAINT]:                     272 ms
[Type - DIMENSION]:                          23 ms
[Type - FUNCTION]:                           27 ms
[Type - PROCEDURE]:                          64 ms
[Type - PACKAGE_SPEC]:                       171 ms
[Type - DB_LINK]:                            14 ms
[Type - SYNONYM]:                            22 ms
[Type - INDEX]:                              202 ms
[Type - TRIGGER]:                            51 ms
[Type - PACKAGE_BODY]:                       252 ms
[Method loadCaptureTable]:                   1864 ms
[Method parseCaptureTableRecords]:           7342 ms
[Method sortCaptureTable]:                   30 ms
[Method createExportChangeLogs]:             3 ms

Export Flags Used:
Export Grants               false
Export Synonyms             false

  • liquibase(lb) gencontrolfile

    Creates an empty changelog, master.xml, with a placeholder to include files in the current working directory. You can use this command when you are creating you own changelog with custom changeset inclusions.

    Example:
    SQL> lb gencontrolfile
lb gencontrolfile
Action successfully completed please review created file controller.xml

  • liquibase(lb) update <CHANGE LOG> {include schema}

    Applies the specified change log using the current connection. You can choose to have the schema included in the DDL by passing in TRUE for include schema. The default value is FALSE.

    Example:
    SQL> lb update foo_table.xml
lb update foo_table.xml

Table "FOO" created.

  • liquibase(lb) updatesql <CHANGE LOG> {include schema}

    Generates and renders to the screen the SQL statements that would be applied for a specific change log. You can choose to have the schema included in the DDL by passing in TRUE for include schema. The default value is FALSE.

    Example:
    SQL> lb updatesql syme_table.xml
lb updatesql syme_table.xml

CREATE TABLE "Syme"
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 NOCOMPRESS LOGGING
  TABLESPACE "SYSAUX"

  • liquibase(lb) rollback <CHANGE LOG> <COUNT>

    Rolls back changes starting from the last change applied using the input change log. The count can be higher than the changes in the change log. 999 is the maximum size and will roll back all the changes.

    Example:
    SQL> lb rollback syme_table.xml 999
lb rollback syme_table.xml 999
Table "Syme" dropped.

  • liquibase(lb) diff <DEST URL> <DEST USER> <DEST PASS> {report}

    Displays differences between the current connection and the specified database. When the report is true, the output will be in the form of a text report. When the report is false, which is the default, the output will be in the form of a change log.

    The destination URL format is HOST:PORT:SID or HOST:PORT/SERVICE.

    Example:
    SQL> lb diff localhost:1521/pdb1 hr2 hr2
lb diff localhost:1521/pdb1 hr2 hr2
Action successfully completed please review created file diffResult.xml

diffResult.xml
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd 
http://www.liquibase.org/xml/ns/dbchangelog 
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
    <changeSet author="SKUTZ (generated)" id="1560183964715-1">
        <createTable tableName="DATABASECHANGELOG_EXPORT">
            <column name="RANK" type="NUMBER">
                <constraints nullable="false"/>
            </column>
            <column name="TYPE" type="VARCHAR2(100 BYTE)">
                <constraints primaryKey="true" 
                primaryKeyName="DATABASECHANGELOG_EXPORT_PK"/>
            </column>
            <column name="SEQ" type="NUMBER">
                <constraints primaryKey="true" 
                 primaryKeyName="DATABASECHANGELOG_EXPORT_PK"/>
            </column>
            <column name="META" type="CLOB">
                <constraints nullable="false"/>
            </column>
            <column name="OBJECT_NAME" type="VARCHAR2(200 BYTE)"/>
            <column name="FILE_NAME" type="VARCHAR2(200 BYTE)"/>
            <column name="DEP_NAME" type="VARCHAR2(200 BYTE)"/>
            <column name="DEP_COUNT" type="NUMBER"/>
        </createTable>
    </changeSet>
    <changeSet author="SKUTZ (generated)" id="1560183964715-2">
        <addColumn tableName="EMPLOYEES">
            <column name="FOOBAR" type="NUMBER"/>
        </addColumn>
    </changeSet>
    <changeSet author="SKUTZ (generated)" id="1560183964715-3">
        <createIndex indexName="ORDERED_ROWS" tableName="DATABASECHANGELOG_EXPORT">
            <column name="RANK"/>
            <column name="SEQ"/>
        </createIndex>
    </changeSet>
    <changeSet author="SKUTZ (generated)" id="1560183964715-4">
        <createIndex indexName="TYPE_INDEX" tableName="DATABASECHANGELOG_EXPORT">
            <column name="TYPE"/>
        </createIndex>
    </changeSet>
</databaseChangeLog>

SQL> lb diff localhost:1521/pdb1 hr2 hr2 true
lb diff localhost:1521/pdb1 hr2 hr2
Action successfully completed please review created file diffResult.txt

diffResult.txt
Reference Database: HR @ jdbc:oracle:thin:@(DESCRIPTION =    (ADDRESS_LIST =      
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))    )    (CONNECT_DATA =      
(SERVICE_NAME = pdb1)    )  ) (Default Schema: HR)
Comparison Database: HR2 @ jdbc:oracle:thin:@localhost:1521/pdb1 (Default Schema: HR2)
Compared Schemas: HR -> HR2
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s): NONE
Missing Column(s): 
     HR.DATABASECHANGELOG_EXPORT.DEP_COUNT
     HR.DATABASECHANGELOG_EXPORT.DEP_NAME
     HR.DATABASECHANGELOG_EXPORT.FILE_NAME
     HR.EMPLOYEES.FOOBAR
     HR.DATABASECHANGELOG_EXPORT.META
     HR.DATABASECHANGELOG_EXPORT.OBJECT_NAME
     HR.DATABASECHANGELOG_EXPORT.RANK
     HR.DATABASECHANGELOG_EXPORT.SEQ
     HR.DATABASECHANGELOG_EXPORT.TYPE
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s): 
     DATABASECHANGELOG_EXPORT_PK UNIQUE  ON HR.DATABASECHANGELOG_EXPORT(SEQ, TYPE)
     ORDERED_ROWS ON HR.DATABASECHANGELOG_EXPORT(RANK, SEQ)
     TYPE_INDEX ON HR.DATABASECHANGELOG_EXPORT(TYPE)
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s): 
     DATABASECHANGELOG_EXPORT_PK on HR.DATABASECHANGELOG_EXPORT(SEQ, TYPE)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Table(s): 
     DATABASECHANGELOG_EXPORT
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE

  • liquibase(lb) status <CHANGE LOG>

    Checks the status of the change log using the current connection. This shows if the change log has been applied and the result of the change log.

    Example:
    SQL> lb status syme_table.xmllb status syme_table.xml1 change
sets have not been applied to HR@jdbc:oracle:thin:@(DESCRIPTION =  (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT =  1521)) ) (CONNECT_DATA = (SERVICE_NAME = pdb1) )
) syme_table.xml::f3d9d927-3c5a-415c-a6fa-1dbc35a9da17::Generated

  • liquibase(lb) validate <CHANGE LOG>

    Verifies if the change log is syntactically correct.

    Example:
    SQL> lb validate syme_table.xml
lb validate syme_table.xml

No issues were found in file syme_table.xml, validation passed.

  • liquibase(lb) changelogsync <CHANGE LOG>

    Writes the SQL statements to mark all changes in the change log as executed in the database to a file.

    Example:
    SQL> lb changelogsync syme_table.xml
lb changelogsync syme_table.xml
Action successfully completed please review created file changelogsync_syme_table.xml

  • liquibase(lb) listlocks <CHANGE LOG>

    Lists who currently has locks on the database change log.

    Example:
    SQL> lb listlocks syme_tbales.xml
lb listlocks syme_tbales.xml

ID     USER
!1     Skutz-laptop (10.39.200.228)

  • liquibase(lb) releaselocks <CHANGE LOG>

    Releases all locks on the database change log.

    Example:
    SQL> lb releaselocks syme_table.xml
lb releaselocks syme_table.xml

Operation completed successfully all locks on syme_table.xml released

  • liquibase(lb) clearchecksums <CHANGE LOG>

    Removes the current checksums from the database. In the next run, the checksums will be recomputed.

    Example:
    SQL> lb clearchecksums syme_table.xml
lb clearchecksums syme_table.xml

Operation completed successfully all checksums cleared for syme_table.xml

  • liquibase(lb) help <COMMAND>

    Lists the available Liquibase commands. Type a command with no options for help on a specific command.

    Example:
    SQL> lb help

lb help
Provides a command line interface to Liquibase change management features from within SQLcl!
Available commands are

liquibase(lb) genobject <object_type> <object_name>
liquibase(lb) genschema
liquibase(lb) gencontrolfile
liquibase(lb) update <CHANGE LOG> {include schema}
liquibase(lb) updatesql <CHANGE LOG> {include schema}
liquibase(lb) rollback <CHANGE LOG> <COUNT>
liquibase(lb) diff <DEST URL> <DEST USER> <DEST PASS> {report}
liquibase(lb) status <CHANGE LOG>
liquibase(lb) validate <CHANGE LOG>
liquibase(lb) changelogsync <CHANGE LOG>
liquibase(lb) listlocks <CHANGE LOG>
liquibase(lb) releaseLocks <CHANGE LOG>
liquibase(lb) clearchecksums <CHANGE LOG>
liquibase(lb) help <COMMAND>

2.5 ChangeSets

The following table lists the changeSets and provides a description for each of them. To learn more about changeSets, see <changeSet> tag.

ChangeSet Description
CreateOracleConstraint Creates a constraint from SQL.
CreateOracleFunction Creates a function from SQL.
CreateOracleGrant Creates a grant from SQL.
CreateOraclePackageBody Creates a package body from SQL.
CreateOraclePackageSpec Creates a package specification from SQL.
CreateOracleProcedure Creates a procedure from SQL.
CreateOraclePublicSynonym Creates a public synonym from SQL.
CreateOracleRefConstraint Creates a referential constraint from SQL.
CreateOracleSynonym Creates a synonym from SQL.
CreateOracleTrigger Creates a trigger from SQL.
CreateOracleTypeBody Creates a type body from SQL.
CreateOracleTypeSpec Creates a type spec from SQL.
CreateSxmlObject Creates a function from SQL.
DropOracleConstraint Drops a constraint.
DropOracleFunction Drops a function.
DropOracleGrant Drops a grant.
DropOraclePackageBody Drops a package body.
DropOraclePackageSpec Drops a package specification.
DropOracleProcedure Drops a procedure.
DropOracleRefConstraint Drops a referential constraint.
DropOracleTrigger Drops a trigger.
DropOracleTypeBody Drops a type body.
DropOracleTypeSpec Drops a type specification.
DropOracleSynonym Drops a synonym.
DropSxmlObject Drops an SXML object. If the object was created through createSxmlObject, this rolls back the object to the last state. If not created, the object is just dropped. This is primarily used internally for SXML object handling.
RunOracleScript Runs an Oracle script.

2.6 Examples Using Liquibase

Some examples for using the Liquibase feature in SQLcl are:

Capture and Deploy an Object

To deploy the emp table from hr to hr2:

  1. Connect to hr.
    SQL> connect hr/hr
connect hr/hr
Connected.
  2. Capture the object.
    SQL> lb genobject table emp
lb genobject table emp
Action successfully completed please review created file emp_table.xml
  3. Connect to the other user.
    SQL> connect hr2/hr2
connect hr2/hr2
Connected.
  4. Ensure the object does not already exist.
    SQL> drop table emp
drop table emp

Table EMP dropped.
  5. Create the object in the current schema.

    Note:

    As the schema name has changed, you must set include schema to false or it will try and create the object in the HR schema. 
    SQL> lb update emp_table.xml false
lb update emp_table.xml false

Table "EMP" created.
  6. Verify the object was created.
    SQL> desc emp 
desc emp 
Name Null? Type 
---- ----- ------ 
ID NUMBER

Capture and Deploy a Schema

To capture the HR schema and reproduce it in the HR2 schema:

sql.exe hr/hr@pdb1

SQL> lb genschema
lb genschema
[Type - TYPE_SPEC]: 142 ms
[Type - TYPE_BODY]: 27 ms
[Type - SEQUENCE]: 61 ms
[Type - CLUSTER]: 25 ms
[Type - TABLE]: 447 ms
[Type - MATERIALIZED_VIEW_LOG]: 18 ms
[Type - MATERIALIZED_VIEW]: 6 ms
[Type - VIEW]: 143 ms
[Type - REF_CONSTRAINT]: 261 ms
[Type - DIMENSION]: 17 ms
[Type - FUNCTION]: 63 ms
[Type - PROCEDURE]: 66 ms
[Type - PACKAGE_SPEC]: 29 ms
[Type - DB_LINK]: 19 ms
[Type - SYNONYM]: 19 ms
[Type - INDEX]: 199 ms
[Type - TRIGGER]: 39 ms
[Type - PACKAGE_BODY]: 39 ms
[Method loadCaptureTable]: 1620 ms
[Method parseCaptureTableRecords]: 6433 ms
[Method sortCaptureTable]: 25 ms
[Method createExportChangeLogs]: 3 ms

Export Flags Used:
Export Grants false
Export Synonyms false

SQL> connect system/sparrow
connect system/sparrow
Connected.

setup the hr2 user --

drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;

SQL> connect hr2/hr2
connect hr2/hr2
Connected.

SQL> lb update controller.xml
lb update controller.xml

Sequence "DEPARTMENTS_SEQ" created.

Sequence "LOCATIONS_SEQ" created.

Sequence "EMPLOYEES_SEQ" created.

Table "COUNTRIES" created.

Table "REGIONS" created.

Table "LOCATIONS" created.

Table "DEPARTMENTS" created.

Table "JOBS" created.

Table "EMPLOYEES" created.

Table "JOB_HISTORY" created.

Table "Syme" created.

Table "FOO" created.

View "EMP_DETAILS_VIEW" created.

Table "COUNTRIES" altered.

Table "LOCATIONS" altered.

Table "DEPARTMENTS" altered.

Table "EMPLOYEES" altered.

Table "EMPLOYEES" altered.

Table "EMPLOYEES" altered.

Table "DEPARTMENTS" altered.

Table "JOB_HISTORY" altered.

Table "JOB_HISTORY" altered.

Table "JOB_HISTORY" altered.

Function FUNCTION1 compiled

Procedure SECURE_DML compiled

Procedure ADD_JOB_HISTORY compiled

Procedure PROCEDURE1 compiled

Index "LOC_COUNTRY_IX" created.

Index "JHIST_JOB_IX" created.

Index "LOC_STATE_PROVINCE_IX" create.

Index "EMP_DEPARTMENT_IX" created.

Index "JHIST_EMPLOYEE_IX" created.

Index "LOC_CITY_IX" created.

Index "JHIST_DEPARTMENT_IX" created.

Index "EMP_JOB_IX" created.

Index "EMP_MANAGER_IX" created.

Index "EMP_NAME_IX" created.

Index "DEPT_LOCATION_IX" created.

Trigger SECURE_EMPLOYEES compiled

Trigger "SECURE_EMPLOYEES" altered.

Trigger UPDATE_JOB_HISTORY compiled

Generate the Master Control File

SQL> lb gencontrolfile
lb gencontrolfile
Action successfully completed please review created file controller.xml

SQL> !type controller.xml
!type controller.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<include file="{filename.xml}"/>
</databaseChangeLog>

Capture and Deploy a Schema and then Upgrade it and Redeploy

  1. Migrate the HR schema to the HR2 schema, then modify the HR schema and update HR2.
    Drop/create HR2 user as system

SQL> connect system/sparrow 
connect system/sparrow 
Connected. 
SQL> drop user hr2 cascade;  
 
User HR2 dropped. 
 
SQL> create user hr2 identified by hr2; 
 
User HR2 created. 
 
SQL> grant connect,resource, create view to hr2; 
 
Grant succeeded. 
 
SQL> alter user hr2 quota unlimited on users; 
 
User HR2 altered. 
 
SQL> 
alter user hr2 quota unlimited on sysaux; 
 
User HR2 altered. 

SQL> !mkdir v1 
!mkdir v1 
 
SQL> cd v1 
cd v1 
    SQL> connect hr/hr 
connect hr/hr 
Connected. 

SQL> lb genschema 
lb genschema 
[Type - TYPE_SPEC]: 163 ms 
[Type - TYPE_BODY]: 30 ms 
[Type - SEQUENCE]: 57 ms 
[Type - CLUSTER]: 27 ms 
[Type - TABLE]: 311 ms 
[Type - MATERIALIZED_VIEW_LOG]: 19 ms 
[Type - MATERIALIZED_VIEW]: 8 ms 
[Type - VIEW]: 155 ms 
[Type - REF_CONSTRAINT]: 293 ms 
[Type - DIMENSION]: 29 ms 
[Type - FUNCTION]: 77 ms 
[Type - PROCEDURE]: 65 ms 
[Type - PACKAGE_SPEC]: 31 ms 
[Type - DB_LINK]: 13 ms 
[Type - SYNONYM]: 18 ms 
[Type - INDEX]: 188 ms 
[Type - TRIGGER]: 41 ms 
[Type - PACKAGE_BODY]: 37 ms 
[Method loadCaptureTable]: 1562 ms 
[Method parseCaptureTableRecords]: 8437 ms 
[Method sortCaptureTable]: 37 ms 
[Method createExportChangeLogs]: 4 ms 
 
Export Flags Used: 
Export Grants false 
Export Synonyms false
  2. Modify the HR Schema.
    SQL> ALTER TABLE COUNTRIES ADD (COLUMN1 VARCHAR2(20) ); 

Table COUNTRIES altered. 
 
SQL> ALTER TABLE "Syme" ADD (COLUMN1 VARCHAR2(20) ); 
 
Table "Syme" altered. 
 
SQL> ALTER TABLE "Syme" ADD (COLUMN2 VARCHAR2(20) ); 
 
Table "Syme" altered. 
 
SQL> ALTER TABLE "Syme" ADD (COLUMN3 VARCHAR2(20) ); 
 
Table "Syme" altered. 
 
SQL> CREATE VIEW VIEW1 AS SELECT * FROM "Syme"; 
 
View VIEW1 created. 
 
SQL> CREATE OR REPLACE PROCEDURE PROCEDURE2 (PARAM1 IN VARCHAR2,PARAM2 IN VARCHAR2,PARAM3 IN VARCHAR2, PARAM4 IN VARCHAR
2) AS BEGIN NULL; END PROCEDURE2; 
 
Procedure PROCEDURE2 compiled
  3. Make a change to the v2 directory.
    SQL> cd .. 
cd .. 
SQL> !mkdir v2 
!mkdir v2 
 
SQL> cd v2 
cd v2 
S
  4. Export v2 of the HR schema.
    SQL> lb genschema 
lb genschema 
[Type - TYPE_SPEC]: 44 ms 
[Type - TYPE_BODY]: 30 ms 
[Type - SEQUENCE]: 51 ms 
[Type - CLUSTER]: 26 ms 
[Type - TABLE]: 447 ms 
[Type - MATERIALIZED_VIEW_LOG]: 13 ms 
[Type - MATERIALIZED_VIEW]: 6 ms 
[Type - VIEW]: 110 ms 
[Type - REF_CONSTRAINT]: 258 ms 
[Type - DIMENSION]: 16 ms 
[Type - FUNCTION]: 56 ms 
[Type - PROCEDURE]: 62 ms 
[Type - PACKAGE_SPEC]: 29 ms 
[Type - DB_LINK]: 12 ms 
[Type - SYNONYM]: 13 ms 
[Type - INDEX]: 205 ms 
[Type - TRIGGER]: 39 ms 
[Type - PACKAGE_BODY]: 34 ms 
[Method loadCaptureTable]: 1451 ms 
[Method parseCaptureTableRecords]: 7015 ms 
[Method sortCaptureTable]: 23 ms 
[Method createExportChangeLogs]: 4 ms 
 
Export Flags Used: 
Export Grants false 
Export Synonyms false
  5. To import v1, change back to the v1 directory and connect as HR2.
    SQL> cd ../v1 
cd ../v1 
SQL> connect hr2/hr2 
connect hr2/hr2 
Connected.
  6. Now import the capture into the HR2 schema.
    SQL> lb update controller.xml 
lb update controller.xml  
Sequence "DEPARTMENTS_SEQ" created. 
Sequence "LOCATIONS_SEQ" created. 
Sequence "EMPLOYEES_SEQ" created. 
Table "COUNTRIES" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "REGIONS" created. 
Table "LOCATIONS" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "DEPARTMENTS" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "JOBS" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "EMPLOYEES" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "JOB_HISTORY" created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "Syme" created. 
Table "FOO" created. 
View "EMP_DETAILS_VIEW" created. 
Table "COUNTRIES" altered. 
Table "LOCATIONS" altered. 
Table "DEPARTMENTS" altered. 
Table "EMPLOYEES" altered. 
Table "EMPLOYEES" altered. 
Table "EMPLOYEES" altered. 
Table "DEPARTMENTS" altered. 
Table "JOB_HISTORY" altered. 
Table "JOB_HISTORY" altered. 
Table "JOB_HISTORY" altered. 
Function FUNCTION1 compiled 
Procedure SECURE_DML compiled 
Procedure ADD_JOB_HISTORY compiled 
Procedure PROCEDURE1 compiled 
Index "LOC_COUNTRY_IX" created. 
Index "JHIST_JOB_IX" created. 
Index "LOC_STATE_PROVINCE_IX" created. 
Index "EMP_DEPARTMENT_IX" created. 
Index "JHIST_EMPLOYEE_IX" created. 
Index "LOC_CITY_IX" created. 
Index "JHIST_DEPARTMENT_IX" created. 
Index "EMP_JOB_IX" created. 
Index "EMP_MANAGER_IX" created. 
Index "EMP_NAME_IX" created. 
Index "DEPT_LOCATION_IX" created. 
Trigger SECURE_EMPLOYEES compiled 
Trigger "SECURE_EMPLOYEES" altered. 
Trigger UPDATE_JOB_HISTORY compiled
  7. Check a table to verify.
    SQL> desc "Syme" 
desc "Syme" 
Name Null? Type 
---- ----- ------ 
ID NUMBER 
SQL> desc countries 
desc countries 
Name Null? Type 
------------ -------- ------------ 
COUNTRY_ID NOT NULL CHAR(2) 
COUNTRY_NAME VARCHAR2(40) 
REGION_ID NUMBER
  8. Switch to v2 and import the capture.
    SQL> cd ../v2 
cd ../v2 
SQL> lb update controller.xml 
lb update controller.xml 
Table "HR2"."COUNTRIES" altered. 
Table "HR2"."COUNTRIES" altered. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "HR2"."LOCATIONS" altered. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "HR2"."DEPARTMENTS" altered. 
Table "HR2"."DEPARTMENTS" altered. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "HR2"."EMPLOYEES" altered. 
Table "HR2"."EMPLOYEES" altered. 
Table "HR2"."EMPLOYEES" altered. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "HR2"."JOB_HISTORY" altered. 
Table "HR2"."JOB_HISTORY" altered. 
Table "HR2"."JOB_HISTORY" altered. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Comment created. 
Table "HR2"."Syme" altered. 
Table "HR2"."Syme" altered. 
Table "HR2"."Syme" altered. 
View "VIEW1" created. 
Table "COUNTRIES" altered. 
Table "LOCATIONS" altered. 
Table "DEPARTMENTS" altered. 
Table "EMPLOYEES" altered. 
Table "EMPLOYEES" altered. 
Table "EMPLOYEES" altered. 
Table "DEPARTMENTS" altered. 
Table "JOB_HISTORY" altered. 
Table "JOB_HISTORY" altered. 
Table "JOB_HISTORY" altered. 
Function FUNCTION1 compiled 
Procedure SECURE_DML compiled 
Procedure ADD_JOB_HISTORY compiled 
Procedure PROCEDURE1 compiled 
Procedure PROCEDURE2 compiled 
Trigger SECURE_EMPLOYEES compiled 
Trigger UPDATE_JOB_HISTORY compiled
  9. Verify that you are on v2.
    SQL> desc "Syme" 
desc "Syme" 
Name Null? Type 
------- ----- ------------ 
ID NUMBER 
COLUMN1 VARCHAR2(20) 
COLUMN2 VARCHAR2(20) 
COLUMN3 VARCHAR2(20) 
SQL> desc countries 
desc countries 
Name Null? Type 
------------ -------- ------------ 
COUNTRY_ID NOT NULL CHAR(2) 
COUNTRY_NAME VARCHAR2(40) 
REGION_ID NUMBER 
COLUMN1 VARCHAR2(20)