Example 1: Converting to a multifile database
This example uses the EXPORT and IMPORT statements to convert the
online sample database, personnel, to a multifile database.
SQL> export database
cont> filename PERSONNEL
cont> into PERS;
SQL>
SQL> import database
cont> from PERS
cont> filename MF_PERSONNEL
cont> default storage area MFP0
cont> create storage area MFP0
cont> filename MFP0_DEFAULT
cont> page format is UNIFORM
cont> create storage area MFP1
cont> filename MFP1
cont> create storage area MFP2
cont> filename MFP2
cont> create storage map EMPLOYEES_MAP
cont> for EMPLOYEES
cont> store randomly across (MFP1, MFP2);
SQL>
SQL> show storage area;
Storage Areas in database with filename MF_PERSONNEL
MFP0 Default storage area
MFP1
MFP2
RDB$SYSTEM List storage area.
Note that the storage area RDB$SYSTEM was created implicitly
in this example. The database administrator could add a CREATE
STORAGE AREA RDB$SYSTEM clause to this IMPORT example so that the
name, location and space allocation for the RDB$SYSTEM area can
be controlled.
Example 2: Importing a database created with ANSI/ISO-style
privileges
This example imports a database originally created using ACLS
style protection to create a new database with ANSI style
protections.
SQL> import database
cont> from PERS
cont> alias NEW_PERS
cont> filename MF_PERSONNEL
cont> protection is ANSI
cont> ;
SQL> show protection on database NEW_PERS;
Protection on Alias NEW_PERS
[DEV,SMITH]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,OPERATOR,DBADM,SECURITY,DISTRIBTRAN
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
SQL>
SQL> show protection on table NEW_PERS.EMPLOYEES;
Protection on Table NEW_PERS.EMPLOYEES
[DEV,SMITH]:
With Grant Option: SELECT,INSERT,UPDATE,DELETE,SHOW,CREATE,ALTER,DROP,
DBCTRL,REFERENCES
Without Grant Option: NONE
[*,*]:
With Grant Option: NONE
Without Grant Option: NONE
Example 3: Importing a database and displaying statistics
This example imports a database and uses the TRACE option to
display DIO, CPU, and PAGE FAULT statistics.
SQL> IMPORT DATABASE FROM personnel.rbr
cont> FILENAME personnel_new.rdb
cont> TRACE
cont> CREATE INDEX LOCAL_INDEX ON jobs (job_code);
IMPORTing STORAGE AREA: RDB$SYSTEM
IMPORTing table COLLEGES
Completed COLLEGES. DIO = 103, CPU = 0:00:00.89, FAULTS = 169
Starting INDEX definition COLL_COLLEGE_CODE
Completed COLL_COLLEGE_CODE. DIO = 25, CPU = 0:00:00.24, FAULTS = 26
IMPORTing table DEGREES
Completed DEGREES. DIO = 96, CPU = 0:00:01.15, FAULTS = 9
Starting INDEX definition DEG_COLLEGE_CODE
Completed DEG_COLLEGE_CODE. DIO = 27, CPU = 0:00:00.36, FAULTS = 1
Starting INDEX definition DEG_EMP_ID
Completed DEG_EMP_ID. DIO = 39, CPU = 0:00:00.49, FAULTS = 2
IMPORTing table DEPARTMENTS
Completed DEPARTMENTS. DIO = 99, CPU = 0:00:00.70, FAULTS = 3
IMPORTing table EMPLOYEES
Completed EMPLOYEES. DIO = 182, CPU = 0:00:01.60, FAULTS = 21
.
.
.
Starting CONSTRAINT definition SH_EMPLOYEE_ID_IN_EMP
Completed SH_EMPLOYEE_ID_IN_EMP. DIO = 48, CPU = 0:00:00.56, FAULTS = 2
Starting CONSTRAINT definition WS_STATUS_CODE_DOM_NOT_NULL
Completed WS_STATUS_CODE_DOM_NOT_NULL. DIO = 36, CPU = 0:00:00.23, FAULTS = 0
Completed import. DIO = 3530, CPU = 0:00:32.97, FAULTS = 2031
SQL>
Example 4: Reserving Sequence Slots During an Import Operation
SQL> IMPORT DATABASE FROM MF_PERSONNEL.RBR
cont> FILENAME 'mf_personnel.rdb' BANNER
cont> RESERVE 64 SEQUENCES;
.
.
.
Unused Sequences were 32 now are 64
IMPORTing STORAGE AREA: RDB$SYSTEM
IMPORTing STORAGE AREA: DEPARTMENTS
IMPORTing STORAGE AREA: EMPIDS_LOW
Example 5: Specifying the BANNER option
SQL> import data from x file mf_personnel BANNER;
Exported by Oracle Rdb X7.1-201 Import/Export utility
A component of Oracle Rdb SQL X7.1-201
Previous name was mf_personnel
It was logically exported on 29-MAY-2003 12:32
Multischema mode is DISABLED
Database NUMBER OF USERS is 50
Database NUMBER OF CLUSTER NODES is 16
Database NUMBER OF DBR BUFFERS is 20
Database SNAPSHOT is ENABLED
Database SNAPSHOT is IMMEDIATE
Database JOURNAL ALLOCATION is 512
Database JOURNAL EXTENSION is 512
Database BUFFER SIZE is 6 blocks
Database NUMBER OF BUFFERS is 20
Adjustable Lock Granularity is Enabled Count is 3
Database global buffering is DISABLED
Database number of global buffers is 250
Number of global buffers per user is 5
Database global buffer page transfer is via DISK
Journal fast commit is DISABLED
Journal fast commit checkpoint interval is 0 blocks
Journal fast commit checkpoint time is 0 seconds
Commit to journal optimization is Disabled
Journal fast commit TRANSACTION INTERVAL is 256
LOCK TIMEOUT is 0 seconds
Statistics Collection is ENABLED
Unused Storage Areas are: 0
System Index Compression is DISABLED
Journal was Disabled
Unused Journals are: 1
Journal Backup Server was: Manual
Journal Log Server was: Manual
Journal Overwrite was: Disabled
Journal shutdown minutes was 60
Asynchronous Prefetch is ENABLED
Async prefetch depth buffers is 5
Asynchronous Batch Write is ENABLED
Async batch write clean buffers is 5
Async batch write max buffers is 4
Lock Partitioning is DISABLED
Incremental Backup Scan Optim uses SPAM pages
Unused Cache Slots are: 1
Workload Collection is DISABLED
Cardinality Collection is ENABLED
Metadata Changes are ENABLED
Row Cache is DISABLED
Detected Asynchronous Prefetch is ENABLED
Detected Asynchronous Prefetch Depth Buffers is 4
Detected Asynchronous Prefetch Threshold Buffers is 4
Open is Automatic, Wait period is 0 minutes
Shared Memory is PROCESS
Unused Sequences are: 32
The Transaction Mode(s) Enabled are:
ALL
IMPORTing STORAGE AREA: RDB$SYSTEM
IMPORTing STORAGE AREA: DEPARTMENTS
IMPORTing STORAGE AREA: EMPIDS_LOW
IMPORTing STORAGE AREA: EMPIDS_MID
IMPORTing STORAGE AREA: EMPIDS_OVER
IMPORTing STORAGE AREA: EMP_INFO
IMPORTing STORAGE AREA: JOBS
IMPORTing STORAGE AREA: MF_PERS_SEGSTR
IMPORTing STORAGE AREA: SALARY_HISTORY
IMPORTing table CANDIDATES
IMPORTing table COLLEGES
IMPORTing table DEGREES
IMPORTing table DEPARTMENTS
IMPORTing table EMPLOYEES
IMPORTing table JOBS
IMPORTing table JOB_HISTORY
IMPORTing table RESUMES
IMPORTing table SALARY_HISTORY
IMPORTing table WORK_STATUS
IMPORTing view CURRENT_SALARY
IMPORTing view CURRENT_JOB
IMPORTing view CURRENT_INFO
Example 6: Using the COMMIT EVERY option
SQL> import database
cont> from 'TEST$DB_SOURCE:MF_PERSONNEL'
cont> filename 'MF_PERSONNEL'
cont>
cont> commit every 10 rows
cont>
cont> create storage area DEPARTMENTS
cont> filename 'DEPARTMENTS'
cont> page format is mixed
cont> snapshot filename 'DEPARTMENTS'
cont> create storage area EMPIDS_LOW
cont> filename 'EMPIDS_LOW'
cont> page format is mixed
cont> snapshot filename 'EMPIDS_LOW'
cont> create storage area EMPIDS_MID
cont> filename 'EMPIDS_MID'
cont> page format is mixed
cont> snapshot filename 'EMPIDS_MID'
cont> create storage area EMPIDS_OVER
cont> filename 'EMPIDS_OVER'
cont> page format is mixed
cont> snapshot filename 'EMPIDS_OVER'
.
.
.
cont> ; ! end of import
Definition of STORAGE AREA RDB$SYSTEM overridden
Definition of STORAGE AREA MF_PERS_SEGSTR overridden
Definition of STORAGE AREA EMPIDS_LOW overridden
Definition of STORAGE AREA EMPIDS_MID overridden
Definition of STORAGE AREA EMPIDS_OVER overridden
Definition of STORAGE AREA DEPARTMENTS overridden
Definition of STORAGE AREA SALARY_HISTORY overridden
Definition of STORAGE AREA JOBS overridden
Definition of STORAGE AREA EMP_INFO overridden
COMMIT EVERY ignored for table EMPLOYEES due to PLACEMENT VIA INDEX processing
COMMIT EVERY ignored for table JOB_HISTORY due to PLACEMENT VIA INDEX processing
SQL>