Oracle杂记（一）

记录Oracle日常稍有水平代码，成长自己，方便他人！

一、实现目标，分组拼接

原始数据->目标结果

->

步骤如下：

1、按照sghdbh, lh,进行分组，然后针对sgbh进行组内序号生成RN

SELECT sghdbh, lh,sgbh, ROW_NUMBER() OVER(PARTITION BY sghdbh, lh ORDER BY sgbh DESC) RN  

          FROM DW_ACD_HDZT2 

效果图：

2、根据分组的sghdbh和lh，对sgbh进行逗号拼接

SELECT sghdbh,lh, SYS_CONNECT_BY_PATH(sgbh, ',') sgbh  

FROM (SELECT sghdbh, lh,sgbh, ROW_NUMBER() OVER(PARTITION BY sghdbh, lh ORDER BY sgbh DESC) RN  

          FROM DW_ACD_HDZT2  where sghdbh='20160601101044')  

START WITH RN = 1 

CONNECT BY RN - 1 = PRIOR RN  

       AND sghdbh = PRIOR sghdbh  

效果图：

3、对每个组取sgbh列最大长度拼接行，并且去掉左边逗号

SELECT sghdbh,lh, LTRIM(SYS_CONNECT_BY_PATH(sgbh, ',')), ',') sgbh  

FROM (SELECT sghdbh, lh,sgbh, ROW_NUMBER() OVER(PARTITION BY sghdbh, lh ORDER BY sgbh DESC) RN  

          FROM DW_ACD_HDZT2  where sghdbh='20160531203260')  

START WITH RN = 1 

CONNECT BY RN - 1 = PRIOR RN  

       AND sghdbh = PRIOR sghdbh  

GROUP BY sghdbh,lh; 

最后结果：

二、查看windows jdk版本方法：

分别输入：

java -d32 -version

java -d64 -version

三、oracle通过servername查找sid

方法：

1、首先需要当前用户具有dba权限；

2、然后执行：

select instance_name from v$instance

四、oracle删除正在处于连接中的用户

方法：

--查看用户的连接状况

select username,sid,serial# from v$session;

--找到要删除用户的sid,和serial，并删除

alter system kill session'8,1131';

五、查看表空间的名称及大小

SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 

FROM dba_tablespaces t, dba_data_files d 

WHERE t.tablespace_name = d.tablespace_name 

GROUP BY t.tablespace_name; 

六、查看表空间的使用情况

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$ts_avail a, 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 file_id, file_name, tablespace_name, autoextensible, increment_by

FROM dba_data_files

WHERE tablespace_name = 'ITS_DATA'

ORDER BY file_id desc;



alter tablespace tablespaceName add datafile 

'+DATA/orcl/datafile/zhywjg10'  size 20480M;

八、通过修改SEC_D的数据文件为自动扩展达到表空间SEC_D为自动扩展的目的

 alter database datafile '/u01/app/oracle/oradata/orcl/sec_d01.dbf' autoextend on;

九、如用户一段时间没有作操作或会话没有同数据库交换数据，通过操时控制断开联接

假设你的用户名为 user_a，你要设计的时间是 50分钟没有操作就断开。

create profile my_profile limit idle_time 50;

alter user user_a profile my_profile;

10、查看表容量

select segment_name ,bytes/1024/1024/1024 from user_segments where segment_type='TABLE';

或者

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name；

over！后续追加