0412-0416

Previous Table of Contents Next

Page 412

All of us who have had the pleasure of experiencing Personal Oracle7 will agree that the Windows front end on the Import and Export utilities is a nice addition to the old familiar utilities. I think that many Personal Oracle7 users are developing applications. I find myself exporting tables from development platforms at the job site and then importing them into my Personal Oracle database so I can develop whenever and whatever I like.

Using the Personal Oracle7 version of Import and Export is quite a bit different from the old standards. Personal Oracle7 has a nice GUI interface that is hard to beat. It still works internally the same way as the character versions and outputs files that you can transfer to other Oracle instances. The main difference in Personal Oracle7 is the Windows front end. The title bar for the Export utility in Personal Oracle7 has the caption Database Exporter. The first thing you notice is that parameters are represented by textboxes, checkboxes, or pull-down lists. The advanced options are Record Length (RECORDLENGTH), Buffer Size (BUFFER), Record Export in System Data (RECORD), Keep Components Consistent (CONSISTENT), Compress When Imported (COMPRESS), Log File (LOG) Increment Type (INCTYPE), and Statistics Type (STATISTICS). The other Export parameters follow similar translations.

The Database Importer closely resembles the Exporter's look and feel. I do not go into detail because its functionality is fairly obvious.

Import and Export Hints and Tips

You can use Import and Export for tasks other than backing up data. I often use Import and Export for several other constructive purposes, and this section outlines a few of the best hints and tips.

Create an Index File

Import can create what is known as an index file. This is a SQL script of not only the creation scripts for the indexes in the export file but also creation scripts for the other database objects in the export file. All the lines in the file except for the index creation scripts are commented out. The commented-out code contains creation scripts for the remaining objects in the export file. With some editing, you can create a script that can be used to create all the tables and indexes and so on. This is often necessary if you use the parameter COMPRESS=Y.

Adjusting Storage Parameters

Using the parameter COMPRESS=Y rewrites the original storage parameters so that the table or index resides on one contiguous extent. Compressing the extents is a good idea as long as you have the contiguous extents available. We suggest pre-creating the tables and indexes with your edited INDEXFILE. This ensures that you can create the objects. Of course, if any table or index fails to create, you can adjust the initial and next extents in the INDEXFILE and attempt to create

Page 413

the table again. This is also a good time to adjust the PCTFREE, PCTUSED, and TABLESPACE parameters (or any of the other table parameters) in the table and index creation statements. You should study and adjust the size requirement if necessary at this time, especially if the storage parameters are set to their defaults. Here are some good places to start if you have a mess of default storage parameters:

Parameter	Defaults to	Set to
PCTFREE	10%	5_20%
PCTUSED	40%	55_90%
PCTINCREASE	50%	0%
INITIAL	10240 or 2048	All data in one extent plus some growth, if possible
NEXT	10240 or 20480	25% up to the size of INITIAL

There are other storage parameters, but these five are typically the most important to me.

Reorganizing Data

Users own tables, indexes, and other objects. The tables and indexes that users create reside in tablespaces. If you were to export a user's tables, drop them, and then import them, they would end up in the user's default tablespace again. If you need to move tables from one tablespace to another, export the tables and change the user 's default tablespace and quotas to another tablespace that you want to contain the tables. Then, when you re-import the tables, they are created in the user's new default tablespace.

Reducing Database Fragmentation

When a database has many small blocks of contiguous free space, you might want to export with the FULL=Y parameter. Re-create the database and then do a full database import. Tablespaces also need similar help. You should plan carefully where and how many data files a tablespace will have. To recreate the tablespace, export all the objects, using the nifty script I provided, if you like. Drop the tablespace including its contents. Re-create the tablespace and then import the tables into the fresh new tablespace.

Importing Tables with LONG and LONG RAW Datatypes

Did you know it is possible to export a table with extremely large columns (LONG datatypes can be up to 2GB in length) only to discover that some of the rows cannot be imported? It hasn't happened to me (yet). The reason is that Export can break up the long data while exporting, but Import must read the data for each row into contiguous memory to import. If you do not have enough memory, the import fails that row.

Page 414

Summary

Import and Export not only provide data recovery, but they have database management uses as well. You will probably find yourself using Import and Export often to keep your database tuned well by reducing fragmentation and enabling yourself to change the storage parameters of tables and their locations or owners . Whatever task you choose for Import and Export, you will find them to be a stable pair of utilities that are easy to use.

Page 415

Part IV

In This PART

• Installation
• Managing the Database
• Managing Disk Space
• Managing Users
• Backup and Recovery
• Performance Tuning and Optimization
• Database Security

Database Administration

Page 416

Previous Table of Contents Next