Export/Import
White Paper: Oracle 8i Export/Import June 11, 1999
Ivan Strand
Documentaion Source Material:
http://technet.oracle.com :
Oracle 8i Documentation: Utilities
Oracle 8i Documentation: Admiinistrator's Guide, Chapter 9: Managing
Tablespaces
UITS DBA Library :
Oracle8, A Beginners Guide
Oracle8, The Complete Reference
Description
----------------------------------------
Export and Import are parameter-driven utilities used to make copies of Oracle objects. Based on the export mode used various levels of objects are moved to the export file and made available for import. Import can only read files created by the Export utility. The Export utility can only create an output from an Oracle database environment.
The group of objects retrieved from the database is controlled through an export mode parameter provided at the time the utility is executed. There are four modes: Table mode, User mode, Full-database mode and Tablespace mode. These modes determine what objects are exported from the source environment and thus what objects are available for import. For a table listing these modes and the objects exported see "Appendix A: Objects Exported In Each Mode".
The different modes permit export to be used for many different purposes. In Table mode a user may replicate a table or set of tables from one Oracle server to another or simply from one tablespace to another on the same server. In User mode, a DBA or privileged user may use export to replicate the complete enviornment of a user or list of users. In Tablespace mode, the underlying DDL of a tablespace may be copied to another tablespace. A tablespace can be copied to another database by exporting the source metadata in tablespace mode and copying the datafiles through standard operating system file utilities.
Finally, in Full database mode, export extracts all the object definitions and data (optionally) from a complete database. Import may then reload a complete database or selectively load objects or sets of objects from the full database export file.
In addition to copying and replicating existing Oracle database objects, Export and Import are also used as part of a backup and recovery strategy. Using full database mode, as an example, export can create a complete base-line backup. Using full database mode with the option ROWS=N (no rows) a complete image of the object definitions and structure of the data environment can be exported.
Export and import can be used to defragment tables. All objects associated with a user will be exported in User mode. Import will recreate the tables, views, procedures, etc based on the currently-defined space settings for the tables.
Possible Applications
----------------------------------------
The following list shows ways Export/Import could possibly be used in the DSS environment including testing and development environment.
Use Export/Import to Create the ODS:
Use Export/Import to Create A Test or Development Environment:
Use Export/Import for Backup:
Use Export/Import to Reorganize and Defragment Table Data:
Implementation
----------------------------------------
Using Export/Import to Create the ODS:
The Export/Import utilities offer several different levels of extraction and replication based on the mode setting. It should be possible to use the execution-time mode parameter to build all or part of the Operational Data Store (ODS) that feeds the DSS reporting and data-group build processes.
Export/Import may be used, for example, to replicate the data objects (tables and indexes) or the definition components (DDL) or both. The level of DDL extraction can be controlled. While table and use modes constrain the export of data and DDL to a list of tables or users, Tablespace mode or Full database mode includes DDL definitions for all tables and indexes within the tablespace or database. For full database mode the row data may be optionally included and for tablespace mode the data can be replicated using standard file-system copy utilities.
The way Export is used to copy data and definition components into the DSS environment will be dependent on the design of the source-level operational databases. The boundaries between tablespaces and users in that operational data will determine to some extent the ability to make efficient use of Export/Import for replicating that data.
Tablespaces can be transported between databases in release 8i. Since the data files that hold the table and index data owned by the tablespace can be copied using standard operating system utilities, replication is much faster than copying table data using standard Export/Import functions. The caveat is that since tablespace replication transfers table and index data at the physical file level, there are some Oracle and operating-system level limitations. (Please see the white paper "Transportable Tablespaces" for a detailed look at this replication approach.)
Using Export/Import to Create A Test or Development Environment:
It is possible to replicate all or part of a database environment using Export/Import. In building a test or development environment it may be desirable to start with existing data and structure from a database that already exists. While it may require multiple Export/Import passes it should be possible to use the various mode settings to copy definition and row and index data. Thus while it is possible to clone a complete database into a new environment it is also possible to replicate a sub-set of the source database in that new environment.
Using Export/Import for Backup:
Using full database mode, Export can be used to create a full baseline image of a database for backup purposes. While the complete database can be reloaded from the full-database export it is also possible to use table or user mode import settings to re-create one or more tables or users.
Using Export/Import to Reorganize and Defragment Table Data:
Export/Import can be used to defragment tables. Since the old table is dropped, Import can create a new version of the table using the current storage parameters. Import will write the new table to contiguous data blocks within the constraints of the extent sizes.
Export normally consolidates data (at Import time) into one extent. A tablespace may have been created with a PCTINCREASE parameter. PCTINCREASE controls the amount of free space allocated when an extent (first or next) is created. When a table is imported a PCTINCREASE value greater than zero causes this free space to be allocated in the initial extent. If the table is large, a large amount of un-used space can be allocated for a table that is intended to be read only.
The consolidation of tables into one initial extent can be controlled (suppressed) by over-riding the default COMPRESS=Y run-time parameter for Export and making it COMPRESS=N. Then when import loads the table the current storage parameters for initial extent size and next extent size will be used..
PCTINCREASE causes the extent size to grow (exponentially) which can have the advantage in the operational environment of reducing the number of extents and non-contiguous space. But for the read-only DSS environment, this expansion space is useless and wasteful. When PCTINCREASE is used in the tablespace definition, COMPRESS=N can minimize the amount of space allocated during the import.
Evaluation:
----------------------------------------
Export and Import are primary utilities in the Oracle toolbox. They can and will be used to replicate tables, user environments and backup and recovery tasks in day-to-day work in the development and production environments.
The goal of this paper and the intent of this evaluation is to look at how Export and Import can be used to create all or part of the DSS environment.
In creating that environment two primary needs must be fulfilled. 1) Creating the ODS (Operational Data Store) and 2) Creating the DSS Datagroups. This evaluation section will address Creating the ODS describing possible ways Export/Import might be used and possible issues involved.
Creating the ODS using Transportable Tablespace:
From a best-case perspective, it appears that using the "Transportable Tablespace" process to replicate data and metadata has much to offer. As discussed in the separate white paper "Transportable Tablespaces", this approach to replication copies the table and index data at the physical file level and as such is much more efficient than the SQL-based object-at-a-time method used by Export/Import. While the row and index data is copied at the physical file level, the metadata from the data dictionary is replicated using Export/Import in tablespace mode.
Some issues:
Two primary limitations must be overcome to use Export/Import with transportable tablespaces. First, tablespaces can only be copied between two Oracle 8i databases. Second, tablespaces can be transported only between databases on identical hardware and operating system platforms.
Beyond these primary limitations, there are also issues of tablespace design and content. Since a complete tablespace is being replicated, the size could be an issue. If a tablespace on the source operational system is quite large and/or contains table data that is meaningless to the DSS environment, that overhead may limit the usefulness of the transportable tablespace. If, however, the contents of the OLTP tablespace clearly matches the needs of the ODS then the transportable tablespace method of replication will be very efficient.
Creating the ODS using Table, User or Full Database Mode Export/Import:
In addition to Tablespace mode, it is also possible to export and import in table, user and full-datbase mode. As with transportable tablespace mode, the choice of which mode may depend to a great extent on the organization of the data in the source database. If, for example, the tables needed are bundled in schemas that correspond to applications it may be possible to create the ODS in User mode where users or pseudo-users represent those application schemas.
It is possible to export a complete database and selectively import a subset of that database. At Export time the full database can be extracted. A list of tables or users can be used at the time of import to build or replace a set of tables.
It may be required that table-mode export be used to build the ODS. If the organization of tablespaces and users in the operational database is vastly different from the organization of the DSS database it may be necessary to refresh the DSS environment in table mode.
Some issues:
The Export/Import mode used could depend on the differences between the operational and DSS Oracle environments. On one extreme it may be possible that the organization of tables, schemas and tablespaces is identical in both environments. On the other hand, it may be that there is a large difference in the organization and definition of two environments. It is likely that the flexibility of the Export/Import utilities would handle these variations but that flexibility may come at the expense of efficient data transfer.
As such, is important to understand the differences between the various extract modes used by Export. In addition to the differences in the Oracle modes it is equally important to understand exactly what database metadata components must be transferred into the DSS environment.
While the table in Appendix A describes the data and metadata components transferred in all four of the Export/Import modes, Appendix B concentrates on two modes: User mode and Full Database mode. The rationale for including this table follows.
Of the possible modes available, Tablespace mode is the most desirable from the standpoint of efficiency and should be used where possible.
It may be that there will be problems with using tablespace replication or that a mix of export/import modes will be required depending on the structure of users and data in the various application areas.
If Export/Import can be used but must operate at either table, user or full database mode, what are the differences? What metadata components are missing when transfer occurs at a level other than full database?
User mode export was chosen as a target for comparison against full database because of the potential use of schemas to distinguish application areas. In Appendix B: User Mode and Full Database Mode Compared, the list of exported components and data handled by these two modes is shown.
Appendix C contains a list of component descriptions. This list includes those components that are exported in full database mode but not exported in user mode.
Views: A final issue to remember: Views are only exported in full database mode.
Evaluation Conclusion:
Export and Import utilities are designed to replicate Oracle data and definitions from one environment to another. Of that there is no doubt. The questions and issues have to do with efficiency and structure. What is the most efficient use of Export/Import based on the structure of our data environments.
Table-level replication can be used to copy data and indexes from one environment to another. User-level replication may be able to be used to copy a set of tables and the associated data dictionary metadata that correspond to application schemas. Tablespace repliation may be able to be used to great advantage if the contents of the operational tablespaces represent reasonably well the data that should be copied into the DSS environment to create the ODS for an application area.
In order to further clarify the ways that Export/Import might be used to copy operational data into the DSS environment, the following possible actions may be of benefit:
1) Exercise the Export/Import utilities to experiment with the various modes. Attempt to highlight the differences between modes with an emphasis on identifying the issues that any or all of those modes generate.
2) Obtain additional information about the potential structure of operational data. Determine and describe the various ways that this data might be organized with regard to tablespaces and user schemas. Attempt to show how various Export/Import modes would be used to replicate this data depending on the type of organization.
3) Pursue more substantive information regarding the relevance of each metadata component. Is that component necessary or vital to the use of the data in the DSS environtment? Is that component necessary or vital to the Oracle administration of the DSS environment?
4) Learn through consultants and representatives of other organizations the possibilities and/or issues associated with using Export/Import to build the data warehouse.
-------------------------------------------- //// -------------------------------------------------
Appendix A: Objects Exported In Each Mode
-------------------------------------------------------------------------------------
Export mode abbreviations: T = Table U = User F = Full database TS= Tablespace
NOTE: (pu) in component description means privileged user.
action,post-schema procedural actions and objects(pu) : : :F : action,post-schema procedural actions and objects : :U : : action,post-table actions(ddl) : : : :TS action,post-table actions(pu) : : :F : action,post-table actions :T :U : : action,post-table procedural actions and objects(ddl) : : : :TS action,post-table procedural actions and objects(pu) : : :F : action,post-table procedural actions and objects :T :U : : action,pre-table actions(ddl) : : : :TS action,pre-table actions(pu) : : :F : action,pre-table actions :T :U : : action,pre-table procedural actions(ddl) : : : :TS action,pre-table procedural actions(pu) : : :F : action,pre-table procedural actions :T :U : : alias,directory aliases (all) : : :F : analyze,analyze cluster(pu) : : :F : analyze,analyze table(pu) : : :F : analyze,analyze table : :U : : analyze,analyze tables :T : : : auditing,auditing information(pu) : : :F : auditing,auditing information :T :U : : auditing,default and system auditing : : :F : auditing,system auditing(pu) : : :F : comment,column and table comments(ddl) : : : :TS comment,column and table comments(pu) : : :F : comment,column and table comments :T :U : : constraint,referential integrity constraints (all)(pu) : : :F : constraint,referential integrity constraints(ddl) : : : :TS constraint,referential integrity constraints : :U : : constraint,table constraints (primary, unique, check)(ddl) : : : :TS constraint,table constraints (primary, unique, check) :T :U : : constraint,table constraints(primary, unique, check)(pu) : : :F : constraint,table referential constraints :T :U : : context,application contexts : : :F : data,nested table data(pu) : : :F : data,nested table data :T :U : : data,table data by partition(pu) : : :F : data,table data by partition :T :U : : definition,cluster definitions (all) : : :F : definition,cluster definitions : :U : :TS definition,object type definitions used by table(pu) : : :F : definition,object type definitions used by table :T :U : : definition,object type definitions used by the table(ddl) : : : :TS definition,table definition (table rows are not included)(ddl) : : : :TS definition,table definitions(pu) : : :F : definition,table definitions :T :U : : definition,tablespace definitions : : :F : definition,user definitions : : :F : dimension,dimensions(pu) : : :F : dimension,dimensions : :U : : function,stored procedures, packages, and functions (all)(pu) : : :F : function,user stored procedures, packages, and functions : :U : : grant,role grants : : :F : grant,system privilege grants : : :F : grant,table grants (owner's) :T :U : : grant,table grants(ddl) : : : :TS grant,table grants(pu) : : :F : index,bitmap indexes(note; not functional or domain indexes)(ddl: : : :TS index,indexes owned by other users(as pu) :T : : : index,indextypes(pu) : : :F : index,indextypes : :U : : index,table indexes (owner's) :T :U : : index,table indexes(ddl) : : : :TS index,table indexes(pu) : : :F : library,foreign function libraries (all) : : :F : library,foreign function libraries : :U : : link,database links : :U :F : log,snapshot logs (all)(pu) : : :F : log,snapshot logs : :U : : materialized view,snapshot,snapshots and materialized views : :U : : materialized view,snapshots and materialized views (all)(pu) : : :F : object,object types (all) : : :F : object,object types : :U : : object,post-schema procedural actions and objects(pu) : : :F : object,post-schema procedural actions and objects : :U : : object,post-table procedural actions and objects(pu) : : :F : object,post-table procedural actions and objects : :U : : operator,operators(pu) : : :F : operator,operators : :U : : package,stored procedures, packages, and functions (all)(pu) : : :F : package,user stored procedures, packages, and functions : :U : : password,password history(pu) : : :F : procedure,procedural objects : :U : : procedure,stored procedures, packages, and functions (all)(pu) : : :F : procedure,user stored procedures, packages, and functions : :U : : profile,profiles : : :F : queue,job queues (all)(pu) : : :F : queue,job queues : :U : : quota,tablespace quotas : : :F : refresh,refresh groups and children (all)(pu) : : :F : refresh,refresh groups : :U : : resource,resource costs : : :F : role,default roles : : :F : role,roles : : :F : rollback,rollback segment definitions : : :F : security,security policies for table :T :U : : sequence,sequence numbers : :U :F : snapshot,snapshots and materialized views (all)(pu) : : :F : snapshot,snapshots and materialized views : :U : : synonym,synonyms (all)(pu) : : :F : synonyms,private synonyms : :U : : trigger,table triggers (owner's) :T : : : trigger,triggers (all)(pu) : : :F : trigger,triggers owned by other users(as pu) :T : : : trigger,triggers(2) : :U : : trigger,triggers(ddl) : : : :TS view,views (all)(pu) : : :F : --------------------------------------- Table MODE Export --------------------------------------- action,post-table actions :T :U : : action,post-table procedural actions and objects :T :U : : action,pre-table actions :T :U : : action,pre-table procedural actions :T :U : : analyze,analyze tables :T : : : auditing,auditing information :T :U : : comment,column and table comments :T :U : : constraint,table constraints (primary, unique, check) :T :U : : constraint,table referential constraints :T :U : : data,nested table data :T :U : : data,table data by partition :T :U : : definition,object type definitions used by table :T :U : : definition,table definitions :T :U : : grant,table grants (owner's) :T :U : : index,indexes owned by other users(as pu) :T : : : index,table indexes (owner's) :T :U : : security,security policies for table :T :U : : trigger,table triggers (owner's) :T : : : trigger,triggers owned by other users(as pu) :T : : : --------------------------------------- User MODE Export --------------------------------------- action,post-schema procedural actions and objects : :U : : action,post-table actions :T :U : : action,post-table procedural actions and objects :T :U : : action,pre-table actions :T :U : : action,pre-table procedural actions :T :U : : analyze,analyze table : :U : : auditing,auditing information :T :U : : comment,column and table comments :T :U : : constraint,referential integrity constraints : :U : : constraint,table constraints (primary, unique, check) :T :U : : constraint,table referential constraints :T :U : : data,nested table data :T :U : : data,table data by partition :T :U : : definition,cluster definitions : :U : :TS definition,object type definitions used by table :T :U : : definition,table definitions :T :U : : dimension,dimensions : :U : : function,user stored procedures, packages, and functions : :U : : grant,table grants (owner's) :T :U : : index,indextypes : :U : : index,table indexes (owner's) :T :U : : library,foreign function libraries : :U : : link,database links : :U :F : log,snapshot logs : :U : : materialized view,snapshot,snapshots and materialized views : :U : : object,object types : :U : : object,post-schema procedural actions and objects : :U : : object,post-table procedural actions and objects : :U : : operator,operators : :U : : package,user stored procedures, packages, and functions : :U : : procedure,procedural objects : :U : : procedure,user stored procedures, packages, and functions : :U : : queue,job queues : :U : : refresh,refresh groups : :U : : security,security policies for table :T :U : : sequence,sequence numbers : :U :F : snapshot,snapshots and materialized views : :U : : synonyms,private synonyms : :U : : trigger,triggers(2) : :U : : --------------------------------------- Full Database MODE Export --------------------------------------- action,post-schema procedural actions and objects(pu) : : :F : action,post-table actions(pu) : : :F : action,post-table procedural actions and objects(pu) : : :F : action,pre-table actions(pu) : : :F : action,pre-table procedural actions(pu) : : :F : alias,directory aliases (all) : : :F : analyze,analyze cluster(pu) : : :F : analyze,analyze table(pu) : : :F : auditing,auditing information(pu) : : :F : auditing,default and system auditing : : :F : auditing,system auditing(pu) : : :F : comment,column and table comments(pu) : : :F : constraint,referential integrity constraints (all)(pu) : : :F : constraint,table constraints(primary, unique, check)(pu) : : :F : context,application contexts : : :F : data,nested table data(pu) : : :F : data,table data by partition(pu) : : :F : definition,cluster definitions (all) : : :F : definition,object type definitions used by table(pu) : : :F : definition,table definitions(pu) : : :F : definition,tablespace definitions : : :F : definition,user definitions : : :F : dimension,dimensions(pu) : : :F : function,stored procedures, packages, and functions (all)(pu) : : :F : grant,role grants : : :F : grant,system privilege grants : : :F : grant,table grants(pu) : : :F : index,indextypes(pu) : : :F : index,table indexes(pu) : : :F : library,foreign function libraries (all) : : :F : link,database links : :U :F : log,snapshot logs (all)(pu) : : :F : materialized view,snapshots and materialized views (all)(pu) : : :F : object,object types (all) : : :F : object,post-schema procedural actions and objects(pu) : : :F : object,post-table procedural actions and objects(pu) : : :F : operator,operators(pu) : : :F : package,stored procedures, packages, and functions (all)(pu) : : :F : password,password history(pu) : : :F : procedure,stored procedures, packages, and functions (all)(pu) : : :F : profile,profiles : : :F : queue,job queues (all)(pu) : : :F : quota,tablespace quotas : : :F : refresh,refresh groups and children (all)(pu) : : :F : resource,resource costs : : :F : role,default roles : : :F : role,roles : : :F : rollback,rollback segment definitions : : :F : sequence,sequence numbers : :U :F : snapshot,snapshots and materialized views (all)(pu) : : :F : synonym,synonyms (all)(pu) : : :F : trigger,triggers (all)(pu) : : :F : view,views (all)(pu) : : :F : --------------------------------------- Tablespace MODE Export --------------------------------------- action,post-table actions(ddl) : : : :TS action,post-table procedural actions and objects(ddl) : : : :TS action,pre-table actions(ddl) : : : :TS action,pre-table procedural actions(ddl) : : : :TS comment,column and table comments(ddl) : : : :TS constraint,referential integrity constraints(ddl) : : : :TS constraint,table constraints (primary, unique, check)(ddl) : : : :TS definition,cluster definitions : :U : :TS definition,object type definitions used by the table(ddl) : : : :TS definition,table definition (table rows are not included)(ddl) : : : :TS grant,table grants(ddl) : : : :TS index,bitmap indexes(note; not functional or domain indexes)(ddl: : : :TS index,table indexes(ddl) : : : :TS trigger,triggers(ddl) : : : :TS
Appendix B; User And Full Database Modes Compared
-------------------------------------------------------------------------------------
This table compares those components exported in user mode and full database mode. An * in front of the component is for a component that is exported ONLY in full database mode. Appendix C gives a definition and/or comments for that component.
action,post-schema procedural actions and objects : :User : : action,post-schema procedural actions and objects(pu) : : :Full : action,post-table actions :Table :User : : action,post-table actions(pu) : : :Full : action,post-table procedural actions and objects :Table :User : : action,post-table procedural actions and objects(pu) : : :Full : action,pre-table actions :Table :User : : action,pre-table actions(pu) : : :Full : action,pre-table procedural actions :Table :User : : action,pre-table procedural actions(pu) : : :Full : *alias,directory aliases (all) : : :Full : *analyze,analyze cluster(pu) : : :Full : analyze,analyze table : :User : : analyze,analyze table(pu) : : :Full : auditing,auditing information :Table :User : : auditing,auditing information(pu) : : :Full : *auditing,default and system auditing : : :Full : *auditing,system auditing(pu) : : :Full : comment,column and table comments :Table :User : : comment,column and table comments(pu) : : :Full : constraint,referential integrity constraints : :User : : constraint,referential integrity constraints (all)(pu) : : :Full : constraint,table constraints (primary, unique, check) :Table :User : : constraint,table constraints(primary, unique, check)(pu) : : :Full : constraint,table referential constraints :Table :User : : *context,application contexts : : :Full : data,nested table data :Table :User : : data,nested table data(pu) : : :Full : data,table data by partition :Table :User : : data,table data by partition(pu) : : :Full : definition,cluster definitions : :User : :Tablespace definition,cluster definitions (all) : : :Full : definition,object type definitions used by table :Table :User : : definition,object type definitions used by table(pu) : : :Full : definition,table definitions :Table :User : : definition,table definitions(pu) : : :Full : *definition,tablespace definitions : : :Full : *definition,user definitions : : :Full : dimension,dimensions : :User : : dimension,dimensions(pu) : : :Full : function,stored procedures, packages, and functions (all)(pu) : : :Full : function,user stored procedures, packages, and functions : :User : : grant,role grants : : :Full : grant,system privilege grants : : :Full : grant,table grants (owner's) :Table :User : : grant,table grants(pu) : : :Full : index,indextypes : :User : : index,indextypes(pu) : : :Full : index,table indexes (owner's) :Table :User : : index,table indexes(pu) : : :Full : library,foreign function libraries : :User : : library,foreign function libraries (all) : : :Full : link,database links : :User :Full : log,snapshot logs : :User : : log,snapshot logs (all)(pu) : : :Full : materialized view,snapshot,snapshots and materialized views : :User : : materialized view,snapshots and materialized views (all)(pu) : : :Full : object,object types : :User : : object,object types (all) : : :Full : object,post-schema procedural actions and objects : :User : : object,post-schema procedural actions and objects(pu) : : :Full : object,post-table procedural actions and objects : :User : : object,post-table procedural actions and objects(pu) : : :Full : operator,operators : :User : : operator,operators(pu) : : :Full : package,stored procedures, packages, and functions (all)(pu) : : :Full : package,user stored procedures, packages, and functions : :User : : *password,password history(pu) : : :Full : procedure,procedural objects : :User : : procedure,stored procedures, packages, and functions (all)(pu) : : :Full : procedure,user stored procedures, packages, and functions : :User : : *profile,profiles : : :Full : queue,job queues : :User : : queue,job queues (all)(pu) : : :Full : *quota,tablespace quotas : : :Full : refresh,refresh groups : :User : : refresh,refresh groups and children (all)(pu) : : :Full : *resource,resource costs : : :Full : *role,default roles : : :Full : *role,roles : : :Full : *rollback,rollback segment definitions : : :Full : security,security policies for table :Table :User : : sequence,sequence numbers : :User :Full : snapshot,snapshots and materialized views : :User : : snapshot,snapshots and materialized views (all)(pu) : : :Full : synonym,synonyms (all)(pu) : : :Full : synonyms,private synonyms : :User : : trigger,triggers (all)(pu) : : :Full : trigger,triggers(2) : :User : : *view,views (all)(pu) : : :Full :
Appendix C: Descriptions of Components Exported in Full Database Mode
--------------------------------------------------------------------------------------
Definition and/or comments about components exported in full database mode only when compared to user mode.
Analyze Cluster
-----------------------------------
Cluster statistics gathered by the ANALYZE command. Stored in the Data Dictionary.
Application Context
-----------------------------------
Application context facilitates the implementation of fine-grained access control. It allows you to implement security policies with functions and then associate those security policies with applications. Each application can have its own application-specific context. Users are not allowed to arbitrarily change their context (for example, through SQL*Plus).
Application contexts permit flexible, parameter-based access control, based on attributes of interest to an application. For example, context attributes for a human resources application could include "position", "organizational unit", and "country" while attributes for an order-entry control might be "customer number" and "sales region".
Auditing - System and Default
-----------------------------------
Auditing is a set of ORACLE installation and data dictionary features that allow the DBA and users to track usage of the database. The DBA can set default auditing activity. The auditing information is stored in the data dictionary.
Default Roles
-----------------------------------
When a user is first created the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. The ALTER USER command is used to change the user's default roles.
Directory Alias
-----------------------------------
A "DIRECTORY" in Oracle is an alias for an operating system physical directory. For columns of type BFILE, Oracle does not export or replicate the BFILE contents. Export and Import only propagate the names of the files and the directory aliases referenced by the BFILE columns. Additonally, Oracle does not validate or modify the directory alias based on the location of the table after import.
Password History
-----------------------------------
As of Oracle8, passwords can expire, and accounts may be locked due to repeated failed attempts to connect. When you change your password, a password history may be maintained in order to prevent reuse of previous passwords.
Profiles
-----------------------------------
A profile is a set of resource consumption limits assigned to users of the Oracle database.
Resource Costs
-----------------------------------
Each system has its own characteristics; some system resources may be more valuable than others. Oracle enables you to give each system resource a cost. Costs weight each system resource at the database level. Costs are only applied to the composite limit of a profile; costs do not apply to set individual resource limits explicitly.
Roles
-----------------------------------
A set or privileges that a user can grant to another user.
Role Grants
-----------------------------------
Privileges granted to a role.
Rollback Segment Definitions
-----------------------------------
A rollback segment is a storage space within a tablespace that holds transaction information used to guarantee data integrity during a rollback and used to provide read consistency across multiple transactions.
System Privilege Grants
-----------------------------------
System privileges granted.
Tablespace Definitions
-----------------------------------
Data dictionary information about the database tablespaces.
Tablespace Quotas
-----------------------------------
Oracle can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. The tablespace quota security feature permits selective control over the amount of disk space that can be consumed by the objects of specific schemas.
User Definitions
-----------------------------------
Data dictionary information about the database users.
View
-----------------------------------
A view is a datasbase object that is a logical representaion of a table. It is derived from a table but has no storage of its own and often may be used in the same manner as a table.
===============================================================================================