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 inliquibase/lib/ext
. - the privilege to create a table.
- the extension installed in your Liquibase environment. Add the
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.
The different commands are:
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.
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
.
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
.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
- Connect to hr.
SQL> connect hr/hr connect hr/hr Connected.
- Capture the object.
SQL> lb genobject table emp lb genobject table emp Action successfully completed please review created file emp_table.xml
- Connect to the other user.
SQL> connect hr2/hr2 connect hr2/hr2 Connected.
- Ensure the object does not already exist.
SQL> drop table emp drop table emp Table EMP dropped.
- Create the object in the current schema.
Note:
As the schema name has changed, you must set include schema tofalse
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.
- 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
- 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
- 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
- Make a change to the v2 directory.
SQL> cd .. cd .. SQL> !mkdir v2 !mkdir v2 SQL> cd v2 cd v2 S
- 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
- 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.
- 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
- 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
- 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
- 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)