Oracle Database

Table of Contents

1. Oracle Database

In 1977, Larry Ellison, Bob Miner, and Ed Oates started the consultancy Software Development Laboratories(SDL), which became Relational Software, Inc. (RSI). In 1983, RSI became Oracle Systems Corporation and then later Oracle Corporation.

The first commercial RDBMS was built using PDP-11 assembler language. Although they created a commercial version of RDBMS in 1977, it wasn't available for sale until 1979 with the launch of Oracle version 2. The company decided against starting with version 1 because they were afraid that the term "version 1" might be viewed negatively in the marketplace. USA Air Force and then CIA were the first customers to use Oracle 2.

参考:
The History of Oracle: http://www.dba-oracle.com/t_history_oracle.htm
Introduction to the Oracle Database: http://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm
Database SQL Language Reference: http://docs.oracle.com/database/121/SQLRF/toc.htm
Database PL/SQL Language Reference: http://docs.oracle.com/database/121/LNPLS/toc.htm
Database Error Messages: http://docs.oracle.com/database/121/ERRMG/toc.htm

2. 安装和启动

下面以 Oracle Database 11gR2 为例(12c 也类似)介绍其在 Linux 中的安装过程。

Oracle Database 11g Release 2 can be download from: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Reference:
Database Installation Guides: http://docs.oracle.com/cd/E11882_01/nav/portal_11.htm
Database Installation Guide for Linux: http://docs.oracle.com/cd/E11882_01/install.112/e47689/toc.htm
http://blog.sina.com.cn/s/blog_b30d7fc80101df9n.html
http://wenku.baidu.com/view/95c925bdcc22bcd126ff0c50.html
http://www.2cto.com/database/201402/280150.html

2.1. 准备工作

There are many tasks that you must complete before you start installer. Only some significant preinstallation tasks are described here.

2.1.1. 创建用户和组

Oracle recommends that you create one software owner to own each Oracle software installation (typically, oracle, for the database software). You must create at least one software owner the first time you install Oracle software on the system.

Create Oracle Inventory group

# groupadd oinstall

Create OSDBA group

# groupadd dba

Add user oracle (software owner), specify the primary group using the -g option and any required secondary group using the -G option:

# useradd -g oinstall -G dba oracle

2.1.2. 准备安装目录和其权限

在启动安装向导前,先准备安装目录和其权限。

# mkdir -p /opt/oracle/product/11.2.0/db_1
# chown -R oracle:oinstall /opt/oracle
# chmod -R 775 /opt/oracle

2.2. 安装数据库软件

切换到之前创建的用户 oracle,解压下载的安装包,启动安装向导即可开始安装,按照安装向导的提示,一步一步操作即可。

$ su - oracle
$ unzip linux.x64_11gR2_database_1of2.zip
$ unzip linux.x64_11gR2_database_2of2.zip
$ ./database/runInstaller                  # 会启动图形界面的安装向导

说明 1:在安装过程中,如果选择"Install database software only",安装结束后仅仅是安装了软件,不会创建数据库,可以用 ./bin/dbca 工具创建和管理数据库。
说明 2:在安装过程中,如何发现系统配置不合要求,安装向导会提示你用 root 用户执行某个脚本,照做即可。
说明 3:安装向导会检测其依赖软件是否存在于系统中,但它只检测固定版本号的软件,如果系统中版本更高,安装向导也有提示依赖软件不存在。遇到这种情况直接忽略即可。

2.3. 创建数据库

用 Database Configuration Assistant (DBCA)创建和配置数据库比较简单,推荐使用。除此外,也可以用 CREATE DATABASE 语句创建数据库。

$ su - oracle
$ export ORACLE_HOME=/opt/oracle/product/11.2.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
$ dbca &

参考:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN002

2.3.1. SYS 用户和 SYSTEM 用户

在使用工具 dbca 或者使用 CREATE DATABASE 语句创建数据库时,你需要指定 SYS 用户和 SYSTEM 用户的密码。

SYS 用户和 SYSTEM 用户的用处及区别如下:
SYS: This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the SYS schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the SYS schema. The SYS user is granted the SYSDBA privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.
SYSTEM: This account can perform all administrative functions except the following: (1) Backup and recovery, (2) Database upgrade. While this account can be used to perform day-to-day administrative tasks

2.4. 配置网络

使用工具 netca (Network Configuration Assistant),可以用来创建和管理 tnsnames.ora 和 listener.ora 文件。

2.5. 启动和关闭实例

启动实例(假设实例名为 test1)步骤如下:

$ su - oracle
$ export ORACLE_SID=test1
$ sqlplus / as sysdba
SQL> startup
SQL> quit

参考:
Database Administrator's Guide, 3 Starting Up and Shutting Down: http://docs.oracle.com/cd/E11882_01/server.112/e25494/start.htm#ADMIN004

2.6. Tips

2.6.1. 什么是 Global Database Name 和 SID

使用工具 dbca 创建一个数据库时,需要用户填入 Global Database Name 和 SID(Oracle System Identifier)。

oracle_dbca_SID.png

Figure 1: Oracle SID

Global Database Name 和 SID 的含义和区别在 dbca 的界面中已经说得较详细了。
Global Database Name 用来唯一标记 Oracle 数据库,最后部分常是域名;而 SID 用来标记某一台机器上的 Oracle 实例。 在简单的配置环境中,它们可以相同。

注意: 如果没有指定 SERVICE_NAME,则 Global Database Name 就是 SERVICE_NAME。 配置文件 tnsnames.ora 中需要指定 SERVICE_NAME。

2.6.2. 解锁 scott 用户

scott 是 Oracle database 内置用户,默认没有启用,用下面方法可解锁 scott 用户:

$ su - oracle
$ sqlplus / as sysdba
SQL> alter user scott account unlock;
SQL> alter user scott identified by tiger;

参考:
http://stackoverflow.com/questions/9862364/how-to-login-to-the-scot-account-after-installing-oracle-11g

3. tnsnames.ora 和 listener.ora


在 oracle 安装目录$HOME/network/admin 下,有两个重要文件 tnsnames.ora 和 listener.ora,其功能描述如下:

tnsnames.ora
用在 oracle client 端,用户配置连接数据库的别名参数,就像系统中的 hosts 文件一样。
listener.ora
用在 oracle server 端,配置 oracle 服务端程序的监听办法,限制某些 ip 等配置。

tnsnames.ora 的基本格式如下:

<net_service_name> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <service_name>)
    )
  )

如:

ORA11 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ora11.xxx.yyy.com)
    )
  )

说明 1:net_service_name 可以随便给其设名字,在客户端连接数据库时需要这个名字,如: sqlplus user/pass@net_service_name
说明 2:service_name 是服务器端中设置的服务名,可以在服务器端运行 lsnrctl status 来查询 service_name。

参考:
Local Naming Parameters (tnsnames.ora): http://docs.oracle.com/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF007
Oracle Net Listener Parameters (listener.ora): http://docs.oracle.com/cd/E11882_01/network.112/e10835/listener.htm#NETRF008

3.1. 创建和管理 tnsnames.ora 和 listener.ora (netca, netmgr)

工具 netca (Network Configuration Assistant),可以用来创建和管理 tnsnames.ora 和 listener.ora 文件。

要创建和管理 tnsnames.ora 或 listerner.ora,可以分别选择 netca 欢迎界面中的 Local Net Service Name configuration 或 Listener configuration。

注:用工具 netmgr 也可以创建和管理 tnsnames.ora 和 listener.ora(注:netca 可看作是一个简化版本的 netmgr)

3.2. 临时修改 tnsnames.ora(无需编辑权限)

Create a copy of the tnsnames.ora file in a directory that you can write to, modify the file accordingly, then set the TNS_ADMIN environment variable to the location of that directory. eg:

$ cp $ORACLE_HOME/network/admin/tnsnames.ora /tmp/tnsnames.ora
# edit the /tmp/tnsnames.ora file to add your entries

# Set the $THS_ADMIN environment variable so that sqlplus knows where to look
$ export TNS_ADMIN=/tmp

参考:http://dba.stackexchange.com/questions/13075/how-to-use-sqlplus-to-connect-to-an-oracle-database-located-on-another-host-with

4. 启动 listener 和连接测试

4.1. listener 管理工具(lsnrctl)

lsnrctl 用于管理 oracle listener,它仅存在于 Oracle Server 中,在 Oracle Client 中没有!

输入 lsnrctl 命令后,进入交互状态。

如,查看 listener 状态(或直接运行 lsnrctl status ):

$ lsnrctl
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-JAN-2015 21:26:51

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                07-JAN-2015 17:15:32
Uptime                    21 days 4 hr. 7 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/oracle/app/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /home/oracle/app/diag/tnslsnr/xxx/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx.yyy.com)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "test01" has 1 instance(s).
  Instance "test01", status READY, has 1 handler(s) for this service...
The command completed successfully

如,启动 listener:

LSNRCTL> start

4.2. 连通测试工具(tnsping)

使用 tnsping net_service_name 可以进行连通测试。下面是一个运行例子:

$ tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 01-JAN-2016 19:06:30

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/app/product/11.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = orcl.xx.yy.com)))
OK (0 msec)

注:net_service_name 可以从文件 tnsnames.ora 中查看,参考节 3

5. Instance 和 Service

A database has at least one instance. An instance is comprised of a memory area called the System Global Area (SGA) and Oracle processes.

An Oracle database is represented to clients as a service; that is, the database performs work on behalf of clients. A database can have one or more services associated with it.

oracle_one_service_for_each_db.gif

Figure 2: One Service for each Database

oracle_multiple_services_associated_with_one_db.gif

Figure 3: Multiple Services Associated with One Database

The service name (sales.us.acme.com and finance.us.acme.com) is specified by the SERVICE_NAMES parameter in the initialization parameter file. The service name defaults to the global database name.

参考:http://docs.oracle.com/cd/B19306_01/network.102/b14212/concepts.htm

5.1. 查看服务器中运行了哪些 instance

实例(instance)对应着数据库“进程”,在 Linux 系统中,可以直接通过查看运行进程的名字得知当前系统运行了哪些实例。

如果系统中有两个运行实例:“orcl”和“test01”,那么执行命令 ps -ef | grep ora_ 会得到类似于下面的输出:

$ ps -ef | grep ora_
oracle    3572     1  0  2014 ?        00:04:32 ora_smco_test01
oracle    4035     1  0  2014 ?        00:04:30 ora_smco_orcl
oracle    8605     1  0 21:54 ?        00:00:00 ora_j000_test01
oracle    8607     1  0 21:54 ?        00:00:00 ora_j001_test01
oracle    8617     1  0 21:54 ?        00:00:00 ora_j000_orcl
oracle    8619     1  0 21:54 ?        00:00:00 ora_j001_orcl
user01    9118 11047  0 21:54 pts/70   00:00:00 grep ora_
oracle   13810     1  0  2014 ?        00:17:08 ora_pmon_test01
oracle   13813     1  0  2014 ?        04:26:51 ora_vktm_test01
oracle   13820     1  0  2014 ?        00:03:35 ora_gen0_test01
oracle   13824     1  0  2014 ?        00:05:37 ora_diag_test01
oracle   13826     1  0  2014 ?        00:04:00 ora_dbrm_test01
oracle   13830     1  0  2014 ?        00:09:07 ora_psp0_test01
oracle   13835     1  0  2014 ?        00:40:29 ora_dia0_test01
oracle   13838     1  0  2014 ?        00:03:44 ora_mman_test01
oracle   13840     1  0  2014 ?        00:08:59 ora_dbw0_test01
oracle   13842     1  0  2014 ?        00:11:16 ora_lgwr_test01
oracle   13847     1  0  2014 ?        00:20:24 ora_ckpt_test01
oracle   13849     1  0  2014 ?        00:05:25 ora_smon_test01
oracle   13854     1  0  2014 ?        00:01:23 ora_reco_test01
oracle   13856     1  0  2014 ?        00:17:25 ora_mmon_test01
oracle   13861     1  0  2014 ?        00:29:54 ora_mmnl_test01
oracle   13863     1  0  2014 ?        00:01:41 ora_d000_test01
oracle   13874     1  0  2014 ?        00:01:36 ora_s000_test01
oracle   15108     1  0  2014 ?        00:01:29 ora_qmnc_test01
oracle   15525     1  0  2014 ?        00:18:58 ora_cjq0_test01
oracle   16627     1  0  2014 ?        00:01:53 ora_q000_test01
oracle   16642     1  0  2014 ?        00:01:18 ora_q001_test01
oracle   17393     1  0 21:49 ?        00:00:00 ora_w000_orcl
oracle   20824     1  0  2014 ?        00:17:01 ora_pmon_orcl
oracle   20830     1  0  2014 ?        04:27:47 ora_vktm_orcl
oracle   20837     1  0  2014 ?        00:03:39 ora_gen0_orcl
oracle   20843     1  0  2014 ?        00:05:38 ora_diag_orcl
oracle   20848     1  0  2014 ?        00:04:01 ora_dbrm_orcl
oracle   20852     1  0  2014 ?        00:09:34 ora_psp0_orcl
oracle   20855     1  0  2014 ?        00:39:48 ora_dia0_orcl
oracle   20860     1  0  2014 ?        00:04:00 ora_mman_orcl
oracle   20865     1  0  2014 ?        00:08:53 ora_dbw0_orcl
oracle   20868     1  0  2014 ?        00:07:56 ora_lgwr_orcl
oracle   20870     1  0  2014 ?        00:20:34 ora_ckpt_orcl
oracle   20873     1  0  2014 ?        00:06:55 ora_smon_orcl
oracle   20878     1  0  2014 ?        00:01:24 ora_reco_orcl
oracle   20882     1  0  2014 ?        00:21:12 ora_mmon_orcl
oracle   20886     1  0  2014 ?        00:29:58 ora_mmnl_orcl
oracle   20888     1  0  2014 ?        00:01:40 ora_d000_orcl
oracle   20891     1  0  2014 ?        00:01:36 ora_s000_orcl
oracle   21954     1  0  2014 ?        00:01:30 ora_qmnc_orcl
oracle   22134     1  0  2014 ?        00:18:01 ora_cjq0_orcl
oracle   22542     1  0  2014 ?        00:01:17 ora_q000_orcl
oracle   22545     1  0  2014 ?        00:01:51 ora_q001_orcl
oracle   28841     1  0 21:47 ?        00:00:00 ora_w000_test01

5.2. database 名字和 instance 名字的区别

A database is a set of files (data, redo, ctl and so on)
An instance is a set of processes (SMON, PMON, DBWR, etc) and a shared memory segment (SGA).
A database may be mounted and opened by many INSTANCES (Parallel Server) concurrently.
An instance may mount and open ANY database -- however it may only open a single database at any time.

参考:
Difference between DB_NAME and Instance_Name: https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:533422350794

6. sqlplus

sqlplus 可以连接 Oracle 数据库。
假设 tnsnames.ora 中有类似下面配置:

<net_service_name> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = <service_name>)
    )
  )

可用下面格式连接数据库:

$ sqlplus user/pass@net_service_name

如果执行 sqlplus 的机器和想要连接的数据库服务器是同一台机器,除了用上面的方法外,还可以导出环境变量 ORACLE_SID 后,直接运行 sqlplus user/pass 即可连接到数据库了。

6.1. 使用“Easy Connect”连接数据库

Easy Connect 是一种方便的连接数据库方法,无需修改 tnsnames.ora 文件。

CONNECT username/password@[//]host[:port][/service_name]

如:

$ sqlplus /nolog
SQL > connect scott/tiger@xx.yy.com:1521/orcl

或者直接:

$ sqlplus scott/tiger@xx.yy.com:1521/orcl

参考:http://www.orafaq.com/wiki/EZCONNECT

6.2. 查看命令帮助

SQL> help command
或
SQL> ? command

例如,下面命令将显示 desc 命令的用法:

SQL> ? desc

6.3. 执行 sql 文件

方法一:在启动 sqlplus 时指定文件。
sqlplus usr/pw@sid @/path/to/file.sql

方法二:进入 sqlplus 后,再执行。

SQL> start file.sql
或
SQL> @file.sql

注:默认 sql 文件在 oracle 的 bin 目录或当前目录下,如果脚本在其它路径下可用其完整路径。

6.4. 运行操作系统命令

在操作系统命令前加 host 或感叹号即可,如:

SQL> host ls
或
SQL> ! ls

6.5. 把输出保存到文件,控制输出格式

保存输出内容到 1.log,屏幕上同时也会显示输出内容。

SQL> spool 1.log

关闭 SPOOL

SQL> spool off

The SPOOL output is driven by a few SQLPlus settings:

SET LINESIZE nn
maximum line width; if the output is longer it will wrap to display the contents of each result row.
SET TRIMSPOOL OFF|ON
if set OFF (the default), every output line will be padded to LINESIZE. If set ON, every output line will be trimmed.
SET PAGESIZE nn
number of lines to output for each repetition of the header. If set to zero, no header is output; just the detail.

在 sqlplus 中运行 select 语句时,如果发现输出格式很乱。首先可尝试把 LINESIZE 设置大些,如 set linesize 3000

6.6. 使用 autotrace

实例:查看 group by 内部细节
第一步,准备表和记录

SQL> CREATE TABLE tab1 (a integer, b varchar(10), c varchar(10));
SQL> INSERT INTO tab1 VALUES(5, 'ee', 'ee');
SQL> INSERT INTO tab1 VALUES(8, 'hh', 'hh');
SQL> INSERT INTO tab1 VALUES(2, 'bb', 'bb');

第二步,打开 autotrace

SQL> set autotrace on explain

第三步,直接测试和增加 NO_USE_HASH_AGGREGATION 后再测试 group by

SQL> select a,count(*) as num from tab1 group by a;

	 A	  NUM
---------- ----------
	 2	    1
	 5	    1
	 8	    1

Execution Plan
----------------------------------------------------------
Plan hash value: 2586220722

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	4 |    52 |	4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY	   |	  |	4 |    52 |	4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TAB1 |	4 |    52 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


SQL> select /*+ NO_USE_HASH_AGGREGATION */ a,count(*) as num from tab1 group by a;

	 A	  NUM
---------- ----------
	 2	    1
	 5	    1
	 8	    1

Execution Plan
----------------------------------------------------------
Plan hash value: 2411159224

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	4 |    52 |	4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY	   |	  |	4 |    52 |	4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TAB1 |	4 |    52 |	3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

从输出的结果中可以看到:前一种情况,内部使用的是 HASH GROUP BY,后一种情况,内部使用的是 SORT GROUP BY。

第四步,关闭 autotrace

SQL> set autotrace off

参考:"SQL*Plus Users Guide and Reference" Release 11.2, Chapter 8 Tuning SQL*Plus. Controlling the Autotrace Report

7. PL/SQL

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL.

PL/SQL includes procedural language elements such as conditions and loops.

参考:http://www.tutorialspoint.com/plsql/

7.1. Basic Syntax

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be nested within other PL/SQL blocks using BEGIN and END. Here is the basic structure of a PL/SQL block:

DECLARE
   <declarations section>
BEGIN
   <executable command(s)>
EXCEPTION
   <exception handling>
END;

说明 1:Declarations Section 和 Exception Handling Section 可以省略。
说明 2:如果程序在 SQL command line 中执行,在程序结束时要加个斜线 /

如 Hello World 程序:

$ cat 1.sql
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello world');
END;
/

在 sqlplus 中运行上面程序(要 SET SERVEROUTPUT ON 才能显示 DBMS_OUTPUT.PUT_LINE 的输出内容),如:

SQL> SET SERVEROUTPUT ON
SQL> @1.sql
Hello world

PL/SQL procedure successfully completed.

参考:http://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm

7.2. PL/SQL 实例:创建 table

下面 PL/SQL 例子会创建一个表 TAB1,当表 TAB1 已经存在时不会提示错误。

DECLARE
v_sql LONG;
BEGIN

v_sql := 'CREATE TABLE TAB1 (
  FOO NUMBER,
  BAR NUMBER
)';
EXECUTE IMMEDIATE v_sql;

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN
      NULL;  -- suppresses ORA-00955 exception (表已经存在时会报ORA-00955异常)
    ELSE
      RAISE;
    END IF;
END;
/

说明:也可以先检测表是否存在,不存在时再执行创建表的语句。请看下节例子。

7.3. PL/SQL 实例:创建 index

下面 PL/SQL 例子先检查索引 IDX_TAB1_FOO 是否存在,不存在时创建它。

DECLARE
    i INTEGER;
BEGIN
    SELECT COUNT(*) INTO i FROM user_indexes WHERE index_name = 'IDX_TAB1_FOO';
    IF i = 0 THEN
        EXECUTE IMMEDIATE 'CREATE INDEX IDX_TAB1_FOO ON TAB1(FOO)';
    END IF;
END;
/

7.4. PL/SQL 实例:基本 cursor 操作

DECLARE
  emp_no   emp.empno%type;
  emp_name emp.ename%type;

  CURSOR cur1 IS SELECT empno, ename FROM emp WHERE empno < 7700;

BEGIN
  DBMS_OUTPUT.PUT_LINE('Begin test.');

  OPEN cur1;

  LOOP
    FETCH cur1 INTO emp_no, emp_name;
    EXIT WHEN cur1%notfound;
    dbms_output.put_line('number:' || emp_no || ' name:' || emp_name);
  END LOOP;

  CLOSE cur1;

  DBMS_OUTPUT.PUT_LINE('Begin test.');

END;
/

运行测试如下:

SQL> SET SERVEROUTPUT ON
SQL> @1.sql
Begin test.
number:7369 name:SMITH
number:7499 name:ALLEN
number:7521 name:WARD
number:7566 name:JONES
number:7654 name:MARTIN
number:7698 name:BLAKE
Begin test.

PL/SQL procedure successfully completed.

8. SQL*LOADER(sqlldr)

8.1. What is SQL*Loader and what is it used for?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
SQL*Loader (sqlldr) is the utility to use for high performance data loads. The data can be loaded from any text file and inserted into the database.

8.2. How does one use the SQL*Loader utility?

One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:

$ sqlldr username@server/password control=loader.ctl
$ sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:

load data
 infile 'c:\data\mydata.csv'
 into table emp
 fields terminated by "," optionally enclosed by '"'
 ( empno, empname, sal, deptno )

The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20

Optionally, you can work with tabulation delimited files by using one of the following syntaxes:

fields terminated by "\t"
fields terminated by X'09'

Additionally, if your file was in Unicode, you could make the following addition.

load data
 CHARACTERSET UTF16
 infile 'c:\data\mydata.csv'
 into table emp
 fields terminated by "," optionally enclosed by '"'
 ( empno, empname, sal, deptno )

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data
 infile *
 replace
 into table departments
 (  dept     position (02:05) char(4),
    deptname position (08:27) char(20)
 )
begindata
COSC  COMPUTER SCIENCE
ENGL  ENGLISH LITERATURE
MATH  MATHEMATICS
POLY  POLITICAL SCIENCE

9. 备份和恢复工具(exp/imp, expdp/impdp)

Oracle 10g 以前常用 exp/imp 进行备份和恢复,Oracle 10g 中引入了更快的备份和恢复工具 Oracle Data Pump(expdp/impdp)。

参考:
http://www.dba-oracle.com/t_differences_imp_impdp_import_data_pump.htm
http://oracle-base.com/articles/10g/oracle-data-pump-10g.php

备份和恢复工具(exp/imp, expdp/impdp)和 SQL*LOADER 的区别:
SQL*Loader 用于加载格式化的文本文件,而 imp/impdp 工具是恢复用 exp/expdp 备份生成的二进制文件到数据库。

10. 优化器(RBO, CBO)

Oracle 的优化器有两种:
RBO(Rule-Based Optimizer): 基于规则的优化器
CBO(Cost-Based Optimizer): 基于代价的优化器

从 Oracle 10g 开始,RBO 已经被弃用,但是我们依然可以通过 Hint 方式来使用它。

sql_processing_overview.png

Figure 4: SQL Processing Overview(Oracle 9i)

参考:
http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm
http://blog.csdn.net/tianlesoftware/article/details/5824886
http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#PFGRF94565

11. NLS(National Language Support)

NLS (National Language Support) is used to define national date, number, currency and language settings. For example, it can be used to change the currency symbol from $ to € (Euro); the language from English to Dutch, etc.

有三个不同的字符集相关参数:

nls_database_parameters 数据库服务器字符集
可用 select * from nls_database_parameters 查看,其来源于 props$,是表示数据库的字符集。
nls_instance_parameters 客户端字符集
可用 select * from nls_instance_parameters 查看,其来源于 v$parameter(查询结果和 show parameter 一致),表示客户端的字符集的设置,可能是参数文件,环境变量或者是注册表会话字符集环境。
nls_session_parameters 会话字符集
可用 select * from nls_session_parameters 查看,其来源于 v$nls_parameters,可能是会话的环境变量或者是 alter session 完成,如果会话没有特殊的设置,将与 nls_instance_parameters 一致。

参考:
http://blog.csdn.net/nini1109/article/details/4158796
http://hi.baidu.com/dba_hui/item/3c4f67e26fea83bfce2d4f12

11.1. 实例:如何支持中文做列名(修改环境变量 NLS_LANG)?

直接在 SQL*Plus 中执行下面语句会出错,
CREATE TABLE TEST_CN (测试 1 CHAR(4));

如果把列名加上双引号不会出错,但问题是客户的代码就是没有双引号的!
CREATE TABLE TEST_CN ("测试 1" CHAR(4));

解决方法:
在启动 SQL*Plus 前,设置环境变量 NLS_LANG
export NLS_LANG="Simplified chinese_china.zhs16gbk"

这样,执行下面语句就不会出错了:CREATE TABLE TEST_CN (测试 1 CHAR(4));

参考:http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eleven.htm#BGBBGICH

12. Oracle RAC

Oracle Real Application Clusters (RAC) is a database clustering solution that allows more than one instance to mount and open an Oracle database. RAC can only be used on special clustered systems with shared disk storage and a private network interconnect.

A normal Oracle installation consists of a single Oracle instance that accesses a database on the same computer system. In contrast, RAC allows multiple instances on different computer systems (nodes in a cluster) to access the same database files simultaneously. Communication between instances is managed by the Distributed Lock Manager (DLM). To address the possibility of two or more instances attempting to modify the same information simultaneously, Oracle uses up to ten additional background processes, named LCK0 through LCK9, to lock the resources in use by these instances.

oracle_one_inistance_for_each_db.gif

Figure 5: One Instance for Each Database

oracle_RAC.gif

Figure 6: Multiple Instances Associated with an Oracle RAC Database

参考:
http://docs.oracle.com/cd/B28359_01/rac.111/b28254/admcon.htm#i1058057

13. Oracle Scheduler

Oracle Database includes Oracle Scheduler, an enterprise job scheduler to help you simplify the scheduling of hundreds or even thousands of tasks. Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the DBMS_SCHEDULER PL/SQL package.

如:用 Oracle Scheduler 实现每天运行一个外部脚本:

begin
dbms_scheduler.create_job (
   job_name           =>  'migrate_files',
   job_type           =>  'executable',
   job_action         =>  '/home/oracle/test.sh',
   start_date         =>  '01-mar-2009 02:00:00 am',
   repeat_interval    =>  'freq=daily',
   enabled            =>  true);
end;
/

参考:
Overview of Oracle Scheduler: https://docs.oracle.com/cd/E11882_01/server.112/e25494/schedover.htm
Scheduling Jobs with Oracle Scheduler: https://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034
dbms_scheduler.create_job tips: http://www.dba-oracle.com/t_linux_oracle_dbms_scheduler_create_job.htm

14. Oracle Call Interface(OCI)

Oracle Call Interface (OCI) is the comprehensive, high performance, native C language interface to Oracle Database. It exposes the full power of Oracle Database to custom or packaged C applications. Oracle In-Memory Database Cache also supports access from OCI programs.

OCI is highly reliable. Oracle tools such as SQL*Plus, Real Application Testing (RAT), SQL*Loader, and Data-Pump all use OCI. OCI also provides the foundation on which other language-specific interfaces such as Oracle JDBC-OCI, Oracle Data Provider for Net (ODP.Net), Oracle Precompilers, Oracle ODBC, and Oracle C++ Call Interface (OCCI) drivers are built. OCI is also used by leading scripting language drivers such as PHP OCI8, ruby-oci8, Perl DBD::Oracle, Python cx_Oracle, and the statistical programming language R's ROracle driver.

参考:
http://www.oracle.com/technetwork/database/features/oci/index-090945.html
http://en.wikipedia.org/wiki/Oracle_Call_Interface
Oracle Call Interface Programmer's Guide

15. Oracle Streams

Oracle Streams allows data propagation between Oracle Databases (homogeneous) and between Oracle and non-Oracle databases (heterogeneous environment).
Oracle Streams can be used for:

  • Replication
  • Message Queuing
  • Loading data into a Data Warehouse
  • Event Notification
  • Data Protection

In July 2009, Oracle acquired GoldenGate, a company with a heterogeneous replication solution. As of 2012 Oracle Corporation encourages customers with new applications to use Oracle GoldenGate rather than Streams.

参考:
Oracle Streams Concepts and Administration
http://en.wikipedia.org/wiki/Oracle_Streams
http://www.orafaq.com/wiki/Oracle_Streams

16. Oracle Database Advanced Queuing

Oracle Database Advanced Queuing (AQ) provides message queuing as an integrated part of Oracle Database. AQ provides this functionality by integrating the queuing system with the database, thereby creating a message-enabled database. By providing an integrated solution, AQ frees application developers to devote their efforts to their specific business logic rather than having to construct a messaging infrastructure.

Oracle Database Advanced Queuing is implemented in database tables, all operational benefits of high availability, scalability, and reliability are also applicable to queue data. Standard database features such as recovery, restart, and security are supported by Oracle Database Advanced Queuing.

Table 1: AQ Functions in OCI Versus PL/SQL
PL/SQL Function OCI Function
DBMS_AQ.ENQUEUE OCIAQEnq()
DBMS_AQ.DEQUEUE OCIAQDeq()
DBMS_AQ.LISTEN OCIAQListen(), OCIAQListen2()
DBMS_AQ.ENQUEUE_ARRAY OCIAQEnqArray()
DBMS_AQ.DEQUEUE_ARRAY OCIAQDeqArray()

参考:
Oracle Database Advanced Queuing User's Guide
https://docs.oracle.com/database/121/LNOCI/oci09adv.htm#LNOCI16700

17. Tips

17.1. 查看数据库版本信息

使用 SELECT * FROM v$version 可以查看数据库版本信息,如:

SQL> SELECT * FROM v$version
Banner
--------------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

17.2. 创建新用户

第 1 步,用 sys 以 sysdba 的角色登录(假设 sys 密码为 syspw):

$ sqlplus sys/syspw@xx.yy.com:1521/orcl as SYSDBA

如果数据库就在当前机器上,则可以先切换到 software owner 用户(安装数据库软件时创建的用户,一般为 oracle),再直接登录为 sysdba。

$ su - oracle
$ sqlplus / as SYSDBA

第 2 步,创建用户(假设新用户为 jack,密码为 12345):

SQL> create user jack identified by 12345;

第 3 步,对新用户进行授权:

GRANT create session TO jack;
GRANT unlimited tablespace TO jack;
GRANT create table TO jack;
GRANT create view TO jack;
GRANT create any trigger TO jack;
GRANT create any procedure TO jack;
GRANT create sequence TO jack;
GRANT create synonym TO jack;

说明 1:如果不执行 GRANT create session TO jack; ,新用户 jack 将无法使用 sqlplus 登录到数据库。
说明 2:如果不执行 GRANT unlimited tablespace TO jack; ,新用户 jack 将无法在新建的表中插入记录。

参考:http://stackoverflow.com/questions/18403125/how-to-create-a-new-schema-new-user-in-oracle-11g

17.3. 重置已过期的用户密码

问题:用户无法连接 Oracle 数据库,提示密码过期。
解决办法:用 sys 以 sysdba 的角色登录后,重置用户密码。

第 1 步,用 sys 以 sysdba 的角色登录(假设 sys 密码为 syspw):

$ sqlplus sys/syspw@xx.yy.com:1521/orcl as SYSDBA

如果需要重置密码的用户所使用数据库就在当前机器上,则可以先切换到 software owner 用户(安装数据库软件时创建的用户,一般为 oracle),再直接登录为 sysdba。

$ su - oracle
$ sqlplus / as SYSDBA

第 2 步,重置用户密码(假设重置用户 user1 的密码为 userpw1):

SQL> alter user user1 identified by userpw1;
SQL> commit;

说明:
sys 用户一定要以 sysdba 的角色登录,否则会提示下面错误:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

17.4. 为用户增加 sysdba 角色

为用户 user1 增加 sysdba 角色(这个操作普通用户无权执行,sys 用户可以执行),如:

SQL> grant sysdba to user1;

上面操作成功执行后,用户 user1 可以用角色 sysdba 登录了,如:

$ sqlplus user1/passwd1@net_service_name as sysdba;

如果用户 user1 已经登录了,则可以直接切换到 sysdba 角色,如:

SQL> connect / as sysdba;

17.5. 查看当前用户下所有数据表

USER_TABLES describes the relational tables owned by the current user.

SQL> SELECT TABLE_NAME FROM USER_TABLES;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
ORATEST
ACTIONLOG
BRANCH
TELLER
HISTORY
TESTAPPC
ORATESTOMQ_01

17.6. 用函数 sys_context 查看当前配置

sys_context 函数是 Oracle 提供的一个获取环境上下文信息的预定义函数。该函数用来返回一个指定 namespace 下的 parameter 值。

实例 1:查看当前 schema

SQL> SELECT sys_context('USERENV','CURRENT_SCHEMA') AS schema from dual;

SCHEMA
--------------------------------------------------------------------------------
SCOTT

实例 2:查看当前 DB_NAME,INSTANCE_NAME,SERVICE_NAME

SQL> SELECT sys_context('USERENV','DB_NAME'), sys_context('USERENV','INSTANCE_NAME'),           sys_context('USERENV', 'SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','INSTANCE_NAME')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','SERVICE_NAME')
--------------------------------------------------------------------------------
orcl
orcl
orcl

参考:
SYS_CONTEXT: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

17.7. 查看系统中的 Sequences/Triggers/Indexes/Tables

在 3 个和 Sequences 相关的 catalog views :

DBA_SEQUENCES
all sequences that exist.
ALL_SEQUENCES
all sequences that you have permission to see.
USER_SEQUENCES
all sequences that you own.

要查看当前用户所拥有的Sequences,可以用下面语句:

select * from user_sequences;

类似地,可用下面语句查找用户所拥有的Triggers/Indexes/Tables:

select * from user_triggers;
select * from user_indexes;
select * from user_tables;

Author: cig01

Created: <2013-04-28 Sun>

Last updated: <2018-01-19 Fri>

Creator: Emacs 27.1 (Org mode 9.4)