Oracle SQL参考

索引：

创建表
修改表
创建完整性约束的表
增加/修改完整性约束
设置约束的时效
创建索引
创建视图
创建序列
创建同义词
创建/修改用户
创建操作系统用户
权限管理
设置表空间限额
资源限制
管理表空间
控制文件
段、区间和数据块
回滚段
表分区
索引分区

1.创建表

CREATE TABLE [Schema.]table_name
( column datatype [DEFAULT expression]
[, column datatype [DEFAULT expression] ]
[… other column .. ]
)

2.修改表

ALTER TABLE [Schema.]table_name
[ ADD column datatype [ DEFAULT expression] ]
[ MODIFY column [datatype] [DEFAULT expression] ]
[ DROP column ]

3.创建完整性约束的表

CREATE TABLE [Schema.]table_name (
{ column datatype [DEFAULT expression]
  [CONSTRAINT constraint]
  {  [ NOT] NULL
     | {UNIQUE|PRIMARY KEY
  }
  | REFERENCES [ Schema.]table [(column)] [ON DELETE CASCADE]
  |CHECK (condition)
}
[another constraint specification ]
| [CONSTRAINT constraint ]
{ {UNIQUE|PRIMARY KEY} (column [, column] …)
  | FOREIGN KEY (column [, column] …) REFERENCES \
         [Schema.] table [(column [, column] …)] \
         [ ON DELETE {CASCADE|SET NULL}]
  | CHECK (condition)
}
[, … other columns/constraints or constraints ]
)

4.增加/修改完整性约束

ALTER TABLE [Schema.] table_name
 ADD [CONSTRAINT constraint]
 { {UNIQUE|PRIMARY KEY} (column [, column] …)
 | FOREIGN KEY (column [, column] …) REFERENCE \
        [Schema.] table [ (column [, column] …)] \
        [ ON DELETE {CASCADE|SET NULL}]
 | CHECK （condition） }

5.设置约束的时效

SET CONSTRAINT[S]
{ [Schema.]constraint [, [Schema.]constraint] …
| ALL}
{IMMEDIATE | DEFERRED }

6.创建索引

CREATE [UNIQUE] INDEX  [Schema.] index
  ON  [Schema.] table
  ( column [ASC|DESC] [, column [ASC|DESC] ] …)

7.创建视图

CREATE [ OR REPLACE] VIEW [Schema.] view
AS subquery
[ WITH READ ONLY ]

8.创建序列

序列是一个能生成一系列唯一整数值的模式对象，它仅适用于使用简单数值列作为主键的表。

CREATE SEQUENCE [Schema.] sequence
[ START WITH integer ]
[ INCREMENT BY integer ]
[ MAXVALUE integer | NOMAXVALUE ]
[ MINVALUE integer | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ ORDER | NOORDER ]

使用方法：

CREATE SEQUENCE order_ids
  START WITH 2
  INCREMENT BY 1
  NOMAXVALUE;

为了替用户生成一个新的序列号，SQL语句必须引用该序列和它的NEXTVAL伪列。如：

INSERT INTO orders( id, c_id, orderdate, status )
VALUES ( order_ids.NEXTVAL, 2, ’18-JUN-99’, ‘B’ );

使用当前的序列号，使用CURRVAL伪列,可以任意次重用当前的序列号。如：

INSERT INTO items( o_id, id, quantity)
  VALUES(order_ids.CURRVAL, 1, 1 );
INSERT INTO items( o_id, id, quantity)
  VALUES(order_ids.CURRVAL, 2, 4 );

9.创建同义词

同义词是存储在数据库中的表、视图、序列或其他模式对象的别名。有公用同义词和私有同义词。

CREATE [PUBLIC] SYNONYM [Schema.] synonym
  FOR [Schema.] object

10.创建/修改用户

{CREATE|ALTER} USER user
 IDENTIFIED BY password
 [DEFAULT TABLESPACE tablespace ]
 [TEMPORARY TABLESPACE tablespace ]
 [PASSWORD EXPIRE]
 [ACCOUNT {LOCK|UNLOCK}]

可指定用户缺省表空间，缺省为SYSTEM表空间。

可指定用户临时表空间，缺省临时表空间为SYSTEM空间。

系统缺省用户帐号：

SYS/CHANGE_ON_INSTALL：拥有数据库的数据字典对象。
SYSTEM/MANAGER：缺省的数据库管理员。

11.创建操作系统用户

创建操作系统用户方法：

对于Oracle7.x：

create user ops$username identified externally …;

对于Oracle8i：

create user username identified externally…;

其中username是操作系统的用户。

使用时，以username用户登陆，执行“sqlplus /”即可，不需输入用户名和密码。

12.权限管理

(1).系统权限常用列表

权限	说明
CREATE SESSION	能连接到数据库服务器并建立数据库会话
CREATE TABLE	能在自己的模式中创建表
CREATE ANY TABLE	能在数据库的任何模式中创建表
CREATE ANY TYPE	能在数据库的任何模式中创建类型及关联类型体
SELECT ANY TABLE	能查询数据库中的任何表
EXECUTE ANY PROCEDURE	能在数据库中执行任何存储过程、存储函数或打包部件
EXECUTE ANY TYPE	能引用和执行数据库中任何类型的方法

(2).授予用户系统权限

GRANT privilege [, privilege ] …
 TO user [, user ] …
 [WITH ADMIN OPTION ]

要向所有用户授权，可以使用PUBLIC。

(3).撤销用户系统权限

REVOKE privilege [, privilege ] …
 FROM user [, user ] …

(4).对象权限列表

对象类型	对象权限	特别说明
表	SELECT、INSERT、UPDATE、DELETE、ALTER、INDEX、REFERENCES	INDEX权限允许为表创建索引；当声明了参照完整性约束时，REFERENCES允许参照这个表；可以为INSERT、UPDATE和REFERENCES权限授予列选择权。
视图	SELECT、INSERT、UPDATE、DELETE	可以为INSERT、UPDATE权限授予列选择权。
序列	SELECT、ALTER	SELECT权限能利用序列的NEXTVAL和CURRVAL来生成和重用序列号。
过程、函数、包、对象类型	EXECUTE

(5).授予用户对象权限

GRANT
 { privilege [, privilege ] …
 | ALL [PRIVILEGES] }
 ON { [Schema.]object | DIRECTORY directory }
 TO user [, user ] …
 [WITH GRANT OPTION]

(6).撤销用户对象权限

REVOKR
 { privilege [, privilege] …
 | ALL [PRIVILEGE ] }
 ON { [Schema.]object | DIRECTORY directory }
 FROM user [, user] …
 [CASCADE CONSTRAINTS]
 [FORCE]

(7).预定义数据库角色

角色	说明
CONNECT	能连接到数据库，在相关模式中创建表、视图、同义词、序列和一些其它的对象类型。
RESOURCE	能在相关模式中创建表、序列、数据镞、过程、函数、包、触发器、对象类型、基于函数的索引和用户自定义的操作符。
DBA	能执行任何数据库功能。
SELECT_CATALOG_ROLE	查询管理员（DBA_）数据字典视图
EXECUTE_CATALOG_ROLE	能运行预制的DBMS工具包

(8).创建角色

GRANT ROLE role
[ NOT IDENTIFIED | IDENTIFIED BY password ]

(9).给角色授予系统权限和角色

GRANT privilege|role [, privilege|role ] …
TO role [, role ] …
[WITH ADMIN OPTION]

(10).给角色授予对象权限

同“授予用户对象权限”。但不允许向一个角色授予用于表的REFERENCES对象权限。

(11).向用户授予角色

GRANT role [, role ] …
TO user [, user ] …
[WITH ADMIN OPTION ]

(12).设置用户的缺省角色

ALTER USER user
 [ DEFAULT ROLE
 { role [, role ] …
 | ALL [ EXCEPT role [, role ] … ]
 | NONE } ]

(13).启用和禁用角色

SET ROLE
 { role [IDENTIFIED BY password] \
 [, role [IDENTIFIED BY password]…]
 | ALL [EXCEPT role [, role ] .. ]
 | NONE }

13.设置表空间限额

ALTER USER user
 [ QUOTA {integer [ K | M] |UNLIMITED } ON tablespace] …

若要为所有的表空间授予无限制的定额，可直接给用户授予UNLIMITED TABLESPACE权限。如：

GRANT UNLIMITED TABLESPACE TO rlane；

不能向角色授予该权限。

14.资源限制

(1).启用资源限制

缺省时，启动数据库不实施资源限制。要启用/禁止该限制，使用如下命令：

ALTER SYSTEM
  SET RESOURCE_LIMIT = { TRUE | FALSE }

该命令只对当前实例有效，若要永久使用资源限制，编辑服务器的初始化参数文件，包含如下设置：

RESOURCE_LIMIT = TRUE

(2).创建资源配置表

CREATE PROFILE profile  LIMIT
 [SESSIONS_PER_USER {integer|UNLIMITED|DEFAULT}]
 [CPU_PER_SESSION {integer|UNLIMITED|DEFAULT}]
 [CPU_PER_CALL {integer|UNLIMITED|DEFAULT}]
 [CONNECT_TIME {integer|UNLIMITED|DEFAULT}]
 [IDLE_TIME {integer|UNLIMITED|DEFAULT}]
 [LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]
 [LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]
 [COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]
 [PRIVATE_SGA {integer [K|M]| UNLIMITED | DEFAULT}]
 [FAILED_LOGIN_ATTEMPTS {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_LIFE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_REUSE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_REUSE_MAX {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_LOCK_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_GRACE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_VERIFY_FUNCTION {NULL | function | DEFAULT } ]

(3).更改资源配置表设置

ALTER PROFILE profile LIMIT
[SESSIONS_PER_USER {integer|UNLIMITED|DEFAULT}]
 [CPU_PER_SESSION {integer|UNLIMITED|DEFAULT}]
 [CPU_PER_CALL {integer|UNLIMITED|DEFAULT}]
 [CONNECT_TIME {integer|UNLIMITED|DEFAULT}]
 [IDLE_TIME {integer|UNLIMITED|DEFAULT}]
 [LOGICAL_READS_PER_SESSION {integer | UNLIMITED | DEFAULT}]
 [LOGICAL_READS_PER_CALL {integer | UNLIMITED | DEFAULT}]
 [COMPOSITE_LIMIT {integer | UNLIMITED | DEFAULT}]
 [PRIVATE_SGA {integer [K|M]| UNLIMITED | DEFAULT}]
 [FAILED_LOGIN_ATTEMPTS {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_LIFE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_REUSE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_REUSE_MAX {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_LOCK_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_GRACE_TIME {integer | UNLIMITED | DEFAULT}]
 [PASSWORD_VERIFY_FUNCTION {NULL | function | DEFAULT } ]

(4).手工强制用户口令过期

ALTER USER user
 PASSWORD EXPIRE

(5).使用口令复杂性检查

将PASSWORD_VERIFY_FUNCTION设置为一个函数名，若不使用，则将其置为NULL。

函数可参考$ORACLE_HOME/rdbms/admin/utlpwdmg.sql。

(6).设置用户的配置表

ALTER USER user
 PROFILE profile

(7).更改数据库的缺省配置表

ALTER PROFILE default LIMIT …

缺省配置表名为profile。

15.管理表空间

(1).创建表空间

CREATE TABLESPACE tablespace
 DATAFILE
   ‘filename’ [SIZE integer [K|M] ] [REUSE]
    [ AUTOEXTEND
     ( OFF
     | ON
      [NEXT integer [K|M] ]
      [MAXSIZE {UNLIMITED |integer [K|M]}] } ]
   [, … other data file specifications … ]
 [ ONLINE|OFFLINE]
 [PERMANENT|TEMPORARY]

(2).修改数据文件的存储特性

ALTER DATABASE
 DATAFILE ‘filename’
 { RESIZE integer [K|M]
 | AUTOEXTEND
 { OFF
 | ON [NEXT integer [K|M]] [MAXSIZE {UNLIMITED | \
                            integer[K|M] } ] } }

(3).转化成临时表空间或永久表空间

ALTER TABLESPACE tablespace
 [ PERMANENT | TEMPORARY ]

(4).合并表空间数据文件中的空闲空间

ALTER TABLESPACE tablespace
 COALESCE

(5).控制表空间的可用性

ALTER TABLESPACE tablespace
 { ONLINE | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE
 | FOR RECOVER ] }

16.控制文件

查看当前的控制文件，可查表v$controlfile。或者查询v$parameter视图：

SELECT name, value FROM v$parameter
  WHERE name = ‘control_files’;

17.段、区间和数据块

Oracle为数据库对象(表、索引、数据镞等)分配一组连续的数据块作为区间。段是专用于数据库对象存储的所有区间的集合。

数据库可以包含许多不同类型的段，如数据段、索引段、LOB段、溢出段、会滚段和临时段。

当创建数据库时，可以指定数据块的大小，块的大小必须等于服务器操作系统块的大小或其倍数的大小。

(1).在指定的表空间创建表

CREATE TABLE table （
…
) TABLESPACE tablespace;

(2).控制表区间的数量和大小

在创建表语句之后增加STORAGE子句：

CREATE/ALTER TABLE table (
 …
)
STORAGE (
 [ INITIAL integer [K|M] ]
 [ NEXT integer [K|M] ]
 [ MINEXTENTS integer ]
 [ MAXEXTENTS { integer | UNLIMITED } ]
 [ PCTINCREASE integer ]

(3).更改表区间的设置

ALTER TABLE table
STORAGE (
 [ NEXT integer [K|M] ]
 [ MAXEXTENTS { integer | UNLIMITED } ]
 [ PCTINCREASE integer ]

(4).设置对象的数据块

在对象的CREATE命令后面增加如下设置：

[ PCTUSED integer ] [ PCTFREE integer ]
[ INITRANS integer ] [ MAXTRANS integer ]
[ STORAGE (
… other storage parameters …
[ FREELIST GROUPS integer ] [ FREELISTS integer ] ) ]

(5).为对象存储进行缺省设置

可以在对象使用的表空间中进行缺省设置：

CREATE/ALTER TABLESPACE tablespace
…
DEFAULT STORAGE (
[ INITIAL integer [K|M] ]
 [ NEXT integer [K|M] ]
 [ MINEXTENTS integer ]
 [ MAXEXTENTS { integer | UNLIMITED } ]
 [ PCTINCREASE integer ]

18.回滚段

Oracle使用回滚段来记录事务的回滚数据，回滚段是一个区间构成的环形结构。

每个Oracle数据库都至少有一个会滚段，即SYSTEM(系统)回滚段。当创建新的数据库后，应该创建附加的回滚段。

数据库中可以有多个回滚段。多个事务可以共享一个单独的回滚段，但一个事物从不使用超过一个的回滚段。

(1).创建回滚段

CREATE [PUBLIC] ROLLBACK SEGMENT segment
 [TABLESPACE tablespace ]
 [STORAGE (
  [INITIAL integer [K|M] ]
  [NEXT integer [K|M] ]
  [MINEXTENTS integer ]
  [MAXEXTENTS integer ]
  [OPTIMAL { integer [K|M] | NULL } ] ) ]

回滚段始终不少于两个区间(即MINEXTENTS>=2)。

(2).控制回滚段的可用性

ALTER ROLLBACK SEGMENT segment
 { ONLINE | OFFLINE }

(3).更改回滚段的存储设置

ALTER ROLLBACK SEGMENT segment
STORAGE (
  [NEXT integer [K|M] ]
  [MINEXTENTS integer ]
  [MAXEXTENTS integer ]
  [OPTIMAL { integer [K|M] | NULL } ] ) ]

(4).指定事务使用的回滚段

SET TRANSACTION USE ROLLBACK SEGMENT segment

19.表分区

(1).创建范围分区表

CREATE TABLE table
( … columns and constraints … )
PARTITION BY RANGE ( column [, column] … )
 ( PARTITION [ partition ]
  VALUES LESS THAN ( {value list | MAXVALUE} )
  [ PCTFREE integer ] [ PCTUSED integer ]
  [ INITRANS integer ] [ MAXTRANS integer ]
  [ TABLESPACE tablespace ]
  [ STORAGE ( … ) ]
[, … ] )

(2).更改范围分区表

增加：

ALTER TABLE table
 ADD PARTITION [ partition ]
  VALUES LESS THAN ( {value list | MAXVALUE} )
  [ PCTFREE integer ] [ PCTUSED integer ]
  [ INITRANS integer ] [ MAXTRANS integer ]
  [ TABLESPACE tablespace ]
  [ STORAGE ( … ) ]

删除：

ALTER TABLE table
 DROP PARTITION [ partition ]

(3).创建散列分区表

PARTITION BY HASH ( column [, column ] … )
 { PARTITION integer STORE IN ( tablespace [, tablespace ] … )
 | ( PARTITION [ parition ]
  [ PCTFREE integer ] [ PCTUSED integer ]
  [ INITRANS integer ] [ MAXTRANS integer ]
  [ TABLESPACE tablespace ]
  [ STORAGE ( … ) ]
 [, … ] ) }

20.索引分区

(1).创建全局索引

在CREATE INDEX中增加GLOBAL、PARTITION BY RANGE子句，形式同前面CREATE TABLE。

(2).创建局部、等同分区索引

在CREATE INDEX语句中增加LOCAL关键字。

Duangw