| Previous | Table of Contents | Next |
There are a variety of maintenance tasks that affect objects. The prior section discussed object fragmentation. There are also permissions to be maintained so users have the correct access to the objects. Indexes may need to be added or rebuilt for performance reasons. A whole series of various check and referential constraints need to be added and maintained. Columns may need to be added as well as column attributes changed. The SQL command ALTER TABLE is useful until the requirement is to add a new column into the middle of an existing object. To do this with Server Manager or SQL*Plus, one will have to spool the data out of the table or rename the existing object, create a script of the existing object, alter the script with the new column, drop the existing object (if not renamed), create the new object, repopulate the table with the data, and make sure all referential integrity, permissions, indexes, and any other reference to the object are re-established. Chapter 22, “Oracle8 Tools,” will discuss some third-party tools that can assist the administrator with time-consuming tasks such as these.
If the desired task is to successfully create a series of tables, views, and grants, dropping all objects involved if any of the creates are not successful, the CREATE SCHEMA command might be useful. If any of the steps fail, the entire operation is rolled back.
Referential integrity is a relationship between a parent table and a child table through the use of unique primary keys and foreign keys. Oracle8 (and Oracle7) supports the Referential Integrity Constraint that enforces this relationship, thus elevating the developers from the time-consuming task of programming such a feature.
Note:
The child table must be in the same database as the parent table.
Tip:
The option ON DELETE CASCADE (part of the CREATE CONSTRAINT syntax) might be helpful when deleting rows from the parent table, automating the referential integrity delete process.
Oracle has always supplied Export and Import routines to perform the movement of data in and out of Oracle tables. Oracle also supplies SQL*Loader, a fully configurable data loader tool. SQL*Plus can be used to format data, but was not really designed as a data extract tool. There are third-party unload tools that fill the gap on the unloading of data from the Oracle environment. These tools are discussed further in Chapter 22.
Export can easily export the entire database, an entire user schema, or individual tables. The biggest difficulty with Export/Import is that it is proprietary to the Oracle environment. That is, Import cannot be used to populate any Oracle table except from a file created by Export. These routines handle most export/import situations within the Oracle environment.
Note:
Oracle documentation recommends manually disabling referential integrity constraints and then importing the parent/child tables (or self referencing tables) as conflicts may occur because of the order of the rows being loaded. See the Oracle8 Server Utilities, Chapter 2, “Oracle Database Architecture,” the section titled “Disabling Referential Constraints,” for more details.
Export has a direct path unload option that can access data directly from the Oracle data files and not use SELECT statements and the associated buffering overhead of the Oracle RDBMS. Export still can create only one file of output which, depending on operating systems, may hit a file size limit when exporting larger objects. Export and Import can handle all the new partitioning features, loading all partitions or single partitions, as desired. Import can also rename a schema owner. Both tools are accessed via command line. Both tools have a series of parameters that control the work to be done. Both tools have three modes of operations: an interactive mode, a command line mode with all options specified on the command line, and a command line mode with all options specified in a parameter file.
Oracle’s Export has been improved with Oracle8 in that it has a direct path access option to the data where it does not incur the overhead of the Oracle buffering in accessing the data to be exported. It goes directly to the underlying files and accesses the data directly.
The command line (see Listing 19.2) has the instructions to start Export, export the user Scott’s schema, and compress multiple table extents into one initial extent upon import.
Listing 19.2. Export command line example and output.
Unix->exp scott/tiger file=example.exp owner=scott grants=Y rows=Y compress=y Export: Release 8.0.3.0.0 - Production on Tue Feb 3 9:01:15 1998 (c) Copyright 1997 Oracle Corporation. All rights reserved. Connected to: Oracle8 Server Release 8.0.3.0.0 - Production PL/SQL Release 8.0.3.0.0 - Production Export done in US7ASCII character set . exporting object type definitions for user SCOTT About to export SCOTT’s objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT’s tables via Conventional Path ... . . exporting table BONUS 0 rows exported . . exporting table DEPT 4 rows exported . . exporting table EMP 14 rows exported . . exporting table SALGRADE 5 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting referential integrity constraints . exporting triggers . exporting posttables actions . exporting snapshots . exporting snapshot logs . exporting job queues . exporting refresh groups and children Export terminated successfully without warnings.
| Previous | Table of Contents | Next |