用到的语句

1.设置某表的字段authority 为原来长度的6位

update operator_data_auth oda  set oda.authority = SUBSTR(oda.authority,1,6)  where  oda.authority like '620%'

2.全库的导入和打出

imp beijing/beijing file=beijing20130420.dmp  full = y;
exp username/password@sid file=d:\exp.dmp full=y

3.查找表的注释

select table_name ,comments from user_tab_comments

4.查找每个表的字段和类型

select u.TABLE_NAME, u.DATA_TYPE , count(*) as cut from user_tab_columns u 

where u.DATA_TYPE = 'BLOB' or u.DATA_TYPE='CLOB'

group by u.TABLE_NAME, u.DATA_TYPE order by u.DATA_TYPE

5.查询所有的存储过程

select * from DBA_objects where object_type='PROCEDURE' and owner='OMGAP'

6.查询所有的序列

select * from dba_sequences seq where seq.sequence_name like '%UNIMAS%'

7.查询所有的触发器

select * from all_triggers

8.连接字符串

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee

9.截取字符

substr(T5.NAME||T6.NAME||T2.SIGN_ADDR_DETAIL,0,30)

10.查询字符串

SELECT CASE T1.CONCE_TYPE

         WHEN 1 THEN

          '买方集中度'

         WHEN 2 THEN

          '区域集中度'

         WHEN 3 THEN

          '行业集中度'

         ELSE

          ''

       END AS CONCE_TYPE ,

       T2.NAME||' '||T3.NAME||' '||T4.NAME||' '||T5.NAME||' '||T6.CUST_NAME AS ATTR ,

       TO_CHAR(T1.AMOUNT,'999,999,999,999.99') AS AMOUNT ,

       TO_CHAR(T1.AVAILABLE_BALANCE,'999,999,999,999.99') AS AVAILABLE_BALANCE ,

       TO_CHAR(T1.FROZEN_AMOUNT,'999,999,999,999.99') AS FROZEN_AMOUNT,

       TO_CHAR(T1.EXPIRY_DATE_START,'YYYY-MM-DD') AS EXPIRY_DATE_START ,

       TO_CHAR(T1.EXPIRY_DATE_END,'YYYY-MM-DD') AS EXPIRY_DATE_END ,

       DECODE(T1.STATUS,0,'启用',1,'禁用') AS STATUS

  FROM T_SYS_CONCENTRATE_RATIO T1

  LEFT JOIN T_AREA T2  ON  T2.ID = T1.CONCE_AREA1 

  LEFT JOIN T_AREA T3  ON  T3.ID = T1.CONCE_AREA2

  LEFT JOIN T_SYS_LINKAGECONFIG T4 ON T4.ID = T1.INDUSTRY_FLAG 

  LEFT JOIN T_SYS_LINKAGECONFIG T5 ON T5.ID = T1.INDUSTRY_FLAG2

  LEFT JOIN T_CUST_CUSTOMER T6 ON T6.CUST_ID = T1.VENDEE_ID

11.Oracle查询某用户下的表，创建时间
SELECT OBJECT_NAME ,CREATED FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='TEST' ORDER BY CREATED DESC;