Oracle 12C SQLDeveloper：Microsoft Access迁移指南

需积分: 10 65 浏览量更新于2024-07-22 收藏 370KB PDF 举报

"Oracle 12C 001 - Oracle 12c Admin Document" 在Oracle 12c中，我们关注的是Oracle数据库的第十二个主要版本，这是一个先进的企业级关系数据库管理系统，旨在提供高性能、高可用性和可扩展性。Oracle 12c引入了多项创新功能，以满足现代云环境的需求。 1. **多租户架构 (Multitenant Architecture)** Oracle 12c的最大亮点是其多租户架构，这使得在一个容器数据库(Container Database, CDB)中可以容纳多个独立的数据库（Pluggable Databases, PDBs）。这种设计简化了管理、备份和恢复，以及资源的集中分配，提高了效率。 2. **SQL Developer for Microsoft Access Migrations** 描述中提到的Oracle SQL Developer是Oracle提供的一个强大的数据库开发工具，它支持对数据库的各种操作，包括迁移。对于从Microsoft Access迁移到Oracle的用户，此文档提供了额外的指南，帮助用户顺利进行数据和应用的迁移过程。SQL Developer提供了一个直观的界面，使得迁移工作变得更加简单，减少潜在问题。 3. **性能优化** Oracle 12c包含了多种性能优化特性，如自动工作负载 Repository (Automatic Workload Repository, AWR) 和 SQL Performance Analyzer，它们可以帮助管理员识别和解决性能瓶颈。此外，新的In-Memory Option加速了联机事务处理(OLTP)和联机分析处理(OLAP)查询。 4. **高级安全特性** Oracle 12c强化了安全策略，如Fine-Grained Auditing (FGA) 和 Transparent Data Encryption (TDE)，确保敏感数据的安全。同时，Secure Application Roles 提供了更细粒度的访问控制，防止未经授权的访问。 5. **存储优化** 通过Automatic Storage Management (ASM) 的改进，Oracle 12c提供了更好的存储管理和性能。ASM 支持自动数据条带化和镜像，以提高I/O性能和数据冗余。 6. **数据库云服务 (Database Cloud Service)** Oracle 12c 支持数据库云服务，允许用户在私有或公有云环境中部署和管理数据库，实现快速扩展和资源优化。 7. **PL/SQL增强** PL/SQL编程语言在12c中得到了增强，包括新的语法和函数，使得开发更加高效。例如，改进的类型系统和更好的异常处理机制。 8. **数据库内嵌式语言 (Embedded Language)** Oracle 12c 支持在数据库内部执行多种语言，如Java，这使得数据库应用程序可以利用多种技术栈。 9. **数据仓库优化** Oracle 12c 引入了新的列存储选项，专为数据仓库工作负载设计，显著提高了分析查询的速度。 10. **高可用性与灾难恢复** Oracle Data Guard 和 Active Data Guard 功能提供了强大的高可用性和灾难恢复解决方案，确保业务连续性。 Oracle 12c 提供了全面的数据库解决方案，涵盖了从开发到管理的各个层面，特别是在云计算和大数据时代，它提供了创新的技术来应对不断增长的挑战。Oracle SQL Developer作为迁移工具，是将其他数据库系统，如Microsoft Access，迁移到Oracle 12c的强大助手，确保平滑过渡和无缝集成。

Overview of the Microsoft Access Architecture

2-2 Oracle SQL Developer Supplementary Information for Microsoft Access Migrations

definitions, parameters, recordsets, tables, fields, indexes, relationships, users, and

groups from Microsoft Jet database engine.

Microsoft Jet database engine manages links to external data sources. Links to dBase,

Paradox, and Btrieve are made through an internal Jet ISAM driver interface. These

DLLs are included with Microsoft Access. Microsoft Jet database engine also supports

a link to ODBC that allows access to a wide range of DBMS servers.

2.1.1 Microsoft Jet Database Engine Recordsets

When Microsoft Jet database engine processes a query, it returns a recordset (a cursor)

for the resulting set of records. Microsoft Jet database engine supports snapshots and

dynasets types of recordsets.

A snapshot is a picture of data as it existed at the time the query was run. When

returning a snapshot, Microsoft Jet database engine runs the query to completion,

extracts all of the resulting rows and columns into a virtual table, and presents this

virtual table to the user. The user of a snapshot can perform a full range of operations

on a snapshot, such as query the snapshot and base forms and reports on the snapshot.

You cannot make changes to snapshot data. The snapshot data does not reflect changes

made by other users after you have opened it.

A dynaset is a live view of the data. When returning a dynaset, Microsoft Jet database

engine extracts the key values from the data and stores them in memory. When a user

requests rows of data from the dynaset, Microsoft Jet database engine fetches the rows

of interest by looking them up in the base tables via the internally stored key values.

Once you open a dynaset, the set of key values cannot change. Thus, while the data

pointed to by the key value may change and is reflected to the user, new rows added

after the query begins is not part of the set of key values and is not made visible to the

user. Rows that you delete after you run the keyset query are still part of the set of key

values. However, these rows are marked #DELETED# when displayed to the user.

The dynaset model is a powerful and flexible model that gives the user of PC-based

information the opportunity to browse large quantities of data and update the data.

When used with local data, dynasets are fast and effective. However, the dynaset

model presents one of the key performance challenges when Microsoft Access works

with an RDBMS server, such as Oracle.

2.1.2 Microsoft Jet Database Engine Multi-User Updates

Microsoft Jet database engine handles updates by multiple users by using optimistic

and pessimistic locks.

Using pessimistic locking, Microsoft Jet database engine places a hard lock on the data

page that contains the row being edited. Other users cannot start editing the locked

row until the lock is abandoned or the changes are written to disk.

Microsoft Jet database engine employs an optimistic locking scheme when working

with Oracle. An optimistic locking scheme does not place a hard lock on the source

tables. Instead, when you want to commit a change, Microsoft Jet database engine

checks to make sure that another user has not modified the data before it posts the

changes.

2.1.3 Microsoft Jet Database Engine Enforced Referential Integrity

Microsoft Jet database engine supports declarative referential integrity. This includes

primary key/foreign key relationships with one-to-one and one-to-many cardinality

with cascading UPDATEs and DELETES.

Overview of the Oracle Architecture

Migration Process 2-3

2.1.4 Microsoft Jet Database Engine Query Processor

The Microsoft Jet database engine query processor does not support a full

implementation of SQL. It optimizes queries, especially when the query references

both local tables and remote tables. Microsoft Jet database engine can connect to a

wide range of data sources and process queries against all of them. Transaction

support is limited to native file format database tables. Microsoft Jet database engine

relies on the transaction support of any RDBMS attached via ODBC.

2.1.5 Microsoft Jet Database Engine Transactions

Microsoft Jet database engine supports an explicit transaction model. Transactions are

not started until a BeginTrans statement is executed. Transactions are committed

with CommitTrans and aborted with Rollback. In addition to using transactions to

group units of work, you can use transactions to improve performance. If a program

makes numerous references to a table, grouping the work in a single transaction forces

Microsoft Jet database engine to perform the operation in memory. It then commits all

work to disk when the transaction is committed. However, this type of transaction

may not map directly to Oracle.

2.2 Overview of the Oracle Architecture

Oracle databases are powerful, flexible, and scalable relational database management

system (RDBMS) servers that run on a range of computer systems, from personal

computers to largest mainframes. Oracle is designed to run effectively in a

client/server environment and supports hundreds to thousands of users.

The Oracle architecture supports advanced server features, such as record locking with

version references, (not page locking as provided by Microsoft Access), advanced

query optimization, the PL/SQL programming language, data replication, distributed

database management, and other important features.

The architectural features discussed here are only a few of the features found in

Oracle. These features are focused on the elements that pertain to working with

Microsoft Access. See Oracle Database Concepts for a description of the Oracle

architecture. You can also see the following:

■ Oracle Database Administrator's Guide

■ Oracle Database PL/SQL Language Reference

2.2.1 Triggers and Stored Procedures

Oracle allows you to write and store code in the DBMS along with data. You can

associate trigger code with an UPDATE, INSERT, or DELETE event for each row or for

an entire table. You can also set a trigger to run before an event or after an event. For

example, you can set a trigger to run after a row is updated.

A stored procedure is a general routine, either function or subroutine, that is stored in

pre-compiled form on the server. A trigger may call stored procedures, but triggers are

only activated by specific database activity, such as the insertion of a row in a table.

When using Microsoft Access with Oracle, triggers and stored procedures play a role

in mapping the functionality of Microsoft Access to Oracle, such as in the support for

the Microsoft Access AUTONUMBER (COUNTER) data type in Oracle.

SQL Developer also converts certain Microsoft Access field validation rules to stored

procedures within Oracle.

Overview of the Oracle Architecture

2-4 Oracle SQL Developer Supplementary Information for Microsoft Access Migrations

2.2.2 PL/SQL Programming Language

The PL/SQL Programming Language is an ALGOL-based language, similar to Pascal.

PL/SQL is a modern, full-featured programming language with exception handling.

You can use PL/SQL to write stored programs and triggers in Oracle. It is also the

programming language used in many of the client-side tools of Oracle, such as Forms

from the Oracle Developer suite of products.

2.2.3 Sequences

A sequence is a unique number generator that is implemented in shared memory on a

server. It is designed to provide a set of unique values for PL/SQL programs for use as

primary keys. Sequences are designed for high performance applications that may

otherwise single-thread on table-based unique number generators. You use sequences,

along with supporting code in a trigger, to emulate the COUNTER field type in

Microsoft Access.

2.2.4 Transactions

Unlike Microsoft Access, Oracle supports an implicit transaction model. Each SQL

statement is part of a logical transaction. A logical transaction begins with the first

SQL statement and ends with a Commit or Rollback statement. Immediately after

either of these statements, a new transaction takes effect with the next SQL statement.

Microsoft Access developers use transactions to improve the performance of Microsoft

Jet database engine. Grouping database statements in a transaction forces Microsoft Jet

database engine to attempt to complete all database work in memory. Microsoft Jet

database engine defers writing to disk until the transaction is committed. When this

use of transactions is mapped to Oracle through the Open Database Connectivity

(ODBC), Microsoft Jet database engine sends only the outer most pair of Begin or

Commit transaction requests. Oracle keeps an open transaction during the entire

processing period. You must decide if you want this outcome when you move from

Microsoft Access to Oracle.

2.2.5 Database Users

For each migrated Microsoft Access database, two database users are created in the

Oracle Model:

■ A user with the same name as the Microsoft Access database unique name. This

user owns the Oracle objects that correspond to user-created objects in the

Microsoft Access database.

■ A user named OMWB_EMULATION. This user owns Oracle objects (such as

packages, stored procedures, and types) that emulate a limited subset of Microsoft

Access functions. (To see the emulation of these functions, expand the omwb_

emulation user tree in the Oracle Model hierarchy.)

2.2.6 Other Oracle Features

A database administrator has great flexibility when configuring Oracle. You can write

data on multiple disks for increased performance. You can tune rollback and recovery

options. You can allocate computer resources to optimize the configuration for each

server. Oracle also supports distributed processing, so you can distribute data across

multiple systems. Oracle offers a version of the server called Trusted Oracle Server for

applications that require a higher level of user and use authentication.

剩余59页未读，继续阅读

playgoplaygoplay

粉丝: 0

Oracle 12C SQLDeveloper：Microsoft Access迁移指南

《Oracle 12c数据库基础教程》教学课件—03Oracle数据库管理工具.pdf

window oracle12c补丁Opatch

Oracle DG 通过rman部署过程(单例Oracle 12c-Oracle 12c)

Oracle12c数据库基础教程Oracle12c数据库对象管理.pptx

Oracle 12c

oracle 12c

Oracle12c数据库基础教程Oracle12c数据库PLSQL语言基础.pptx

oracle12c-vagrant：一个自动配置Oracle 12c的无用信息框

Oracle12c数据库基础教程Oracle12c数据库游标、存储过程和触发器.pptx

官方资料：Oracle 12c Database New Features(Oracle 12c数据库新特性).pdf

最新资源