Creates an Oracle Rdb database from an interchange .rbr file.
You use the IMPORT statement with the EXPORT statement to make
changes to Oracle Rdb databases that cannot be made any other
way. The EXPORT statement unloads a database to an .rbr file.
The IMPORT statement re-creates the database with changes that
may not be allowed by an ALTER DATABASE statement. The IMPORT
statement lets you:
o Convert from a single-file to a multifile database, and vice
versa.
o Change database root file parameters that you cannot change
with the ALTER DATABASE statement:
- COLLATING SEQUENCE
- SEGMENTED STRING STORAGE AREA
- PROTECTION IS ANSI/ACLS
- DEFAULT STORAGE AREA
o Change storage area parameters that you cannot change with the
ALTER DATABASE statement:
- PAGE SIZE
- PAGE FORMAT
- THRESHOLDS
- INTERVAL
- FILENAME, SNAPSHOT FILENAME
o Reload tables with existing rows to take advantage of newly
created hashed indexes.
o Reload tables to take advantage of new or changed storage
maps.
o Move a database to another directory or disk structure.
However, if moving a database is the only change you need
to make, it is more efficient to use the RMU Backup and RMU
Restore commands.
o Create an empty target database that uses the same data
definitions as a source database by copying the metadata,
but not the data, to the target.
If you use the NO DATA option, the IMPORT statement creates an
Oracle Rdb database whose metadata is identical to that found
in the source database used by the EXPORT statement, but the
duplicate database contains no data. The NO DATA option is not
compatible with the repository databases. See the description
in the Arguments section under the NO DATA option.
1 – Environment
You can use the IMPORT statement in interactive SQL only.
2 – Format
IMPORT DATABASE --> FROM <file-spec> -+
+--------------------------------------+
+-+-> FILENAME <file-spec> --+----------------------+-+
| +-> literal-user-auth -+ |
+-> WITH ALIAS <alias> -----------------------------+
+----------------------------<------------------------+
++-+--------------------------------------+-+----------->
| +-> import-options --------------------+ |
| +-> character-sets --------------------+ |
| +-> import-root-file-params-1 ---------+ |
| +-> import-root-file-params-2 ---------+ |
| +-> import-root-file-params-3 ---------+ |
| +-> import-root-file-params-4 ---------+ |
| +-> storage-area-params-1 -------------+ |
| +-> storage-area-params-2 -------------+ |
| +-> create-clause/statement -----------+ |
| +-> drop-statement --------------------+ |
+-----------------<------------------------+
literal-user-auth =
---> USER '<username>' -+------------------------+-->
+-> USING '<password>' -+
import-options=
-+-+-------+-+-> ACL ----------------+-+--->
| +-> NO -+ +-> BANNER -------------+ |
| +-> BATCH UPDATE -------+ |
| +-> CDD LINKS ----------+ |
| +-> DATA ---------------+ |
| +-> FORWARD_REFERENCES -+ |
| +-> TRACE --------------+ |
+-> COMMIT EVERY TABLE --------------+
+-> COMMIT EVERY n ROWS --------------+
character-sets =
-+-+--> DEFAULT CHARACTER SET <support-char-set> --+-+->
| +--> NATIONAL CHARACTER SET <support-char-set> -+ |
| +--> IDENTIFIER CHARACTER SET <names-char-set> -+ |
| +--> DISPLAY CHARACTER SET <support-char-set> ----+
+---------------------- <---------------------------+
import-root-file-params-1 =
-+-> PATHNAME <path-name> -----------------------------+->
+-> attach-options -----------------------------------+
+-> COLLATING SEQUENCE <sequence-name> ------+ |
| +-----------------------------------------+ |
| ++---------------+----+----------------+--+ |
| +-> COMMENT IS -+ +-> '<string> ' -+ | |
| +-----------------------------------------+ |
| +--> <ncs-name> -+-----------------------+---------+
| +-> FROM <library-name> + |
+-> NUMBER OF USERS --> <number-users> ---------------+
+-> NUMBER OF BUFFERS -----> <number-buffers> --------+
+-> NUMBER OF CLUSTER NODES ----> <number-nodes> --+ |
| +---------------------------------------------+ |
| +-+-----------------------------------------+----+
| +-> ( -+-> SINGLE ---+-> INSTANCE --> ) --+ |
| +-> MULTIPLE -+ |
+-> NUMBER OF RECOVERY BUFFERS -> <number-buffers> |
+-> BUFFER SIZE IS -> <buffer-blocks> -> BLOCKS ------+
+-> global-buffer-params -----------------------------+
attach-options =
-+-> DBKEY -+-> SCOPE IS -+-> ATTACH -------+-----------------------+->
+-> ROWID -+ +-> TRANSACTION --+ |
+-> MULTISCHEMA IS -+-> ON --+-------------------------------------+
| +-> OFF -+ |
+-> PRESTARTED TRANSACTIONS ARE -+-> ON --+------------------------+
| +-> OFF -+ |
+-+-------+-> RESTRICTED ACCESS -----------------------------------+
+-> NO -+
global-buffer-params=
--> GLOBAL BUFFERS ARE -+-> ENABLED --+--------------+
+-> DISABLED -+ |
+----------------------------------------------------+
+---------------------------------------------------+--->
+> ( -++-> NUMBER IS <number-glo-buffers> ----++> ) +
|+-> USER LIMIT IS <max-glo-buffers> ---+|
|+-> PAGE TRANSFER VIA --+-> DISK ---+--+|
|| +-> MEMORY -+ |
|+-> LARGE MEMORY IS --+--> ENABLED --+--+
| +--> DISABLED -+ |
+------------------- , <----------------+
import-root-file-params-2 =
-+-> SNAPSHOT IS -+> ENABLED -+-> IMMEDIATE --+-+------------------+->
| | +-> DEFERRED ---+ | |
| +> DISABLED ------------------+ |
+-> DICTIONARY IS ---+---> REQUIRED -------+----------------------+
| +---> NOT REQUIRED ---+ |
+-> ADJUSTABLE LOCK GRANULARITY IS -+-> ENABLED -> alg-options -+-+
| +-> DISABLED ---------------+ |
+-> LOCK TIMEOUT INTERVAL IS <number-seconds> SECONDS ------------+
+-> SEGMENTED STRING -+-> STORAGE AREA IS <area-name> ------------+
+-> LIST -------------+ |
+-> DEFAULT ----------+ |
+-> PROTECTION IS ---+---> ANSI --+-------------------------------+
| +---> ACLS --+ |
+-> RESERVE <n> --+-> CACHE SLOTS -----+--------------------------+
+-> JOURNALS --------+
+-> STORAGE AREAS ---+
+-> SEQUENCES -------+
alg-options =
--+------------------------------+-->
+-> ( --> COUNT IS <n> --> ) --+
import-root-file-params-3 =
-+> CARDINALITY COLLECTION IS --------------+--+-> ENABLED --+--------+>
+> CARRY OVER LOCKS ARE -------------------+ +-> DISABLED -+ |
+> GALAXY SUPPORT IS ----------------------+ |
+> LOCK PARTITIONING IS -------------------+ |
+> LOGMINER SUPPORT IS --------------------+ |
+> METADATA CHANGES ARE -------------------+ |
+> STATISTICS COLLECTION IS ---------------+ |
+> WORKLOAD COLLECTION IS -----------------+ |
+> SYSTEM INDEX -+-> COMPRESSION IS -+-> ENABLED ---+-----+----------+
| | +-> DISABLED --+ | |
| +-> ( -+-> system-index-options -+-> ) --+ |
| +---------- , <-----------+ |
+> PRESTARTED TRANSACTIONS ARE +> ENABLED -> prestart-trans-options ++
| +> DISABLED -------------------------+|
+> SECURITY CHECKING IS ---> security-checking-options --------------+
+> SYNONYMS ARE ENABLED ---------------------------------------------+
system-index-options =
-+-> COMPRESSION IS --------------------------------++- ENABLED -++->
+-> PREFIX CARDINALITY COLLECTION IS --------------++> DISABLED +|
+-> PREFIX CARDINALITY COLLECTION IS ENABLED FULL ---------------+
+-> TYPE IS SORTED --+-----------+-------------------------------+
+-> RANKED -+
prestart-trans-options =
-+---------------------------------+->
+-> WAIT <n> SECONDS FOR TIMEOUT -+
+-> WAIT <n> MINUTES FOR TIMEOUT -+
+-> NO TIMEOUT -------------------+
security-checking-options =
-+-> EXTERNAL -+------------------------------------------------++->
| +-> ( -> PERSONA SUPPORT IS -+-> ENABLED --+> ) -+|
| +-> DISABLED -+ |
+-> INTERNAL -+-----------------------------------------------+-+
+-> ( -> ACCOUNT CHECK IS -+---> ENABLED --+> ) +
+---> DISABLED -+
import-root-file-params-4 =
-+-> ASYNC BATCH WRITES ARE --+-> ENABLED --> async-bat-wr-options -+-+>
| +-> DISABLED -------------------------+ |
++------------+-> ASYNC PREFETCH IS --+ |
|+> DETECTED -+ +-------------------+ |
| +-+-> ENABLED ---> async-prefetch-options +--------+
| +-> DISABLED ---------------------------+ |
+-> ROW CACHE IS -+-> ENABLED --+-+----------------------+-----------+
| +-> DISABLED -+ +-> row-cache-options -+ |
++------+-> INCREMENTAL BACKUP SCAN OPTIMIZATION --------------------+
|+> NO -+ |
+-> MULTITHREAD AREA ADDITIONS ----> multithread-options ------------+
+-> RECOVERY JOURNAL -> ( -> ruj-options -> ) -----------------------+
+-> SHARED MEMORY IS --+-> SYSTEM ---------------------+-------------+
+-> PROCESS --+-------------+---+
+-> RESIDENT -+
asynch-bat-wr-options =
-+----------------------------->-------------------------------+->
+ ( ++-> CLEAN BUFFER COUNT IS <buffer-count> BUFFERS --++> ) +
|+-> MAXIMUM BUFFER COUNT IS <buffer-count> BUFFERS +|
+------------------- , <-----------------------------+
async-prefetch-options =
--+-----------------------------------------------------------+>
+-> ( -+-+-> DEPTH IS <number-buffers> BUFFERS -----+--+> )-+
| +-> THRESHOLD IS <number-buffers> BUFFERS -+ |
+----------------- , <--------------------------+
row-cache-options =
-> ( -++-> CHECKPOINT -+-> ALL ROWS TO BACKING FILE -----------+-++-> ) -->
|| +-> TIMED EVERY <n> SECONDS ------------+ ||
|| +-> UPDATED ROWS TO -+-> BACKING FILE -++ ||
|| +-> DATABASE -----+ ||
|+-> LOCATION IS --> <directory-spec> ---------------------+|
|+-> NO --+--> LOCATION --------+--------------------------+|
|| +--> SWEEP INTERVAL --+ ||
|+-> NUMBER OF SWEEP ROWS IS <n> --------------------------+|
|+-> SWEEP INTERVAL IS <n> SECONDS ------------------------+|
+-------------------------- , <-----------------------------+
multithread-options =
---+-----------------------------------------+-->
+-> ( --+-> ALL AREAS -----------+--> ) --+
+-> LIMIT TO <n> AREAS --+
ruj-options =
-+-> LOCATION IS --> <directory-spec> -+-->
+-> NO LOCATION ----------------------+
+-> BUFFER MEMORY IS -+-> LOCAL ------+
+-> GLOBAL -----+
storage-area-params-1 =
--+-> ALLOCATION IS ---> <number-pages> --> PAGES -----+->
+-> CACHE USING <row-cache-name> --------------------+
+-> NO ROW CACHE ------------------------------------+
+-> extent-params -----------------------------------+
+-> INTERVAL IS --> <number-data-pages> -------------+
+-> LOCKING IS --+-> ROW --+--> LEVEL ---------------+
| +-> PAGE -+ |
+-> PAGE FORMAT IS +-> UNIFORM -+--------------------+
| +-> MIXED ---+ |
+-> PAGE SIZE IS ----> <page-blocks> --> BLOCKS -----+
extent-params =
--+-> EXTENT IS -+-> ENABLED -------------------++-->
| +-> DISABLED ------------------+|
| +-> <extent-pages> --> PAGES --+|
| +-> (extension-options) -------+|
+---------------------<------------------------+
extension-options =
---> MINIMUM OF <min-pages> PAGES, --+
+------------------------------------+
+--> MAXIMUM OF <max-pages> PAGES, --+
+------------------------------------+
+--> PERCENT GROWTH IS <growth> ------->
storage-area-params-2 =
--+-> CHECKSUM CALCULATION IS -----------+--+-> ENABLED --+-+->
+-> SNAPSHOT CHECKSUM CALCULATION IS --+ +-> DISABLED -+ |
+-> SNAPSHOT ALLOCATION IS --> <snp-pages> ----> PAGES ---+
+-> SNAPSHOT EXTENT IS -+-> <extent-pages> ----> PAGES --++
| +-> (extension-options) ---------+|
+-> SNAPSHOT FILENAME --> <file-spec> --------------------+
+-> THRESHOLDS ARE ( <val1> +-----------------------+> ) -+
| +> ,<val2> -+----------++ |
| +> ,<val3> + |
+------------------------------<--------------------------+
create-clause/statement =
-+-> create-cache-clause -----------+->
+-> create-index-statement --------+
+-> create-storage-area-clause ----+
+-> create-storage-map-statement --+
drop-statement =
---+-> DROP CACHE <row-cache-name> -----+->
+-> DROP INDEX <index-name> ---------+
+-> DROP STORAGE AREA <area-name> ---+
+-> DROP STORAGE MAP <map-name> -----+
3 – Arguments
3.1 – ACL
Syntax options:
ACL | NO ACL
Specifies that the IMPORT statement uses the access control lists
from the original database when it creates the new database. The
ACL option is the default. If you are using the IMPORT statement
to restructure a database, you typically want to use the ACL
option and preserve the access control lists.
The NO ACL option overrides the ACLs from the original database
and uses the database system default ACLs. Specify NO ACL if
you are using the IMPORT statement to rebuild a database on a
different system. The NO ACL option makes you the owner of the
new database and creates default access control lists.
3.2 – BANNER
Syntax options:
BANNER | NO BANNER
This clause requests that IMPORT display informational messages
during the import of the database header, such as product
identification, and values for some database parameters. The
default is NO BANNER which will mean most IMPORT statements
generate no output.
3.3 – BATCH_UPDATE
Syntax options:
BATCH UPDATE | NO BATCH UPDATE
Specifies whether the IMPORT statement stores user data and
indexes using batch-update transactions (BATCH UPDATE) or
read/write transactions for each table (NO BATCH UPDATE). The
NO BATCH UPDATE option is the default.
A batch-update transaction is faster but does not perform
recovery-unit journaling, which means you cannot recover the
database in the event of a failure during the IMPORT operation.
With the NO BATCH UPDATE option, you can recover the database.
For more information about batch-update transactions, see the
SET_TRANSACTION statement.
3.4 – CDD_LINKS
Syntax options:
CDD LINKS | NO CDD LINKS
Determines whether the IMPORT statement tries to reestablish
links between database definitions originally based on repository
definitions (domains and tables created with the FROM path name
clause) and their sources in the repository.
The default depends on whether or not the IMPORT statement
specifies the PATHNAME option. If the IMPORT statement does
specify PATHNAME, the default is CDD LINKS; if it does not
specify PATHNAME, the default is NO CDD LINKS.
The CDD LINKS option specifies that the IMPORT statement tries
to reestablish repository links even if you do not specify
the PATHNAME option. If you specify CDD LINKS and the database
repository definition on which a database definition was based
does not exist, the IMPORT statement generates a warning message.
The NO CDD LINKS option specifies that the IMPORT statement
does not establish data repository links even if you specify
the PATHNAME option. Specify NO CDD LINKS if you are using the
IMPORT statement to rebuild a database on a different system.
3.5 – COMMIT_EVERY
Syntax options:
COMMIT EVERY TABLE | COMMIT EVERY n ROWS
Specifies whether the IMPORT statement commits entire tables, or
commits a certain number of rows at regular intervals. If you use
the COMMIT EVERY n ROWS clause, you can supply a value from 1 to
2147483647 for n.
The default is COMMIT EVERY TABLE. If you use the COMMIT EVERY n
ROWS clause, the table will be left with a partial set of rows if
the IMPORT process fails.
NOTE
If the table being imported includes a storage map with the
PLACEMENT VIA INDEX clause, then the COMMIT EVERY clause
is ignored for that table. A message is displayed to inform
the database administrator of the tables that did not have
COMMIT EVERY applied. This condition is shown in Example 6.
3.6 – create-cache-clause
See the CREATE CACHE clause for a complete description.
3.7 – create-index-statement
See the CREATE INDEX statement for a complete description.
3.8 – create-storage-area-clause
See the CREATE STORAGE_AREA statement for a complete description.
3.9 – create-storage-map-statement
See the CREATE STORAGE_MAP statement for a complete description.
3.10 – DATA
Syntax options:
DATA | NO DATA
Specifies whether the database created by the IMPORT statement
includes the data and metadata contained in the source database,
or the metadata only. DATA is the default.
When you specify the NO DATA option, you import the metadata
that defines a database from an .rbr file and exclude the data.
Duplicating the metadata of a database while excluding the data
offers the following benefits:
o You can use established, tested metadata to create a database
to store new data. Standardized metadata can be created once
but used in multiple databases.
o You can use the duplicated metadata to test the database
structure. You can experiment with storage areas and storage
maps, and by entering sample data, you can test other aspects
of database structure.
o If a database needs testing by someone outside of your group,
you can submit the database metadata without exposing any
sensitive data. Also, if the database is very large, you need
not submit multiple reels of tape to the tester.
NOTE
The NO DATA option is not compatible with repository
databases (CDD$DATABASE.RDB). An .rbr file, created by an
EXPORT statement with the DATA option (the default) and
generated from a CDD$DATABASE.RDB file, cannot be used with
the NO DATA option for the IMPORT statement. SQL issues an
error message stating that the NO DATA option is not valid
for repository databases.
3.11 – DROP_CACHE
Syntax options:
DROP CACHE row-cache-name
Prevents the specified row area from being imported.
3.12 – DROP INDEX index-name
Prevents the specified index from being imported.
3.13 – DROP_STORAGE_AREA
Prevents the specified storage area from being imported.
3.14 – DROP STORAGE MAP map-name
Prevents the specified storage map from being imported.
3.15 – FILENAME file spec
Specifies the file associated with the database.
If you omit the FILENAME argument, the file specification takes
the following defaults:
o Device: the current device for the process
o Directory: the current directory for the process
o File name: the alias (if you omit the FILENAME argument, you
must specify the WITH ALIAS clause)
Use either a full file specification or a partial file
specification. You can use a logical name for all or part of a
file specification.
If you use a simple file name, SQL creates the database in the
current default directory. Because the IMPORT statement may
create more than one file with different file extensions, do
not specify a file extension with the file specification.
3.16 – FORWARD_REFERENCES
Syntax options:
FORWARD_REFERENCES | NOFORWARD_REFERENCES
The EXPORT interchange file contains declarations of all routines
that will be referenced by other definitions. The default is
to declare the interfaces to those routines prior to creating
domains, tables, views, triggers, functions, procedures and
modules that may need them. The default is FORWARD_REFERENCES.
Use NO FORWARD_REFERENCES to disable these declarations. However,
this may result in definition failures during the IMPORT.
If you include the FORWARD_REFERENCES option on the IMPORT
command line then informational messages will be generated for
each declared routine.
3.17 – FROM file spec
Names the interchange .rbr file that the IMPORT statement uses as
a source to create a new database.
3.18 – import-root-file-params
Parameters that control the characteristics of the database root
file associated with the database, or characteristics stored in
the database root file that apply to the entire database.
For more information on other "import-root-file-params-1",
"import-root-file-params-2", "import-root-file-params-3", and
"import-root-file-params-4", see the descriptions of "root-
file-params-1", "root-file-params-2", "root-file-params-3", and
"root-file-params-4" in the CREATE DATABASE.
3.19 – limit-to-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
information about the LIMIT TO clause.
3.20 – literal-user-auth
Specifies the user name and password for access to databases,
particularly remote databases.
This literal lets you explicitly provide user name and password
information in the IMPORT statement.
3.21 – order-by-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
information about the ORDER BY clause.
3.22 – PROTECTION_IS
Syntax options:
PROTECTION IS ANSI | PROTECTION IS ACLS
By default, the IMPORT statement retains the protection style
of the database that was exported. However, if you specify
PROTECTION IS ANSI or PROTECTION IS ACLS, then the IMPORT
statement creates a database with that protection type. If
the protection of the database created is different from the
protection of the database that was exported, then no protection
records are imported and you will receive default protections.
3.23 – select-clause
See Select_Expressions in the Oracle Rdb SQL Reference Manual for
information about the SELECT clause.
3.24 – storage-area-params
Specifies parameters that control the characteristics of database
storage area files. You can specify most storage area parameters
for either single-file or multifile databases, but the effect of
the clauses differs.
o For single-file databases, the storage area parameters
specify the characteristics for the single storage area in
the database.
o For multifile databases, the storage area parameters specify
a set of default values for any storage areas created by the
IMPORT statement that do not specify their own values for
the same parameters. The attributes of a storage area are
supplied by the interchange file unless redefined by the
IMPORT statement. The default values apply to the storage
area named in CREATE STORAGE AREA database elements.
For details about storage area parameters, see the CREATE
STORAGE_AREA clause.
NOTE
The CREATE STORAGE AREA clauses can override these
default values. The default values do not apply to any
storage areas created later with the ALTER DATABASE
statement.
3.25 – TRACE
Syntax options:
TRACE | NO TRACE
Specifies whether usage statistics are logged by the IMPORT
statement. The NO TRACE option is the default.
Some actions taken by the IMPORT statement can consume
significant amounts of I/O resources and CPU time. These actions
include the following operations:
o Loading data
o Defining indexes
o Defining constraints
When you specify the TRACE option with the IMPORT statement,
SQL writes a message when each operation begins, and writes a
summary of DIO (direct input/output operations), CPU, and PAGE
FAULT statistics when the operation completes. When the IMPORT
statement finishes execution, a summary of all DIO, CPU, and
PAGE FAULT statistics is displayed. The display also includes
information on access to the .rbr file, database creation, and
loading of data. For more information about these statistics, see
the Oracle Rdb7 Guide to Database Performance and Tuning.
3.26 – USER username
Syntax option:
USER 'username'
Defines a character string literal that specifies the operating
system user name that the database system uses for privilege
checking.
3.27 – USING password
Syntax option:
USING 'password'
Defines a character string literal that specifies the user's
password for the user name specified in the USER clause.
3.28 – WITH ALIAS alias
Specifies the alias for the implicit database attach executed
by the IMPORT statement. An alias is a name for a particular
attachment to a database.
You must specify an alias or a file name. If you omit the WITH
ALIAS clause, the default alias for the database created by
the IMPORT statement is RDB$DBHANDLE. If you omit the FILENAME
argument, the IMPORT statement also uses the alias as the file
name for the database root file and creates the root file in
the current default directory. If you omit WITH ALIAS, you must
specify the FILENAME argument.
4 – Examples
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>