| Previous | Table of Contents | Next |
by Dan Hotka
The Oracle8 server comes with several tools that the Oracle Corporation has supplied with their products for years. Server Manager is the base administrative and monitoring tool for server-side tasks. Oracle has always supplied the Export and Import processes with the database as well as a Data Loader process. SQL*Plus is the main adhoc, character-mode interface to the Oracle environment.
Server Manager replaced SQL*DBA in the Oracle7.2 time frame. SQL*DBA had both a screen mode and a line mode and got its start in the Oracle6 days, taking on functionality from both SQL*Plus and IOR. SQL*Plus was used in Oracle4 and Oracle5 to do most administrative functions (as well as to provide a unique character-mode interface for SQL and reporting), adding space to the database, user and object administration, and so on. IOR was used in the Oracle4 and Oracle5 days to start and stop the Oracle environment. IOR stands for Initialize Oracle; it has 3 parameters: I for initialize, W for warm start, and S for shutdown. It was used to start and stop and for initial installs of the Oracle environment.
Export and Import have been a part of the Oracle product set since the Oracle4 days. They work together and provide functionality as their names imply. Export is used to put individual tables and user schemas or the entire database (all schemas and data) into an operating system file. This file is proprietary in nature and is only intended for processing by Import. Import reads this operating system file created by Export. These tools can be used to backup, recover, and move objects between Oracle instances or even between versions of Oracle.
SQL*Plus is the main adhoc, character-mode interface to the Oracle environment. SQL*Plus can also be used to create dynamic SQL*Plus scripts or even dynamic operating system-specific command language programs.
Besides Oracle’s tools, there are several tools from other software vendors that effectively interface with the Oracle RDBMS.
Server Manager is a server-side tool for Oracle database administration. It has both a screen mode of operation and a line mode. The screen mode has a menu task bar, a view window, and an interactive window where commands can be entered. The line mode is much like SQL*Plus in that it is just a command prompt, can run SQL and PL/SQL interactively or from system files, but does not have any of the report formatting features of SQL*Plus. Server Manager is used primarily to start and stop the Oracle instances and to initiate backup and recovery. Server Manager is also a real-time monitor in that it formats and displays various performance and usage information, primarily from V$ tables.
Server Manager is started from a character mode environment. The interactive line mode is started by entering svrmgrl at the UNIX prompt.
Tip:
Server Manager can process a file from the command prompt as well. svrmgrl @<filename> will start Server Manager and process the contents of <filename>.
Export and Import play two important roles in the Oracle environment: the unloading and loading of schemas and schema data and various backup and recovery functions.
Export can easily export the entire database, an entire user schema, or individual tables. The biggest issue with Export/Import is the data file created 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 easily handle most export and import situations within the Oracle environment.
Note:
Oracle documentation recommends manually disabling referential-integrity constraints and importing the parent table, as some conflicts may cause errors during the load on some self-referencing table constraints.
Export has a direct path unload option; that is, it can access data directly from the Oracle data files and not use SELECT statements and the associated buffering overhead of the Oracle kernel. 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 (where the parameters are submitted via the command line), and a command line mode with a passed parameter file.
Note:
With the direct path option set, Export cannot export rows that contain LOB, BFILE, REF, or object type columns, including VARRAY columns and nested tables.
Oracle’s Export has been improved with Oracle8 in that it has direct-path access to the data; that is, 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.
Note:
To utilize the direct path export option, you must specify it in a parameter file.
Listing 22.1 has the instructions to start Export, export the user Scott’s schema (with the rows and the grants), and compress multiple table extents into one initial extent upon import.
| Previous | Table of Contents | Next |