Move user-schema geodatabases to stand-alone geodatabases in Oracle

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

Note:

After the data is moved from user-schema geodatabases to the master 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 master sde geodatabase

If you don't need to keep data in separate databases and you need to preserve the existing master geodatabase, move the user-schema data into the master 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 master sde geodatabase.

Get 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.

  1. Reconcile and post all versioned edits to the Default version.
  2. Compress the geodatabase while all other geodatabase users are off the system.

    See Manage connections to a geodatabase in Oracle for information in viewing and removing connections.

  3. Delete named versions.

Create a backup of the database

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

Configure the master geodatabase to allow data owners to create data

Owners of user-schema geodatabases cannot own data in the master 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 master 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 master geodatabase when the data is moved will own the data in the master 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.

  1. Each user-schema geodatabase owner must connect to his or her user-schema geodatabase from ArcGIS Pro or ArcCatalog.
  2. 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 master geodatabase. To view privileges, right-click each feature class and table under the database connection, point to Manage and click Privileges.
  3. Grant Select privileges on the data in your user-schema geodatabase to the new user you will use to move the data into the master geodatabase.
  4. Connect to the master geodatabase (Default traditional version) from ArcGIS Pro or ArcCatalog as your new user. This is the user who will own the data in the master geodatabase.
  5. Use one of the following options to move the data:
    • Import the data to the master geodatabase. Right-click the master 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.
  6. Grant privileges on the data in the master 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.
  7. Confirm the data was successfully moved to the master 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.
  8. Once you confirm the data is correct in the master geodatabase, user-schema geodatabase owners must delete all data from their user-schema geodatabases.

Delete user-schema geodatabases

Once all data is moved to the master 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) transactional version.

Move all user-schema and master 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.

Get 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.

  1. Reconcile and post all versioned edits to the Default version.
  2. Compress the geodatabase while all other geodatabase users are off the system.

    See Manage connections to a geodatabase in Oracle for information in viewing and removing connections.

  3. Delete named 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.

  1. Create a geodatabase in the new Oracle instance.
  2. 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:

  1. Connect to the user-schema geodatabase and the new geodatabase from ArcGIS Pro or ArcCatalog.
  2. Take note of who has access to the data in the old geodatabase and what privileges they have, as you must regrant 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.
  3. 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) (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.
    • 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 (ArcGIS Pro) or Export > To Geodatabase (multiple) (ArcCatalog) 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.
  4. 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.
  5. Confirm the data was successfully moved to the new 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 as the normally would.
  6. 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 the data from the master geodatabase and you no longer need the old Oracle database, you can drop it. Once the database is gone, existing connections to the old Oracle database will also stop working, and you'll need to create new connections and republish web layers that had 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.

Get 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.

  1. Reconcile and post all versioned edits to the Default version.
  2. Compress the geodatabase while all other geodatabase users are off the system.

    See Manage connections to a geodatabase in Oracle for information in viewing and removing connections.

  3. Delete named 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.

  1. Create a geodatabase in each new Oracle instance.
  2. 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:

  1. Connect to the user-schema geodatabase and the new geodatabase from ArcGIS Pro or ArcCatalog.
  2. Take note of who has access to the data in the old geodatabase and what privileges they have, as you must regrant 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.
  3. 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) (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.
    • 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 (ArcGIS Pro) or Export > To Geodatabase (multiple) (ArcCatalog) 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.
  4. 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.
  5. Confirm the data was successfully moved to the new 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 as the normally would.
  6. 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 the data from the master geodatabase and you no longer need the old Oracle database, you can drop it. Once the database is gone, existing connections to the old Oracle database will also stop working, and you'll need to create new connections and republish web layers that had referenced the Oracle database.