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
-
In SQL Developer, choose Tools, and then choose Database Export.
For Connection, choose your database connection.
Select Export DDL and clear Export Data.
Set the DDL options according to the following table.
For Save As, choose Separate Files.
For Encoding, choose UTF8.
For File, enter the path to a directory for the output files.
We recommend creating a directory with the same name as the schema.
Choose Next.
On the Types to Export page, select Toggle All Standard
and Toggle All Extension to include all object types.
Choose Next.
On the Specify Objects page, for Schema choose the schema to export.
For Type, choose ALL OBJECTS.
Choose Lookup to populate the object list.
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 |
| Partitioning | True |
| Show Schema | True |
| Terminator | True |
| Storage | True |
| Tablespace | True |
| Drops | False |
| Cascade Drops | False |
| Grants | False |
| Dependents | False |
| Pretty Print | True |
| Add BYTE keyword | True |
| Add Force to Views | False |
| Version | COMPATIBLE |
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:
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.