Oracle有用的SQL语句

1.创建表空间
create tablespace SF1
datafile 'D:\oracle\product\10.2.0\oradata\gdsf2\SF101.dbf' size 10000M
autoextend on next 100M maxsize unlimited logging
extent management local autoallocate
segment space management auto;
2.为表空间追加文件
Alter tablespace SF1
Add datafile 'D:\oracle\product\10.2.0\oradata\gdsf2\SF102.dbf' size 5000m
3.为用户赋权
grant resource,connect to seu
4.导出数据
exp username/password@gdsf file=d:\output.dmp owner=(username)
5.导入数据
imp username/password@gdsf file=d:\imput.dmp full=y ingore=y
6.改变用户的默认表空间
alter   user   pju   default   tablespace  sf1;
7.查看表空间大小
select a.tablespace_name,
used/1024/1024||'M' used,
round(100-b.free/used*100,2)||'%' used_pct,
round(free/1024/1024,2)||'M' free,
round(max_size/1024/1024)||'M' max_size,
round(max_size/1024/1024)-used/1024/1024||'M' "MAX_SIZE-TOTAL"
from
(select tablespace_name,sum(bytes) used,
sum(case when maxbytes=0 then bytes else maxbytes end) max_size
from dba_data_files
group by tablespace_name
)a,
(select tablespace_name,sum(bytes) free
from dba_free_space
group by tablespace_name
)b
where a.tablespace_name=b.tablespace_name;
8. SDE创建用户
-- Create the user
create user sf2domuser
  identified by sf2domuser
  default tablespace SDE_DOM1
  temporary tablespace TEMP;
-- Grant/Revoke role privileges
grant connect to sf2domuser;
-- Grant/Revoke system privileges
grant administer database trigger to sf2domuser with admin option;
grant alter any index to sf2domuser with admin option;
grant alter any table to sf2domuser with admin option;
grant analyze any to sf2domuser with admin option;
grant create any index to sf2domuser with admin option;
grant create any table to sf2domuser with admin option;
grant create any procedure to sf2domuser with admin option;
grant create any sequence to sf2domuser with admin option;
grant create any trigger to sf2domuser with admin option;
grant create any view to sf2domuser with admin option;
grant create any indextype to sf2domuser with admin option;
grant create library to sf2domuser with admin option;
grant create operator to sf2domuser with admin option;
grant create public synonym to sf2domuser with admin option;
grant create session to sf2domuser with admin option;
grant create type to sf2domuser with admin option;
grant create view to sf2domuser with admin option;
grant drop any index to sf2domuser with admin option;
grant debug any procedure to sf2domuser with admin option;
grant drop any sequence to sf2domuser with admin option;
grant delete any table to sf2domuser with admin option;
grant drop any view to sf2domuser with admin option;
grant drop public synonym to sf2domuser with admin option;
grant select any table to sf2domuser with admin option;
grant select any sequence to sf2domuser with admin option;
grant unlimited tablespace to sf2domuser with admin option;
9.关联更新
UPDATE weatherforecast a
   SET a.x = (SELECT b.centerx
                       FROM cityarea b
                      WHERE b.code = a.dscd)
WHERE EXISTS (SELECT 1 FROM cityarea c WHERE c.code = a.dscd)
select * from cityarea b,weatherforecast a where a.dscd = b.code

update weatherforecast a -- 使用别名
set x=(select b.centerx from cityarea b where b.code=a.dscd)
where exists (select 1
from cityarea b
where b.code=a.dscd
)
10.查找表中多余的重复记录
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)
11.获取最新时间的记录集

select d.*
  from (select b.*, a.stnm
          from STU.ST_STBPRP_B a, stu.ST_RIVER_R1 b
         where a.stcd = b.stcd
       
         order by b.tm desc, a.stcd desc) d
where d.tm in (select max(c.tm)
                  from (select b.*, a.stnm
                          from STU.ST_STBPRP_B a, stu.ST_RIVER_R1 b
                         where a.stcd = b.stcd
                       
                         order by b.tm desc, a.stcd desc) c
                 where c.stcd = d.stcd)