oracle数据库和sql server的区别

ORACLE与SQL SERVER的区别 

ORACLE的文件体系结构为： 

数据文件 DBF （真实数据）

日志文件 RDO

控制文件 CTL

参数文件 ORA

SQL SERVER的文件体系结构为： 

MDF （数据字典）

NDF （数据文件）

LDF （日志文件）

ORACLE存储结构： 

在ORACLE里有两个块参数PCTFREE(填充因子)和PCTUSED(复用因子),可控制块确定块本身何时有，何时没有足够的空间接受新信息(对块的存储情况的分析机制) 这样可降低数据行连接与行迁移的可能性。块的大小可设置(OLTP块和DSS块)

在ORACLE中，将连续的块组成区，可动态分配区(区的分配可以是等额的也可以是自增长的)可减少空间分配次数。

在ORACLEl里表可以分为多个段，段由多个区组成，每个段可指定分配在哪个表空间里(段的类型分为：数据段、索引段、回滚段、临时段、CASH段。ORACLE里还可对表进行分区，可按照用户定义的业务规则、条件或规范，物理的分开磁盘上的数据。

这样大大降低了磁盘争用的可能性。

ORACLE有七个基本表空间： 

·SYSTEM表空间(存放数据字典和数据管理自身所需的信息)

·RBS回滚表空间

·TEMP临时表空间

·TOOLS交互式表空间

·USERS用户默认表空间

·INDX索引表空间

·DBSYS福数据表空间

不同的数据分别放在不同的表空间(数据字典与真实数据分开存放),在ORACLE里基表(存储系统参数信息)是加密存储，任何人都无法访问。只能通过用户可视视图查看。

SQL SERVER 存储结构 

以页为最小分配单位，每个页为8K(不可控制，缺乏对页的存储情况的分析机制),

可将8个连续的页的组成一个‘扩展’，以进一步减少分配时所耗用的资源。(分配缺乏灵活性)，在SQL SERVER里数据以表的方式存放，而表是存放在数据库里。

SQL SERVER有五个基本数据库： 

·master(数据字典)

·mode(存放样版)

·tempdb(临时数据库)

·msdb(存放调度信息和日志信息)

·pubs(示例数据库)

真实数据与数据字典存放在一起。对系统参数信息无安全机制。

ORACLE登入管理： 

·SYSTEM/MANAGER (初始帐户)

·SYS/CHANGE_ON_NSTALL

·INSTALL/ORACLE(安装帐户)

·SCOTT/TIGER(示例数据库,测试用)

在ORACLE里默认只有三个系统用户，ORACLE是通过用户登入。

SQL SERVER登入管理： 

·SQL SERVER身份验证

·WINDOWS 身份验证

在SQL SERVER里是通过WINDOWS用户帐户或是用SQL SERVER身份验证连接数据库的。

SQL不是一种语言，而是对ORACLE数据库传输指令的一种方式。

SQL中NULL既不是字符也不是数字，它是缺省数据。ORACLE提供了NVL函数来解决。

ORACLE中的字符串连接为 string1‖string2 ，SQL中为string1+string2

集合 *** 作：在SQL中只有UNION（并 *** 作），ORACLE中包含MINUS（差 *** 作）、 INTERECT（交 *** 作）、UNION（并 *** 作）。

索引：

SQL的索引分为聚集索引和非聚集索引，还包括全文索引；

ORACLE的索引包括：B+树索引，Bitmap位图索引，函数索引，反序索引， 主键索引，散列索引，本地索引。

ORACLE的数据类型比较复杂，有基本数据类型，衍生型，列对象型，表对象型，结构体型；

SQL中的数据比较简单，只有一些简单的基本数据类型无法提供事务 *** 作。

CREATE SNAPSHOT [schema]snapshot

[ [PCTFREE integer] [PCTUSED integer]

[INITRANS integer] [MAXTRANS integer]

[TABLESPACE tablespace]

[STORAGE storage_clause]

[ USING INDEX [ PCTFREE integer | TABLESPACE tablespace

| INITTRANS integer | MAXTRANS integer

| STORAGE storage_clause ]

| [CLUSTER cluster (column [, column])] ]

[ REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXT date]]

AS subquery

schema

is the schema to contain the snapshot If you omit schema, Oracle

creates the snapshot in your schema

snapshot

is the name of the snapshot to be created

Oracle chooses names for the table, views, and index used to

maintain the snapshot by prefixing the snapshot name To limit

these names to 30 bytes and allow them to contain the entire

snapshot name, Oracle Corporation recommends that you limit your

snapshot names to 23 bytes

PCTFREE

PCTUSED

INITRANS

MAXTRANS

establishes values for these parameters for the internal table

Oracle uses to maintain the snapshot's data

TABLESPACE

specifies the tablespace in which the snapshot is to be created If

you omit this option, Oracle creates the snapshot in the default

tablespace of the owner of the snapshot's schema

STORAGE

establishes storage characteristics for the table Oracle uses to

maintain the snapshot's data

USING INDEX

specifies the storage characteristics for the index on a simple

snapshot If the USING INDEX clause not specified, the index is

create with the same tablespace and storage parameters as the

snapshot

CLUSTER

creates the snapshot as part of the specified cluster Since a

clustered snapshot uses the cluster's space allocation, do not use

the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the

TABLESPACE option, or the STORAGE clause in conjunction with the

CLUSTER option

REFRESH

specifies how and when Oracle automatically refreshes the snapshot:

FAST

specifies a fast refresh, or a refresh using only the

updated data stored in the snapshot log associated

with the master table

COMPLETE

specifies a complete refresh, or a refresh that re-

executes the snapshot's query

FORCE

specifies a fast refresh if one is possible or

complete refresh if a fast refresh is not possible

Oracle decides whether a fast refresh is possible at

refresh time

If you omit the FAST, COMPLETE, and FORCE options,

Oracle uses FORCE by default

START WITH

specifies a date expression for the first automatic

refresh time

NEXT

specifies a date expression for calculating the

interval between automatic refreshes

Both the START WITH and NEXT values must evaluate to a time in the

future If you omit the START WITH value, Oracle determines the

first automatic refresh time by evaluating the NEXT expression when

you create the snapshot If you specify a START WITH value but omit

the NEXT value, Oracle refreshes the snapshot only once If you

omit both the START WITH and NEXT values or if you omit the REFRESH

clause entirely, Oracle does not automatically refresh the snapshot

AS subquery

specifies the snapshot query When you create the snapshot, Oracle

executes this query and places the results in the snapshot The

select list can contain up to 253 expressions A snapshot query is

subject to the same restrictions as a view query

PREREQUISITES:

To create a snapshot in your own schema, you must have CREATE

SNAPSHOT system privilege To create a snapshot in another user's

schema, you must have CREATE ANY SNAPSHOT system privilege

Before a snapshot can be created, the user SYS must run the SQL

script DBMSSNAPSQL on both the database to contain the snapshot and

the database(s) containing the tables and views of the snapshot's

query This script creates the package SNAPSHOT which contains both

public and private stored procedures used for refreshing the

snapshot and purging the snapshot log The exact name and location

of this script may vary depending on your operating system

When you create a snapshot, Oracle creates a table, two views, and

an index in the schema of the snapshot Oracle uses these objects

to maintain the snapshot's data You must have the privileges

necessary to create these objects For information on these

privileges, see the CREATE TABLE, CREATE VIEW, and CREATE INDEX

commands

The owner of the schema containing the snapshot must have either

space quota on the tablespace to contain the snapshot or UNLIMITED

TABLESPACE system privilege Also, both you (the creator) and the

owner must also have the privileges necessary to issue the

snapshot's query

To create a snapshot, you must be using Oracle with the procedural

option To create a snapshot on a remote table or view, you must

also be using the distributed option

以上就是关于oracle数据库和sql server的区别全部的内容，包括:oracle数据库和sql server的区别、SQL清除语句、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！