Oracle工作总结基础篇(一)

连接Oracle服务器（SID和服务名的区分）

• 如果orcl设置为 SID：则1521后面用 ‘:’，比如：

<Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="100" maxIdle="10" maxWait="10000" name="jdbc/testDS" password="apex" type="javax.sql.DataSource" url="jdbc:oracle:thin:@127.0.0.1:1521:orcl" username="apex" validationQuery="select 1 from dual" removeAbandoned="true" removeAbandonedTimeout="180" logAbandoned="true"/> 

• 如果orcl设置为Service_name：则1521后面用 ‘/’，比如：

sqlplus连接远程数据库时命令：sqlplus username/password@IP地址:1521/Service_name（默认1521时可以省略）

---

忘记Oracle中 system 和 sys密码时

使用Oracle自带的sqlplus直接修改新密码，步骤 如下：

1. win+R输入cmd进入DOS窗口
2. 输入命令：sqlplus /nolog
3. 输入命令：conn /as sysdba 
4. 开始修改system密码：alter user system identified by 123456
5. 开始修改sys密码：alter user sys identified by 123456

补充知识：普通user 、system 和 sys 区别

• 普通user：一般只拥有resource、connect 角色或权限
• system：数据库内置的一个普通管理员，拥有dba、resource、connect 角色或权限（DBA管理员也是这三个）

                      只能用normal身份登陆，除非对它授予了 sysdba 的系统权限(grant sysdba to system)或者 sysoper 系统权限

• sys： oracle 权限最高的用户，拥有 dba ， sysdba ， sysoper 等角色或权限

                      只能用sysdba、sysoper身份登陆，不能用normal

了解更详细的oracle用户，角色，权限等可参考文章一：https://www.cnblogs.com/ilifeilong/p/7624329.html

                                                                文章二：https://www.cnblogs.com/bowenbk/p/6371368.html

---

恢复delete并commit后的数据（truncate属于DDL语言，删除数据后无法利用如下方法恢复）

insert into tuser select * from tuser as of timestamp(systimestamp-interval '15' minute)

说明：将tuser表中数据恢复到15min之前tuser表的数据，表名 和 回滚时间 可以根据需要修改

 

---

存储过程调用时报无效数字或者 'character to number conversion error '

     问题原因：拼接字符串时： ||START_DATE-1|| 连接符号中间没有用括号括起来
    
     解决方法：||(START_DATE-1)||

---

CASE WHEN 函数使用注意事项

    【方式一】：CASE id WHEN  0  THEN  '管理员' ELSE  ‘普通用户’  END
    【方式二】：CASE  WHEN  id = 0  THEN  '管理员'  ELSE  '普通用户' END

      注意：当对字段进行是否为null判断时，只能用【方式二】       
      因为在方式一中： case NULL when NULL then '1' else '0' end，结果是0，此时是比较null = null 是否成立，而实际null != null
      所以只能使用方式二：case when NULL is NULL then '1' else '0' end，结果是1，此时是比较null is null是否成立，显然null is null.

---

动态SQL中报缺失表达式

    原因：null值直接拼接到动态SQL中后不是显示null，而是显示空白，所以会导致拼出来的SQL中出现：set username= where id=99，这明显会出错，‘=’后面缺失表达式
    解决：将空值进行置换处理，用decode或nvl函数

    注意：decode和nvl函数中的值必须是同一种数据类型，例如 nvl(id,'null') 是错误的,id明显是数值型，'null'是字符串，可以改为nvl(username,'null')

---

select 后面的字段何时加" "

 select 后面的字段如果是oracle关键字，需要加“ ”才能正常显示，且加“ ”后，引号内字段名必须大写，因为oracle建表时默认数据库表字段全部转换为大写

---

Oracle自带多行合并一行函数（单列字段值合并）

•  wm_concat() 

             简单使用：select m,wm_concat(n) from t group by m; 

             特点：默认用 ‘，’拼接每个值

• listagg()

             简单使用：select deptno,listagg(ename,',')within group(order by sal) all_name from emp group by deptno; 

             特点：可以根据需求自定义拼接的符号

---

2019.9.21更新

查看Oracle数据库死锁

select se.sid,se.serial#
from v$locked_object lo 
     ,dba_objects ao
     ,v$session se
where lo.object_id = ao.object_id
      and lo.session_id = se.sid

---

SID与Service_name使用注意

JDBC连接Oracle时，'：'表示SID，'/'表示service_name     PL/SQL远程连接Oracle时一般用service_name（tnsname.ora中），URL连接时一般用SID，也就是'：'
    因为一个service_name可能对应多个SID

---

存储过程中使用DDL语句

存储过程中一般只写DML类型增删改查的语句（ SELECT 、INSERT、 DELETE 、 UPDATE 、Merge into）
    要想使用DDL语句，只能使用动态SQL实现（ CREATE, ALTER, DROP）。如：
    EXECUTE IMMEDIATE ( 'CREATE TABLE test( id int ) ' );
    EXECUTE IMMEDIATE ( 'DROP TABLE test ' );

---

存储过程中报-未找到任何数据源

问题：    

存储过程中用select name into v_name from classInfo where class='20190921' 赋值变量时，如果查不到结果，并不会将null直接赋值给变量而会报错：未找到任何数据源

解决方法：    

第1步：添加一个必定有结果的字段一起赋值。

select count(1) into v_count from classInfo where class='20190921'；
        第2步：判断如果count(1)>0，再进行变量赋值。

select name into v_name from classInfo where class='20190921'；

---

Oracle中LPAD函数（字符填充函数）

lpad( string, padded_length, [ pad_string ] )
    参数1：string 被填充的字符串
    参数2：padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度
                 如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
    参数3：pad_string  填充字符串, 是个可选参数
                这个字符串会填充到string的左边，如果参数未写，将会用空格填充。

---

PLSQL目前常用快捷键设置

工具 —> 首选项 —> 键配置

新建一个SQL窗口
关闭任意类型打开的窗口
选中字母大写
选中字母小写
执行SQL窗口选中语句

---

DBLINK的使用

• 创建DBLINK

CREATE DATABASE LINK NGDP_LINK CONNECT TO test_read IDENTIFIED BY test_read USING ' (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.0.192.111)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=default)
      (SERVICE_NAME=NGDPDB)
    )
  )'

• 通过DBLINK访问远程数据库

select * from Info.tuser@NGDP_LINK;

• 查看当前数据库的DBLINK连接（需要DBA权限）

select owner,object_name,created,status from dba_objects where object_type='DATABASE LINK';

• 创建同义词（方便使用DBLINK时书写）

CREATE SYNONYM L_NGDP_tUser         FOR Info.tuser@NGDP_LINK;

---

查看oracle的表空间和文件关系

select tablespace_name
       ,file_id
       ,file_name
       ,t.STATUS
       ,t.ONLINE_STATUS
       ,round(bytes/(1024*1024),0) total_space
from dba_data_files t
order by tablespace_name;

---

oracle中创建只读用户、授权

create user test_read identified by test_read_20190921;

grant create session to test_read;

注意：

虽然是只读用户，但是如果只读用户selelct 时使用到for update，仍然可以修改数据。

---

LiveBos重新创建表空间和用户

• 表空间

create tablespace MYTABLESPACE
datafile '/oradata/MYFILE/datafile/newtablespace.dbf' 
size 1000m 
autoextend on 
next 100m; 

• 创建新用户

create user jack identified by 123456 default tablespace MYTABLESPACE;

• 授权

grant dba to jack ;
grant resource to jack ;
grant connect to jack ;

• 123456可能会出现拒绝连接，可以更换初始密码为字母开头，连接成功可以再次修改

alter user jack identified by jack_20190921;

 

 

这次总结木有啦，就到这里了，之后会继续更新。

对了，如果文章内容存在 错误或不当，烦请在评论区指出，感谢！