Listing 19.3 imports the file exported by Export. I told it to ignore any errors due to the object’s existence.

Listing 19.3. Import command line example and output.

Unix-> imp scott/tiger  file=example.exp ignore=Y





Import: Release 8.0.3.0.0 - Production on Tue Feb 3 9:31:21 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 file created by EXPORT:V08.00.03 via conventional path

. importing SCOTT’s objects

. . importing table                          BONUS          0 rows

imported

. . importing table                         “DEPT”          4 rows

imported

. . importing table                          “EMP”         14 rows

imported

. . importing table                      “SALGRADE”         5 rows

imported



Import terminated successfully without warnings. 

Oracle also comes with SQL*Loader, a table, or multiple table load facility. This tool is capable of reading a variety of formatted files from a variety of sources (disk files, tape, even named pipes), and with some logic, can populate multiple tables from one pass of the data file being loaded. SQL*Loader has a direct path load option, that is, a feature that allows SQL*Loader to ignore the Oracle kernel and directly load the data into Oracle blocks in the underlying files assigned to the tablespace.

---

Note:  
This is an unprotected write where there is no read consistency, undo ability, or archive logs of the event.

---

SQL*Loader can also save rejected records to a disk file.

Listing 19.4, shows the SQL*Loader command line syntax and the associated control file that will load a single table with data from file EXAMPLE.DAT, and it contains some constant values that will populate the table as well.

Listing 19.4. SQL*Loader command line and control file example.

Unix->sqlldr USERID=hotka/dan CONTROL=EXAMPLE.ctl, LOG=EXAMPLE.log,

 BAD=EXAMPLE.bad





Unix->cat EXAMPLE.ctl

    load data

    infile ‘EXAMPLE.DAT’

    append

    into table APPL_PROGRAM_TABLE

     (PROGRAM_NAME position(1:8) char,

    PROGRAM_SUFFIX constant ‘COB’,

    PROGRAM_SIZE position(15:22) integer external,

    PROGRAM_PATH constant ‘C:\COBOL’,

    ASSIGNED_ANALYST constant ‘HOTKA’) 

Sizing Defaults

Oracle manages its space inside the tablespace in units called extents. An extent can be one or more contiguous Oracle blocks. Extents are preallocated units of disk space assigned individually to data-oriented objects. The Oracle block size is defined at CREATE DATABASE time. Extent sizes are given in sizes of bytes and Oracle does the calculation converting the extent size to Oracle blocks, rounding up to the next multiple of block size.

The tablespace has a default storage clause. This default is used only when a storage clause is not specified on objects being created in that tablespace. The storage parameter can be altered; however, any change in storage parameters will have no effect on already existing object storage usage.

In Listing 19.5, I will create the famous EMP table. Its initial extent will be 25KB (rounded up to the next even Oracle block size), with each additional extent needed of 10KB each. MINEXTENTS is the original number of extents to be allocated, and MAXEXTENTS will be all the extents that will be allocated to this object. If this MAXEXTENTS is reached, the error ORA-01631 max # extents (100) in table xxxxx displays. Pctincrease is a value that will increase the next extent size by this factor over the size of the previously created extent. Data blocks are inserted until the percent of the block is within PCTFREE of being full. Inserts will not be allowed again in the data blocks until the percentage of free space reaches PCTUSED. Free space in blocks is accomplished through deletes or updates that make fields smaller.

Listing 19.5. Create table syntax example.

Create table EMP (

 EMPNO               NUMBER(4) NOT NULL,

 ENAME               VARCHAR2(10),

 JOB                 VARCHAR2(9),

 MGR                 NUMBER(4),

 HIREDATE            DATE,

 SAL                 NUMBER(7,2),

 COMM                NUMBER(7,2),

 DEPTNO              NUMBER(2) NOT NULL)

 storage (

                  initial 25K next 10K

                  pctfree 20 pctused 80

                  minextents 1 maxextents 100

                  pctincrease 0);

---

Note:  
Make sure to take into account NULL fields that will eventually contain data when arriving at a percentage for PCTFREE.

---