Move user-schema geodatabases to stand-alone geodatabases in Oracle—ArcGIS Pro

Use one of the following options to move user-schema geodatabases in Oracle to geodatabases in individual databases:

Move the data from the user-schema geodatabases to the sde geodatabase.
Move content from all user-schema geodatabases to a single stand-alone geodatabase in a new database.
Move each user-schema geodatabase to its own stand-alone geodatabase.

Note:

After the data is moved from user-schema geodatabases to the existing sde geodatabase or a new geodatabase, all users who previously accessed the user-schema geodatabase must update their database connections in ArcGIS Pro projects and ArcMap documents. Publishers must register the new database connections with their ArcGIS Server sites and republish all web services that were connecting to the data in the user-schema geodatabases.

Move the data from the user-schema geodatabases into the sde geodatabase

If you don't need to keep data in separate databases and you need to preserve the existing sde geodatabase, move the user-schema data into the sde geodatabase. You might do this if the following is true:

You need all your data in the same geodatabase so you can join and relate tables.
You want your users to continue using the same Oracle instance.
The people who were previously accessing the data in the user-schema geodatabases cannot replace their database connection files but they can update their existing connections.
You can create new users to own the data in the sde geodatabase.

Move all edits into base tables and delete versions

If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.

Reconcile and post all versioned edits to the Default version.
See Reconcile and post edits to a traditional version or Reconcile and post edits to a branch version for instructions specific to the type of versioning you use.
If you have data that is registered for traditional versioning, compress the geodatabase while all other geodatabase users are off the system.
See Manage connections to a geodatabase in Oracle for information about viewing and removing connections.
Delete named traditional and branch versions.

Create a backup of the database

Before you start moving and deleting data, make a full backup of the Oracle database.

Configure the sde geodatabase to allow data owners to create data

Owners of user-schema geodatabases cannot own data in the sde geodatabase. Therefore, the database administrator must create users for the transfer of data.

The database administrator can use database tools to create users and grant permissions to create data in the sde geodatabase, or the database administrator can run the Create Database User geoprocessing tool, which will create the users and grant required privileges.

Move data and grant privileges

Whichever user is connected to the sde geodatabase when the data is moved will own the data in the sde geodatabase. As part of the following steps, the owner of the user-schema geodatabase must grant the privilege to select all the data in the user-schema geodatabase to the new database user created in the previous section.

Each user-schema geodatabase owner must connect to his or her user-schema geodatabase from ArcGIS Pro or ArcCatalog.
Take note of who has access to the data in the user-schema geodatabase and what privileges they have, as you must regrant privileges after you move the data to the sde geodatabase. To view privileges, right-click each feature class and table under the database connection, point to Manage and click Privileges.
Grant Select privileges on the data in your user-schema geodatabase to the new user you will use to move the data into the sde geodatabase.
Connect to the sde geodatabase (Default traditional version) from ArcGIS Pro or ArcCatalog as your new user. This is the user who will own the data in the sde geodatabase.
Use one of the following options to move the data:
- Import the data to the sde geodatabase. Right-click the sde geodatabase and click Import > Feature Class(es) (ArcGIS Pro) or Import > Feature Class (multiple) (ArcCatalog) to import all feature classes from the user-schema geodatabase. Click Import > Table(s) (ArcGIS Pro) or Import > Table (multiple) (ArcCatalog) to import all nonspatial tables.
- Copy the data from the user-schema geodatabase and paste it into the new geodatabase.
Grant privileges on the data in the sde geodatabase to the same users who had access to the data in the user-schema geodatabase. Be sure the privileges are the same as they were in the user-schema geodatabase.
Confirm the data was successfully moved to the sde geodatabase and privileges are correct. To ensure this, ask the users who were previously connecting to the user-schema geodatabase to update their database connections to access the new geodatabase and work with the data.
Once you confirm the data is correct in the sde geodatabase, user-schema geodatabase owners must delete all data from their user-schema geodatabases.
If data from the user-schema geodatabase was published in web services, stop those web services before deleting the data.

Delete user-schema geodatabases

Once all data is moved to the sde geodatabase and all data is deleted from the user-schema geodatabase, each user-schema geodatabase owner can run the Delete Schema Geodatabase geoprocessing tool. Once deleted, existing connections to the user-schema geodatabases will stop working. However, you can modify the connection files to point to the Default (sde) geodatabase version.

Move all user-schema and sde geodatabase data to a new geodatabase

If you want to start with a new geodatabase and you don't need to keep data in separate geodatabases, create a stand-alone geodatabase and move your data into it. You might do this if the following is true:

You need all your data in the same geodatabase so you can join and relate tables.
The people who were previously accessing the data in the user-schema geodatabases can replace their database connection files to connect to the new geodatabase.

Move all edits into base tables and delete versions

If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.

Reconcile and post all versioned edits to the Default version.
See Reconcile and post edits to a traditional version or Reconcile and post edits to a branch version for instructions specific to the type of versioning you use.
If you have data that is registered for traditional versioning, compress the geodatabase while all other geodatabase users are off the system.
See Manage connections to a geodatabase in Oracle for information about viewing and removing connections.
Delete named traditional and branch versions.

Create a backup of the database

Before you start moving and deleting data, make a full backup of the Oracle database.

Create a geodatabase

The database administrator needs to install and configure a new Oracle instance, create database users in the instance, and grant them the privileges they need. Either the database administrator or geodatabase administrator can create a geodatabase in the database.

Create a geodatabase in the new Oracle instance.
Create users and roles in the new database and grant privileges.
The accounts used to move the data into the new geodatabase must have data creator privileges. If the database administrator uses the Create Database User geoprocessing tool, the required privileges are automatically granted to the new user.

Move data and grant privileges

The user that logs in to the new geodatabase and moves the data from the user-schema geodatabase to the new geodatabase will own the data in the new geodatabase.

Each owner of a user-schema geodatabase must complete the following steps:

Connect to the user-schema geodatabase and the new geodatabase from ArcGIS Pro.
Take note of who has access to the data in the old geodatabase and the privileges they have, as you must grant privileges after you move the data to the new geodatabase. To view privileges, right-click each feature class and table under the database connection, point to Manage and click Privileges.
Use one of the following options to move the data:
- Import the data to the new geodatabase. Right-click the new geodatabase and click Import > Feature Class(es) to import all feature classes from the user-schema geodatabase. Click Import > Table(s) to import all nonspatial tables.
- Export the data from the old geodatabase. Right-click the old geodatabase and click Export > Feature Class(es) To Geodatabase and Export > Table(s) To Geodatabase to export all feature classes and tables from the user-schema geodatabase to the new geodatabase.
- Copy the data from the user-schema geodatabase and paste it into the new geodatabase.
Grant privileges on the data in the new geodatabase to the same users who had access to the data in the old geodatabase. Be sure the privileges are the same as they were in the user-schema geodatabase.
Confirm that the data was successfully moved to the new geodatabase and the privileges are correct. To ensure this, ask the users who were previously connecting to the user-schema geodatabase to update their database connections to access the new geodatabase and work with the data as they normally do.
Once you confirm the data is correct in the new geodatabase, delete all data from the user-schema geodatabase.

Delete geodatabases

Once all data is moved to the new geodatabase and all data is deleted from the user-schema geodatabase, each user-schema geodatabase owner can run the Delete Schema Geodatabase geoprocessing tool. Once deleted, existing connections to the user-schema geodatabases will stop working.

If you moved data from the sde geodatabase and you no longer need the old Oracle database, you can drop it. Once the database is deleted, existing connections to the old Oracle database stop working, and you need to create new connections and republish web layers that referenced the Oracle database.

Move each user-schema geodatabase into its own stand-alone geodatabase

If data needs to stay segregated in separate geodatabases, move data from each user-schema geodatabase into its own stand-alone geodatabase. Use this method if the following is true:

Your user-schema geodatabases were used to group different types of data, and you want to keep this behavior.
You don't need to join or relate tables that were previously in user-schema geodatabases.
The people who were previously accessing the data in the user-schema geodatabases can replace their database connection files to connect to the new geodatabases.

Move all edits into base tables and delete versions

If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.

Reconcile and post all versioned edits to the Default version.
See Reconcile and post edits to a traditional version or Reconcile and post edits to a branch version for instructions specific to the type of versioning you use.
If you have data that is registered for traditional versioning, compress the geodatabase while all other geodatabase users are off the system.
See Manage connections to a geodatabase in Oracle for information about viewing and removing connections.
Delete named traditional and branch versions.

Create geodatabases

The database administrator needs to install and configure new Oracle instances—one to store the data that was stored in each user-schema geodatabase. The database administrator also needs to create database users in each instance and grant them the privileges they need. Either the database administrator or geodatabase administrators can create a geodatabase in each of the new databases.

Create a geodatabase in each new Oracle instance.
Create users and roles in the new databases and grant privileges.
The accounts used to move the data into the new geodatabase must have data creator privileges. If the database administrator uses the Create Database User geoprocessing tool, the required privileges are automatically granted to the new user.

Move data and grant privileges

The user that logs in to the new geodatabase and moves the data from the user-schema geodatabase to the new geodatabase will own the data in the new geodatabase.

Each owner of a user-schema geodatabase must complete the following steps:

Connect to the user-schema geodatabase and the new geodatabase from ArcGIS Pro.
Take note of who has access to the data in the old geodatabase and the privileges they have, as you must grant privileges after you move the data to the new geodatabase. To view privileges, right-click each feature class and table under the database connection, point to Manage and click Privileges.
Use one of the following options to move the data:
- Import the data to the new geodatabase. Right-click the new geodatabase and click Import > Feature Class(es) to import all feature classes from the user-schema geodatabase. Click Import > Table(s) to import all nonspatial tables.
- Export the data from the old geodatabase. Right-click the old geodatabase and click Export > Feature Class(es) To Geodatabase and Export > Table(s) To Geodatabase to export all feature classes and tables from the user-schema geodatabase to the new geodatabase.
- Copy the data from the user-schema geodatabase and paste it into the new geodatabase.
Grant privileges on the data in the new geodatabase to the same users who had access to the data in the old geodatabase. Be sure the privileges are the same as they were in the user-schema geodatabase.
Confirm that the data was successfully moved to the new geodatabase and the privileges are correct. To ensure this, ask the users who were previously connecting to the user-schema geodatabase to update their database connections to access the new geodatabase and work with the data as they normally do.
Once you confirm the data is correct in the new geodatabase, delete all data from the user-schema geodatabase.

Delete geodatabases

Feedback on this topic?