oracle创建表函数关键,Oracle工作总结基础篇(一)

连接Oracle服务器(SID和服务名的区分)

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

如果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 intotuserselect * fromtuseras 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 jackidentified by 123456default tablespace MYTABLESPACE;

授权

grant dba to jack;

grant resource to jack;

grant connect to jack;

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

alter user jackidentified by jack_20190921;

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

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