|
 Use
Oracle9i SPFILE to overcome traditional PFILE Limitations
The following material is from the Oracle9i Database Administrator's Guide, we tested the
examples on Oracle 9.0.1 for SUN Solaris and Windows 2000.
The ALTER SYSTEM statement allows you to set,
change, or delete (restore to default value) initialization parameter values. When the
ALTER SYSTEM statement is used to alter a parameter setting in a traditional
initialization parameter file, the change affects only the current instance, since
there is no mechanism for automatically updating initialization parameters on disk.
They must be manually updated in order to be passed to a future instance. Using a server
parameter file overcomes this limitation.
A server parameter file (SPFILE) can be
thought of as a repository for initialization parameters that is maintained on the
machine where the Oracle database server executes. It is, by design, a server-side
initialization parameter file. Initialization parameters stored in a server parameter
file are persistent, in that any changes made to the parameters while an instance is
running can persist across instance shutdown and startup. This eliminates the need to
manually update initialization parameters to make changes effected by ALTER SYSTEM
statements persistent. It also provides a basis for self tuning by the Oracle
database server.
A server parameter file is initially built from a
traditional text initialization parameter file using the CREATE SPFILE statement. It is a
binary file that cannot be browsed or edited using a text editor. Oracle provides other
interfaces for viewing and modifying parameter settings.
|
| Although you can open the binary server parameter
file with a text editor and view its text, do not manually edit it. Doing so will
corrupt the file. You will not be able to start you instance, and if the instance is
running, it could crash. |
|
At system startup, the default behavior of the
STARTUP command is to read a server parameter file to obtain initialization parameter
settings. The STARTUP command with no PFILE clause, reads the server parameter
file from an operating system specific location. If you choose to use the traditional
text initialization parameter file, you must specify the PFILE clause when issuing the
STARTUP command.
STARTUP PFILE =
/opt/oracle/prod/9.0.1/dbs/initDIA2.ora
The server parameter file must initially be created
from a traditional text initialization parameter file. It must be created prior to its
use in the STARTUP command. The CREATE SPFILE statement is used to create a server
parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this
statement.
The following example creates a server parameter
file from initialization parameter file /opt/oracle/prod/9.0.1/dbs/initDIA2.ora. In this
example no SPFILE name is specified, so the file is created in a platform-specific
default location and is named spfile$ORACLE_SID.ora.
sqlplus /nolog
SQL*Plus: Release 9.0.1.0.0 - Production on Tue Jan 1 11:20:51 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect sys/manager as sysdba;
Connected to an idle instance.
SQL> create spfile from pfile='/opt/oracle/product/9.0.1/dbs/initDIA2.ora';
File created.
The server parameter file is always created on the
machine running the database server. If a server parameter file of the same name already
exists on the server, it is overwritten with the new information.
Oracle recommends that you allow the database server
to default the name and location of the server parameter file. This will ease
administration of your database. For example, the STARTUP command assumes this default
location to read the parameter file.
When the server parameter file is created from the
initialization parameter file, comments specified on the same lines as a parameter
setting in the initialization parameter file are maintained in the server parameter file.
All other comments are ignored.
The CREATE SPFILE statement can be executed before
or after instance startup. However, if the instance has been started using a server
parameter file, an error is raised if you attempt to recreate the same server parameter
file that is currently being used by the instance.
The ALTER SYSTEM statement allows you to set,
change, or delete (restore to default value) initialization parameter values. When the
ALTER SYSTEM statement is used to alter a parameter setting in a traditional
initialization parameter file, the change affects only the current instance, since
there is no mechanism for automatically updating initialization parameters on disk.
They must be manually updated in order to be passed to a future instance. Using a
server parameter file overcomes this limitation.
Use the SET clause of the ALTER SYSTEM statement to
set or change initialization parameter values. Additionally, the SCOPE clause specifies
the scope of a change as described in the following table:
|
| SCOPE = SPFILE |
The change is applied in the server
parameter file only.
The effect is as follows: |
| |
|
| |
- For dynamic parameters, the change is effective at the next
startup and is persistent.
|
| |
|
| |
- For static parameters, the behavior is the same as for
dynamic parameters. This is the only SCOPE specification allowed for static
parameters
|
|
| SCOPE = MEMORY |
The change is applied in memory only.
The effect is as follows: |
| |
|
| |
- For dynamic parameters, the effect is immediate, but it is
not persistent because the server parameter file is not updated.
|
| |
|
| |
- For static parameters, this specification is not
allowed.
|
|
| SCOPE = BOTH |
The change is applied in both the
server parameter file and memory.
The effect is as follows: |
| |
|
| |
- For dynamic parameters, the effect is immediate and
persistent.
|
| |
|
| |
- For static parameters, this specification is not
allowed.
|
|
It is an error to specify SCOPE=SPFILE or SCOPE=BOTH
if the server is not using a server parameter file. The default is SCOPE=BOTH if a server
parameter file was used to start up the instance, and MEMORY if a traditional
initialization parameter file was used to start up the instance.
For dynamic parameters, you can also specify the
DEFERRED keyword. When specified, the change is effective only for future
sessions.
A COMMENT clause allows a comment string to be
associated with the parameter update. When you specify SCOPE as SPFILE or BOTH, the
comment is written to the server parameter file.
The following statement changes the maximum number
of job queue processes allowed for the instance. It also specifies a comment, and
explicitly states that the change is to be made only in memory (that is, it is not
persistent across instance shutdown and startup).
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
COMMENT='Temporary change by M.Zahn'
SCOPE=MEMORY;
System altered.
You can export a server parameter file to create a
traditional text initialization parameter file. Reasons for doing this
include:
-
Creating backups of the server parameter
file
-
For diagnostic purposes, listing all of the
parameter values currently used by an instance. This is analogous to the SQL*Plus
SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2
views.
-
Modifying of the server parameter file by first
exporting it, editing the output file, and then recreating it.
The exported file can also be used to start up an
instance using the PFILE option.
The CREATE PFILE statement is used to export a
server parameter file. You must have the SYSDBA or the SYSOPER system privilege to
execute this statement. The exported file is created on the database server machine. It
contains any comments associated with the parameter in the same line as the parameter
setting.
The following example creates a text initialization
parameter file from a server parameter file where the names of the files are
specified:
CREATE
PFILE='/export/home/oracle/config/9.0.1/initDIA2.ora'
FROM SPFILE='/export/home/oracle/config/9.0.1/spfileDIA2.ora';
You have several options for viewing parameter
settings.
This SQL*Plus command displays the currently
in
use parameter values.
This SQL statement creates a text initialization
parameter file from the binary server parameter file.
This view displays the currently in effect
parameter values.
This view displays the currently in effect
parameter values. It is easier to distinguish list parameter values in this view
because each list parameter value appears as a row.
This view displays the current contents of the
server parameter file. The view returns NULL values if a server parameter file is not
being used by the instance.
The Default
Temporary Tablespace in Oracle 9i
Users can be explicitly assigned a default
temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is
specified, they default to using the SYSTEM tablespace. It is not good practice to
store temporary data in the SYSTEM tablespace. To avoid this problem, and to avoid
the need to assign every user a default temporary tablespace at CREATE USER time, you
can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.
CREATE DATABASE ARK2
CONTROLFILE REUSE
MAXDATAFILES 256
MAXINSTANCES 4
MAXLOGFILES 62
MAXLOGMEMBERS 5
MAXLOGHISTORY 1600
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "AL16UTF16"
DATAFILE 'D:\Ora\ARK2_sys1.dbf' SIZE 200M REUSE
UNDO TABLESPACE undo DATAFILE 'D:\Ora\ARK2_undo1.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tmp
TEMPFILE 'D:\Ora\ARK2_tmp1.dbf' SIZE 512064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
LOGFILE GROUP 1 ('D:\Ora\ARK2_log1A.rdo',
'C:\Ora\ARK2_log1B.rdo') SIZE 5M REUSE,
GROUP 2 ('D:\Ora\ARK2_log2A.rdo',
'C:\Ora\ARK2_log2B.rdo') SIZE 5M REUSE,
GROUP 3 ('D:\Ora\ARK2_log3A.rdo',
'C:\Ora\ARK2_log3B.rdo') SIZE 5M REUSE,
GROUP 4 ('D:\Ora\ARK2_log4A.rdo',
'C:\Ora\ARK2_log4B.rdo') SIZE 5M REUSE;
If you decide later to change the default
temporary tablespace, or to create an initial one after database creation, you can do
so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE),
then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY
TABLESPACE statement. Users will automatically be switched (or assigned) to the new
temporary default tablespace.
sqlplus /nolog
connect sys/syspasswd as sysdba
CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'D:\Ora\ARK2_temp1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
- Users can obtain the name of the current default
temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column
contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains
the default temporary tablespace name.
SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
PROPERTY_VALUE
---------------------
TMP
The following statement assigns a new default
temporary tablespace. The new default temporary tablespace must be an existing
temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE
temp;
Database
altered.
You cannot drop a default temporary tablespace,
but you can assign a new default temporary tablespace, then drop the former one. You
are not allowed to change a default temporary tablespace to a permanent
tablespace.
DROP TABLESPACE tmp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
The following statements take offline and bring
online temporary files:
ALTER DATABASE TEMPFILE
'D:\Ora\ARK2_temp1.dbf' OFFLINE;
Database altered.
ALTER DATABASE TEMPFILE
'D:\Ora\ARK2_temp1.dbf' ONLINE;
Database altered.
Undo Tablespace
instead of Rollback Segments
Historically, Oracle has used rollback
segments to store undo. Space management for these rollback segments has proven to be
quite complex. Oracle now offers another method of storing undo that eliminates the
complexities of managing rollback segment space, and enables DBAs to exert control over
how long undo is retained before being overwritten. This method uses an undo
tablespace.
You cannot use both methods in the same database
instance, although for migration purposes it is possible, for example, to create undo
tablespaces in a database that is using rollback segments, or to drop rollback segments
in a database that is using undo tablespaces. However, you must shutdown and restart your
database in order to effect the switch to another method of managing undo.
Oracle always uses a SYSTEM rollback segment for
performing system transactions. There is only one SYSTEM rollback segment and it is
created automatically at CREATE DATABASE time and is always brought online at instance
startup. You are not required to perform any operations to manage the SYSTEM rollback
segment.
You can create a specific undo
tablespace using the UNDO TABLESPACE clause of the CREATE DATABASE statement.
CREATE DATABASE VEN1
CONTROLFILE REUSE
MAXDATAFILES 256
MAXINSTANCES 4
MAXLOGFILES 62
MAXLOGMEMBERS 5
MAXLOGHISTORY 1600
CHARACTER SET "UTF8"
NATIONAL CHARACTER SET "UTF8"
DATAFILE '/u01/sys/VEN1_sys1.dbf' SIZE
300M REUSE
UNDO TABLESPACE undo DATAFILE
'/u01/sys/VEN1_undo1.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT
5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tmp
TEMPFILE '/u01/tmp/VEN1_tmp1.dbf'
SIZE 512064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 256K
LOGFILE GROUP 1 ('/u01/rdo/VEN1_log1A.rdo',
'/opt/rdo/VEN1_log1B.rdo') SIZE 10M REUSE,
GROUP 2 ('/u01/rdo/VEN1_log2A.rdo',
'/opt/rdo/VEN1_log2B.rdo') SIZE 10M REUSE,
GROUP 3 ('/u01/rdo/VEN1_log3A.rdo',
'/opt/rdo/VEN1_log3B.rdo') SIZE 10M REUSE,
GROUP 4 ('/u01/rdo/VEN1_log4A.rdo',
'/opt/rdo/VEN1_log4B.rdo') SIZE 10M REUSE,
GROUP 5 ('/u01/rdo/VEN1_log5A.rdo',
'/opt/rdo/VEN1_log5B.rdo') SIZE 10M REUSE,
GROUP 6 ('/u01/rdo/VEN1_log6A.rdo',
'/opt/rdo/VEN1_log6B.rdo') SIZE 10M REUSE;
In the ALERT log, you can see, that a
SYSTEM Rollback segment is created:
create rollback segment
SYSTEM tablespace SYSTEM
storage (initial 50K next 50K)
Completed: create rollback segment SYSTEM tablespace SYSTEM
Several Undo Segments are automatically
allocated
CREATE UNDO TABLESPACE UNDO
DATAFILE '/u01/sys/VEN1_undo1.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
Fri Jul 27 08:56:49 2001
Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace
Completed: CREATE UNDO TABLESPACE UNDO DATAFILE
-
ROLLBACK_SEGMENTS
Specifies the rollback segments to be acquired at start up.
-
TRANSACTIONS
Specifies the maximum number of concurrent transactions.
-
TRANSACTIONS_PER_ROLLBACK_SEGMENT
Specifies the number of concurrent transactions that each rollback segment
is expected to handle
-
MAX_ROLLBACK_SEGMENTS
Specifies the maximum number of rollback segments that can be
online for any instance.
-
UNDO_MANAGEMENT
If AUTO, use automatic undo management mode. If MANUAL, use manual undo management
mode.
-
UNDO_TABLESPACE
A dynamic parameter specifying the name of an undo tablespace to use.
-
UNDO_RETENTION
A dynamic parameter specifying the length of time to retain undo. Default is 900
seconds.
-
UNDO_SUPPRESS_ERRORS
If TRUE, suppress error messages if manual undo management SQL statements are issued
when operating in automatic undo management mode. If FALSE, issue error message. This
is a dynamic parameter
### System Managed Undo
### -------------------
undo_management = AUTO
undo_retention = 10800
undo_tablespace = undo
More Information can be found in the
Oracle9i Database Administrator's Guide
Release 1 (9.0.1) "Managing Undo Space".
New Space
Management in Locally Managed Tablespaces
Prior to Oracle8i, all
tablespaces were created as dictionary-managed. Dictionary-managed tablespaces
rely on SQL dictionary tables to track space utilization. Beginning with Oracle8i, you
can create locally managed tablespaces, which use bitmaps (instead of SQL
dictionary tables) to track used and free space.
Beginning with Oracle9i
you can specify how free and used space within a segment is to be managed. Your choices
are:
Specifying this keyword tells Oracle
that you want to use free lists for managing free space within segments. Free lists are
lists of data blocks that have space available for inserting rows. MANUAL is the
default.
This keyword tells Oracle that you
want to use bitmaps to manage the free space within segments. A bitmap, in this case,
is a map that describes the status of each data block within a segment with respect to
the amount of space in the block available for inserting rows. As more or less space
becomes available in a data block, its new state is reflected in the bitmap. Bitmaps
allow Oracle to manage free space more automatically, and thus, this form of space
management is called automatic segment-space management.
Free lists have been the traditional
method of managing free space within segments. Bitmaps, however, provide a simpler and
more efficient way of managing segment space. They provide better space utilization and
completely eliminate any need to specify and tune the PCTUSED, FREELISTS, and
FREELISTS GROUPS attributes for segments created in the tablespace. If such
attributes should be specified, they are ignored.
The following statement creates
tablespace users with automatic segment-space management:
CREATE TABLESPACE users
DATAFILE '/u01/VEN1_users1.dbf' SIZE 10304K
REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT
ONLINE;
-
It is not possible to create a temporary
tablespace with automatic space management:
-
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/VEN1_temp1.dbf' SIZE
512064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE
256K
SEGMENT SPACE MANAGEMENT
AUTO;
ERROR at line 4:
ORA-30573: AUTO segment space management not valid for this type of
tablespace
-
Your specification at tablespace
creation time of your method for managing available space in segments, applies to all
segments subsequently created in the tablespace. Also, your choice of method cannot
be subsequently altered.
-
Only permanent, locally managed
tablespaces can specify automatic segment-space management.
-
For LOBs, you cannot specify
automatic segment-space management.
Redefining Tables Online with
DBMS_REDEFINITION
In highly available systems, it is
occasionally necessary to redefine large "hot" tables to improve the performance of
queries or DML performed against these tables. Oracle provide a mechanism to redefine
tables online. This mechanism provides a significant increase in availability compared to
traditional methods of redefining tables that require tables to be taken
offline.
When a table is redefined online, it is
accessible to DML during much of the redefinition process. The table is locked in the
exclusive mode only during a very small window which is independent of the size of the
table and the complexity of the redefinition.
Online table redefinition enables you
to:
-
Modify the storage parameters of
the table
-
Move the table to a different
tablespace in the same schema
-
Add support for parallel
queries
-
Add or drop partitioning
support
-
Re-create the table to reduce
fragmentation
-
Add or drop a column
The original table EMP and
DEPT are created with the following statements:
CREATE TABLE emp
(empno NUMBER(4) PRIMARY KEY
NOT NULL,
ename
VARCHAR2(10),
job
VARCHAR2(9),
mgr
NUMBER(4),
hiredate DATE,
sal
NUMBER(7,2),
comm
NUMBER(7,2),
deptno
NUMBER(2));
INSERT INTO emp VALUES
(7369,'SMITH','CLERK',7902,
TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NULL,20);
INSERT INTO emp VALUES
(7499,'ALLEN','SALESMAN',7698,
TO_DATE('20-FEB-1981','DD-MON-YYYY'),1600,300,30);
INSERT INTO emp VALUES
(7521,'WARD','SALESMAN',7698,
TO_DATE('22-FEB-1981','DD-MON-YYYY'),1250,500,30);
INSERT INTO emp VALUES
(7566,'JONES','MANAGER',7839,
TO_DATE('2-APR-1981','DD-MON-YYYY'),2975,NULL,20);
INSERT INTO emp VALUES
(7654,'MARTIN','SALESMAN',7698,
TO_DATE('28-SEP-1981','DD-MON-YYYY'),1250,1400,30);
INSERT INTO emp VALUES
(7698,'BLAKE','MANAGER',7839,
TO_DATE('1-MAY-1981','DD-MON-YYYY'),2850,NULL,30);
INSERT INTO emp VALUES
(7782,'CLARK','MANAGER',7839,
TO_DATE('9-JUN-1981','DD-MON-YYYY'),2450,NULL,10);
INSERT INTO emp VALUES
(7788,'SCOTT','ANALYST',7566,
TO_DATE('09-DEC-1982','DD-MON-YYYY'),3000,NULL,20);
INSERT INTO emp VALUES
(7839,'KING','PRESIDENT',NULL,
TO_DATE('17-NOV-1981','DD-MON-YYYY'),5000,NULL,10);
INSERT INTO emp VALUES
(7844,'TURNER','SALESMAN',7698,
TO_DATE('8-SEP-1981','DD-MON-YYYY'),1500,
0,30);
INSERT INTO emp VALUES
(7876,'ADAMS','CLERK',7788,
TO_DATE('12-JAN-1983','DD-MON-YYYY'),1100,NULL,20);
INSERT INTO emp VALUES
(7900,'JAMES','CLERK',7698,
TO_DATE('3-DEC-1981','DD-MON-YYYY'),950,NULL,30);
INSERT INTO emp VALUES
(7902,'FORD','ANALYST',7566,
TO_DATE('3-DEC-1981','DD-MON-YYYY'),3000,NULL,20);
INSERT INTO emp VALUES
(7934,'MILLER','CLERK',7782,
TO_DATE('23-JAN-1982','DD-MON-YYYY'),1300,NULL,10);
CREATE TABLE dept
(deptno NUMBER(2) PRIMARY KEY NOT NULL,
dname VARCHAR2(14),
loc
VARCHAR2(13));
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW
YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;
The new table EMP is redefined as
follows:
In order to perform an online
redefinition of a table the user must perform the following steps.
... by invoking the
DBMS_REDEFINITION.CAN_REDEF_TABLE() procedure. If the table is not a candidate for online
redefinition, then this procedure raises an error indicating why the table cannot be
online redefined.
EXEC
dbms_redefinition.can_redef_table('SCOTT','EMP');
PL/SQL procedure successfully completed.
... (in the same schema as the table to
be redefined) with all of the desired attributes.
CREATE TABLE int_emp (
empno NUMBER PRIMARY KEY NOT NULL,
name VARCHAR2(100),
salary NUMBER,
hiredate DATE,
deptno NUMBER DEFAULT 10);
... by calling
DBMS_REDEFINITION.START_REDEF_TABLE(), providing the following:
If the column mapping information is
not supplied, then it is assumed that all the columns (with their names unchanged) are to
be included in the interim table. If the column mapping is supplied, then only those
columns specified explicitly in the column mapping are considered.
EXEC
dbms_redefinition.start_redef_table('SCOTT','EMP','INT_EMP',
'EMPNO EMPNO,
ENAME NAME,
SAL*1.10 SALARY,
HIREDATE HIREDATE');
PL/SQL procedure successfully completed.
... grants and constraints on the
interim table. Any referential constraints involving the interim table (that is, the
interim table is either a parent or a child table of the referential constraint) must be
created disabled. Until the redefinition process is either completed or aborted, any
trigger defined on the interim table will not execute.
When the redefinition is completed, the
triggers, constraints, indexes and grants associated with the interim table replace those
on the table being redefined. The referential constraints involving the interim table
(created disabled) transfer to the table being redefined and become enabled after the
redefinition is complete.
ALTER TABLE int_emp ADD (
CONSTRAINT fk_emp_dept
FOREIGN KEY (deptno)
REFERENCES dept (deptno));
ALTER TABLE int_emp DISABLE CONSTRAINT
fk_emp_dept;
CREATE OR REPLACE TRIGGER trg_hiredate
BEFORE UPDATE OF hiredate ON int_emp FOR EACH ROW
BEGIN :new.hiredate := SYSDATE; END;
/
ALTER TRIGGER trg_hiredate DISABLE;
After the redefinition process has been
started by calling START_REDEF_TABLE() and before FINISH_REDEF_TABLE() has been called,
it is possible that a large number of DML statements have been executed on the original
table. If you know this is the case, it is recommended that you periodically synchronize
the interim table with the original table. This is done by calling the
DBMS_REDEFINITION.SYNC_INTERIM_TABLE() procedure. Calling this procedure reduces the time
taken by FINISH_REDEF_TABLE() to complete the redefinition process.
The small amount of time that the
original table is locked during FINISH_REORG_TABLE() is independent of whether
SYNC_INTERIM_TABLE() has been called.
EXEC
dbms_redefinition.sync_interim_table('SCOTT','EMP','INT_EMP');
PL/SQL procedure successfully completed.
... executing the
DBMS_REDEFINITION.FINISH_REDEF_TABLE() procedure to complete the redefinition of the
table. During this procedure, the original table is locked in the exclusive mode for a
very small window. This window is independent of the amount of data in the original
table. Also, as part of this procedure, the following occurs:
-
The original table is redefined
such that it has all the attributes, indexes, constraints, grants and triggers of the
interim table.
-
The referential constraints
involving the interim table now involve the post redefined table and are
enabled.
EXEC
dbms_redefinition.finish_redef_table('SCOTT','EMP','INT_EMP');
PL/SQL procedure successfully completed.
... any indexes created on the interim
table and that are now defined on the redefined table.
The following is the end result of the
redefinition process:
-
The original table is redefined
with the attributes and features of the interim table.
-
The triggers, grants, indexes and
constraints defined on the interim table after START_REDEF_TABLE() and before
FINISH_REDEF_TABLE() are now defined on the post-redefined table. Any referential
constraints involving the interim table before the redefinition process was finished
now involve the post-redefinition table and are enabled.
-
Any indexes, triggers, grants and
constraints defined on the original table (prior to redefinition) are transferred to
the interim table and are dropped when the user drops the interim table. Any
referential constraints involving the original table before the redefinition now
involve the interim table and are disabled.
-
Any PL/SQL procedures and cursors
defined on the original table (prior to redefinition) are invalidated. They are
automatically revalidated (this revalidation can fail if the shape of the table was
changed as a result of the redefinition process) whenever they are used
next.
select CONSTRAINT_NAME,TABLE_NAME,STATUS from
user_constraints;
CONSTRAINT_NAME
TABLE_NAME
STATUS
------------------------------ ------------------------------ --------
FK_EMP_DEPT
EMP
ENABLED
select TRIGGER_NAME,TABLE_NAME,status from
user_triggers;
TRIGGER_NAME
TABLE_NAME
STATUS
------------------------------ ------------------------------ --------
TRG_HIREDATE
EMP
DISABLED
ALTER TRIGGER trg_hiredate ENABLE;
The following restrictions apply to the online
redefinition of tables:
-
Tables must have primary keys to be candidates
for online redefinition.
-
The table to be redefined and the final
redefined table must have the same primary key column.
-
Tables that have materialized views and
materialized view logs defined on them cannot be online redefined.
-
Tables that are materialized view container
tables and Advanced Queuing tables cannot be online redefined.
-
The overflow table of an index-organized table
cannot be online redefined.
-
Tables with user-defined types (objects, REFs,
collections, typed tables) cannot be online redefined.
-
Tables with FILE columns cannot be online
redefined.
-
Tables with LONG columns cannot be online
redefined. Tables with LOB columns are acceptable.
-
The table to be redefined cannot be part of a
cluster.
-
Tables in the SYS and SYSTEM schema cannot be
online redefined.
-
Temporary tables cannot be redefined.
-
There is no horizontal subsetting
support.
-
Only simple deterministic expressions can be
used when mapping the columns in the interim table to those of the original table.
For example, subqueries are not allowed.
-
If new columns (which are not instantiated with
existing data for the original table) are being added as part of the redefinition,
then they must not be declared NOT NULL until the redefinition is
complete.
-
There cannot be any referential constraints
between the table being redefined and the interim table.How to combine Automatic Memory
Management
(AMM) with manually sized SGA
ONLINE option
for ANALYZE VALIDATE STRUCTURE statement
You can specify that you want to perform structure
validation online while DML is occurring against the object being validated. There can be
a slight performance impact when validating with ongoing DML affecting the object, but
this is offset by the flexibility of being able to perform ANALYZE online. The following
statement validates the emp table and all associated indexes online:
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE
ONLINE;
Suspending and Resuming a
Database
The ALTER SYSTEM SUSPEND statement suspends a
database by halting all input and output (I/O) to datafiles (file header and file data)
and control files, thus allowing a database to be backed up without I/O interference.
When the database is suspended all preexisting I/O operations are allowed to complete and
any new database accesses are placed in a queued state.
The suspend command suspends the database, and is
not specific to an instance. Therefore, in an Oracle Real Application Clusters
environment, if the suspend command is entered on one system, then internal locking
mechanisms will propagate the halt request across instances, thereby quiescing all active
instances in a given cluster. However, do not start a new instance while you suspend
another instance, since the new instance will not be suspended.
Use the ALTER SYSTEM RESUME statement to
resume normal database operations. You can specify the SUSPEND and RESUME from different
instances. For example, if instances 1, 2, and 3 are running, and you issue an ALTER
SYSTEM SUSPEND statement from instance 1, then you can issue a RESUME from instance 1, 2,
or 3 with the same effect.
The suspend/resume feature is useful in systems that
allow you to mirror a disk or file and then split the mirror, providing an alternative
backup and restore solution. If you use a system that is unable to split a mirrored disk
from an existing database while writes are occurring, then you can use the suspend/resume
feature to facilitate the split.
The suspend/resume feature is not a suitable
substitute for normal shutdown operations, however, since copies of a suspended database
can contain uncommitted updates.
Do not use the ALTER SYSTEM SUSPEND statement as a
substitute for placing a tablespace in hot backup mode. Precede any database suspend
operation by an ALTER TABLESPACE BEGIN BACKUP statement.
The following statements illustrate ALTER SYSTEM
SUSPEND/RESUME usage. The V$INSTANCE view is queried to confirm database
status.
SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED
SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE
Oracle9i "AL16UTF16"
National Character Set
The national character set is specified when you
create an Oracle database:
CREATE
DATABASE ARK1
CONTROLFILE REUSE
MAXDATAFILES 256
MAXINSTANCES 4
MAXLOGFILES 62
MAXLOGMEMBERS 5
MAXLOGHISTORY 1600
CHARACTER SET "WE8ISO8859P1"
NATIONAL CHARACTER SET "AL16UTF16"
DATAFILE 'D:\Oradata\sys\ARK1_sys1.dbf' SIZE 200M
REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('D:\Oradata\rdo\ARK1_log1A.rdo',
'C:\Oradata\rdo\ARK1_log1B.rdo') SIZE 5M REUSE,
GROUP 2
('D:\Oradata\rdo\ARK1_log2A.rdo',
'C:\Oradata\rdo\ARK1_log2B.rdo') SIZE 5M REUSE,
GROUP 3
('D:\Oradata\rdo\ARK1_log3A.rdo',
'C:\Oradata\rdo\ARK1_log3B.rdo') SIZE 5M REUSE,
GROUP 4
('D:\Oradata\rdo\ARK1_log4A.rdo',
'C:\Oradata\rdo\ARK1_log4B.rdo') SIZE 5M REUSE;
Oracle9i only supports UTF8 and AL16UTF16 character
sets for NCHAR. AL16UTF16 is the name of the new character set introduced in Oracle9i to
store 16 bit fixed width Unicode data.
The default NATIONAL CHARACTER SET (NLS_NCHAR_CHARACTERSET) for databases
created with Oracle9i will be the new character set "AL16UTF16". As this character set
does not exist in Oracle8/8i then applications need a client side NLS patch in order to
make sense of data stored in NCHAR, NVARCHAR2 or NCLOB columns in any Oracle9i database
using AL16UTF16.
New SQL join support in
Oracle9i
Historically, the Oracle database has supported the
join syntax defined for SQL/86. In the following example this older standard join syntax
is shown:
CREATE TABLE tour (
tour_name VARCHAR2(10),
duration NUMBER(2));
CREATE TABLE registration (
tour_name VARCHAR2(10),
duration NUMBER(2),
guest VARCHAR2(10));
CREATE TABLE guest (
guest VARCHAR2(10),
vip NUMBER(2));
INSERT INTO tour (tour_name, duration)
VALUES ('Moench', 1);
INSERT INTO tour (tour_name, duration)
VALUES ('Jungfrau', 6);
INSERT INTO tour (tour_name, duration)
VALUES ('Eiger', 3);
INSERT INTO tour (tour_name, duration)
VALUES ('Gruenhorn', 4);
INSERT INTO registration (tour_name, duration, guest)
VALUES ('Gruenhorn', 4, 'Mueller');
INSERT INTO registration (tour_name, duration, guest)
VALUES ('Moench', 1, 'Maier');
INSERT INTO registration (tour_name, duration, guest)
VALUES ('Moench', 6, 'Holzer');
INSERT INTO registration (tour_name, duration, guest)
VALUES ('Moench', 1, 'Konrad');
INSERT INTO registration (tour_name, duration, guest)
VALUES ('Gruenhorn', 4, 'Jenzer');
INSERT INTO guest (guest, vip)
VALUES ('Mueller', 2);
INSERT INTO guest (guest, vip)
VALUES ('Maier', 3);
INSERT INTO guest (guest, vip)
VALUES ('Holzer', 10);
INSERT INTO guest (guest, vip)
VALUES ('Konrad', 1);
INSERT INTO guest (guest, vip)
VALUES ('Jenzer', 5);
COMMIT;
SELECT t.tour_name, t.duration, r.guest
FROM tour t, registration r
WHERE t.tour_name = r.tour_name
AND t.duration = r.duration;
Before Oracle9i, Oracle databases supported outer
joins through the use of a proprietary syntax:
SELECT t.tour_name, t.duration, r.guest
FROM tour t, registration r
WHERE t.tour_name = r.tour_name(+)
AND t.duration = r.duration(+)
ORDER BY 1,2,3;
Note the use of "(+)" in the WHERE clause
following the names of the REGISTRATION table columns in order to make that table the
optional table in the join.
The traditional join syntax has its problems.
Given a complex query, it can often be difficult to parse the WHERE clause to separate
the join conditions from other restrictions placed on the query results. And
programmers occasionally overlook specifying any join conditions at all, leading to a
Cartesian product.
ANSI standard SQL join syntax brings some new
keywords and clauses to Oracle9i, and these allow you to specify joins entirely in the
FROM clause of a SELECT statement. Consider the problem of joining the TOUR and
REGISTRATION tables. Using the traditional approach, you begin by listing the two tables
in the FROM clause of your query, as follows:
FROM tour t, registration r
Using the new ANSI syntax, however, you dispense
with the commas and instead explicitly specify the type of join you want. To do an inner
join between the TOUR and REGISTRATION tables, you write:
FROM tour t
INNER JOIN registration r
Having specified that you want an inner join, you
specify the conditions of that join. Rather than mixing your join conditions with your
other WHERE clause restrictions, you specify your join conditions in an ON clause that is
part of the FROM clause:
SELECT t.tour_name, t.duration, r.guest
FROM tour t INNER JOIN registration r
ON t.tour_name = r.tour_name
AND t.duration = r.duration;
TOUR_NAME DURATION GUEST
---------- ---------- ----------
Gruenhorn 4 Mueller
Gruenhorn 4 Jenzer
Moench 1 Maier
Moench 1 Konrad
The new syntax offers several advantages:
-
All the information for a join is specified in
one place. You no longer need to wade through a complex WHERE clause, trying to
separate out join conditions from other restrictions on query results.
-
You aren't allowed to "forget" to specify join
conditions. Once you specify an inner join, for example, Oracle9i requires that you
use the ON clause—or another clause—to explicitly specify the join
condition.
-
You can perform a full outer join, something not
possible with the previous Oracle database join syntax. You don't have to worry about
omitting an occurrence of "(+)," thus inadvertently converting an outer join into an
inner join.
So far, we have used the ON clause to specify join
conditions. The ON clause allows you to specify any Boolean expression as a join
condition. Most joins, however, are equi-joins. An equi-join is one in which you
compare related columns from two tables for equality. So, if the columns defining a join
are named identically in the two tables, you can take advantage of some simplified syntax
that also increases the clarity of your queries. Instead of using the ON clause to
specify a Boolean join condition, you can specify the join columns in a USING
clause.
SELECT tour_name, duration, r.guest
FROM tour t INNER JOIN registration r
USING (tour_name,duration);
TOUR_NAME DURATION GUEST
---------- ---------- ----------
Moench 1 Maier
Moench 1 Konrad
Gruenhorn 4 Mueller
Gruenhorn 4 Jenzer
The USING clause in this example specifies that rows
from the two tables be joined when they have the same values in their respective
TOUR_NAME and DURATION columns. Listing 3 shows an example of an equi-join performed with
the USING clause.
If you use column aliases like these with the
USING clause, you'll receive an "invalid column name" error. When you specify the
USING clause, the database engine merges the two COURSE_NAME columns and recognizes only
one such column in the result. That column is not associated with either of the joined
tables, so you can't qualify it with an alias. This makes sense because, by definition,
an equi-join means that there's only one COURSE_NAME value for each row returned by the
query.
The new ANSI syntax recognizes three types of outer
join: left outer joins, right outer joins, and full outer joins.
Left and right outer joins are really the same thing - all rows from one table are
included, along with any matching rows from the other table. The only difference between
a left and a right outer join is the order in which you list the tables. The following
two queries, are semantically identical:
SELECT t.tour_name, t.duration, r.guest
FROM tour t LEFT OUTER JOIN registration r
ON t.tour_name = r.tour_name
AND t.duration = r.duration
ORDER BY 1,2,3;
SELECT t.tour_name, t.duration, r.guest
FROM registration r RIGHT OUTER JOIN tour t
ON t.tour_name = r.tour_name
AND t.duration = r.duration
ORDER BY 1,2,3;
The full outer join represents a new capability,
returning all rows from both tables. Rows are matched on the join columns where
possible, and NULLs are used to fill in the empty columns for any rows that don't have a
match in the other table. Here is a pre-Oracle9i simulated full outer join and an
Oracle9i full outer join.
SELECT t.tour_name, t.duration, r.guest
FROM tour t, registration r
WHERE t.tour_name = r.tour_name(+)
AND t.duration = r.duration(+)
UNION
SELECT r.tour_name, r.duration, r.guest
FROM registration r
WHERE NOT EXISTS (
SELECT *
FROM tour t2
WHERE t2.tour_name = r.tour_name
AND t2.duration = r.duration
)
ORDER BY 1,2,3;
SELECT tour_name, duration, r.guest
FROM tour t FULL OUTER JOIN registration r
USING (tour_name, duration)
ORDER BY 1,2,3;
TOUR_NAME DURATION GUEST
---------- ---------- ----------
Eiger 3
Gruenhorn 4 Jenzer
Gruenhorn 4 Mueller
Jungfrau 6
Moench 1 Konrad
Moench 1 Maier
Moench 6 Holzer
If you prefer to see the columns as NULL, use an ON
clause to specify the join condition:
SELECT t.tour_name, t.duration, r.guest
FROM tour t FULL OUTER JOIN registration r
ON t.tour_name = r.tour_name
AND t.duration = r.duration
ORDER BY 1,2,3;
TOUR_NAME DURATION GUEST
---------- ---------- --------
Eiger 3
Gruenhorn 4 Jenzer
Gruenhorn 4 Mueller
Jungfrau 6
Moench 1 Konrad
Moench 1 Maier
Holzer
Because the ON clause was used, the TOUR_NAME and
DURATION columns for Holzer's row are NULL.
You can specify multiple join conditions to join
more than two tables in a query. By default, Oracle9i processes the joins from left to
right. The following examples are equivalent and produces the same output. The first
query joins TOUR to REGISTRATION and then joins the result to GUEST.
USING syntax:
SELECT tour_name, duration, guest, g.vip
FROM tour t INNER JOIN registration r
USING (tour_name, duration)
INNER JOIN guest g USING (guest)
ORDER BY 1,2,3,4;
ON syntax:
SELECT t.tour_name, t.duration, r.guest, g.vip
FROM tour t INNER JOIN registration r
ON t.tour_name = r.tour_name
AND t.duration = r.duration
INNER JOIN guest g
ON r.guest = g.guest
ORDER BY 1,2,3,4;
Pre Oracle9i syntax:
SELECT t.tour_name, t.duration, r.guest, g.vip
FROM tour t, registration r, guest g
WHERE t.tour_name = r.tour_name
AND t.duration = r.duration
AND r.guest = g.guest
ORDER BY 1,2,3,4;
TOUR_NAME DURATION GUEST VIP
---------- ---------- ---------- ----------
Gruenhorn 4 Jenzer 5
Gruenhorn 4 Mueller 2
Moench 1 Konrad 1
Moench 1 Maier 3
Generate Insert Statements For
Existing Data In A Table
Suppose you have a lookup table with existing data
on your test system and you need an INSERT script to fill the same, empty table on the
production system. Then you need our script GENINS.SQL, which solves exactly this situation.
For an example, we use the well known table DEPT.
SQL> SELECT * FROM dept;
DEPTNO
DNAME LOC
---------- -------------- --------
10 ACCOUNTING NEW
YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40 OPERATIONS
BOSTON
Now generate the INSERT script:
sqlplus scott/tiger
SQL> start genins.sql
Enter value for table_name: dept
Enter
value for col1: <RETURN>
Enter value for col1_value: <RETURN>
Enter value for col1: <RETURN>
Enter value for col1_value: <RETURN>
The generated SQL code looks as follows:
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (30,'SALES','CHICAGO');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (40,'OPERATIONS','BOSTON');
Add a new column MODDATE to the table DEPT, and initialize the new
column with SYSDATE in the generated script.
SQL> alter table dept add (moddate DATE);
SQL> start genins.sql
Enter value for table_name: dept
Enter
value for col1: moddate
Enter value for col1_value: SYSDATE
Enter value for col1: <RETURN>
Enter value for col1_value: <RETURN>
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (10,'ACCOUNTING','NEW YORK',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (20,'RESEARCH','DALLAS',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (30,'SALES','CHICAGO',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (40,'OPERATIONS','BOSTON',SYSDATE);
You can see, that the new column MODDATE is
initialized with SYSDATE in the generated output.
Suppose, you want to change the LOC column to the
Default Value 'THUN'.
SQL> start genins.sql
Enter value for table_name: dept
Enter value for col1: moddate
Enter value for col1_value: SYSDATE
Enter value for col2: loc
Enter value for col2_value: ''THUN''
You can see, that the existing column LOC is
initialized with 'THUN' in the generated output.
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (10,'ACCOUNTING','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (20,'RESEARCH','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (30,'SALES','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (40,'OPERATIONS','THUN',SYSDATE);
The script GENINS.SQL may help you to generate
INSERT scripts for existing data in a table on test system to initialize the same table
on another system. You have the possibility to initialize existing columns (max 2
columns) with a Default Value.
Due a limitation of the package DBMS_OUTPUT (Line length overflow,
limit of 255 bytes per line), the generated VALUE (....) list can actually have a maximal
size of 255 characters.
The Script GENINS.SQL can be downloaded from
here.
Loading Data using External
Tables
Oracle 9i allows you read-only access to data
in external tables. External tables are defined as tables that do not reside in the
database, and can be in any format for which an access driver is provided. By
providing Oracle with metadata describing an external table, Oracle is able to expose the
data in the external table as if it were data residing in a regular database table. The
external data can be queried directly and in parallel using SQL.
You can, for example, select, join, or sort external
table data. You can also create views and synonyms for external tables. However, no DML
operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on
external tables.
The means of defining the metadata for external
tables is through the CREATE TABLE ... ORGANIZATION EXTERNAL statement. This external
table definition can be thought of as a view that allows running any SQL query against
external data without requiring that the external data first be loaded into the database.
An access driver is the actual mechanism used to read the external data in the
table.
Oracle provides an access driver for external
tables. It allows the reading of data from external files using the Oracle loader
technology. The ORACLE_LOADER access driver provides data mapping capabilities which are
a subset of the control file syntax of SQL*Loader utility.
Oracle's external tables feature provides a valuable
means for performing basic extraction, transformation, and transportation (ETT) tasks
that are common for datawarehousing.
The following example creates an external table,
then uploads the data to a database table. We have tested the examples in the Oracle9i
Database Administrator's Guide Release 1 (9.0.1) using Oracle 9.0.1 on Windows
2000.
The file empxt1.dat in C:\Users\Zahn\Work contains
the following sample data:
7369,Schmied,Schlosser,7902,17.12.1980,800,0,20
7499,Zaugg,Verkäufer,7698,20.02.1981,1600,300,30
7521,Müller,Verkäufer,7698,22.02.1981,1250,500,30
7566,Holzer,Informatiker,7839,02.04.1981,2975,0,20
7654,Zahn,Verkäufer,7698,28.09.1981,1250,1400,30
7698,Sutter,Informatiker,7839,01.05.1981,2850,0,30
7782,Graf,Informatiker,7839,09.06.1981,2450,0,10
The file empxt2.dat in C:\Users\Zahn\Work contains
the following sample data:
7788,Gasser,Analytiker,7566,19.04.1987,3000,0,20
7839,Kiener,Lehrer,,17.11.1981,5000,0,10
7844,Stoller,Verkäufer,7698,08.09.1981,1500,0,30
7876,Amstutz,Automechaniker,7788,23.05.1987,1100,0,20
7900,Weigelt,Automechaniker,7698,03.12.1981,950 ,0,30
7902,Wyss,Analytiker,7566,03.12.1981,3000,0,20
7934,Messerli,Automechaniker,7782,23.01.1982,1300,0,10
The following SQL statements create an external table and load its
data into database table EMP of the user scott.
sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on
Sat Jan 26 10:44:48 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
CONNECT SYS/MANAGER AS SYSDBA;
SET ECHO ON;
CREATE OR REPLACE DIRECTORY dat_dir AS
'C:\Oracle\Data';
CREATE OR REPLACE DIRECTORY log_dir AS 'C:\Oracle\Log';
CREATE OR REPLACE DIRECTORY bad_dir AS 'C:\Oracle\Bad';
Directory created.
GRANT READ ON DIRECTORY dat_dir TO scott;
GRANT WRITE ON DIRECTORY log_dir TO scott;
GRANT WRITE ON DIRECTORY bad_dir TO scott;
Grant succeeded.
CONNECT scott/tiger;
DROP TABLE empxt;
CREATE TABLE empxt
(empno NUMBER(4),
ename VARCHAR2(20),
job VARCHAR2(20),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile bad_dir:'empxt%a_%p.bad'
logfile log_dir:'empxt%a_%p.log'
fields terminated by ','
missing field values are null
( empno,
ename,
job,
mgr,
hiredate char date_format date mask
"dd.mm.yyyy",
sal,
comm,
deptno
)
)
LOCATION ('empxt1.dat', 'empxt2.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;
Table created.
ALTER SESSION ENABLE PARALLEL DML;
Session altered.
The first few statements in this example create the
directory objects for the operating system directories that contain the data
sources, and for the bad record and log files specified in the access parameters. You
must also grant READ or WRITE directory object privileges, as appropriate.
The TYPE specification is given only to
illustrate its use. If not specified, ORACLE_LOADER is the default access driver. The
access parameters, specified in the ACCESS PARAMETERS clause, are opaque to Oracle. These
access parameters are defined by the access driver, and are provided to the access driver
by Oracle when the external table is accessed.
The PARALLEL clause enables parallel query on
the data sources. The granule of parallelism is by default a data source, but parallel
access within a data source is implemented whenever possible. For example, if PARALLEL=3
were specified, then more than one parallel execution server could be working on a data
source.
The REJECT LIMIT clause specifies that there
is no limit on the number of errors that can occur during a query of the external data.
For parallel access, this limit applies to each parallel query slave independently. For
example, if REJECT LIMIT 10 is specified, each parallel query process is allowed 10
rejections. Hence, the only precisely enforced values for REJECT LIMIT on parallel query
are 0 and UNLIMITED.
SELECT * FROM empxt;
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM DEP
----- -------- -------------- ---- --------- ----- ---- ---
7788 Gasser Analytiker 7566 19-APR-87
30000 20
7839 Kiener
Lehrer
17-NOV-81 50000 10
7844 Stoller Verkäufer 7698 08-SEP-81
15000 30
7876 Amstutz Automechaniker 7788 23-MAY-87 11000 20
7900 Weigelt Automechaniker 7698 03-DEC-81 9500 30
7902 Wyss Analytiker 7566 03-DEC-81
30000 20
7934 Messerli Automechaniker 7782 23-JAN-82 13000 10
7369 Schmied Schlosser 7902 17-DEC-80
8000 20
7499 Zaugg Verkäufer 7698
20-FEB-81 1600 300 30
7521 Müller Verkäufer 7698
22-FEB-81 1250 500 30
7566 Holzer Informatiker 7839 02-APR-81 29750
20
7654 Zahn Verkäufer 7698
28-SEP-81 1250 1400 30
7698 Sutter Informatiker 7839 01-MAY-81 28500
30
7782 Graf Informatiker 7839 09-JUN-81
24500 10
CREATE TABLE emp2 AS SELECT * FROM empxt;
Table created.
DELETE FROM emp2;
14 rows deleted.
INSERT INTO emp2 SELECT * FROM empxt;
14 rows created.
In this example, the INSERT INTO statement generates a dataflow
from the external data source to the Oracle SQL engine where data is processed. As data
is parsed by the access driver from the external table sources and provided to the
external table interface, the external data is converted from its external representation
to its Oracle internal data type.
UPDATE empxt SET empno = 6534 WHERE empno =
7654;
ERROR at line 1:
ORA-30657: operation not supported on ext organized table
External Tables are a great alternative for the well
known SQL*Loader Utility. Everything can be done from the database side, no more external
processes for data loading and complicated Control Files - Congratulation Oracle
!
No DML operations (UPDATE, INSERT, or DELETE) are possible, and no
indexes can be created, on external tables.
Oracle9i Database Administrator's Guide Release 1 (9.0.1)
Managing Tables
Secure Random Number
Generator
Oracle 9i's DBMS_OBFUSCATION_TOOLKIT now
includes a secure random number generator, DES3GETKEY. Secure random number
generation is a very important aspect of cryptography; predictable cryptographic keys can
easily be decrypted by a person or machine performing crypto analysis.
The DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY procedure
computes a 16-byte random value. At any rate, one of the ways to call DES3GETKEY is as a
function that returns a random value of type RAW. By using the built-in RAWTOHEX
function, it's possible to produce a more usable "hex" value (which is really just a
VARCHAR2).
The procedure requires an 80-byte seed value. A seed
value is needed to kick-start the algorithm to compute the random value. A simple way to
do this is to hardcode the seed (which we actually generated by repeated invocations to
the procedure). Despite the constant seed, the random value does not repeat.
The function SECURE_RAND produces the 16-byte random
value
sqlplus scott/tiger
CREATE OR REPLACE FUNCTION secure_rand
RETURN VARCHAR2 IS
seedval RAW(80) := HEXTORAW('72DD046BF9892A3544B7587475FDF5A0'
|| 'B8F6C44F5C33B57C8156E5CBE92A8477'
|| 'F4F8FCDE5A21236CA1D7938C4D5E47A8'
|| 'D7BBC407DB6DB8EB7695BA5565218C4F'
|| 'D66D5C41523FDCBA8D92CDBD8DC75C54');
BEGIN
RETURN RAWTOHEX(DBMS_OBFUSCATION_TOOLKIT.DES3GETKEY(
seed => seedval));
END;
/
Function created.
Test the function with the following code
fragment
set serveroutput on
DECLARE
rand_val VARCHAR2(32);
BEGIN
rand_val := secure_rand;
DBMS_OUTPUT.PUT_LINE(rand_val);
END;
/
The first call to the function returns a value such as:
86A6D743152E41608D4FF7AFFEFC3EFA
PL/SQL procedure successfully completed.
The second call to the function returns a value such as:
382BDDB7A56E736615A89BC596FEAFEA
PL/SQL procedure successfully completed.
Manually install Oracle9i
JVM (9.0.1)
If you want to install the Oracle 9i Java Virtual
Machine, you have to run the following Scripts as user SYS. Using the database
configuration assistant, this scripts will be executed.
Make sure, your INIT.ORA Paramaters
SHARED_POOL_SIZE
and JAVA_POOL_SIZE are big enough. We have successfully installed the 9i JVM with
the following settings on a Windows 2000 server.
shared_pool_size = 200000000
java_pool_size = 100000000
Note, that Oracle Home is: D:\Ora9i
sqlplus /nolog
connect sys/.... as sysdba;
-- Setup a database for
running Java and the ORB
@D:\Ora9i\javavm\install\initjvm.sql;
-- INITialize (load) XML components in JServer
@D:\Ora9i\xdk\admin\initxml.sql;
-- Loads NCOMP'ed XML Parser
@D:\Ora9i\xdk\admin\xmlja.sql;
-- Loads the XMLSQL Utility (XSU) into the database.
@D:\Ora9i\rdbms\admin\catxsu.sql;
-- Install the Oracle Servlet Engine (OSE)
@D:\Ora9i\javavm\install\init_jis.sql D:\Ora9i;
-- Adds the set of default end points to the server
-- with hardcoded values for the admin service
@D:\Ora9i\javavm\install\jisaephc.sql D:\Ora9i;
-- Turn on J Accelerator
@D:\Ora9i\javavm\install\jisja.sql D:\Ora9i;
-- Register EJB\Corba Dynamic Registration Endpoint
@D:\Ora9i\javavm\install\jisdr.sql 2481 2482;
-- Init Java server pages ???
@D:\Ora9i\jsp\install\initjsp.sql;
-- Turn on J Accelerator for JSP libs
@D:\Ora9i\jsp\install\jspja.sql;
-- Script used to load AQ\JMS jar files into the
database
@D:\Ora9i\rdbms\admin\initjms.sql;
-- Load RepAPI server classes and publish 'repapi' obj
@D:\Ora9i\rdbms\admin\initrapi.sql;
-- Loads sql, objects, extensibility and xml related
java
@D:\Ora9i\rdbms\admin\initsoxx.sql;
-- Loads appctxapi.jar for JavaVm
enabled
-- Database.Called by jcoreini.tsc
@D:\Ora9i\rdbms\admin\initapcx.sql;
-- Script used to load CDC jar files into the database
@D:\Ora9i\rdbms\admin\initcdc.sql;
-- Loads the Java stored procedures as required by the
-- Summary Advisor.
@D:\Ora9i\rdbms\admin\initqsma.sql;
-- Initialize sqlj type feature in
9i db
@D:\Ora9i\rdbms\admin\initsjty.sql;
-- Load java componenets for AQ HTTP
Propagation
@D:\Ora9i\rdbms\admin\initaqhp.sql;
| 
