Oracle有用的SQL语句

[b]1.创建表空间[/b]
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;
[b]2.为表空间追加文件[/b]
Alter tablespace SF1
Add datafile 'D:\oracle\product\10.2.0\oradata\gdsf2\SF102.dbf' size 5000m
[b]3.为用户赋权[/b]
grant resource,connect to seu
[b]4.导出数据[/b]
exp username/password@gdsf file=d:\output.dmp owner=(username)
[b]5.导入数据[/b]
imp username/password@gdsf file=d:\imput.dmp full=y ingore=y
[b]6.改变用户的默认表空间[/b]
alter user pju default tablespace sf1;
[b]7.查看表空间大小[/b]
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;
[b]8. SDE创建用户[/b]
-- 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;
[b]9.关联更新[/b]
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
)
[b]10.查找表中多余的重复记录[/b]
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
[b]11.获取最新时间的记录集[/b]

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)