sql小结(1)

//在crud中调用函数                                                                        
select Func_getUTCDate() from dual;
------------------------------------------------------------------------------------------
//oracle的分页
select * from (select t. * , rownum as linenum  from  t_person t where rownum < 10 ) 
where linenum > 4;

select * from T_person t where t.fatherid = 0 connect by prior t.id = t.fatherid;
------------------------------------------------------------------------------------------
 //创建临时表
 create global temporary table temp_tbl(col_a varchar2(30)) on commit delete rows 

 create global temporary table temp_tbl(col_a varchar2(30)) on commit preserve rows 
------------------------------------------------------------------------------------------
 alter system kill session 'sid,serial#';
 
 select * from v$session where username  = upper('scott');
------------------------------------------------------------------------------------------
//decode的高级版本
 select (case when 10> 5 then 'ok' when 10=5 then 'fail' else 'lll' end) from dual;

一个汉字在oracle中是占用了两个字节的。 
英文字母或符号只占用一个字节。 
Char(10)最多可存放5个汉字。

------------------------------------------------------------------------------------------
create materialized view t_user_money
         refresh
         start with sysdate next sysdate + 5/1440
         with rowid
         as
         select username, money from t_u_money@UMLink;
commit;
------------------------------------------------------------------------------------------
手动刷新快照 
begin 
dbms_refresh.refresh('"CS"."SN_ANSON"'); 
end; 
------------------------------------------------------------------------------------------
//第一种方法(这也是默认的方法on demand)
 create materialized view test_view 
 refresh  force on demand
 start with sysdate next sysdate + 1/1440   --(注意)系统会自动创建job,每分钟更新一次
 as
 select * from emp;

//第二种方法
 create materialized view test_view 
 refresh  force on commit 
 as
 select * from emp;
------------------------------------------------------------------------------------------
//自定义补充的格式
 SQL>  select lpad ( 'xuxu',20,'*') from dual;

LPAD('DAGA',20,'*')
--------------------
****************xuxu
------------------------------------------------------------------------------------------
0是一个特殊的值，它在oracle中存储为128. 
 
//手工删除物理上的db文件后，造成启动失败处理如下：
alter database datafile '/opt/oracle/oradata/lbs8.dbf'offline drop;


//字符串，从1开始，左包括
select t.*, t.rowid from t_task t order by to_number(substr(t.id,5));

最高效的删除重复记录方法 ( 因为使用了ROWID)例子： 
DELETE FROM EMP E
	WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

select substr('hello',0,3) from dual;
和select substr('hello',1,3)from dual ;相同
-------------------------------------------------------------------------------------------
SQL> select * from user_role_privs;

USERNAME         GRANTED_ROLE       ADMIN_OPTION DEFAULT_ROLE OS_GRANTED
---------------- ------------------ ------------ ------------ ----------
SCOTT            CONNECT            NO           YES          NO

show parameter;//显示oracle的系统参数

select * from v$version;

alter user scott account unlock;

//查看表空间的free
select * from dba_free_space;

#显示每条sql语句占用的内存
select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem ;

select * from v$sgastat; --显式SGA的状态信息。