Oracle小结

    -- 连接本地实例
conn /@orcl as sysdba;


-- 创建表空间 “newer”
CREATE TABLESPACE HUANG DATAFILE 'D:\DB_Source\TESTDATE.DBF' SIZE 100M reuse AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;


-- 创建用户 “newer"
CREATE USER newer PROFILE DEFAULT IDENTIFIED BY newer DEFAULT TABLESPACE NEWER TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;


-- 对用户 "newer" 授权
GRANT DBA TO newer;
exit;    


select * from table_users  查看所有表
select * from all_users   查看所有用户
 
 
 
可要是在外部应用程序调用查看ORACLE中的表结构时，这个命令就不能用了。
只能用下面的语句代替：


看字段名与数据类型   
select   *   from   cols     WHERE   TABLE_name=upper('table_name');  （user_tab_columns缩写cols）


查看全部列


select   *   from   user_tab_columns   where   table_name   =   upper(‘tablename’);  


查看某些列
select  column_name,data_type,data_length,DATA_PRECISION ,DATA_SCALE 
from all_tab_columns where table_name=upper('tablename');


可以通过user_constraints查看所有约束   
select   *   from   user_constraints   where   table_name   =   upper('tablename');


查看主键约束：   
select   *   from   user_constraints   where   constraint_type='P'   
and   TABLE_name=upper('table_name')  ;






--函数
--单行函数
--字符函数，数值函数，转换函数，日期函数，普通函数


--字符函数


select concat('hello','world') from dual;
select upper('hello') from dual;
select lower(ename) from emp;
select LPAD('hello',10,'$') from dual
select RPAD('hello',10,'$') from dual
select substr('fdsfdsfdsfds',2,6) from dual;
select instr('fsfdsfdsf','ds',2,2) from dual;
select initcap('hello world') from dual


--数值函数
--round(),trunc()
select round((sysdate - hiredate)/30) as "hiredate" from emp
select round(12.346,-2) from dual;
select trunc(12.348,2) from dual;


select POWER(2, 3) from dual;
select MOD(10,2) from dual;


--转换函数
--to_char,to_number,to_date
--fm去掉前导零
select to_char(sysdate,'yyyy-fmmm-dd') from dual;
select to_char(100.00,'$999.00') from dual;
select
  e.job,e.ename,e.hiredate
from
  emp e
where
  e.hiredate between to_date('1981-2-19','yyyy-mm-dd') and to_date('1981-12-03','yyyy-mm-dd')




--日期函数
--add_months()
--month_between()


--普通函数
--nvl()如果表达式1的值为null,返回表达式2的值
--nvl2()如果表达式1的值不为null,返回表达式2,否则返回表达式3
--nullif()当表达式1等于表达式2时,返回null,否则返回表达式1的值
--COALESCE()如果表达式1的值不为空,返回表达式1的值,否则返回表达式2的值,如果表达式2的值为空,返回表达式3的值
--decode()




oracle权限问题集合




1.oracle设置不同权限的用户去访问同一表空间


1.create user testa identified by testa;
2.alter user testa default tablespace users temporary tablespace temp;
3.grant connect to testa;
4.grant select any table to testa;


2.ORACLE中设置同一个用户对两个表空间的权限的代码


alter user 用户 quota unlimited on 表空间A;
alter user 用户 quota unlimited on 表空间B;
或者放开所有表空间
grant unlimited tablespace to 用户;
或者索性给所有权限
grant resource,connect,dba to 用户;


3.ORACLE设置只能访问表权限的用户


grant select on table1 to user1;
grant select on table2 to user1;






oracle临时表空间作用 收藏
Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。
重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题sql语句的执行，temp表空间会一直增长。直到耗尽硬盘空间。
网上有人猜测在磁盘空间的分配上，oracle使用的是贪心算法，如果上次磁盘空间消耗达到1GB，那么临时表空间就是1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。
临时表空间的主要作用：
索引create或rebuild
Order by 或 group by
Distinct 操作
Union 或 intersect 或 minus
Sort-merge joins
analyze




 ORACLE中SQL取最后一条记录的几种方法 收藏
  在ETL过程中，经常会碰到取结果集的最后或最前一条记录。如取活期存款的当前利率，开户金额，协定利率等。如果不用LOOKUP的方式，如通过游标取或者ETL工具LOOKUP组件什么的，在一条SQL里实现，目前实现有几种方法。


1.以时间或其他字段分组后在自连自己，这样不仅可以带出需要LOOKUP的字段，还可以带出其他需要的字段。


SELECT A.CDDPTY CDDPTY,A.CDCURR CDCURR,A.CDVLDT CDVLDT,   
             A.CDYRAT CDYRAT   
FROM DCPPDATA.TBBFMCDRT A INNER JOIN    
           (SELECT B.CDDPTY,B.CDCURR,MAX(B.CDVLDT) CDVLDT   
             FROM DCPPDATA.TBBFMCDRT B   
             GROUP BY B.CDDPTY, B.CDCURR) C    
             ON A.CDDPTY =C.CDDPTY   
             AND A.CDCURR =C.CDCURR   
             AND A.CDVLDT =C.CDVLDT 


2.用ROW_NUMBER() OVER(ORDER BY filedName)




SELECT B.CDDPTY,B.CDCURR,   
             ROW_NUMBER() OVER(ORDER BY B.CDVLDT DESC)   
             FROM DCPPDATA.TBBFMCDRT B   
             WHERE ROWNUM = 1