每天都要做出不同的选择: oracle

7月19日

如何使用sqlplus的HELP功能：

说明：在默认安装中，是没有安装联机帮助文档的，因此我们要在使用sqlplus时使用联机

帮助文档，就必须自己运行两个脚本：

安装目录为：

$ORACLE_HOME\sqlplus\admin\help

命令：

Sqlplus system/<system password> //记得一定要使用system用户登陆

@$oracle_home\sqlplus\admin\help\helpbld.sql helpus.sql

//根据实际情况输入你的ORACLE_HOME

测试：

SQL> help

HELP

----

Accesses this command line help system. Enter HELP INDEX for a list

of topics.

In iSQL*Plus, click the Help button to display iSQL*Plus help.

HELP [topic]

//不足的是，在sqlplus中help命令只可以查到少数命令的使用：

SQL> help index

Enter Help [topic] for help.

@ COPY PAUSE SHUTDOWN

@@ DEFINE PRINT SPOOL

/ DEL PROMPT SQLPLUS

ACCEPT DESCRIBE QUIT START

APPEND DISCONNECT RECOVER STARTUP

ARCHIVE LOG EDIT REMARK STORE

ATTRIBUTE EXECUTE REPFOOTER TIMING

BREAK EXIT REPHEADER TTITLE

BTITLE GET RESERVED WORDS (SQL) UNDEFINE

CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE

CLEAR HOST RUN WHENEVER OSERROR

COLUMN INPUT SAVE WHENEVER SQLERROR

COMPUTE LIST SET

CONNECT PASSWORD SHOW

7月4日

完整性约束的状态

在学习的过程中，自己看了好几遍还是不能把约束的四种状态分清楚，经过一个高人指点，终于茅塞顿开，现在，把我的理解过程跟大家分享一下：

完整性约束可以是以下的其中一种转态：

l 禁止非验证（DISABLE NOVALIDATE）

l 禁止验证（DISABLE VALIDATE）

l 允许非验证（ENABLE NOVALIDATE）

l 允许验证（ENABLE VALIDATE）

怎么理解这四种状态呢，它们之间又有什么区别，我们可以这样来理解，数据库中的数据是分为新数据（New data）和现有数据（Existing data）的，而禁止（DISABLE）与允许(ENABLE)是针对新数据（New data）的，验证与非验证是针对现有数据（Existing data）的。

让我们先来理解允许、禁止以及验证非验证的含义：

如果约束是允许(ENABLE)的，当New data在插入或更新的时候会被检测，凡是不符合约束规则的数据都不允许被插入；

如果约束是禁止(DISABLE)的，当New data在插入或更新的时候，不管是否符合约束规则都能够进入数据库。

如果约束被设为非验证状态（NOVALIDATE），那么不管数据库中的现有数据(Existing data)是否符合约束规则，都不需要进行验证。

如果约束被设为验证状态(VALIDATE)，那么就会对数据库中所有的现有数据（Existing data）进行验证，而如果约束是从非验证状态转为验证状态，那么就要先把那些不满足条件的数据删除或更新。

禁止验证（DISABLE VALIDATE）是属于一个特殊的状态，如果约束处于这一状态，则对约束字段的任何修改都是不允许的，也就是不允许任何的DML操作，就相当与该约束字段被锁定了。另外，基于约束的索引也会被删除，约束被禁止。

18:59 | 添加评论 | 固定链接 | 写入日志 | oracle

6月22日

MTS/dedicate

oracle server有两个可选的配置：MTS，dedicate：

1、MTS（multiple thread servers）多线程服务器：
顾名思义，它用少数的共享服务器进程执行很多的客户连接。它是用调度器、共享服务器和队列来实现的。
一般来说，在支持大量用户的应用中，多线程服务器的性能更好。

MTS在小用户情况下比专有服务器要消耗更多的资源，因此在在线用户数较少的情况下不要把服务器设为MTS

2、Dedicate方式是指独占进程服务器方式:
Oracle为每个连接到instance的client启动一个专用的前台服务进程。这个进程仅仅为一个用户会话服务；

我们可以通过查看tnsname.ora（$oracle_home\network\admin\tnsname.ora）文件来查看oracle server到底是选择哪一种方式：

tnsname.ora

--------------------------------------------------------

ZYY_ZYY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = zyy)(PORT = 1521))

)

(CONNECT_DATA =

(sid = zyy)

(SERVER = DEDICATED)

)

---------------------------------------

其中，

如果：Server=DEDICATED
则为独占进程服务器
如果：server=SHARED
则为共享服务器

16:33 | 添加评论 | 固定链接 | 写入日志 | oracle

ORACLE客户端连服务器的注意事项（转）

1. 通过SQL*NET协议，ORACLE客户端连服务器时一般需要配置sqlnet.ora和tnsnames.ora。
它们默认的目录在 $ORACLE_HOME/network/admin 目录下

也可以设置环境变量TNS_ADMIN指向你想用的sqlnet.ora和tnsnames.ora目录
例如：
TNS_ADMIN=/home/oracle/config/9.0.1;export TNS_ADMIN

sqlnet.ora文件决定找数据库服务器别名的方式

默认的参数有
NAMES.DEFAULT_DOMAIN = WORLD
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)

如果你的ORACLE客户端和服务器默认的域名不一样，需要用#号注释第一行
#NAMES.DEFAULT_DOMAIN = WORLD使它不起作用。

   NAMES.DIRECTORY_PATH指定找服务器别名的顺序 (本地的tnsnames.ora文件, 命名服务器, 主机名方式)

   服务器的sqlnet.ora里可以设置检查客户端是否alive的时间间隔
   sqlnet.expire_time = 10

   tnsnames.ora文件里写数据库服务器别名的详细内容，有以下几种写法:

   # 一般的写法              APPDB =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.35)(PORT = 1521))
       )
       (CONNECT_DATA =
         (SERVICE_NAME = appdb)
       )
     )

   # 明确标明用dedicated方式连接数据库   APPD=
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.35)(PORT=1521))
     (CONNECT_DATA=
       (SERVICE_NAME=appdb)
       (SERVER=DEDICATED)))

   # 对多个listener端口做均衡负载方式连接数据库
   APPS =
     (DESCRIPTION =
     (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.35)(PORT = 1521))
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.35)(PORT = 1856))
      )
       (CONNECT_DATA =
         (SERVICE_NAME = appdb)
       )
     )

# 注意：如果数据库服务器用MTS，客户端程序需要用database link时最好明确指明客户端用dedicated直连方式,
#       不然会遇到很多跟分布式环境有关的ORACLE BUG。
#     一般情况下数据库服务器用直接的连接会好一些，除非你的实时数据库连接数接近1000。
2. /etc/hosts (UNIX)
   或者windows\hosts(WIN98) winnt\system32\drivers\etc\hosts (WIN2000)
   客户端需要写入数据库服务器IP地址和主机名的对应关系。

   127.0.0.1       localhost
   192.168.0.35    oracledb oracledb
   192.168.0.45    tomcat tomcat
   202.84.10.193   bj_db   bj_db

   有些时候我们配置好第一步后，tnsping 数据库服务器别名显示是成功的，
   但是sqlplus username/password@servicename不通,jdbc thin link 也不通的时候,
   一定不要忘了在客户端做这一步，原因可能是DNS服务器里没有设置这个服务器IP地址和主机名的对应关系。

   如果同时有私有IP和Internet上公有IP，私有IP写在前面，公有IP写在后面。

   编辑前最好留一个备份，增加一行时也最好用复制粘贴，避免编辑hosts时空格或者tab字符错误。

3. UNIX下ORACLE多数据库的环境，OS客户端需要配置下面两个环境变量

   ORACLE_SID=appdb;export ORACLE_SID
   TWO_TASK=appdb;export TWO_TASK

   来指定默认的目标数据库。

16:15 | 添加评论 | 固定链接 | 写入日志 | oracle

Listener的三种处理客户端请求的方式（转）

1. Dedicated Connection
当客户端发出请求后，listener直接建立一个新的进程处理客户端请求，
当连接建立后，Listener会放弃对客户端与该进程通讯的控制权。
所以该连接也称为dedicated 或者bequeath连接。注意该新建的进程
是在客户端请求时候Listener建立的。

2.Prespawned Connection
Prespawned connection是Dedicated connection的特例。如果你希望客户端
能与服务器快速建立连接，而又是Dedicated connection的话，
可以采用该方式。因为当客户端发送连接请求到服务器的时候，不需要等待Listener
去建立一个进程来服务该客户端，因为这些进程已经预先Spawn了(Listener在启动时候
就已经根据配置参数Spawn相应数量的进程，放在一个Prespawned processes Pool里面，
此参数PRESPAWN_MAX在listener.ora文件里面配置)。所以称之为Prespawned.
注意：PRESPAWN_MAX这个参数是指总的prespawned processes，你可以指定每种
协议prespawn的process(即pool_size参数)，当然，总数不能超出PRESPAWN_MAX的值。

3.Multithreaded Connection
这种连接方式也有相应的服务客户端的进程，称之为Dispatcher，
它和Prespawned process有点相似，也是预先Spawn的。不过跟Prespawned process
有两点区别:
a)dispatcher是在instance启动时候就spawn的，而prespawned process
是在listener启动时候spawn的。
b)dispatcher是共享的，也就是说，它不只是为一个客户端服务，当它接受了
客户端连接后，会把客户请求放在request queue里面，让另外一个进程
shared server process进行处理；跟着它就会服务另外一个客户端的请求。
c)dispatcher在完成与客户端的成功连接后，需要向Listener注册该连接(Prespawned
process没这个步骤)。需要这个步骤是因为可以让listener知道当前每个dispatcher的
负载情况，使dispatcher不会超出它能handle的最大连接数 (通过配置init.ora文件
里面的mts_dispatchers参数的子参数CON和SESS就可以决定dispatcher能handle的最大
连接数。不过别混淆CON和SESS子参数的意义，因为如果你需要实现connection
pooling的话，就要靠这两个参数)；另一方面，可以实现我们常说的load-balancing（负载平衡）。

11:56 | 添加评论 | 固定链接 | 写入日志 | oracle

lsnrctl命令的使用

获取lsnrctl的帮助信息：

C:\>lsnrctl help

LSNRCTL for 32-bit Windows: Version 9.2.0.4.0 - Production on 22-6月 -2006 11:05

:46

以下操作可用

星号 (*) 表示修改符或扩展命令：

start stop status

services version reload

save_config trace change_password

quit exit set*

show*

其中比较常用的有：

1、启动：

lsnrctl start

2、停止：

lsnrctl stop

3、当前Listener的状态：

Lsnrctl status

11:51 | 添加评论 | 固定链接 | 写入日志 | oracle

6月21日

查看数据字典

1、怎样查看哪些用户拥有SYSDBA、SYSOPER权限？

SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;

2、怎样查得数据库的SID ?

SQL> select name from v$database;

NAME
---------
ZYY

也可以直接查看 init.ora文件

3、查看数据库的版本信息，包含版本信息，核心版本信息，位数信息(32位或64位)等

SQL> select *
2 from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

12:23 | 添加评论 | 固定链接 | 写入日志 | oracle

6月20日

系统权限与用户权限

在数据库中，权限可以分为两种

1、系统权限

2、对象权限

系统权限：

该权限要求用户具有进行系统级活动的能力，即执行一个特定的数据库操作或一类数据库操作。这些操作包括创建、删除和修改表、视图、回滚段与过程。

对象权限：

每个对象权限使得用户能够对特定的对象执行特定的动作，比如表、视图、序列、过程、函数或包。

需要注意的是：

1、在授予对象权限时应该是：with grant option子句

在授予系统权限时应该用：with admin option子句

2、任何拥有含ADMIN OPTION的系统权限的用户的都可以从数据库中任何其他用户中回收相

应的权限，而回收者不一定是最初授予该权限的用户。

如果撤销的是对象权限，撤销者必须是该权限的初始授权者。

3、1)在撤销系统权限时，不会有任何连锁效应，不管授权时是否使用WITH ADMIN OPTION。

比如： DBA 给A授予了CREATE ANY TABLE 的权限，并且带有WITH ADMIN OPTION,

之后A给B授予了CREATE ANY TABLE 的权限。

当DBA从A那回收了CREATE ANY TABLE的权限后，A所创建的表仍然存在，但他

不能在创建任何新表了。B却仍然拥有表和CREATE ANY TABLE的系统权限，即

对A的权限的回收不影响到B.

2)在使用了WITH GRANT OPTION授予对象权限后，撤销对象权限会有连锁效应。

可以利用数据字典视图system_privilege_map获得完整的系统权限。

我们可以先看看视图system_privilege_map的结构：

SQL> desc system_privilege_map;
名称是否为空? 类型
----------------------------------------- -------- --------------------------

PRIVILEGE                                 NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(40)
PROPERTY                                  NOT NULL NUMBER

18:08 | 添加评论 | 固定链接 | 写入日志 | oracle

刚创建的用户怎样才能成功创建一个表

当一个用户刚被创建时是不具备任何权限的，因此要在该用户模式下创建表，需授予CREATE SESSION、CREATE TABLE、以及UNLIMITED TABLESPACE（或分配配额）权限，因为：

当用户要连接到数据库时必须拥有CREATE SESSION权限

当用户要创建表时必须拥有CREATE TABLE权限，同时用户还需要在表空间中拥有配额或者被授予UNLIMITED TABLESPACE。现在我们来做一个测试：

斜体代表在sys用户下的操作，加粗代表在用户test下的操作：

1）、创建用户TEST，密码为passwd_1:

SQL> CREATE USER test

2 IDENTIFIED BY passwd_1

3 ;

用户已创建

2）当用TEST连接数据库时：

SQL> conn test/passwd_1

ERROR:

ORA-01045: user TEST lacks CREATE SESSION privilege; logon denied

警告: 您不再连接到 ORACLE。

//因为缺少CREATE SESSION的权限，登陆失败。

3）利用SYS给TEST授予CREATE SESSION权限：

SQL> grant create session to test;

授权成功。

4）SQL> conn test/passwd_1

已连接。

5）在test的方案中创建表exam1:

SQL> create table exam1

2 (student_id int,

3 paper_id int);

create table exam1

*

ERROR 位于第 1 行:

ORA-01031: 权限不足

//因为未给TEST用户授予create table 权限，因此不能够创建表exam1.

6) 给TEST用户授予CRETE TABLE 权限

SQL> grant create table to test;

授权成功。

7）SQL> create table exam1

2 (student_id int,

3 paper_id int);

create table exam1

*

ERROR 位于第 1 行:

ORA-01950: 表空间'SYSTEM'中无权限

//因为在创建用户时没有指定表空间，因此默认的表空间是SYSTEM表空间，而TEST用户还需要在表空间SYSTEM中既没有拥有配额又没有被授予UNLIMITED TABLESPACE权限，因此对于这种情况有两种解决办法：

第一种方法：

SQL> alter user test

2 quota 15m on system;

用户已更改。

//在SYSTEM表空间中，给用户TEST分配15M的使用空间

SQL> create table exam1

2 (student_id int,

3 paper_id int);

表已创建

第二种方法：

SQL> grant unlimited tablespace to test

2 ;

授权成功。

SQL> create table exam2

2 (student_id int,

3 paper_id int);

表已创建

18:00 | 添加评论 | 固定链接 | 写入日志 | oracle