1.Oracle以管理员身份登录
sqlplus system as sysdba
sqlplus system as sysdba
2.正则拆分字段
select REGEXP_SUBSTR(org_code, ‘[^,]+’, 1, 1),REGEXP_SUBSTR(org_code, ‘[^,]+’, 1, 2) from (
select ‘1002405001,his’ as org_code from dual) aa;
select REGEXP_SUBSTR(org_code, '[^,]+', 1, 1),REGEXP_SUBSTR(org_code, '[^,]+', 1, 2) from (
select '1002405001,his' as org_code from dual) aa;
3.oracle 查询字符集
SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’;
SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = 'NLS_CHARACTERSET';
4.sqlserver 查看数据库版本
SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition,
SERVERPROPERTY(‘EngineEdition’) AS EngineEdition;
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;
备注:
ProductVersion:SQL Server 的版本号。
ProductLevel:SQL Server 的补丁级别(例如,RTM、SP1、CUx 等)。
Edition:SQL Server 的版本类型(例如,Enterprise Edition、Standard Edition 等)。
EngineEdition:SQL Server 的引擎版本编号。
5.SQLSERVER时间加一天
SELECT DATEADD(DAY, 1, GETDATE()) AS NextDay;
SELECT DATEADD(DAY, 1, GETDATE()) AS NextDay;
备注:
DAY 是你想要添加的时间单位。
1 是你想要添加的数量。
GETDATE() 是获取当前日期和时间的函数。
6.Oracle修改字符集
修改字符集可能会导致数据丢失或损坏,因此在进行此操作之前,请务必备份你的数据库。
在生产环境中更改字符集之前,最好在测试环境中进行充分的测试。
始终参考 Oracle 的官方文档以获取最准确和最新的信息。
7.Oracle时间加一天
select current_timestamp+1 from dual;—获取完整的日期和时间信息,包括时区,oracle 服务器的时间
select current_timestamp+1 from dual;
select current_date+1 from dual;—只需要日期部分,不需要具体的时间,oracle 服务器的时间
select current_date+1 from dual;
select sysdate+1 from dual;–返回当前系统时间(包括年、月、日、时、分和秒)
select sysdate+1 from dual;
备注:
Oracle服务器时间可能与实际时间有差异,一般会差±8H
select sysdate +1/(24*60) from dual;–当前时间加1分钟
8.oracle关联更新字段
UPDATE table2 t2
SET t2.some_column = (SELECT some_value FROM table1 t1 WHERE t1.some_condition = t2.some_common_column)
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.some_condition = t2.some_common_column);
UPDATE table2 t2
SET t2.some_column = (SELECT some_value FROM table1 t1 WHERE t1.some_condition = t2.some_common_column)
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.some_condition = t2.some_common_column);
备注:
把table1换成sql语句也可以
9.查看死锁(需要足够的权限,如DBA权限)
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v
l
o
c
k
e
d
o
b
j
e
c
t
l
o
,
d
b
a
o
b
j
e
c
t
s
a
o
,
v
locked_object lo, dba_objects ao, v
lockedobjectlo,dbaobjectsao,vsession sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
10. 处理死锁
将上面查询出来的sid以及serial放入到下面的sql中进行替换就可以了
alter system kill session ‘sid,serial’;
alter system kill session 'sid,serial';
select CLIENT_IDENTIFIER,v.inst_id,v.status,‘alter system kill session ‘’’ || v.sid || ‘,’ || v.serial# || ‘’’ immediate;',
v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL from gv$session v;
select CLIENT_IDENTIFIER,v.inst_id,v.status,'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;',
v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL from gv$session v;
11.时分秒转换成逻辑数字以及逻辑数字转换会时分秒,各个数据库原理均一致
69952实际上=时X60X60+分X60+秒 =19X60X60+25X60+52
-----19:25:52 除以3600,取整得小时 除以3600取余数,余数再除以60取整得分钟 除以60取余的 秒
select round(69952/60/60,0) as hour,round(mod(69952,6060)/60,0) as min,mod(69952,60) as sec,
(round(69952/60/60,0)||‘:’||round(mod(69952,6060)/60,0)||‘:’||mod(69952,60))::time as time
select round(69952/60/60,0) as hour,round(mod(69952,60*60)/60,0) as min,mod(69952,60) as sec,
(round(69952/60/60,0)||':'||round(mod(69952,60*60)/60,0)||':'||mod(69952,60))::time as time
12.创建虚拟字段(虚拟列)
ALTER TABLE table_name add (source_txt AS ("id" || '_' || "code" || '_' || "name"));
备注:table_name是表名字 source_txt是虚拟字典名称 id、code、name是table_name表中现有的字段
13.dump文件上传(部署数据库)
登录DBA账户
sqlplus 用户名/密码@ip:端口号/服务号as sysdba
创建数据库目录
CREATE DIRECTORY dmp_dir1 AS '/home/oracle/dmp_test/dqm';
--dmp文件路径 /home/oracle/dmp_test/dqm 根据实际改成自己的
--创建用户
create user 用户名identified by 密码;
给定权限
grant create session, connect, resource to 用户名;
1).GRANT:这是SQL语句的关键字,用于授予权限。
2).CREATE SESSION:这个权限允许用户连接到数据库实例。每个用户至少需要这个权限才能连接到数据库。
3).CONNECT:虽然这个权限在Oracle 12c及更高版本中不再单独存在(因为它已经被CREATE SESSION隐式包含),但在旧版本的Oracle中,CONNECT权限允许用户连接到数据库,但它不提供任何资源或创建对象的权限。在较新的版本中,你可以安全地忽略它,因为它已经被CREATE SESSION替代。
4).RESOURCE:RESOURCE角色是一个预定义的角色,它包含了一组权限,允许用户创建表、序列、触发器、过程、函数等。这通常是一个“通用”的权限集,适用于大多数数据库用户。
–执行命令
impdp 用户名/密码@ip:1521/服务名 DIRECTORY=dmp_dir1 DUMPFILE=dump文件 REMAP_TABLESPACE=dump文件表空间名字:新表空间名字 REMAP_SCHEMA=dump文件SCHEMA名字:新SCHEMA名字 logfile=yhpt_dqm_`date +%Y%m%d%H%M%S`.log FULL=y
--如果还有错误,根据日志进行修改
再根据实际情况给定该用户其他权限,如增删改查权限、创建视图、函数、存储过程等权限
14 特殊join说明
1)如果只写了join,则系统默认是内连接,比如:
select * from table_1 a
join table_2 b on a.id=b.id;
--等同于
select * from table_1 a
inner join table_2 b on a.id=b.id;
--或等同于
select * from table_1 a, table_2 b where a.id=b.id;
- (+)一般在旧版oracle常用,举例
select * from table_1 a, table_2 b where a.id=b.id(+);
--等同于右连接
select * from table_1 a
right join table_2 b on a.id=b.id;
说明:本文是用来本人自己学习与查阅用的,会不定时扩展,可能存在一些语法或其他问题,欢迎大家指正。