Backups, scripts and DDL
Scripts
The step-by-step operations that you use when you are performing a query or designing a table are just not efficient enough when you have to deal with many queries or tables at the same time. If you know in advance that you need to perform, say, 30 SQL operations in a set sequence, a much better solution is to connect the sequence together as a single script.
Scripts have particular importance in database administration and you will need to be able to use them to complete the coursework for the module.
Creating a new schema
After a little practice with scripts, the later sections of this practical will take you through the steps required to set up a new database schema from scratch. This will provide you with a model for how to complete the equivalent part of the coursework. This exercise will be based on a scenario in which you have been asked to implement the schema below. Its purpose is to store details of the sightings of endangered species by a community of wildlife observers.
sighting (sighting_id, observer_id, species_id, sighting_date, sighting_notes, region_id)
observer (observer_id, observer_fname, observer_sname)
genus (genus, family)
species (species_id, genus, species, common_name )
country (country_id, country, continent)
region (region_id, region, country_id)
There are several steps to this exercise, and to get the most benefit you should complete them in order. Along the way you will become aware of some common problem situations and their solutions.
Setting up a DBA connection
The connection explorer pane in SQL Developer lets you browse the contents of a schema and gives to access to all of the operations that an ordinary user might want to perform. There is a second explorer pane for performing database administration (DBA) tasks. You can display it using the View menu as shown in the graphic below.
When the DBA explorer first appears, there are no connections visible. You need to specify which connection to use for DBA operations. Since the only connection you currently have is for HR, an ordinary user without DBA privileges, you first need to set up a new one for the SYSTEM user.
Using the same method as before, click on the green cross in the top connections explorer pane and create a new connection called SYSTEM_XE. If you have any difficulty, you can check back to last week's practical instructions.
Once the new connection is set up, click on the green cross in the DBA explorer pane and select the new connection in the dialog as shown below.
Oracle backups
Crow's feet