oracle sql 杂（待整理）

create tablespace MyTableSpaceTest datafile 'F:\llccdd2016-8-3\test\mytest_off1.dbf' size 1048K autoextend off;   --创建表空间，数据文件，大小1048K，不自动增长
alter tablespace MyTableSpaceTest add datafile 'F:\llccdd2016-8-3\test\mytest_off2.dbf' size 1048K autoextend off;  --表空间添加数据文件，大小1048K，不自动增长
create tablespace MyTableSpaceTest2 datafile 'F:\llccdd2016-8-3\test\mytest_on.dbf' size 1048K autoextend on next 1M;  --创建表空间，数据文件，大小1048K，自动增长，每次增长1M


drop tablespace MyTableSpaceTest;  --删除表空间（数据文件还在，只能手动删除？）


create table test2 (  --创建表到指定表空间
  tn number,
  tv varchar2(50)
) tablespace MYTABLESPACETEST;


insert into test2 values (1,'Jack');
commit;


select * from test2;








SELECT t1 表空间,z 总表空间,z-s 已用表空间,s 剩余表空间,ROUND((z-s)/z*100,2) "使用率%"
     From （Select tablespace_name t1,Sum(bytes) s
              From DBA_FREE_SPACE Group by tablespace_name),
           (Select tablespace_name t2,Sum(bytes) z
           From DBA_DATA_FILES Group by tablespace_name) Where t1=t2;




 1. 查看所有表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 || 'M' from dba_data_files
 group by tablespace_name;
2. 已经使用的表空间大小
SQL> select tablespace_name,sum(bytes)/1024/1024 || 'M'
 from dba_free_space
 group by tablespace_name;
3. 所以使用空间可以这样计算
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 || 'M'
 total from dba_data_files
group by tablespace_name) a, 
( select tablespace_name,sum(bytes)/1024/1024|| 'M' free from dba_free_space
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;


4. 下面这条语句查看所有segment的大小。
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
5. 还有在命令行情况下如何将结果放到一个文件里。
SQL> spool out.txt
SQL> select * from v$database;
SQL> spool off






















SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

　　ORDER BY 1;

create table dep(
 deptno  varchar2(50) primary key,
 deptname   varchar2(50)
);
insert into dep values ('1','人事部');
insert into dep values ('2','技术部');
insert into dep values ('3','采购部');
commit;


create table dep_emp(
 deptno  varchar2(50),
 empno   varchar2(50),
 ename   varchar2(50),
 sal     number(10,2)
);


alter table dep_emp add constraint deptno_fk foreign key (deptno) references dep(deptno) on delete cascade;


insert into dep_emp values ('1','011','张三',3000);
insert into dep_emp values ('1','012','李四',3400);
insert into dep_emp values ('2','021','王五',4300);
insert into dep_emp values ('2','021','张三',3200);
insert into dep_emp values ('3','031','李四',3400);
insert into dep_emp values ('3','032','王五',2300);
commit;


--select * from user_tables;
--select * from dep;
--select * from dep_emp;

--显示各部门员工的工资，并附带显示该部分的最高工资。


SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录，也就是表中的所有记录
            --unbounded：不受控制的，无限的
            --preceding：在...之前
            --following：在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM DEP_EMP E;
  
--按照deptno分组，然后计算每组值的总和


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM DEP_EMP;
  
--对各部门进行分组，并附带显示第一行至当前行的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM DEP_EMP;
  
--当前行至最后一行的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM DEP_EMP;
  
--当前行的上一行(rownum-1)到当前行的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM DEP_EMP;
  
--当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总


SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM DEP_EMP;










select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by cube(d.deptno);  --分组后还有对于薪水的总体总计
select d.deptno,d.empno,sum(d.sal),grouping(d.empno) from dep_emp d group by rollup(d.deptno,d.empno);  --分组后每组的总计和总体的总计
select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by d.deptno;          --普通分组  


--cube，总计显示在上面(过于深奥，比rollup更细)
select d.deptno,sum(d.sal),grouping(d.deptno) from dep_emp d group by cube(d.deptno);  --分组后还有对于薪水的总体总计
select d.deptno,d.empno,sum(d.sal),grouping(d.empno) from dep_emp d group by cube(d.deptno,d.empno);  --分组后每组的总计和总体的总计


--grouping用作小计（次数）,可以看做树型的层级
--partition by 暂看成分组（不是很理解）






--ratio_to_report(所占百分比)
select dense_rank()over(order by d.deptno desc),ratio_to_report(d.deptno)over() from dep_emp d;  --连续排名
select rank()over( order by d.deptno desc),ratio_to_report(d.deptno)over() from dep_emp d;   --非连续排名