Example 1: Creating a single-file database
This command file example creates a single-file database that
contains one table, EMPLOYEES, made up of domains defined within
the CREATE DATABASE statement. The EMPLOYEES table has the same
definition as that in the sample personnel database.
For an example that creates a multifile version of the personnel
database, see the CREATE STORAGE_AREA.
SQL> -- By omitting a FILENAME clause, the database root file
SQL> -- takes the file name from the alias:
SQL> CREATE DATABASE ALIAS personnel
cont> --
cont> -- This CREATE DATABASE statement takes default
cont> -- database root file and storage area parameter values.
cont> --
cont> -- Create domains.
cont> -- Note that database elements do not terminate with semicolons.
cont> --
cont> CREATE DOMAIN ID_DOM CHAR(5)
cont> --
cont> CREATE DOMAIN LAST_NAME_DOM CHAR(14)
cont> --
cont> CREATE DOMAIN FIRST_NAME_DOM CHAR(10)
cont> --
cont> CREATE DOMAIN MIDDLE_INITIAL_DOM CHAR(1)
cont> --
cont> CREATE DOMAIN ADDRESS_DATA_1_DOM CHAR(25)
cont> --
cont> CREATE DOMAIN ADDRESS_DATA_2_DOM CHAR(20)
cont> --
cont> CREATE DOMAIN CITY_DOM CHAR(20)
cont> --
cont> CREATE DOMAIN STATE_DOM CHAR(2)
cont> --
cont> CREATE DOMAIN POSTAL_CODE_DOM CHAR(5)
cont> --
cont> CREATE DOMAIN SEX_DOM CHAR(1)
cont> --
cont> CREATE DOMAIN DATE_DOM DATE
cont> --
cont> CREATE DOMAIN STATUS_CODE_DOM CHAR(1)
cont> --
cont> -- Create a table:
cont> --
cont> CREATE TABLE EMPLOYEES
cont> (
cont> EMPLOYEE_ID ID_DOM
cont> CONSTRAINT EMP_EMPLOYEE_ID_NOT_NULL
cont> NOT NULL
cont> NOT DEFERRABLE,
cont> LAST_NAME LAST_NAME_DOM,
cont> FIRST_NAME FIRST_NAME_DOM,
cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM,
cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM,
cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM,
cont> CITY CITY_DOM,
cont> STATE STATE_DOM,
cont> POSTAL_CODE POSTAL_CODE_DOM,
cont> SEX SEX_DOM,
cont> CONSTRAINT EMP_SEX_VALUES
cont> CHECK (
cont> SEX IN ('M', 'F') OR SEX IS NULL
cont> )
cont> NOT DEFERRABLE,
cont> BIRTHDAY DATE_DOM,
cont> STATUS_CODE STATUS_CODE_DOM,
cont> CONSTRAINT EMP_STATUS_CODE_VALUES
cont> CHECK (
cont> STATUS_CODE IN ('0', '1', '2')
cont> OR STATUS_CODE IS NULL
cont> )
cont> NOT DEFERRABLE
cont> )
cont> --
cont> -- End CREATE DATABASE by specifying a semicolon:
cont> ;
Example 2: Creating a database not using the repository
The following example:
o Creates the database root file acct.rdb in the default working
directory
o Creates the snapshot file acct.snp in the default working
directory
o Does not store the database definition in the repository
o Enables writing to the snapshot file
o Sets the allocation of the snapshot file to 200 pages
SQL> CREATE DATABASE ALIAS acct
cont> FILENAME acct
cont> SNAPSHOT IS ENABLED IMMEDIATE
cont> SNAPSHOT ALLOCATION IS 200 PAGES;
Example 3: Creating a database with the snapshot file disabled
This statement creates a database root file and, to save disk
space, disables snapshot writing and sets the initial allocation
size to 1.
SQL> CREATE DATABASE ALIAS PERS
cont> FILENAME personnel
cont> SNAPSHOT IS DISABLED
cont> SNAPSHOT ALLOCATION IS 1 PAGES;
Example 4: Creating a database with ANSI/ISO-style privileges
This statement creates a database in which all ANSI/ISO-style
privileges are granted to the creator of the database, WARRING,
and no privileges are granted to the identifier [*,*], the PUBLIC
identifier.
SQL> CREATE DATABASE ALIAS EXAMPLE
cont> FILENAME ansi_test
cont> PROTECTION IS ANSI;
SQL>
SQL> SHOW PROTECTION ON DATABASE EXAMPLE;
Protection on Alias EXAMPLE
[SQL,WARRING]:
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
Example 5: Creating a database with a German collating sequence
This statement creates a database named LITERATURE and specifies
a collating sequence named GERMAN (based on the GERMAN collating
sequence defined in the NCS library).
SQL> CREATE DATABASE FILENAME literature
cont> COLLATING SEQUENCE GERMAN GERMAN;
SQL> SHOW COLLATING SEQUENCE
User collating sequences in schema with filename LITERATURE
GERMAN
Example 6: Creating a database with global buffers
This statement creates a database named parts.rdb.
SQL> CREATE DATABASE ALIAS PARTS FILENAME parts
cont> GLOBAL BUFFERS ARE ENABLED (NUMBER IS 110, USER LIMIT IS 17);
Example 7: Creating a database specifying the database default
and national character sets
The following SQL statements create a database specifying the
database default character set of DEC_KANJI and the national
character set of KANJI. Use the SHOW DATABASE statement to see
the database settings.
SQL> SET DIALECT 'SQL99';
SQL> CREATE DATABASE FILENAME mia_char_set
cont> DEFAULT CHARACTER SET DEC_KANJI
cont> NATIONAL CHARACTER SET KANJI
cont> IDENTIFIER CHARACTER SET DEC_KANJI;
SQL> --
SQL> SHOW CHARACTER SET;
Default character set is DEC_MCS
National character set is DEC_MCS
Identifier character set is DEC_MCS
Literal character set is DEC_MCS
Display character set is UNSPECIFIED
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
See the SHOW for information on the SHOW CHARACTER SETS
statement.
Example 8: This example demonstrates how to:
o Create a multifile database
o Reserve slots for journal files, storage areas, and row caches
o Restrict access to the database for the current session
o Enable system index compression, row caching, and workload
collection
o Disable statistics and cardinality collection
o Specify a default storage area
o Specify ROW as the lock-level default for the database
o Delay closing the database
o Create and assign a row cache to a storage area
o Specify the location of the recovery-unit journal file
SQL> CREATE DATABASE FILENAME sample
cont> SNAPSHOT IS DISABLED
cont> RESERVE 10 JOURNALS
cont> RESERVE 10 STORAGE AREAS
cont> RESERVE 5 CACHE SLOTS
cont> SYSTEM INDEX COMPRESSION IS ENABLED
cont> ROW CACHE IS ENABLED
cont> WORKLOAD COLLECTION IS ENABLED
cont> RESTRICTED ACCESS
cont> STATISTICS COLLECTION IS DISABLED
cont> CARDINALITY COLLECTION IS DISABLED
cont> LOCKING IS ROW LEVEL
cont> DEFAULT STORAGE AREA IS area1
cont> OPEN IS AUTOMATIC (WAIT 5 MINUTES FOR CLOSE)
cont> RECOVERY JOURNAL (LOCATION IS 'SQL_USER1:[DAY]')
cont> CREATE CACHE cache1
cont> CACHE SIZE IS 1000 ROWS
cont> ROW LENGTH IS 1000 BYTES
cont> CREATE STORAGE AREA area1
cont> CACHE USING cache1;
SQL>
SQL> SHOW DATABASE *;
Default alias:
Oracle Rdb database in file sample
Multischema mode is disabled
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Disabled
Carry over locks are enabled
Lock timeout interval is 0 seconds
Adjustable lock granularity is enabled (count is 3)
Global buffers are disabled (number is 250, user limit is 5,
page transfer via disk)
Journal fast commit is disabled
( checkpoint interval is 0 blocks,
checkpoint timed every 0 seconds,
no commit to journal optimization,
transaction interval is 256 )
AIJ File Allocation: 512
AIJ File Extent: 512
Statistics Collection is DISABLED
Unused Storage Areas: 10
Unused Journals: 10
System Index Compression is ENABLED
Restricted Access
Journal is Disabled
Backup Server: Manual
Log Server: Manual
Overwrite: Disabled
Notification: Disabled
Asynchronous Prefetch is Enabled (depth is 5)
Asynchronous Batch Write is Enabled (clean buffers 5, max buffers 4)
Lock Partitioning is DISABLED
Incremental Backup Scan Optim uses SPAM pages
Shutdown Time is 60 minutes
Unused Cache Slots: 5
Workload Collection is Enabled
Cardinality Collection is Disabled
Metadata Changes are Enabled
Row Cache is Enabled (Sweep interval is 1 second,
No Location)
Detected Asynch Prefetch is Enabled (depth is 4, threshold is 4)
Default Storage Area AREA1
Mode is Open Automatic (Wait 5 minutes for close)
RUJ File Location SQL_USER1:[DAY]
Database Transaction Mode(s) Enabled:
ALL
Dictionary Not Required
ACL based protections
Storage Areas in database with filename sample
RDB$SYSTEM List storage area.
AREA1 Default storage area.
Journals in database with filename sample
No Journals Found
Cache Objects in database with filename sample
CACHE1
SQL> SHOW CACHE cache1;
CACHE1
Cache Size: 1000 rows
Row Length: 1000 bytes
Row Replacement: Enabled
Shared Memory: Process
Large Memory: Disabled
Window Count: 100
Reserved Rows: 20
Sweep Rows: 3000
Reserving Slots for Sequences
No Sweep Thresholds
Allocation: 100 blocks
Extent: 100 blocks
Example 9: Reserving Slots for Sequences
SQL> CREATE DATABASE FILENAME many_sequences
cont> RESERVE 320 SEQUENCES;
Example 10: Creating a Database with a Row Cache
SQL> create database
cont> filename SAMPLE
cont> snapshot is disabled
cont> reserve 10 journals
cont> reserve 10 storage areas
cont> reserve 5 cache slots
cont> system index (compression is enabled, type sorted ranked)
cont> row cache is enabled
cont> workload collection is enabled
cont> restricted access
cont> default storage area is AREA1
cont> open is automatic (wait 5 minutes for close)
cont>
cont> create cache CACHE_AREA1
cont> shared memory is process
cont> row length is 1000 bytes
cont> cache size is 204 rows
cont> checkpoint all rows to backing file
cont>
cont> create storage area AREA1
cont> page format is UNIFORM
cont> cache using CACHE_AREA1
cont> ;
SQL>
SQL> show database *
Default alias:
Oracle Rdb database in file SAMPLE
Multischema mode is disabled
Number of users: 50
Number of nodes: 16
Buffer Size (blocks/buffer): 6
Number of Buffers: 20
Number of Recovery Buffers: 20
Snapshots are Disabled
Carry over locks are enabled
Lock timeout interval is 0 seconds
Adjustable lock granularity is enabled (count is 3)
Global buffers are disabled (number is 250, user limit is 5,
page transfer via disk)
Journal fast commit is disabled
( checkpoint interval is 0 blocks,
checkpoint timed every 0 seconds,
no commit to journal optimization,
transaction interval is 256 )
AIJ File Allocation: 512
AIJ File Extent: 512
Statistics Collection is ENABLED
Unused Storage Areas: 10
Unused Journals: 10
Unused Cache Slots: 5
Unused Sequences: 32
Restricted Access
Journal is Disabled
Backup Server: Manual
Log Server: Manual
Overwrite: Disabled
Notification: Disabled
Asynchronous Prefetch is Enabled (depth is 5)
Asynchronous Batch Write is Enabled (clean buffers 5, max buffers 4)
Lock Partitioning is DISABLED
Incremental Backup Scan Optim uses SPAM pages
Shutdown Time is 60 minutes
Workload Collection is Enabled
Cardinality Collection is Enabled
Metadata Changes are Enabled
Row Cache is Enabled
Row cache: No Location
Row cache: checkpoint updated rows to backing file
Detected Asynch Prefetch is Enabled (depth is 4, threshold is 4)
Default Storage Area AREA1
Mode is Open Automatic (Wait 5 minutes for close)
No RUJ File Location
recovery journal buffers are in local memory
Database Transaction Mode(s) Enabled:
ALL
Shared Memory: Process
Large Memory: Disabled
Security Checking is External
System Index Compression is ENABLED
System Index:
Type is sorted ranked
Prefix cardinality collection is enabled
Logminer support is disabled
Galaxy support is disabled
Prestarted transactions are enabled
Dictionary Not Required
ACL based protections
Storage Areas in database with filename SAMPLE
AREA1 Default storage area
RDB$SYSTEM List storage area.
Journals in database with filename SAMPLE
No Journals Found
Cache Objects in database with filename SAMPLE
CACHE_AREA1