sql技巧

1.like查询时，输入如："%"、"_"查不到想要的结果，使用escape转义要查询的字符，可正确查出结果

select * from table where name like '%|%%' escape '|'; 

上例是将“%”（百分号），转义为“|”（竖）

2.查看表空间

select 
  a.a1 as 表空间名称,
  c.c2 as  类型,
  c.c3 as 区管理,
  b.b2 / 1024 / 1024 as 表空间大小M,
  (b.b2 - a.a2) / 1024 / 1024 as 已使用M,
  substr((b.b2 - a.a2) / b.b2 * 100,1,5) as 利用率
from 
  (
    select tablespace_name a1, sum(nvl(bytes, 0)) a2
    from dba_free_space
    group by tablespace_name
  ) a,
  (
    select tablespace_name b1, sum(bytes) b2
    from dba_data_files
    group by tablespace_name
   ) b,
   (
     select tablespace_name c1, contents c2, extent_management c3
     from dba_tablespaces
   ) c
where a.a1 = b.b1  and c.c1 = b.b1;

 

3.修改表空间大小

alter tablespace ORCL_TBS add datafile '/ora10g/progame/oracle/oradata/orcl/orcl_tbs2.dbf' size 4000m;

注：修改表空间"PRE_ONLINE_TBS"大小为4000m；
    "/ora10g/progame/oracle/oradata/orcl/orcl_tbs2.dbf"是新建表空间的文件路径。