Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: Removing Objects
Oracle Tips by Burleson

You can right click and select Drop Object to drop an object from the database.  If the object has dependencies, you will not be able to drop the object and will get an error message. For example, if you try to drop the TestSchema schema while it still contains tables, you will get an error such as is in Figure 5.33.

Figure 5.33: Drop Command Error

If you drop the tables in the schema first, the Drop Schema command will work.  You can also select Drop Cascade, which will also drop the dependant objects.  Be careful with this command because you are not able to see what will be dropped and you may drop objects that you did not plan to drop.

Migrating Oracle Objects

Developer Studio migration is different from replication.  You would use Developer Studio to initially capture database objects for long term migration and for development uses.  Replication would be the solution of choice for long-term data synchronization.  I will speak about replication in a later chapter.

There are three types of migration (Oracle objects to EnterpriseDB Advanced Server) that can be accomplished with Developer Studio:  Online single object, online schema and scheduled refresh.

Migrate a Single Object

Migrating a single object is simple.  Choose the object (I will be using the Oracle HR.locations table for my example) that you want to migrate.  It can be a table, view, procedure or other object type supported by EnterpriseDB Advanced Server.  Right-click on the object and choose Online Migration (Figure 5.34).

Figure 5.34: Single Object Online Migration

Selecting Online Migration raises the Migration Dialog (Figure 5.35).  Choose the parameters you would like.  If the object already exists and you want to refresh the data only, choose Data Only.  Uncheck those boxes if you don't care about the existing indexes, triggers, and constraints.  You may allow the new schema name to default to the same schema name as is in Oracle or you can give it a new one.  In this instance, I chose to give it a new name.  When ready, press Run.

Figure 5.35: Single Object Online Migration Details

The details of the migration are listed on the migration dialog.  Any errors received will appear on this screen. 

You can check the navigator (Figure 5.36) to verify that the new schema was created and that the new table exists.

Figure 5.36: Replicated Table

Migrate a Schema

Migrating a schema is much like migrating a single object.  Right click on the schema name and choose Online Migration.  The schema-level online migration dialog (Figure 5.37) contains fewer options than the single object dialog.  You do not have a choice of data only or of ignoring table objects such as indexes and constraints.

Figure 5.37: Schema Level Online Migration

Check the output for errors or warnings.  If you are creating a new schema, you should only have to worry about space issues.  If you are reusing a schema, you may have object name collisions.

You can verify that the schema was created by viewing it in the navigator (Figure 5.38).

Figure 5.38: Verify Schema Creation

Schedule a Refresh

One way to replicate data from Oracle to EnterpriseDB Advanced Server is to recreate the object on a regular schedule.  You would need to drop the existing objects in EnterpriseDB and then run a refresh.  Rather than having to run the refresh manually, you can schedule a repeating refresh (Figure 5.39).

Figure 5.39: Scheduled Refresh

You can choose to schedule the refresh daily at a specific time, or multiple times daily at a repeating interval of time.  This type of refresh is very useful in development and testing but I would use a more robust, enterprise type replication solution for a long-term production solution.

SQL & SPL Development

Code development in Developer Studio is accomplished either through the SQL Interactive window or through the SQL Terminal. 


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter