Oracle基本知识(持续更新)

数据库、实例、用户、表空间、数据文件

1、数据库

Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

**全局数据库名：**就是一个数据库的标识，在安装时就要想好，以后一般不修改，修改起来也麻烦，因为数据库一旦安装，数据库名就写进了控制文件，数据库表，很多地方都会用到这个数据库名。

启动数据库：也叫全局数据库，是数据库系统的入口，它会内置一些高级权限的用户如SYS，SYSTEM等。我们用这些高级权限账号登陆就可以在数据库实例中创建表空间，用户，表了。

查询当前数据库名：

select name from v$database;

2、实例

用Oracle官方描述：实例是访问Oracle数据库所需的一部分计算机内存和辅助处理后台进程，是由进程和这些进程所使用的内存(SGA)所构成一个集合。

其实就是用来访问和使用数据库的一块进程，它只存在于内存中。就像Java中new出来的实例对象一样。

我们访问Oracle都是访问一个实例，但这个实例如果关联了数据库文件，就是可以访问的，如果没有，就会得到实例不可用的错误。

实例名指的是用于响应某个数据库操作的数据库管理系统的名称。她同时也叫SID。实例名是由参数instance_name决定的。

查询当前数据库实例名：

select instance_name from v$instance;

数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。比如我们作开发，要连接数据库，就得连接数据库实例名：

jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）

一个数据库可以有多个实例，在作数据库服务集群的时候可以用到。

3、表空间

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。

有了数据库，就可以创建表空间。

表空间(tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间（称作SYSTEM表空间）。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。

创建表空间语法：

Create TableSpace 表空间名称  
DataFile          表空间数据文件路径  
Size              表空间初始大小  
Autoextend on

如：

create tablespace db_test  
datafile 'D:\oracle\product\10.2.0\userdata\db_test.dbf'  
size 50m  
autoextend on;

创建多个文件的表空间（初始大小1024M，自增长，每次增加100M 无限制大小）

create tablespace T_TEST_DETAIL_INFO01
datafile '/home/oracle/app/oradata/PTP/datafile/T_TEST_DETAIL_INFO_01.dbf'
size 1024m autoextend on next 100 m maxsize unlimited,
'/home/oracle/app/oradata/PTP/datafile/T_TEST_DETAIL_INFO_02.dbf'
size 1024m autoextend on next 100 m maxsize unlimited;

查看已经创建好的表空间：

select default_tablespace, temporary_tablespace, d.username  
from dba_users d

删除表空间

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;

4、用户

Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并授权用户访问创建表需要表空间的权限。

上面我们建好了数据库和表空间，接下来建用户：

创建新用户：

CREATE USER          用户名  
IDENTIFIED BY        密码  
DEFAULT TABLESPACE   表空间(默认USERS)  
TEMPORARY TABLESPACE 临时表空间(默认TEMP) 

如：

CREATE USER utest  
IDENTIFIED BY utestpwd  
DEFAULT TABLESPACE USERS  
TEMPORARY TABLESPACE TEMP;（这里临时表空间不能使用我们创建的db_test，不知为何？） 

--向用户credit授予表空间配额
alter user utest quota 50M on creditTab1;

--将表空间授权给用户
grant unlimited tablespace to scott;

有了用户，要想使用用户账号管理自己的表空间，还得给它分权限：

1》. connect role(连接角色)

–临时用户，特指不需要建表的用户，通常只赋予他们connect role.

–connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。

–拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link）

2》. resource role(资源角色)

–更可靠和正式的数据库用户可以授予resource role。

–resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

3》. dba role(数据库管理员角色)

–dba role拥有所有的系统权限

–包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有

GRANT CONNECT TO utest;  
GRANT RESOURCE TO utest;  
GRANT dba TO utest;--dba为最高级权限，可以创建数据库，表等。
#撤销权限  revoke connect, resource from 用户名;
revoke connect, resource from test;

查看数据库用户：

select  * from dba_users;

删除用户：

#语法：drop user 用户名;
drop user test;
#若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。
#语法： drop user 用户名 cascade;
drop user test cascade;

5、数据文件（dbf、ora）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真 正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

6、表

有了数据库，表空间和用户，就可以用自定义的用户在自己的表空间创建表了。有了表，我们可以开发了。

7、关系示意图

实例下有和，授权访问，是管理的，经授权在中创建，随机存储到不同的中。即通过授权访问，一个用户可以访问多个表空间的表

Oracle将指定表分配给一个新用户进行查看数据

system：数据库管理账户 ， myoracle：被访问用户 ， youroracle：访问用户 ， 密码：password ，testtable 被分配查询的表名

1.首先使用myoracle进行登录oracle： 使用命令窗口输入以下sql语句创建youroracle用户： create user youroracle identified by password;

2.使用system账户登录 ：使用命令窗口创建session条件：grant create session to youroracle;

3.使用myoracle登录oracle：使用命令窗口分配访问权限 ：grant select on testtable to youroracle;

4.使用youroracle账户 密码是：password 进行登录，在查看自己的tables中就会看到 myoracle.testtable的表了。

使用以上步骤就可以完成Oracle中一个用户访问另一个用户的表了，如果想访问说有的表，但都是select权限的话 可以使用 grant select on any tables to youroracle;即可。

命名规范

1、表命名

l 命名最好望文生义,避免使用数据库键词

l 命名一律为大写字母(或小写，不要大下写混合)

l 英文单词单数命名.例:FACTORY

l 英文单词缩写命名.例:DEPT

l 英文单词之间用下划线连结,且每个单词皆为单数.例:TASK_RESULT

l 用来存储历史资料,命名以HISTORY结尾.例:TASK_HISTORY

l 用来存储日志资料,命名以LOG结尾.例:TASK_LOG

l 用来存储类别资料,命名以TYPE结尾.例:TASK_TYPE

2、表字段

l 命名最好望文生义,避免使用数据库关键词

l 英文单词单数命名.例:MAIL

l 英文单词缩写命名.例:DEPT_ID

l 英文单词之间用下划线连结,且每个单词皆为单数.例:USER_NAME

l 字段用来存储流水号,命名以ID结尾.例: TASK_ID

l 字段用来存储代码,命名以NO结尾.例: TASK_NO

l 字段用来存储日期,命名以DATE结尾.例:CREATE_DATE

l 字段用来存储数量,命名以QTY结尾.例:PLAN_QTY

l 字段用来存储类别,命名以TYPE结尾.例:STATION_TYPE

l 字段用来存储名称,命名以NAME结尾.例:ENDUSER_NAME

l 字段用来存储描述,命名以DESC结尾.例:STATION_DESC

l 字段用来存储代码,命名以CODE结尾.例:ERROR_CODE

l 字段用来存储标志,命名以FLAG开头.例:DEBUG_FLAG

3、索引

命名以表名+ INDEX+两位流水号(01~99).例:ENDUSER_INDEX01

4、键值

l 主键命名以表名+PK+一位流水号(1~9).例:DEPT_PK1

l 附键命名以表名+AK+一位流水号(1~9).例:DEPT_AK1

l 外键命名以表名+FK+一位流水号(1~9).例:DEPT_FK1

l 检查条件以表名+CK+一位流水号(1~9).例:DEPT_CK1

5、视图

l 命名以V_开头 例:V_DEPT

l 其它规则参考资料表命名规则

6、 触发器

以表名+TR+一位流水号(1~9) 命名.例:DEPT_TR1

7、存储过程

l 命名以PC开头

l 实现单一资料表交易功能,以PC+表名命名.例:PC_LINE

l 实现关联资料表交易功能,以PC+功能命名.例:PC_GETDATA

l 命名需让人明白其主要功能

8、函数

l 检查功能的函数,以Is+检查项目命名.例:bool IsCharValid(char)

l 获取资料功能的函数,以Get+数据项命名.例:string GetLineNo(char)

l 设定资料功能的函数,以Set+数据项命名.例:int SetLineNo(char)

9、序列

l 命名以SEQ开头

l 以表的字段名命名.例: SEQ_LINE_ID

l 若与其它表的字段名相同,则以表名+字段名命名.例:SEQ_CODE_TYPE_ID

10、表空间

l 存储资料的命名为项目名称.例:CRM

l 存储索引的命名为项目名称+IDX.例:CRMIDX

l 存储历史的命名为项目名称+OLD.例:CRMOLD

l 存储临时的命名为项目名称+LOG.例:CRMLOG.

11、数据文件

命名以所属表空间开头+两位流水号(01~99)+.ORA.例:CRM01.ORA

12、包

命名以功能缩写开头+PKG+流水号(1~9).例:CRMPKG1

13、用户

l 项目管理者以项目名称命名.例:CRM

l 开发者通用名称DEVELOPER

l 初学者通用名称BEGINNER

基本数据类型

ORACLE基本数据类型（亦叫内置数据类型 built-in datatypes)可以按类型分为：字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。

字符串类型的数据可依编码方式分成数据库字符集（CHAR/VARCHAR2/CLOB/LONG)和国际字符集(NCHAR/NVARCHAR2/NCLOB)两种。数据库中的字符串数据都通过字符集将字符转换为数字后(二进制)，才存储到数据块中。通过不同的编码集转换，即便是相同的字符，也可能会转换成不同的二进制编码。这也是产生乱码的原因。数据库的编码格式一般是在创建数据库时指定的。当然也可以修改数据库的编码。

查看数据库视图所包含的数据类型:SELECT * FROM DBA_TYPES WHERE OWNER IS NULL.

一 字符串类型

字符串数据类型还可以依据存储空间分为固定长度类型（CHAR/NCHAR) 和可变长度类型（VARCHAR2/NVARCHAR2)两种.

所谓固定长度：是指虽然输入的字段值小于该字段的限制长度，但是实际存储数据时，会先自动向右补足空格后，才将字段值的内容存储到数据块中。这种方式虽然比较浪费空间，但是存储效率较可变长度类型要好。同时还能减少数据行迁移情况发生。

所谓可变长度：是指当输入的字段值小于该字段的限制长度时，直接将字段值的内容存储到数据块中，而不会补上空白，这样可以节省数据块空间。

1.1 CHAR类型 CHAR(size [BYTE | CHAR])

CHAR类型，定长字符串，会用空格填充来达到其最大长度。非NULL的CHAR（12）总是包含12字节信息。CHAR字段最多可以存储2,000字节的信息。如果创建表时，不指定CHAR长度，则默认为1。另外你可以指定它存储字节或字符，例如 CHAR(12 BYTYE) CHAR(12 CHAR).一般来说默认是存储字节，你可以查看数据库参数

NLS_LENGTH_SEMANTICS的值。

SQL Code

SQL> show parameter nls_length_semantics;
NAME          TYPE      VALUE
------------------  ----------- ----------------- 
nls_length_semantics  string     BYTE
eg:
CREATE TABLE TEST
(
NAME_OLD CHAR(10),
NAME_NEW CHAR(10 CHAR)
)
INSERT INTO TEST
( NAME_OLD, NAME_NEW)
SELECT 'ABCDEFGHIJ' , '你清除字节与字符' FROM DUAL;
COMMIT;
INSERT INTO TEST
( NAME_OLD, NAME_NEW)
SELECT '你清除字节与字符' , 'ABCDEFGHIJ' FROM DUAL;
ORA-12899: 列 "SYS"."TEST"."NAME_OLD" 的值太大 (实际值: 24, 最大值: 10) 

注意：数据库的NLS_CHARACTERSET 为AL32UTF8，即一个汉字占用三到四个字节。如果NLS_CHARACTERSET为ZHS16GBK，则一个字符占用两个字节。

如果串的长度小于或等于250（0x01~0xFA）， Oracle 会使用1 个字节来表示长度。对于所有长度超过250 的串，都会在一个标志字节0xFE 后跟有两个字节来表示长度。因此，如果有一个包含“Hello World”的VARCHAR2(80)，则在块中可能如图12.-1 所示

1.2 NCHAR类型

这是一个包含UNICODE格式数据的定长字符串。NCHAR字段最多可以存储2,000字节的信息。它的最大长度取决于国家字符集。另外查询时，如果字段是NCHAR类型，则需要如下书写

SELECT translated_description FROM product_descriptions WHERE translated_name = N'LCD Monitor 11/PM';

1.3 VARCHAR类型

不要使用VARCHAR数据类型。使用VARCHAR2数据类型。虽然VARCHAR数据类型目前是VARCHAR2的同义词，VARCHAR数据类型将计划被重新定义为一个单独的数据类型用于可变长度的字符串相比，具有不同的比较语义。

1.4 VARCHAR2类型

变长字符串，与CHAR类型不同，它不会使用空格填充至最大长度。VARCHAR2最多可以存储4,000字节的信息。

1.5 NVARCHAR2类型

这是一个包含UNICODE格式数据的变长字符串。 NVARCHAR2最多可以存储4,000字节的信息。

二. 数字类型

2.1 NUMBER类型

NUMBER(P,S)是最常见的数字类型，可以存放数据范围为10^130~10126（不包含此值)，需要1~22字节(BYTE)不等的存储空间。

P 是Precison的英文缩写，即精度缩写，表示有效数字的位数，最多不能超过38个有效数字

S是Scale的英文缩写，可以使用的范围为-84~127。Scale为正数时，表示从小数点到最低有效数字的位数，它为负数时，表示从最大有效数字到小数点的位数

下面是官方文档的示例

Actual Data	Specified As	Stored As
123.89	NUMBER	123.89
123.89	NUMBER(3)	124
123.89	NUMBER(6,2)	123.89
123.89	NUMBER(6,1)	123.9
123.89	NUMBER(3)	124
123.89	NUMBER(4,2)	exceeds precision
123.89	NUMBER(6,-2)	100
.01234	NUMBER(4,5)	.01234
.00012	NUMBER(4,5)	.00012
.000127	NUMBER(4,5)	.00013
.0000012	NUMBER(2,7)	.0000012
.00000123	NUMBER(2,7)	.0000012
1.2e-4	NUMBER(2,5)	0.00012
1.2e-5	NUMBER(2,5)	0.00001

2.2 INTEGER类型

INTEGER是NUMBER的子类型，它等同于NUMBER（38,0），用来存储整数。若插入、更新的数值有小数，则会被四舍五入。

例如：

CREATE TABLE TEST
(
ID INTEGER
)
#查看表TEST的DDL定义如下所示
CREATE TABLE "SYS"."TEST"
( "ID" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
INSERT INTO TEST
SELECT 12.34 FROM DUAL;
INSERT INTO TEST
SELECT 12.56 FROM DUAL;
SQL> SELECT * FROM TEST;
ID
\----------
12
13

2.3 浮点数

浮点数可以有一个十进制数点任何地方从第一个到最后一个数字，或者可以在所有有没有小数点。指数可能（可选） 用于以下数量增加的范围 （例如， 1.777e-20)。刻度值不适用于浮点数字，因为可以显示在小数点后的位数的数量不受限制。

二进制浮点数不同数量的值由 Oracle 数据库内部存储的方式。使用小数精度数存储值。完全相同号码存储范围和数量由支持的精度内的所有文本。正是因为使用小数精度（数字 0 到 9） 表示文本存储文本。使用二进制精度 （数字 0 和 1） 存储二进制浮点数。这种存储方案不能代表所有确切地使用小数精度的值。频繁地，将值从十进制转换为二进制的精度时出现的错误时撤消值回从二进制转换为十进制精度。在字面 0.1 是一个这样的例子。

Oracle 数据库提供了专为浮点数的两种数值数据类型：

BINARY_FLOAT

BINARY_FLOAT 是 32 位、 单精度浮点数字数据类型。可以支持至少6位精度,每个 BINARY_FLOAT 的值需要 5 个字节，包括长度字节。

BINARY_DOUBLE

BINARY_DOUBLE 是为 64 位，双精度浮点数字数据类型。每个 BINARY_DOUBLE 的值需要 9 个字节，包括长度字节。

在数字的列中，浮点数有小数精度。在 BINARY_FLOAT 或 BINARY_DOUBLE 的列中，浮点数有二进制的精度。二进制浮点数支持的特殊值无穷大和 NaN （不是数字）。

您可以指定列在表 2-4 范围内的浮点数。"数字文本"中定义了用于指定浮点数的格式。

Table 2-3 Floating Point Number Limits

Value	Binary-Float	Binary-Double
Maximum positive finite value	3.40282E+38F	1.79769313486231E+308
Minimum positive finite value	1.17549E-38F	2.22507485850720E-308

2.5 FLOAT类型

FLOAT类型也是NUMBER的子类型。

Float(n),数 n 指示位的精度，可以存储的值的数目。N 值的范围可以从 1 到 126。若要从二进制转换为十进制的精度，请将 n 乘以 0.30103。要从十进制转换为二进制的精度，请用 3.32193 乘小数精度。126 位二进制精度的最大值是大约相当于 38 位小数精度。

三.日期类型

日期类型用于存储日期数据，但是并不是使用一般的格式（2012-08-08）直接存储到数据库的。

3.1 DATE类型

DATE是最常用的数据类型，日期数据类型存储日期和时间信息。虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。为每个日期值，Oracle 存储以下信息： 世纪、 年、 月、 日期、 小时、 分钟和秒。一般占用7个字节的存储空间。

3.2 TIMESTAMP类型

这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位

3.3 TIMESTAMP WITH TIME ZONE类型

这是TIMESTAMP类型的变种，它包含了时区偏移量的值

3.4 TIMESTAMP WITH LOCAL TIME ZONE类型

3.5 INTERVAL YEAR TO MOTH

3.6 INTERVAL DAY TO SECOND

四.LOB类型

内置的LOB数据类型包括BLOB、CLOB、NCLOB、BFILE（外部存储）的大型化和非结构化数据，如文本、图像、视屏、空间数据存储。BLOB、CLOB、NCLOB类型

4.1 CLOB 数据类型

它存储单字节和多字节字符数据。支持固定宽度和可变宽度的字符集。CLOB对象可以存储最多 (4 gigabytes-1) * (database block size) 大小的字符

4.2 NCLOB 数据类型

它存储UNICODE类型的数据，支持固定宽度和可变宽度的字符集，NCLOB对象可以存储最多(4 gigabytes-1) * (database block size)大小的文本数据。

4.3 BLOB 数据类型

它存储非结构化的二进制数据大对象，它可以被认为是没有字符集语义的比特流，一般是图像、声音、视频等文件。BLOB对象最多存储(4 gigabytes-1) * (database block size)的二进制数据。

4.4 BFILE 数据类型

二进制文件，存储在数据库外的系统文件，只读的，数据库会将该文件当二进制文件处理

五.RAW & LONG RAW类型

5.1 LONG类型

它存储变长字符串，最多达2G的字符数据（2GB是指2千兆字节， 而不是2千兆字符），与VARCHAR2 或CHAR 类型一样，存储在LONG 类型中的文本要进行字符集转换。ORACLE建议开发中使用CLOB替代LONG类型。支持LONG 列只是为了保证向后兼容性。CLOB类型比LONG类型的限制要少得多。 LONG类型的限制如下：

1.一个表中只有一列可以为LONG型。(Why?有些不明白)

2.LONG列不能定义为主键或唯一约束，

3.不能建立索引

4.LONG数据不能指定正则表达式。

5.函数或存储过程不能接受LONG数据类型的参数。

6.LONG列不能出现在WHERE子句或完整性约束（除了可能会出现NULL和NOT NULL约束）

官方文档描叙如下：

The use of LONG values is subject to these restrictions:
A table can contain only one LONG column.
You cannot create an object type with a LONG attribute.
LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
LONG columns cannot be indexed.
LONG data cannot be specified in regular expressions.
A stored function cannot return a LONG value.
You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.
Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
In addition, LONG columns cannot appear in these parts of SQL statements:
GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
The UNIQUE operator of a SELECT statement
The column list of a CREATE CLUSTER statement
The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
SQL built-in functions, expressions, or conditions
SELECT lists of queries containing GROUP BY clauses
SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
SELECT lists of CREATE TABLE ... AS SELECT statements
ALTER TABLE ... MOVE statements
SELECT lists in subqueries in INSERT statements

5.2 LONG RAW 类型

能存储2GB 的原始二进制数据（不用进行字符集转换的数据）

5.3 RAW类型

用于存储二进制或字符类型数据，变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。这种类型最多可以存储2,000字节的信息

六.ROWID & UROWID类型

在数据库中的每一行都有一个地址。然而，一些表行的地址不是物理或永久的，或者不是ORACLE数据库生成的。

例如，索引组织表行地址存储在索引的叶子，可以移动。

例如,外部表的ROWID（如通过网关访问DB2表）不是标准的ORACLE的rowid。

ORACLE使用通用的ROWID（UROWIDs）的存储地址的索引组织表和外表。索引组织表有逻辑urowids的，和国外表的外urowids，。UROWID这两种类型的存储在ROWID伪（堆组织的表的物理行id）。

创建基于逻辑的rowid在表中的主键。逻辑的rowid不会改变，只要主键不改变。索引组织表的ROWID伪UROWID数据类型。你可以访问这个伪列，你会堆组织表的ROWID伪（即使用一个SELECT …ROWID语句）。如果你想存储的rowid索引组织表，那么你就可以定义一列的表型UROWID到列检索值的ROWID伪。