Oracle 082
1.表被删除时，表上的约束、索引一起被删除，表被放入回收站（默认）。视
图和synonym不会被删除
2、TNS\sqlnet\listenner
-
最近看到好多人说到tns或者数据库不能登录等问题，就索性总结了下面的文档。
首先来说Oracle的网络结构，往复杂处说能加上加密、LDAP等等。。这里不做讨论，重点放在基本的网
络结构也就是我们最常用的这种情况
三个配置文件
listener.ora、sqlnet.ora、tnsnames.ora ，都是放在$ORACLE_HOME\network\admin目录下。
重点:三个文件的作用和使用
#-----------------------
sqlnet.ora-----作用类似于linux或者其他unix的nsswitch.conf文件，通过这个文件来决定怎么样找一个
连接中出现的连接字符串，
例如我们客户端输入
sqlplus sys/oracle@orcl
假如我的sqlnet.ora是下面这个样子
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES,HOSTNAME)
那么，客户端就会首先在tnsnames.ora文件中找orcl的记录.如果没有相应的记录则尝试把orcl当作一个
主机名，通过网络的途径去解析它的ip地址然后去连接这个ip上GLOBAL_DBNAME=orcl这个实例，当然我
这里orcl并不是一个主机名
如果我是这个样子
NAMES.DIRECTORY_PATH= (TNSNAMES)
那么客户端就只会从tnsnames.ora查找orcl的记录
括号中还有其他选项，如LDAP等并不常用。
#------------------------
Tnsnames.ora------这个文件类似于unix 的hosts文件，提供的tnsname到主机名或者ip的对应，只有
当sqlnet.ora中类似
NAMES.DIRECTORY_PATH= (TNSNAMES) 这样，也就是客户端解析连接字符串的顺序中有TNSNAM
ES是，才会尝试使用这个文件。
例子中有两个，ORCL 对应的本机，SALES对应的另外一个IP地址，里边还定义了使用主用服务器还是共
享服务器模式进行连接，一句一句说
#你所要连接的时候输入得TNSNAME
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
#下面是这个TNSNAME对应的主机，端口，协议
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
#使用专用服务器模式去连接需要跟服务器的模式匹配，如果没有就根据服务器的模式
#自动调节
(SERVER = DEDICATED)
#对应service_name，SQLPLUS>show parameter service_name;
#进行查看
(SERVICE_NAME = orcl)
)
)
#下面这个类似
SALES =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.188.219)(PORT = 1521))
)
(CONNECT_DATA =
@wjwang2007
(SERVER = DEDICATED)
(SERVICE_NAME = sales)
)
)
#----------------------
客户端完了我们来看服务器端
listener.ora------listener监听器进程的配置文件
关于listener进程就不多说了，接受远程对数据库的接入申请并转交给oracle的服务器进程。所以如果不
是使用的远程的连接，listener进程就不是必需的，同样的如果关闭listener进程并不会影响已经存在的数据库
连接。
Listener.ora文件的例子
#listener.ora Network Configuration File: #E:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\list
ener.ora
# Generated by Oracle configuration tools.
#下面定义LISTENER进程为哪个实例提供服务
#这里是ORCL，并且它对应的ORACLE_HOME和GLOBAL_DBNAME
#其中GLOBAL_DBNAME不是必需的除非使用HOSTNAME做数据库连接
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boway)
(ORACLE_HOME = E:\oracle\product\10.1.0\Db_2)
(SID_NAME = ORCL)
)
)
#监听器的名字，一台数据库可以有不止一个监听器
#再向下面是监听器监听的协议，ip，端口等，这里使用的tcp1521端口，并且使#用的是主机名
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boway)(PORT = 1521))
)
上面的例子是一个最简单的例子，但也是最普遍的。一个listener进程为一个instance(SID)提供服务。
监听器的操作命令
$ORACLE_HOME/bin/lsnrctl start,其他诸如stop,status等。具体敲完一个lsnrctl后看帮助。
上面说到的三个文件都可以通过图形的配置工具来完成配置
$ORACLE_HOME/netca 向导形式的
$ORACLE_HOME/netmgr
本人比较习惯netmgr,
profile 配置的是sqlnet.ora也就是名称解析的方式
service name 配置的是tnsnames.ora文件
listeners配置的是listener.ora文件，即监听器进程
具体的配置可以尝试一下然后来看一下配置文件。
这样一来总体结构就有了，是当你输入sqlplus sys/oracle@orcl的时候
1. 查询sqlnet.ora看看名称的解析方式，发现是TNSNAME
2. 则查询tnsnames.ora文件，从里边找orcl的记录，并且找到主机名，端口和service_name
3. 如果listener进程没有问题的话，建立与listener进程的连接。
4. 根据不同的服务器模式如专用服务器模式或者共享服务器模式，listener采取接下去的动作。默认是专
用服务器
7、普通用户只能创建private synonym。不同用户创建的private synonym可以
同名。可以为synonym再创建synonym。synonym是一种数据库对象，有ID
9、where子句必须位于group by之前，where不能使用count****函数
11、在对大型数据处理时，可能会需要压缩数据。
压缩数据可以减少对磁盘空间、内存的占用，还可以减少I/O的操作。同时，由于查询的数据块更少，所以查询压缩数据的操作也会更为快速。
但是处理压缩数据需要消耗更多的CPU资源：读取数据需要解压操作，写入数据需要压缩操作。
在创建表空间的时候可以启用数据压缩。
在表空间级别启用数据压缩的意义在于：对于启用了数据压缩的表空间，该表空间中的所有数据表都会具有(或：继承)压缩的特性。
通过“row compress advanced”的“create database …”SQL语句的选项启用数据压缩的特性。
13、substitution 置换
@与@@的区别
@ 用来运行一个SQL脚本文件，等于Start命令。@命令调用当前目录下的，或指定全路径，或可以通过SQLPATH环境变量搜寻到的脚本文件
@@用在SQL脚本文件中，用来指定用@@执行的文件与@@所在的当前SQL文件在同一目录，而不用指定全路径
&与&&的区别
& 用来创建一个临时变量，每当遇到这个临时变量时，都会提示你输入一个值
&&用来创建一个持久变量，当用&&命令引用这个变量时，不会每次遇到该变量就提示用户键入值，而只是在第一次遇到时提示一次。 当你不想使用这个变量名的时候 你可以用undefine 命令解除他
14、 首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。
创建语句如下：
CREATE SEQUENCE seqTest
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
15、
-
1.在用命令启动监听的时候的时候，会读取 listener.ora 文件中定义监听时候所用的参数来创建监听服务(进程)
2.在监听服务启动完毕后，也就是lsnrclt start命令成功结束后，通过该命令在系统中已经创建好的的监听服务(进程)本身已经和listener.ora文件没有关系。
3.但是当需要用lsnrctl status,lsnrctl stop 等命令对系统中已经在运行的的监听服务(进程)进行管理时还是需要借助listener.ora这个文件。
4.当执行lsnrctl status,lsnrctl stop 等命令的时候，首先命令程序会从listener.ora 文件中读取出对应监听的ip和端口，然后用读取的ip和端口连接系统上正在运行的监听服务(进程)，然后监听服务(进程)根据具体的命令，返回对应的结果
17、intersect 在SQL语句中，使用SET运算符（如INTERSECT）对复合查询的输出进行排序的默认列是哪一列？
默认对第一列进行排序
18、TRUNC（for dates）
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date 一个日期值
fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
下面是该函数的使用情况：
TRUNC（TO_DATE（'24-Nov-1999 08:00 pm'），'dd-mon-yyyy hh:mi am'）
='24-Nov-1999 12:00:00 am'
TRUNC（TO_DATE（'24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'），'hh'） ='24-Nov-1999 08:00:00 am'
trunc（sysdate,'yyyy'） --返回当年第一天。
trunc（sysdate,'mm'） --返回当月第一天。
trunc（sysdate,'d'） --返回当前星期的第一天。
trunc（sysdate,'dd'）--返回当前年月日
2.TRUNC（for number）
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推。
. MOD 取余
FLOOR: returns the smallest integer greater than or equal to a specified number.
ceil函数的返回值是等于其参数n的一个整数或者说是大于参数n的最小整数
20、在Oracle中，Initcap()函数可以将每个单词中的第一个字符设置为大写，其余的设置为小写。下面就是Initcap()函数的语法及具体用法介绍。
-
19 、ORACLE默认的日期形式是: DD-MON-RR 日期类型可以加减数字,功能是在该日期上加减对应的天数*。如:’10-AUG-06’+15结果是’25-AUG-06’ 日期类型之间可以进行减操作
TO_NUMBER:将字符串转为数字（注：无日期转数字）。此函数作用不大，算术运算时Oracel会自动将纯数字字符串转为数值型
20、NULLIF是Oracle 9i新增加的函数。
格式如下：
NULLIF（表达式1，表达式2）
NULLIF函数比较表达式1和表达式2。
如果两个表达式相等就返回空值(NULL)。
如果不等就返回表达式1。
COALESCE
-
含义：COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。
-
用法：COALESCE（expression_1, expression_2, ...,expression_n）
-
例子
1.SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 其返回结果为：3 2.SELECT A.ID,SUM(COALESCE(A.SHUL,0)+COALESCE(A.PZSHUL,0)) from t_order as A group by A.ID 这样就不会因为数据库中的值为NULL而计算错误 -
返回值：返回第一个非NULL表达式的类型
nullif需要两个参数，相同类型，第二个参数可以为NULL。COALESCE需要若干参数，相
同类型，第二个及以后可以为NULL
-
21、关于Oracle Timezone的一点总结 https://blog.csdn.net/qq_26679869/article/details/83863690
26、当分组函数有多层嵌套时，SELECT不能有其他列。当分组函数单层使用时，
SELECT之后未使用分组函数的列需要同时出现在group by****子句中
使用分组函数与其他列名在一起查询的时候必须使用group by子句进行分组
28、: EM Express只能用来管理当前数据库，不能启动、关闭数据库。它所需要的
metadata也位于当前数据库中。DBCA默认为数据库配置EM Express
30、union all进行并集运算，不消除重复行，union消除重复行。两条SELECT语句之后的
列，数量相同，数据类型兼容即可，名称不必相同。两个空行被认为是重复行