连接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直接修改新密码,步骤 如下:
- win+R输入cmd进入DOS窗口
- 输入命令:sqlplus /nolog
- 输入命令:conn /as sysdba
- 开始修改system密码:alter user system identified by 123456
- 开始修改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;
这次总结木有啦,就到这里了,之后会继续更新。
对了,如果文章内容存在 错误或不当,烦请在评论区指出,感谢!