oracle sql 杂（待整理）

来源：互联网发布：武林外传知乎精华编辑：程序博客网时间：2024/06/11 12:38

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; --非连续排名

0 0