//在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的状态信息。