Oracle 常用查询

主要记录一些工作中偶尔会用到的一些 Oracle 专有的查询和操作。

切换当前 schema，以及查看当前 schema 和当前登录 User。

alter session set current_schema=<target_schema>;
select user, sys_context('USERENV','CURRENT_SCHEMA') from dual;

--查看当前用户的所有表
select * from user_tables;
--查看所有用户表的列
select * from user_tab_columns;
--查看表的创建，修改时间
select * from user_objects;

查看所有用户

select * from dba_users;
select * from all_users;
select * from user_users;

查看用户或角色系统权限（直接赋给用户或角色的系统权限）

select * from dba_sys_privs;
select * from user_sys_privs;

--查看角色（只能看登录用户所拥有的角色）所包含的权限
select * from role_sys_privs;
--查看当前用户所拥有的权限
select * from session_privs;

比如我要查询用户 Bobby 所拥有的权限：

SQL> select * from dba_sys_privs where grantee='BOBBY';

GRANTEE             PRIVILEGE                   ADMIN_OPTION
------------------- --------------------------- ---------------
BOBBY               CREATE_TRIGGER               NO
BOBBY               UNLIMITED TABLESPACE         NO

查看用户对象权限

select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

查看所有角色

select * from dba_roles;

查看用户所拥有的角色

select * from dba_role_privs;
select * from user_role_privs;

比如我要查询用户 Bobby 所拥有角色：

SQL> select * from dba_role_privs where grantee='BOBBY';

GRANTEE         GRANTED_ROLE        ADMIN_OPTION        DEFAULT_ROLE
--------------- ------------------- ------------------- ---------------
BOBBY                 DBA              NO                   YES

查看哪些用户具有 sysdba 或者 sysoper 系统权限（查询时需要具有相应的权限）

select * from V$PWFILE_USERS;

查看一个用户所有的权限及角色

select privilege from dba_sys_privs where grantee='BOBBY'
union
select privilege from dba_sys_privs where grantee in （
  select GRANTED_ROLE from dba_role_privs where grantee='BOBBY'
);

查看数据库 NLS_LENGTH_SEMANTICS 参数值（这个参数只有两个可选值，即 byte 和 char。默认是 byte）

select * from nls_database_parameters t where t.parameter='NLS_LENGTH_SEMANTICS';

--修改字符串的单位，varchar2(32 CHAR)
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;

查看数据库 NLS_CHARACTERSET 参数值

select * from nls_database_parameters t where t.parameter='NLS_CHARACTERSET';

查看当前库中被锁的资源以及被谁锁了

select o.object_name,s.machine, l.os_user_name, s.sid, s.serial#, s.username, s.logon_time 
from v$locked_object l, dba_objects o, v$session s
where l.object_id=o.object_id and l.session_id=s.sid;

根据 sid 查看锁操作的具体 SQL 语句，如果 SQL 不重要，可以直接 kill 掉。

select sql_text from v$session a, v$sqltext_with_newlines b
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
and a.sid=&sid
order by piece;

kill 掉该事务（需要具有相应权限才行）

alter system kill session '&sid,&serial';

左/右两端补空格的 Oracle 函数。

lpad(string, padded_length, [padding_string])
rpad(string, padded_length, [padding_string])

修改表结构

--修改列名
alter table taget_table RENAME COLUMN col_1 to col_1;

--添加列
alter table 表名 ADD (字段名 字段类型) [default '默认值'] [ null / not null]

--删除列
alter table 表名 drop (字段名)

--修改列长度或类型，（如果长度由长变短，且数据库中现有值超过这个最大长度，那么直接修改会失败，需要修改表中现有值）
alter table employee_info modify sex char(2);

创建同义词

--首先将所有者（onwer）的表的权限分配给目标用户 (target_uesr)
GRANT SELECT, INSERT, UPDATE ON table_owner.TABLE TO target_user;

--再给目标用户(target_uesr) 创建同义词，指向所有者(owner) 的表
CREATE OR REPLACE SYNONYM target_user.TABLE FOR table_owner.TABLE;

查找所有表上的外键约束

select * from user_constraints where constraint_type='R';

--生成禁用所有外键的 SQL
select 'alter table ' || table_name || ' disable constraint' || constraint_name || ';' 
from user_constraints where constraint_type='R'; 

NVL2(expr1, expr2, expr3) 表达式

如果参数表达式 expr1 的值为 NULL，则 NVL2 函数的返回值即 expr3 的值，否则为 expr2 的值。

查看表的赋权情况

select * from user_tab_privs_made;

查看 SQL 的执行记录

select * from v$sql where sql_text like 'delete from employee%';

select sql_text, module, first_load_time from v$sqlarea
where first_load_time > '2021-04-05' and sql_text like '%EMPLOYEE%'
order by first_load_time desc;

时间计算

使用内置函数 numtodsinterval 增加小时数，分钟和秒

-- 加 8 小时
select sysdate, (sysdate + numtodsinterval(8, 'hour')) as LOCAL_TIME from dual;

加一个简单的数来增加天

-- 加 1 天
select sysdate, (sysdate + 1) as NEXT_DAY from dual;

使用内置函数 add_months 来增加年和月

-- 加 1 月
select sysdate, add_months(sysdate, 1) as NEXT_MONTH from dual;

to_date 和 to_char 函数

to_date('2021-04-05 11:40:45', 'yyyy-mm-dd hh24:mi:ss')
to_char(data_value, 'yyyy-mm-dd hh24:mi:ss')

SQL 语句中使用 json_value 计算 JSON 值

select json_body
from table1
where json_value(json_body, '$.reqbody.country')='CHINA'
and create_time > (sysdate-1);

查看数据中每个表所占大小

select t.bytes, round(BYTES/1024/1024, 2) as MB, t.*
from sys.dba_segments t
where t.OWNER='<USRE_NAME>'
and segment_type='TABLE'
order by t.bytes desc;

--或
select t.bytes, round(BYTES/1024/1024, 2) as MB, t.*
from user_segments t
where segment_type='TABLE'
order by MB desc;

查看数据库允许的最大连接数

select value from v$parameter where name='processes';
--或者 
show parameter processes; 

--查看当前会话连接数
select count(*) from v$session;
--查看当前进程连接数
select count(*) from v$process;
--查看数据库并非连接数
select count(*) from v$session where status='ACTIVE';
--查看当前数据库建立的会话情况
select sid,serial#,username,program,machine,status from v$session; 

查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext, machine 
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;

查询当前数据库信息

select * from v$instance;

查询数据库启动时间和运行时间

select to_char(startup_time, 'DD-MON-YYYY HH24:MI:SS') as 启动时间，
TRUNC(sysdate-(startup_time))||'天'
||TRUNC(24*((sysdate-startup_time)-TURNC(sysdate-startup_time)))||'小时'
||MOD(TRUNC(1440*((sysdate-startup_time)-TURNC(sysdate-startup_time))),60)'分'
||MOD(TRUNC(86400*((sysdate-startup_time)-TURNC(sysdate-startup_time))),60)'秒' as 运行时间
from v$instance;

时间和时区

--数据库设置的时区信息
select dbtimezone from dual;
--修改当前session的时区信息
alter session set TIME_ZONE='+08:00';
select sessiontimezone from dual;
--数据库服务当前的时间，只含时分秒
select sysdate from dual;
--客户端当前时间，只含时分秒，不含时区信息
select current_date from dual;
--数据库服务当前的时间，包含时区信息
select systimestamp from dual;
--客户端当前时间，包含小数秒，不含时区信息
select localtimestamp from dual;
--客户端当前时间，包时区信息
select current_timestamp from dual;

sysdate 时获取数据库所在操作系统的时间，与数据库或者会话无关，在session 建立时与服务端同步。current_date返回数据库会话所设置的本地时间，在session 建立的时候与客户端同步，可通过alter session set time_zone='+08:00' 的方式修改。

sessiontimezone 与客户端 session 所在操作系统一致，可通过alter session set time_zone='+08:00' 的方式修改。dbtimezone 为数据库的时区。

Reference: https://www.cnblogs.com/muhai/p/16623341.html

查看数据库表名、列名、约束名长度限制

--查看数据库表名长度限制
select * from all_tab_columns where table_name='USER_TABLES' and column_name='TABLE_NAME';
--查看数据库列名长度限制
select * from all_tab_columns where table_name='USER_TAB_COLUMNS' and column_name='COLUMN_NAME';
--查看数据库约束名长度限制
select * from all_tab_columns where table_name='USER_CONSTRAINTS' and column_name='CONSTRAINT_NAME';

Oracle 常用查询

Comments