oracle常用sql

oracle常用sql收集。

开发类

1、统计某一字段出现的频率，使用分组+count。

查询col和col在表t_table中出现的次数。
select col, count(col) from t_table group by col;

2、查看最新执行的sql

使用dba权限
select * from V$SQL;

3、时间格式化
格式化参数
a、年份,Y表示最后一份;YY表示最后2位;YYY表示最后3位;YYYY表示最后3位；
b、月份,mm用2位数字表示;mon用简写形式,比如11月或者nov;month用全称,比如11月或者November；
c、日期,dd表示当月第几天;ddd表示当年第几天;dy表示当周第几天，简写，比如星期五或者fri;day当周第几天,全写比如星期五或者friday；
d、小时,hh2位数表示12小时制;hh24 表示24小时制；
e、分钟,mi表示分钟
f、秒,ss表示秒，60进制

--格式化成yyyy-mm-dd hh24:mi:ss
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; //显示2016-05-05 12:00:01
--格式化成yy-mm-dd hh24:mi:ss
select to_char(sysdate, 'yy-mm-dd hh24:mi:ss') from dual; //显示16-05-05 12:00:01
--

管理类

1、刷新物化视图
通过调用oracle函数 DBMS_MVIEW.Refresh(‘视图名称’, ‘C’)来刷新物化视图；
通过在sqlplus或者pl/sql developer来调用该函数来刷新。

call DBMS_MVIEW.Refresh('视图名称', 'C');

2、查询用户编码
select userenv(‘language’) from dual;

3、账号权限限制
使用场景：需要对外提供一个账号，但是仅限于指定的几张表、视图、存储过程、函数等资源，一般是只有select权限，这种情况可以通过新建一个账号，然后通过授权指定的权限和资源来实现权限限制的功能。
例如：张三想要student账号下的student表的select权限，实现方式如下:

1、创建用户 zhangsan ,密码 123456
create user zhangsan identified by 123456;
2、设置权限
grant select on student.student to zhangsan;

4、用户密码180天过期

1、查看口令有效期配置
select * from dba_profies where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
2、修改口令默认有效值从180天修改为无限制
alter profile default limit password_life_time unlimited;

统计分析类

1、查看表占用控件大小sql,显示表名、byte, kb, mb

select segment_name, bytes B, bytes / 1024 BK, bytes / 1024 / 1024 MB from user_segments;

2、查看数据库表的行数据的平均大小

select table_name, num_rows, avg_row_len from user_tables;