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