oracle 常用

发布时间:2016-12-8 0:17:53 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"oracle 常用",主要涉及到oracle 常用方面的内容,对于oracle 常用感兴趣的同学可以参考一下。

--1.查出锁定object的session的信息以及被锁定的object名    SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,          l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time       FROM v$locked_object l, all_objects o, v$session s      WHERE l.object_id = o.object_id        AND l.session_id = s.sid   ORDER BY sid, s.serial# ;   --解锁    alter system kill session'sid,serial#' ; --查询所有 oracle jobselect * from dba_jobs -- 执行 begin dbms_job.run(279); end; --删除 begin   dbms_job.remove(2);--:job可以用dba_jobs.job的值代替如:1198  end;    --查询表列信息 select * from user_tab_columns where table_name='tableName';     select wm_concat(column_name)  from user_tab_columns where table_name='tableName'; -- 显示 LRRY_DM,XGRY_DM,LR_SJ,XG_SJ查询用户下所有表和字段信息(select c.owner yh,c.TABLE_NAME BM,t.COMMENTS BZWM,c.COLUMN_NAME ZDM, m.COMMENTS ZD_ZWM,c.DATA_TYPE ZD_SJLX ,to_char(c.DATA_PRECISION) ZD_CD ,c.DATA_SCALE XSBF,c.NULLABLE WK_BJ,' ' ysj_dm,' ' zd_sf_dydmb,' ' ywk_dm,' ' dmj_dm,'1' bz         FROM ALL_TAB_COLS c,ALL_col_comments m ,all_tab_comments t,all_objects obj         where c.TABLE_NAME=m.table_name(+) and c.COLUMN_NAME=m.column_name(+) and c.TABLE_NAME=t.TABLE_NAME(+)         and c.owner=m.OWNER(+) and c.owner=obj.OWNER(+) and c.TABLE_NAME=obj.OBJECT_NAME(+) and obj.OBJECT_TYPE='TABLE'         and c.DATA_TYPE='NUMBER' and c.TABLE_NAME not like 'BIN%'         and c.owner in  ('DB_SWBZ')  Union  select c.owner yh,c.TABLE_NAME BM,t.COMMENTS BZWM,c.COLUMN_NAME ZDM, m.COMMENTS ZD_ZWM,c.DATA_TYPE ZD_SJLX,    case when  c.DATA_TYPE='NVARCHAR2' then to_char(c.CHAR_LENGTH)         when  c.DATA_TYPE='VARCHAR2' and c.DATA_LENGTH=c.CHAR_LENGTH  then to_char(c.CHAR_LENGTH)         when  c.DATA_TYPE='VARCHAR2' and c.DATA_LENGTH>c.CHAR_LENGTH  then to_char(c.CHAR_LENGTH)||' CHAR'         else to_char(c.DATA_LENGTH)           end  ZD_CD,       0 XSBF,c.NULLABLE WK_BJ,' ' ysj_dm,' ' zd_sf_dydmb,' ' ywk_dm,' ' dmj_dm,'1' bz         FROM ALL_TAB_COLS c,ALL_col_comments m ,all_tab_comments t,all_objects obj         where c.TABLE_NAME=m.table_name(+) and c.COLUMN_NAME=m.column_name(+) and c.TABLE_NAME=t.TABLE_NAME(+)         and c.owner=m.OWNER(+) and c.owner=obj.OWNER(+) and c.TABLE_NAME=obj.OBJECT_NAME(+) and obj.OBJECT_TYPE='TABLE'         and c.DATA_TYPE!='NUMBER' and c.TABLE_NAME not like 'BIN%'         and c.owner in  ('DB_SWBZ')          )      order by  BZWM,ZD_ZWM;      --获取表列  select  wm_concat(column_name) from user_tab_columns where table_name='BZ_DMZDBRZB'; ---0 查询用户 select wm_concat(''''||username||'''')   from dba_users t  where t.default_tablespace not in ('SYSTEM', 'SYSAUX')    and account_status = 'OPEN' order by username; ------1数据表清册 select rownum xh, t.OWNER yh,t.TABLE_NAME bm,substr(t.COMMENTS, 0, 1000)  bzwm,    (select count(*) from ALL_TAB_COLS c where t.OWNER=c.owner(+) and t.TABLE_NAME=c.TABLE_NAME(+)) bzdsl,'   ' sm from all_tab_comments t where  t.TABLE_NAME not like 'BIN%' and t.TABLE_TYPE='TABLE' and t.owner in ('用户名','用户名') ;  --用户名大写(根据上一步中查询的用户获得的用户名) --2数据表字段清册 select rownum xh, t.*, rownum rn   from((select  c.owner yh,c.TABLE_NAME BM,substr(t.COMMENTS,0,1000) BZWM,c.COLUMN_NAME ZDM, substr( m.COMMENTS,0,1000) ZD_ZWM,c.DATA_TYPE ZD_SJLX ,to_char(c.DATA_PRECISION) ZD_CD ,c.DATA_SCALE XSBF,c.NULLABLE WK_BJ,' ' ysj_dm,' ' zd_sf_dydmb,' ' ywk_dm,' ' dmj_dm,'1' bz         FROM ALL_TAB_COLS c,ALL_col_comments m ,all_tab_comments t,all_objects obj         where c.TABLE_NAME=m.table_name(+) and c.COLUMN_NAME=m.column_name(+) and c.TABLE_NAME=t.TABLE_NAME(+)         and c.owner=m.OWNER(+) and c.owner=obj.OWNER(+) and c.owner=t.OWNER(+)  and c.TABLE_NAME=obj.OBJECT_NAME(+) and obj.OBJECT_TYPE='TABLE'         and c.DATA_TYPE='NUMBER' and c.TABLE_NAME not like 'BIN%'         and c.owner in  ('用户名','用户名')  Union  select  c.owner yh,c.TABLE_NAME BM,substr(t.COMMENTS,0,1000) BZWM,c.COLUMN_NAME ZDM, substr( m.COMMENTS,0,1000) ZD_ZWM,c.DATA_TYPE ZD_SJLX,    case when  c.DATA_TYPE='NVARCHAR2' then to_char(c.CHAR_LENGTH)         when  c.DATA_TYPE='VARCHAR2' and c.DATA_LENGTH=c.CHAR_LENGTH  then to_char(c.CHAR_LENGTH)         when  c.DATA_TYPE='VARCHAR2' and c.DATA_LENGTH>c.CHAR_LENGTH  then to_char(c.CHAR_LENGTH)||' CHAR'         else to_char(c.DATA_LENGTH)            end  ZD_CD,       0 XSBF,c.NULLABLE WK_BJ,' ' ysj_dm,' ' zd_sf_dydmb,' ' ywk_dm,' ' dmj_dm,'1' bz         FROM ALL_TAB_COLS c,ALL_col_comments m ,all_tab_comments t,all_objects obj         where c.TABLE_NAME=m.table_name(+) and c.COLUMN_NAME=m.column_name(+) and c.TABLE_NAME=t.TABLE_NAME(+)         and c.owner=m.OWNER(+) and c.owner=obj.OWNER(+) and c.owner=t.OWNER(+) and c.TABLE_NAME=obj.OBJECT_NAME(+) and obj.OBJECT_TYPE='TABLE'         and c.DATA_TYPE!='NUMBER' and c.TABLE_NAME not like 'BIN%'         and c.owner in  ('用户名','用户名')          )      order by yh,BZWM,ZD_ZWM)t; 比较大小函数    select greatest (1,2,3,4  )max , least (1 ,2 ,3,4 )min from dual;

上一篇:五、代理模式
下一篇:优秀程序员的10个习惯

相关文章

关键词: oracle 常用

相关评论