View a markdown version of this page

Export Oracle database objects - AWS Database Migration Service

Export Oracle database objects

For an offline source, you upload exported script files that describe your source database objects. You can export them using any available tool. The following tabs describe two approaches: Oracle SQL Developer and Oracle SQLcl.

SQL Developer
  1. In SQL Developer, choose Tools, and then choose Database Export.

  2. For Connection, choose your database connection.

  3. Select Export DDL and clear Export Data.

  4. Set the DDL options according to the following table.

  5. For Save As, choose Separate Files.

  6. For Encoding, choose UTF8.

  7. For File, enter the path to a directory for the output files. We recommend creating a directory with the same name as the schema.

  8. Choose Next.

  9. On the Types to Export page, select Toggle All Standard and Toggle All Extension to include all object types.

  10. Choose Next.

  11. On the Specify Objects page, for Schema choose the schema to export. For Type, choose ALL OBJECTS. Choose Lookup to populate the object list.

  12. Choose Next, review the export summary, and then choose Finish.

DDL export options

Set the following options on the Source/Destination page.

DDL export options
Option Value
PartitioningTrue
Show SchemaTrue
TerminatorTrue
StorageTrue
TablespaceTrue
DropsFalse
Cascade DropsFalse
GrantsFalse
DependentsFalse
Pretty PrintTrue
Add BYTE keywordTrue
Add Force to ViewsFalse
VersionCOMPATIBLE

SQL Developer exports database objects in its own way, and the results can vary. For the best conversion results, use complete and accurate scripts.

SQLcl

With Oracle SQLcl and the project export command, you can fully automate and script the export of database DDL.

Prerequisites

To use Oracle SQLcl to export DDL scripts, you need the following:

  • PowerShell 7.x (pwsh)

  • Oracle SQLcl 26.2.0 or later

  • Java 17 or later

  • Oracle Database 11g R2 or later

  • Database user with SELECT_CATALOG_ROLE or equivalent privileges

Export script

The following PowerShell script initializes an SQLcl project, configures export filters, and exports one SQL file for each database object.

param( [Parameter(Mandatory)][string]$Server, [string]$Port = "1521", [Parameter(Mandatory)][string]$Service, [Parameter(Mandatory)][string]$User, [Parameter(Mandatory)][string]$Password, [Parameter(Mandatory)][string]$Schema, [string]$OutputDir = "./$Schema", [string]$SqlclBin = "sql" ) $ErrorActionPreference = "Stop" $Project = "migration_project" $ConnStr = "${User}/${Password}@${Server}:${Port}/${Service}" if (-not (Test-Path $OutputDir)) { New-Item -ItemType Directory -Path $OutputDir -Force | Out-Null } $OutputDir = (Resolve-Path -LiteralPath $OutputDir).Path $ProjectDir = Join-Path $OutputDir $Project $FiltersDir = Join-Path (Join-Path $ProjectDir ".dbtools") "filters" $LogFile = Join-Path $OutputDir "export.log" function Invoke-Sqlcl([string]$Name, [string]$Content) { $file = Join-Path $OutputDir $Name Set-Content -Path $file -Value $Content -Encoding utf8NoBOM & $SqlclBin $ConnStr "@$file" if ($LASTEXITCODE -ne 0) { throw "SQLcl failed ($Name), exit code $LASTEXITCODE. See $LogFile" } } Write-Host "Exporting schema [$Schema] from ${Server}:${Port}/${Service} ..." Invoke-Sqlcl "export-init.sql" @" spool "$LogFile" project init -d "$OutputDir" -n $Project -s "$Schema" -mr spool off exit "@ if (-not (Test-Path $FiltersDir)) { throw "SQLcl project was not created: $FiltersDir not found. See $LogFile" } Set-Content -Path (Join-Path $FiltersDir "ddl.filters") -Encoding utf8NoBOM -Value @( " owner =ANY('$Schema')" ",export_type != 'ALL_TAB_PRIVS'" ",export_type != 'USER_SYS_PRIVS'" ",export_type != 'ALL_USERS'" ",export_type != 'APEX_APPLICATIONS'" ",export_type != 'ORDS_SCHEMA'" ) Set-Content -Path (Join-Path $FiltersDir "dml.filters") -Encoding utf8NoBOM -Value @( " export_type != 'TABLES_DML'" ",export_type != 'USER_ROLE_PRIVS'" ",export_type != 'ALL_USERS'" ) # Step 3: configure the DDL transforms and export one file for each object. Invoke-Sqlcl "export.sql" @" spool "$LogFile" append cd "$ProjectDir" project config set -name export.format.enable -value true project config set -name export.setTransform.constraintsAsAlter -value true project config set -name export.setTransform.constraints -value true project config set -name export.setTransform.emitSchema -value true project config set -name export.setTransform.sizeByteKeyword -value true project config set -name export.setTransform.sqlterminator -value true project config set -name export.setTransform.partitioning -value true project config set -name export.setTransform.segmentAttributes -value true project config set -name export.setTransform.storage -value true project config set -name export.setTransform.tablespace -value true project export -t 8 -v spool off exit "@ Write-Host "Done."
Important considerations

With SQLcl, some objects might not be exported because of the following known behaviors:

  • SQLcl converts all object names to lowercase when creating filenames. Objects whose names differ only by letter case (for example, PKG_NAME and Pkg_Name) produce the same filename, and the second object silently overwrites the first.

  • SQLcl cannot export object names that contain characters not supported by the file system. On Windows, the characters : * ? " < > | are not allowed in filenames. Object names that contain the dollar sign ($) character cause errors in the SQLcl file-writing mechanism.

  • On macOS, objects whose names differ only by Unicode representation (precomposed versus combining accent) produce the same filename because of filesystem normalization. This does not affect Linux or Windows.

  • SQLcl silently skips database objects in an invalid state (for example, a view that references a dropped table). Review the export log to identify any skipped objects.

Export tools vary in which object types they export and how they write each object. Oracle SQL Developer and Oracle SQLcl are two examples. As a result, the source objects that DMS Schema Conversion shows for an offline source can depend on the tool you used. After you export, compare the exported objects with your source database and add scripts for any that are missing. Don't upload full exports from more than one tool for the same schema, because this creates duplicate objects in the offline source.

The following object categories are the ones most likely to differ between export tools. Check each one after you export:

  • Users and schemas

  • Database links

  • Scheduler jobs, programs, and schedules

DMS Schema Conversion determines the schema that owns an object as follows. For most objects, it uses the schema in the object's fully qualified name. If the name doesn't include a schema, DMS Schema Conversion assigns the object to the PUBLIC schema. For a database link, if a CREATE USER script is in the same folder as the link script, DMS Schema Conversion assigns the link to that schema. Otherwise, DMS Schema Conversion assigns the link to the PUBLIC schema. Place each object's script in the folder for its owner. For more information about the CREATE USER requirement and the folder structure, see Prerequisites for an offline source.

Some tools don't export every category. For example, SQL Developer doesn't export CREATE USER statements, and the SQLcl project export command doesn't export database links. When a category is missing, add the scripts yourself.

Example: Add a missing schema definition

SQL Developer doesn't export a CREATE USER script, so the schema node is missing from the object tree. Add a file to the schema's folder that contains the following statement, replacing HR with your schema name:

CREATE USER HR NO AUTHENTICATION;
Example: Add a missing database link

The SQLcl project export command doesn't export database links, so you add them yourself. DMS Schema Conversion assigns a database link to the schema whose CREATE USER script is in the same folder as the link script. Put the link script in the owner's folder. For example, place a link owned by HR in the same folder as the HR CREATE USER script.

If the folder has no CREATE USER script, DMS Schema Conversion assigns the link to the PUBLIC schema.