Oracle常用SQL语句记录(备查)

--创建表空间
CREATE TABLESPACE SCOTT_TBS DATAFILE "F:\ORACLE\ORADATE\oral\scott_tbs01.dbf" size 100M

--修改用户默认表空间
ALTER USER SCOTT DEFAULT TABLESPACE SCOTT_TBS QUOTA UNLIMITED ON SCOTT_TBS;

--修改表所属表空间
alter table SCOTT.EMP move tablespace SCOTT_TBS;

--修改表空间的大小

alter database datafile 'D:\oracle\product\10.2.0\db_2\oradata\oral\SCOTT_TBS01.DBF' resize 300M;

--查看oracle表空间使用情况

select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name

--分组，列转行

select t.id,
sum(decode(t.b, '100', b, null)) as ptid_100,
sum(decode(t.b, '200', b, null)) as ptid_200,
sum(decode(t.b, '300', b, null)) as ptid_300,
sum(decode(t.b, '400', b, null)) as ptid_400
from 表 t
group by t.id