oracle常用命令
在线执行环境，缺点是需要注册
创建用户
alter session set "_ORACLE_SCRIPT"=true;
CREATE USER TOM IDENTIFIED BY a12345678;
GRANT CONNECT, RESOURCE, DBA TO TOM;
GRANT select on tom.contacts TO TOM;
REVOKE DBA FROM TOM;
REVOKE select on tom.contacts FROM TOM;
DROP USER TOM;
-- 同时删除用户下的所有对象
DROP USER TOM CASCADE;
-- 查看权限
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'TOM';
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
登录sys用户
sqlplus / as sysdba
-- 输入sys
-- 输入密码 as sysdba
修改数据库编码
重要数据记得备份，我导入的数据转码之后汉字全都乱码了
-- 查看oracle数据库的编码
select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';
-- oracle客户端编码
select * from nls_instance_parameters where parameter='NLS_LANGUAGE';
-- 修改oracle的字符集（->ZHS16GBK）
-- 以sysdba的身份登录
conn /as sysdba
-- 关闭数据库
shutdown immediate;
-- 以mount打来数据库
startup mount
--设置session
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
-- 启动数据库
alter database open;
-- 修改字符集
ALTER DATABASE CHARACTER SET ZHS16GBK;
-- 可能会报错,可以跳过超集的检查做更改
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
-- 关闭，重新启动
shutdown immediate;
startup
故障排查
-- 查看数据库连接数
SELECT COUNT(*) FROM v$session;
-- 查看允许的最大连接数
SELECT name, value FROM v$parameter WHERE name = 'sessions';
select current_utilization, limit_value from v$resource_limit where resource_name='sessions';
导出数据库
-- 导出某个用户下全部数据
exp tom/a12345678@orcl file=/path/backup.dmp owner=tom
-- 导出某个用户下特定表
exp tom/a12345678@orcl file=/path/backup.dmp tables=tableA,tableB
导入数据库
-- 导入的时候确定，导出和导入的数据库编码一致
imp tom/a12345678@orcl file=/path/backup.dmp fromuser=tom touser=jack ignore
-- 导入特定表
imp tom/a12345678@orcl file=/path/backup.dmp fromuser=tom touser=jack tables=tableA,tableB ignore
-- or
CREATE DIRECTORY MY_DIR AS '/path/';
impdb "'sys/sys@orcl as sysdba'" directory=MY_DIR DUMPFILE=backup.dmp TABLES=All
缩小oracle数据库内存占用
SELECT * FROM v$parameter WHERE name LIKE '%size%' OR name LIKE '%target%';
show parameter sga;
alter system set sga_max_size=1024M scope=spfile;
alter system set sga_target = 1024M scope=spfile;
show parameter sga;
shut immediate;
startup force;
-- 必须重启服务restart OracleServiceORCL service in windows
缩小oracle数据库磁盘占用
-- 运行下列查询，并复制结果执行即可
set linesize 1000 pagesize 0 feedback off trimspool on
with
hwm as (
-- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
),
hwmts as (
-- join ts# with tablespace_name
select name tablespace_name,relative_fno,hwm_blocks
from hwm join v$tablespace using(ts#)
),
hwmdf as (
-- join with datafiles, put 5M minimum for datafiles with no extents
select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
from hwmts right join dba_data_files using(tablespace_name,relative_fno)
)
select
case when autoextensible='YES' and maxbytes>=bytes
then -- we generate resize statements only if autoextensible can grow back to current size
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
else -- generate only a comment when autoextensible is off
'/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
||'M from '||to_char(ceil(bytes/1024/1024),999999)
||'M after setting autoextensible maxsize higher than current size for file '
|| file_name||' */'
end SQL
from hwmdf
where
bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
/
搜索表
SELECT table_name, owner FROM all_tables where table_name like '%USER%' and owner = 'TOM' ORDER BY owner, table_name;
--- 查找列
select owner, table_name from all_tab_columns where column_name = 'ID';
-- 查看表属性
desc USERINFO;
-- 表名的最大长度
-- version <= 12.1, 30 bytes
-- version >= 12.2, 128 bytes
-- 可使用describe all_tab_columns查看
复制表
-- 同时复制数据
CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name;
-- 只复制表结构，不包含索引
CREATE TABLE New_Table_name AS SELECT * FROM Existing_table_Name;
重命名表
ALTER TABLE table_name RENAME TO new_table_name;
查看正在运行中的会话，sql，锁表
select sid,
serial#,
osuser,
machine,
program,
module
from v$session;
SELECT to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') logontime
username,terminal,osuser,status,sid,serial#,program,floor(last_call_et / 60) "Minutes since active"
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time DESC;
-- 正在运行中sql
SELECT username,terminal,SID,a.module,ELAPSED_TIME,
SERIAL#,SQL_TEXT
FROM V$SESSION a, V$SQL b
WHERE a.SQL_ADDRESS = b.ADDRESS
AND a.STATUS = 'ACTIVE';
-- 查看锁表信息
Select substr(nvl(s.USERNAME,'sys'),1,9) "用户名",
nvl(s.TERMINAL,'None') "终端",
decode(l.LMODE,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',
null) "持有锁",
decode(l.REQUEST,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',
null) "申请锁",
SUBSTR(T1.NAME,1,13) "表名",
substr(l.SID||','||s.SERIAL#,1,10) "SID.SERIAL"
FROM V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where l.sid=s.sid
and t1.obj#=decode(l.id2,0,l.id1,l.id2)
and u1.user#=t1.owner#
and U1.NAME != 'SYS'
and s.TYPE != 'BACKGROUND'
order by 1,2,5;
查看日期
-- 一年前的日期
SELECT add_months(sysdate, -12) FROM DUAL;
-- 昨天的日期
SELECT sysdate - 1 FROM DUAL;
-- 当天开始时间
select trunc(sysdate) from dual;
-- 当年第一天
select trunc(sysdate,'yy') from dual;
-- 当月的第一天
select trunc(sysdate,'mm') from dual;
-- 本小时的开始时间
select trunc(sysdate ,'HH24') from dual;
-- 年份
select extract(year from to_date('20220101','YYYYMMDD')) from dual;
-- 字符串转日期
select to_date('20220101','YYYYMMDD') from dual;
-- 日期转字符串
select to_char(sysdate,'YYYYMMDD') from dual;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
-- 儒略日数(Julian Day)
select to_char(sysdate,'J') from dual;
-- 这个季度最后一天23点
select trunc(add_months(sysdate,3), 'Q') -1/24 "这个季度最后一天23点" from dual;
-- 两分钟后时间
SELECT sysdate + INTERVAL '2' minute FROM dual;
null相关
-- 返回一个非 null 值
SELECT nvl(null, '25') FROM dual;
-- 等于特定值时返回null，可用于被除数为0判断
select nullif(0, 0) from dual;
create table bookinfo (
id number(20),
name varchar2(50)
);
insert into bookinfo values(0,'');
insert into bookinfo values(1,null);
insert into bookinfo values(null,null);
insert into bookinfo values(2,'1 row(s) inserted.');
-- 在oracle中，空字符串会被当做null来处理
-- null比较时，要用is null，is not null
select * from bookinfo where name != '1 row(s) inserted.'; --搜索不到为null的数据
select * from bookinfo where name != '1 row(s) inserted.' or name is null; --可搜索为null的数据
-- 排序时null的位置
-- 可通过NULLS FIRST，NULLS LAST控制
SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST
-- 不指定时，升序为NULLS LAST，降序NULLS FIRST（可简易将null视为最大值）
修改列属性
alter table USERINFO modify address varchar2(350);
-- 缩小列长度，改变类型
alter table USERINFO modify address_temp varchar2(250);
update USERINFO set address = address_temp;
alter table USERINFO drop address;
rename column USERINFO.address_temp to USERINFO.address;
COMMENT ON COLUMN USERINFO.address IS '地址';
-- 查看一个复杂查询语句的结果列属性和长度
-- 使用查询语句创建view，然后使用desc即可
查看目录
CREATE DIRECTORY MY_DIR AS '/path/mydata/';
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='MY_DIR';
批量插入行
-- 使用循环
BEGIN
FOR v_LoopCounter IN 100..120 LOOP
INSERT INTO BOOKINFO (ID,NAME)
VALUES (trunc(dbms_random.value(0,1e19))-1,'test'||v_LoopCounter);
END LOOP;
COMMIT;
END;
-- 使用row generator（dual connect by）
INSERT INTO BOOKINFO (ID,NAME)
select trunc(dbms_random.value(0,1e20))-1, name
from BOOKINFO main
inner join (select 1 from dual connect by level <= 100) sub on 1=1
获取随机数
-- 小数( 0 ~ 1)
select dbms_random.value from dual ;
-- 指定范围内的小数 ( 0 ~ 100 )
select dbms_random.value(0,100) from dual ;
-- 获取长度为20的随机整型数
select trunc(dbms_random.value(0,1e20-1)) from dual ;
-- 随机字符串 'u':大写字母，'l':小写字母， 'a':大、小写字母，'x':数字、大写字母，'p':可打印字符
select dbms_random.string('p', 30) from dual ;
-- 随机日期
select to_date(to_char(add_months(sysdate, -12),'J') + TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual ;
-- 生成GUID(32位)
select sys_guid() from dual ;
其他
-- 取得行号
select rownum from dual;
-- 转大写
select upper('abc') from dual;
统计
-- 建表
-- drop table userinfo;
create table userinfo (
id number(20),
name varchar2(50),
dept number(5),
salary number(8),
createtime date
);
-- 插入批量数据
-- 构造50条数据
insert into userinfo
select trunc(dbms_random.value(0,1e20))-1, dbms_random.string('u', 5),
trunc(dbms_random.value(1,5)),trunc(dbms_random.value(1, 1e7)),
to_date(to_char(add_months(sysdate, -12),'J') + TRUNC(DBMS_RANDOM.VALUE(0,365)),'J')
from dual connect by level <= 50 ;
-- 根据user表数据为每条记录生成3条userorg表记录插入
INSERT INTO userorg (id, user_id, org_id,createdate)
SELECT trunc((sysdate - date '1970-01-01') * 864000000) + rownum, user_id, du.abc,
CREATEDATE + numtodsinterval (rownum ,'second')
FROM user
inner join (select level abc from dual connect by level < 3) du on 1=1;
-- 查询员工id和员工总数
select id,count(*) over() cnt from userinfo order by salary desc;
-- 按工资排序，同时记录工资排名
select id,name,dept,salary,createtime,rownum,rowid,row_number() over(order by salary desc) from userinfo order by salary desc;
-- 查询每个部门工资最高的员工信息
-- rank()/dense_rank() 区别在于排序时遇到相同值时，rank会跳号
select * from (select id,name,dept,salary,createtime,rank() over(partition by dept order by salary desc) rank from userinfo) sub where rank = 1 order by dept;
-- 查询员工信息的同时，查询所在部门最低、最高工资
select id,name,dept,salary,createtime,max(salary) over(partition by dept) dept_max_salary,min(salary) over(partition by dept) dept_min_salary from userinfo;
-- 查询员工信息，查询工资比自己高一位，低一位的工资
select id,name,dept,salary,lead(salary, 1, null) over(partition by dept order by salary) higher,lag(salary, 1, null) over(partition by dept order by salary) lower from userinfo order by dept;
-- 升序排序时，默认是NULLS LAST，降序排序时，默认是NULLS FIRST，如果想null排前面使用nulls first
select id,name,dept,salary,lead(salary, 1, null) over(partition by dept order by salary) higher,lag(salary, 1, null) over(partition by dept order by salary) lower
from userinfo order by higher nulls first;
-- 查询员工信息，查询部门总工资，最高工资，人数
select id,name,dept,salary,sum(salary) over(partition by dept) dept_salary,max(salary) over(partition by dept) dept_max_salary,count(salary) over(partition by dept) dept_staff_num from userinfo order by dept;
-- 查询员工信息，查询工资在公司内属于前五分之几，部门内属于前三分之几
select id,name,dept,salary,ntile(5) over(order by salary desc) comp_rank,ntile(3) over(partition by dept order by salary desc) dept_rank
from userinfo order by dept,comp_rank;
-- 查询部门内最晚入职，工资最高的员工
select dept,max(name) keep(dense_rank last order by createtime),max(salary) keep(dense_rank last order by createtime),max(createtime) keep(dense_rank last order by createtime)
from userinfo group by dept;
使用临时表查询
-- 适用于优化sql，复杂sql中有些共通子查询多次重复出现时，可提取出临时表减少查询次数
with dept1_userinfo as (select * from userinfo where dept = 1 fetch first 1 rows only)
select * from dept1_userinfo inner join (select 1 from dual connect by level <= 100) sub on 1=1;
字符串操作
-- 字符串连接
select 'A' || 'B' from dual;
-- 换行符 CHR(13) \r, CHR(10) \n
select 'A' || chr(10) || 'B' from dual;
-- A
-- B
-- 字符串截取
select substr('ABCDEFG',2, 5) from dual;
-- BCDEF
select substr('ABCDEFG',-5, 2) from dual;
-- CD
-- 查看字符串长度
select length('ABCDEFG号码') from dual;
-- 9
-- 查看字符串字节长度
select lengthb('ABCDEFG号码') from dual;
-- 11
-- 查找字符串
select instr( 'oracle pl/sql cheatsheet', 'l', 6, 2) from dual;
-- 13 - 从六位开始查找，第二次出现的位置，找不到则返回0
-- 替换字符串
select replace('i am here','am','am not') from dual;
-- i am not here --第三个参数可省略，与设为空字符串等效
-- trim字符串
select trim (' removing spaces at both sides ') from dual;
-- removing spaces at both sides
select '|' || trim (' removing spaces at both sides ') || '|' from dual;
-- | removing spaces at both sides | -- 无法trim全角空格
select '|' || trim (LEADING ' ' FROM ' removing spaces at both sides ') || '|' from dual;
select '|' || ltrim (' removing spaces at both sides ') || '|' from dual;
-- |removing spaces at both sides |
select '|' || trim (TRAILING ' ' FROM ' removing spaces at both sides ') || '|' from dual;
select '|' || rtrim (' removing spaces at both sides ') || '|' from dual;
-- | removing spaces at both sides|
select '|' || trim (both '-' FROM '--- removing spaces at both sides ---') || '|' from dual;
select '|' || trim ('-' from ' removing spaces at both sides ') || '|' from dual;
-- | removing spaces at both sides |
-- 大小写转换
select upper('removing spaces at both sides') from dual;
-- REMOVING SPACES AT BOTH SIDES
select lower(upper('removing spaces at both sides')) from dual;
-- removing spaces at both sides
-- 忽略大小写搜索字符串
select * from my_table where upper(column_1) = upper('my_string');
-- 想优化搜索速度的话，可以加索引
CREATE INDEX my_table_idx ON my_table (UPPER(column_1));
-- 注意点是搜索时要确保字段数据中没有null值，索引才能生效
select * from my_table where upper(column_1) is not null and upper(column_1) = upper('my_string');
-- 正则表达式搜索
-- Search for strings that have the word "焼肉" anywhere except at the beginning or the end
SELECT * FROM sample_table WHERE REGEXP_LIKE(text, '.焼肉.');
-- 今日は焼肉を食べたい
-- 忽略大小写
-- Search for strings that have the word "yakiniku" in any case
SELECT * FROM sample_table
WHERE REGEXP_LIKE(text, 'yakiniku', 'i');
-- I love YAKINIKU
位运算
-- 位与运算
SELECT bitand(7, 4) FROM dual;
-- 位或运算 BITOR(x,y) = x + y - BITAND(x,y)
SELECT 5 + 3 - bitand(5, 3) FROM dual;
-- 位与或运算 BITOR(x,y) = x + y - 2 * BITAND(x,y)
SELECT 5 + 3 - 2 * bitand(5, 3) FROM dual;
count函数
-- count(1)和count(*)是相同的
-- COUNT (<expression>) 统计非空值个数
有则更新，无则插入
BEGIN
UPDATE userinfo SET salary = 250 WHERE name = 'tom';
IF SQL%ROWCOUNT = 0
THEN
insert into userinfo(name,salary) values('tom', 100);
END IF;
END;
在更新的时候使用join
update (select b.bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N') t
set t.bonus = 0;
-- 更新第一条(fetch first会报错)
update (select b.bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N' and rownum = 1) t
set t.bonus = 0;
比较两个表的数据
(select * from T1 minus select * from T2) -- all rows that are in T1 but not in T2
union all
(select * from T2 minus select * from T1) -- all rows that are in T2 but not in T1
;
使用connect by抽出层级数据
select lpad('-',2*(level-1),'-')||ename ename,rownum,level
from emp
start with mgr is null
connect by mgr = prior empno;
fetch first 和 rownum比较
oracle中对rownum有优化，所以优先使用rownum
查看执行计划
set serveroutput off;
alter session set statistics_level = all;
-- 执行SQL
select * from tableA where id = 12;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last cost'));
-- 出来表格里E-Rows代表预测值，A-Rows代表实际值
数字类型长度
-- NUMERIC(6, 2) 表示范围 -9999.99-9999.99
sql developer使用
快捷键
ctrl+shift+D 复制行
数据库格式不是UNICODE时的生僻字无法显示问题
把列类型转换为NVARCHAR即可，注意NVARCHAR2的长度是字符
如果原来是GBK编码VARCHAR2(100)最大存50个汉字，转换后NVARCHAR2(50)即可
转换完成后需要排查使用sql中用到这个字段case when或者union的情况，这里很可能会报错，union ‘‘要改成union null或者union N’’
用到mybatis的地方也要修改，有可能插入生僻字时，参数类型要改为NVARCHAR(#{value,jdbcType=NVARCHAR})
reference
- How to Create a User and Grant Permissions in Oracle
- security - How to find the privileges and roles granted to a user in Oracle? - Stack Overflow
- ORACLE TRUNC()函数
- Oracle：trunc()函数简介
- Oracle中除数为0的两种解决办法（decode与nullif）
- RENAME COLUMN statement
- DROP USER
- oracle查看编码以及修改编码
- How do I limit Oracle’s memory use?
- Check session status active, inactive, sql query in Oracle
- ORACLE 如何产生一个随机数
- DBMS_RANDOM : Generating Random Data (Numbers, Strings and Dates) in Oracle
- How to use for loop for insert in Oracle?
- 使用Oracle的存储过程批量插入数据
- Format Models
- What is rowID & rowNum (ROWID vs ROWNUM)
- Oracle分析函数Over()
- SQL oracleでnullを比較する際の注意点
- Analytic functions: FIRST vs FIRST_VALUE
- ORACLE WITH AS 用法,创建临时表
- NULL Values in ORDER BY - Oracle to MySQL Migration
- How to : Insert If Not Exist, Update When Exist, Then Returning the Value ?
- Update statement with inner join on Oracle
- SQL developer shortcut of duplicate line
- ORDER BY clause
- What is the maximum length of a table name in Oracle?
- Pass time interval via variable in Oracle SQL
- Generating lots of rows using connect by – safely!
- oracle diff: how to compare two tables?
- diffrence between ROWNUM an d LEVEL
- Oracle中的层次查询详解 - Jepson6669 - 博客园
- oracle进阶之connect by笔记 - 追逐繁星 - 博客园
- Search an Oracle database for tables with specific column names?
- How to check the maximum number of allowed connections to an Oracle database?
- Oracle maximum sessions allowed
- Oracle shrinking / reclaiming free tablespace space
- Resize your Oracle datafiles down to the minimum without ORA-03297
- Reclaim Unused Space in Oracle
- Oracle Database/SQL Cheatsheet - Wikibooks, open books for an open world
- test oracle REGEXP_LIKE
- Fetch First vs. Rownum | Oracle Scratchpad
- Oracle SQL実行計画の読み方 | コーソルDatabaseエンジニアのBlog
- Oracleのヒント句を使いこなす - Qiita
- [SQL]JOINの3種類のアルゴリズムについて
- PostgreSQL: LIMIT vs FETCH FIRST ROWS … WITH TIES - CYBERTEC
- SQL PARTITION BYの基本と効率的に集計する便利な方法
- [SQL]JOINの3種類のアルゴリズムについて
- NESTED LOOPS & HASH JOIN & SORT MERGE JOIN - 凡轩之 - 博客园
- 「Oracle」改行コード(CHR(13)、CHR(10))を取得、更新、置換する方法 - 最新IT技術情報_arkgame.com
- Oracle NUMBER Data Type By Practical Examples
- oracle 生僻字乱码问题 - 小秦的开发梦 - 博客园 ↗
- SQL Server中生僻字问题存储与查询问题 - 风枫疯 - 博客园 ↗
- NVARCHARの桁数の罠 - bt ↗
- 【SQL Server】データ型の種類｜PG-LIFE ↗
- sql - mybatis text literal n without Tomcat parameters - Stack Overflow
- Exporting many tables on Oracle
- There is no BITOR() in Oracle SQL
- How to use Oracle bitwise operators &, |, ^, ~?
- ２つの数値をビットデータとして扱いビット演算を行なう
Updated: 2023-07-01 20:00
Created: 2022-11-06 19:00