Oracle 常用数据库sql

select numtodsinterval(1000,‘minute’) from dual;–数字转时间格式单位分
Select round(sysdate,‘MONTH’) from dual;—日期四舍五入
Select SYS_EXTRACT_UTC(systimestamp) from dual;–返回格林威治时间
select to_char(trunc(sysdate,‘hh’),‘yyyy.mm.dd hh24:mi:ss’) hh,to_char(trunc(sysdate,‘mi’),‘yyyy.mm.dd hh24:mi:ss’) hhmm from dual;–格式化时间参数之后归零
Select TO_CLOB（n’中国’） from dual;–妆花to_clob类型
Select to_date(‘20130101’,‘yyyymmdd’) from dual;–转化时间格式，缺省支持
create table temp_liutao nologging as select VIEW_NAME,to_lob(text) text from user_views t ；–将LONG或LONG ROW列的数据转变为相应的LOB类型。但需要注意的是，在单纯的select语句中会报错
select to_number(‘1999’) year from dual;–转化成数字；
select TO_NCHAR (10) from dual;–可以将时间、数字、string转化为char
Select TO_SINGLE_BYTE(‘a b c’) from dual;—单字节转化为多字节
Select TRANSLATE(‘中国’ using nchar_cs) from dual;–char_cs转化为nchar_cs；
Select UNISTR (’\00D6’) from dual;–返回对应的unicode码值；

select avg(distinct sal) from gao.table3;–求平均值
select corr(list_,min_) from gao.table3;–功能:返回成对数值的相关系数，其数值使用表达式”covar_pop(expr1,expr2)/(stddev_pop(expr1)stddev_pop(expr2))”使用位置:查询列表和GROUP BY子句.
Select count(distinct sal) from emp;–得到查询中行的数目.如果使用了获得行的总数.如果在参数中传递的是选择列表,那么计算的是非空数值。
Select COVAR_POP(column1,column2) from emp;–返回成对数字的协方差，其数值使用表达式”(sum(expr1*expr2)-sum(expr1)sum(expr2)/n)/n”
Select CUME_DIST(2000) within group (order by sel) from emp; —返回特定数值在一组行数据中的累积分布比例。
Select DENSE_RANK (5000) within group (order by sel) from emp;—返回特定数据在一组行数据中的等级。.
Select min(sal) keep (dense_rank first order by comm desc) from emp;-- first 不能单独使用，必须与其他分组函数结合使用。通过使用该函数，可以取得排序等级的第一级，然后然后使用分组函数汇总该等级的数据。
Select deptno,job,avg(sal),group_id() from emp group by deptno,rollup(deptno,job);-- GROUP ID 功能:9i新增，用于区分分组结果中的重复行。
----------------------------------------------------------------------.。
select corp_code,org_level,count(1), grouping(corp_code), grouping(org_level) from tb_sys_organization group by rollup(corp_code, org_level);
select case grouping(corp_code) when 1 then ‘all_corp’ else corp_code end corp_code, case grouping(org_level) when 1 then ‘all_org’ else org_level end org_level,count(1)from tb_sys_organization group by rollup(corp_code, org_level);
—用于确定统计结果是否使用了特定的表达式，返回0则用到了表达式，1则未用。
Select deptno,job,sum(sal),grouping_id(job,deptno) from emp group by rollup(deptno,jon)— grouping_id 用于返回对应于特定行的grouping位向量的值。
Select min(sal) keep (dense_rank last order by comm) from emp;— last :9i新增，不能单独使用，必须与其他分组函数结合使用。通过使用该函数，可以取得排序等级的最后一级，然后使用分组函数汇总该等级的数据。
select listagg(o.rybs, ‘;’) within group(order by o.rybs) from gk_xszrr owhere rownum <= 100; --LASTAGG 列转恍惚是
— 获得由label界定的最小上界.用于trusted oracle.数据库.使用位置:trusted数据库的选择列表和GROUP BY子句.过程性语言和SQL语句。
select max(distinct sal) from scott.emp;—MAX 获得选择列表或表达式的最大值，ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
select min(all sal) from gao.table3;select min(all sal) from gao.table3;select min(all sal) from gao.table3;–； 得选择列表或表达式的最小值，ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
select percent_rank(3000) within group(order by sal) from emp;-- 语法:PERCENT_RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…)、、 该函数用于返回特定数值在统计级别中所占的比例。
select percentile_cont(.6) within group(order by sal) from emp; --语法:PERCENTILE_CONT(percent_expr)WITHIN GROUP (ORDER BY expr) 、、、 :9i新增，用于返回在统计级别中处于某个百分点的特定数值（按照连续分布模型确定）。
select percentile_cont(.6) within group(order by sal) from emp;–PERCENTILE_DISC(percent_expr)WITHIN GROUP (ORDER BY expr)、、、9i新增，用于返回在统计级别中处于某个百分点的特定数值（按照离散分布模型确定）。
select rank(3000) within group(order by sal) from emp; —语法:RANK(expr1,expr2…)WITHIN GROUP (ORDER BY expr1,expr2…) 、、、功能:该函数用于返回特定数值中所占据的等级。
select stddev(sal) from scott.emp; —语法:STDDEV([DISTINCT|ALL]col) 功能:获得选择列表的标准差.标准差为方差(VARIANCE)的平方根, ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差.
select stddev_pop(sal) from scott.emp; ----语法:STDDEV_POP(col) 、、、功能:返回统计标准差，其数值是统计方差的平方根.
select stddev_samp(sal) from scott.emp;---- 语法:STDDEV_SAMP(col)、、、功能:返回采样标准差，其数值是采样方差的平方根.
Select sum(sal) from emp; —语法:SUM([DISTINCT|ALL]col) 功能:返回选择的数值和总和
select VAR_POP (sal) from scott.emp; —语法:VAR_POP([DISTINCT|ALL]col)、、、功能:返回统计方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr)
select VAR_SAMP(sal) from scott.emp; ----语法:VAR_SAMP([col)、、、功能:返回采样方差.使用公式为(sum(exprexpr)-sum(expr)sum(expr)/count(expr))/(count(expr-1)
select variance(sal) from scott.emp;—语法:VARIANCE([DISTINCT|ALL]col) 、、、、功能:返回选择列或表达式的采样方差.使用公式为(sum(exprexpr)-sum(expr)*sum(expr)/count(expr))/(count(expr-1)
select product_id,CARDINALITY(ad_text) from a; --语法:CARDINALITY (nested_table) 、、、功能:10g新增函数，返回嵌套表的实际元素个数。
select cast(COLLECT(ad_text) as t) from a;–t是嵌套表、、、10g新增函数，用于根据输入列和被选中行建立嵌套表结果。
select cast(POWERMULTISET (ad_text) as t) from a;–t是嵌套表、POWERMULTISET(expr)、功能:10g新增函数，用于生成嵌套表的超集（包含所非空的嵌套表）。
select cast(POWERMULTISET_BY_CARDINALITY(ad_text) as t) from a;–t是嵌套表、语法:POWERMULTISET_BY_CARDINALITY(expr,cardinatility)、、、、功能:10g新增函数，用于根据嵌套表和元素个数，生成嵌套表的超集（包含所非空的嵌套表）。
select SET(nested_table) from a; —语法:SET(nested_table)、、、功能:改函数用于取消嵌套表中的重复结果，并生成新的嵌套表。
select DEREF(address).city from table_name;—语法:DEREF(expr) 功能:该函数用于返回参照对象exp所引用的对象实例。
select MAKE_REF(oc_inventocies,3003) from dual;—语法:MAKE_REF(object_table|object_view,key)、、、功能:该函数可以基于对象视图或对象表（存在基于主键的对象标识符）的一行数据建立REF。
select REF(e) from table_name e; ----语法:REF(expr)、、、功能:该函数用于返回对象行所对应的REF值。
select REFTOHEX(REF(e)) from table_name e; —语法:REFTOHEX(expr)、功能:该函数用于将REF值转变为十六进制字符串。
select value(e).city from table_name e;—语法:VALUE(expr)、、、功能:该函数用于返回行对象所对应的对象实例数据，其中expr用于指定行对象的别名。

导入导出;
客户端：exp help=y
服务端：expdp help=y

抽取表的定义：

select dbms_metadata.get_ddl('TABLE',upper('&i_table_name'),upper('&i_owner')) from dual;

select distinct sid,gvKaTeX parse error: Expected 'EOF', got '#' at position 15: session.SERIAL#̲,gvsession.username,
last_call_et, status, lockwait, machine, logon_time, sql_text, gv$session.osuserfromgv$session, gv$process,gv$sqlarea
where paddr = addr
and sql_hash_value = hash_value
and status = ‘ACTIVE’
and gv$session.username is not null;

ALTER SYSTEM KILL SESSION ‘1181,48803’;

select LAST_ACTIVE_TIME,t.* from gv$sql t where T.MODULE = ‘JDBC Thin Client’ and t.SQL_TEXT like ‘%THIS_%’ order by t.LAST_ACTIVE_TIME desc

SELECT SEGMENT_NAME,t.partition_name,T.BYTES/1024/1024/1024 nums FROM DBA_SEGMENTS T WHERE T.segment_name=upper(‘ZZ_DIFFICULTY_STU’)

–处理分区之后分区值不能改变的BUG
alter table ZZ_AID_SUB_LISTenable row movement;
–处理不能重名命
exec dbms_mview.begin_table_reorganization(‘XSZZ’,‘ZZ_AID_SUB_LIST’);

ALTER TABLE ZZ_AID_SUB_LIST RENAME TO ZZ_AID_SUB_LIST_OLD201711;
EXEC DBMS_MVIEW

alter table pg_other_sub_list parallel ( degree 32);
alter table pg_difficulty_stu parallel ( degree 32);
alter table pg_jm_sub_list parallel ( degree 32);

exec dbms_stats.gather_table_stats(ownname=>‘XSZZ’,tabname=>‘YWJY_DIFFICULTY_STU’,estimate_percent=>100,method_opt=>‘FOR TABLE’,CASCADE=>FALSE)

SELECT NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE FROM TABS WHERE TABLE_NAME=‘YWJY_DIFFICULTY_STU’

====
select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
a.sql_text Statement
from v$sqlarea a,dba_users b
where a.parsing_user_id=b.user_id
and a.disk_reads > 100000
order by a.disk_reads desc;

=====

为了快速的让程序恢复更新使用
select *　from　ｖ＄ｌｏｃｋ　where　ｂｌｏｃｋ＝１；把阻塞的进程全部杀死，是可以了但是治标不治本。找到具体的ｓｑｌ，让开发的同事改应用逻辑。
可以使用ａｗｒ找到具体的等待事件，在ｓｑｌ部分找到对应引起阻塞的ｓｑｌ。
或者使用
select sid,username,event from v$session where state in(‘WAITING’) and wait_class!=‘Idle’;

sid从上面的sql获得

select sid,sql_text from v$sessiona,v$sql b where sid in(282,496) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);

找到具体sql 告诉开发同事

ora 上的同步更改表出现错误

dbv userid=system/oracle file=+DATA/qgxszz/datafile/xszz_data200.dbf blocksize=8192;
检查数据块

dbv file=/u01/oracle/xszzdata/xszz_data02.dbf blocksize=8192;
dbv userid=system/oracle file=+DATA/qgxszz/datafile/xszz_data.273.873561695;
添加空间
ALTER TABLESPACE XSZZ_DATA ADD DATAFILE ‘+DATA/qgxszz/datafile/xszz_data203.dbf’ SIZE 5000M;

oracle 如何查找和kill 对应的job进程
查找当前被锁住的进程：
SELECT object_name, s.sid, s.serial#, p.spid
FROM v$locke{d}_{o}bjectl,db{a}_{o}bjectso,v$session s, v$process p
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
AND s.paddr = p.addr;
Kill 对应的Oracle Session
应为BROKEN后该JOB还在运行，如果要求该JOB立刻停止，就需要找到该job的对应SESSION(SID,SERIAL#)，然后执行以下命令：
ALTER SYSTEM KILL SESSION ‘sid,serial#’;

修改约束：
ALTER TABLE T1 modify CONSTRAINT CK_T1_TYPE CHECK (TYPE IN(‘A’,‘B’,‘C’)) ENABLE NOVALIDATE;

alter table zz_aid_sub_list add partition t_list_p4 values (default) tablespace XSZZ_DATA;

–6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$t{s}_{a}vaila,sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;

===============================================
　select

a.a1 表空间名称,

c.c2 类型,

c.c3 区管理,

b.b2/1024/1024/1024 表空间大小G,

(b.b2-a.a2)/1024/1024/1024 已使用G,

substr((b.b2-a.a2)/b.b2*100,1,5) 利用率

from

(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,

(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,

(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c

where a.a1=b.b1 and c.c1=b.b1;

查看processes
show parameter processes

D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

ALTER TABLESPACE zzpt_base ADD DATAFILE
‘D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ZZPT2_FILE.DBF’ SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10240M;

export ORACLE_SID=MOE
lsnrctl start

–1、查询数据库名：
select name,dbid from v$database;--或者showparameterd{b}_{n}ame;--2、查询实例名：selectinstanc{e}_{n}amefromv$instance;–或者show parameter instance_name;
–3、查询数据库域名：
select value from v$parameterwherename{=}^{\prime }d{b}_{d}omai{n}^{\prime };--或者showparameterdomain;--4、查询数据库服务器：selectvaluefromv$parameter where name=‘service_name’;–或者show parameter service;或者show parameter names;
–5、数据库服务名：此参数是数据库标识类参数，用service_name表示。数据库如果有域，则数据库服务名就是全局数据库名；如果没有，则数据库服务名就是数据库名。查询：show parameter service_name;

—字段
查询所有表名：
select t.table_name from user_tables t;
查询所有字段名：
select t.column_name from user_col_comments t;
查询指定表的所有字段名：
select t.column_name from user_col_comments t where t.table_name = ‘BIZ_DICT_XB’;
查询指定表的所有字段名和字段说明：
select t.column_name, t.column_name from user_col_comments t where t.table_name = ‘BIZ_DICT_XB’;
查询所有表的表名和表说明：
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;
查询模糊表名的表名和表说明：
select t.table_name from user_tables t where t.table_name like ‘BIZ_DICT%’;
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like ‘BIZ_DICT%’;

–查询表的数据条数、表名、中文表名
select a.num_rows, a.TABLE_NAME, b.COMMENTS
from user_tables a, user_tab_comments b
WHERE a.TABLE_NAME = b.TABLE_NAME
order by TABLE_NAME;

1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users; //查看当前用户

二、查看角色

1.当前用户被激活的全部角色
select * from session_roles;

2.当前当前用户被授予的角色
select * from user_role_privs;

3.全部用户被授予的角色
select * from dba_role_privs;

4、查看某个用户所拥有的角色

select * from dba_role_privs where grantee=‘用户名’;

5、查看某个角色所拥有的权限

select * from dba_sys_privs where grantee=‘CONNECT’;

6.查看所有角色
select * from dba_roles;

三、查看权限

1.基本权限查询：
select * from session_privs; --当前用户所拥有的全部权限
select * from user_sys_privs;–当前用户的系统权限
select * from user_tab_privs;–当前用户的对象权限
select * from dba_sys_privs ;–查询某个用户所拥有的系统权限
select * from role_sys_privs;–查看角色(只能查看登陆用户拥有的角色)所包含的权限

2. 查看用户的系统权限(直接赋值给用户或角色的系统权限)
   select * from dba_sys_privs;

select * from user_sys_privs;

2.查看用户的对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

3.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from v$pwfile_users;

扩展

1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map

2、查看一个用户的所有系统权限(包含角色的系统权限)
select privilege from dba_sys_privs where grantee=‘SCOTT’
union
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee=‘SCOTT’ );

3、 查询当前用户可以访问的所有数据字典视图。
select * from dict where comments like ‘%grant%’;

4、显示当前数据库的全称
select * from global_name;

问题 1：如何查询一个角色包括的权限？
a.一个角色包含的系统权限
select * from dba_sys_privs where grantee=‘角色名’

     select * from dba_sya_privs where grantee='COONNECT'; connect要大写
     另外也可以这样查看：   
     select * from role_sys_privs where role='角色名'   

b.一个角色包含的对象权限
select * from dba_tab_privs where grantee=‘角色名’

问题 2：Oracle究竟有多少种角色？
select * from dba_roles;

问题 3：如何查看某个用户，具有什么样的角色？
select * from dba_role_privs where grantee=‘用户名’

问题4：查看哪些用户具有DBA的角色

select grantee from dba_role_privs where granted_role=‘DBA’;