【转帖】oracle 常用SQL查询 - javajohn - BlogJava
来源:百度文库 编辑:神马文学网 时间:2024/05/15 23:19:52
360docimg_501_DECODE(AA﹐V1﹐R1﹐V2﹐R2360docimg_502_.)函数
360docimg_503_解释:
360docimg_504_IF AA = V1 THEN RETURN R1
360docimg_505_IF AA = V2 THEN RETURN R2
360docimg_506_..…
360docimg_507_
360docimg_508_-- ------------------------------------------------------------------------------
360docimg_509_
360docimg_510_jxdco
360docimg_511_ELSE
360docimg_512_RETURN NULL
360docimg_513_
360docimg_514_LPAD(char1,n,char2)函数
360docimg_515_解释:
360docimg_516_字符char1按制定的位数n显示,不足的位数用char2字符串替换左边的空位
360docimg_517_
360docimg_518_字段名之间可以进行算术运算
360docimg_519_例如: (字段名1 * 字段名1) / 3
360docimg_520_
360docimg_521_查询语句可以嵌套
360docimg_522_例如: SELECT …… FROM
360docimg_523_( SELECT …… FROM表名1, [ 表名2, …… ] WHERE 条件) WHERE 条件2;
360docimg_524_
360docimg_525_两个查询语句的结果可以做集合操作
360docimg_526_例如: 并集UNION(去掉重复记录), 并集UNION ALL (不去掉重复记录), 差集MINUS, 交集INTERSECT
360docimg_527_
360docimg_528_分组查询
360docimg_529_SELECT字段名1, 字段名2, …… FROM 表名1, [ 表名2, …… ] GROUP BY字段名1
360docimg_530_[ HAVING 条件 ] ;
360docimg_531_
360docimg_532_两个以上表之间的连接查询
360docimg_533_
360docimg_534_SELECT字段名1, 字段名2, …… FROM 表名1, [ 表名2, …… ] WHERE
360docimg_535_表名1.字段名 = 表名2. 字段名 [ AND …… ] ;
360docimg_536_
360docimg_537_SELECT字段名1, 字段名2, …… FROM 表名1, [ 表名2, …… ] WHERE
360docimg_538_表名1.字段名 = 表名2. 字段名( + ) [ AND …… ] ;
360docimg_539_
360docimg_540_有( + )号的字段位置自动补空值
360docimg_541_
360docimg_542_查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC
360docimg_543_
360docimg_544_SELECT字段名1, 字段名2, …… FROM 表名1, [ 表名2, …… ]
360docimg_545_ORDER BY字段名1, 字段名2 DESC ;
360docimg_546_
360docimg_547_字符串模糊比较的方法
360docimg_548_
360docimg_549_INSTR(字段名, ‘字符串’) > 0
360docimg_550_字段名 LIKE ‘字符串 % ’ [ ‘%字符串%’ ]
360docimg_551_
360docimg_552_每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.
360docimg_553_
360docimg_554_四.ORACLE里常用的数据对象 ( SCHEMA )
360docimg_555_
360docimg_556_1 .索引 ( INDEX )
360docimg_557_
360docimg_558_CREATE INDEX 索引名ON 表名 ( 字段1, [ 字段2, …… ] );
360docimg_559_ALTER INDEX 索引名 REBUILD;
360docimg_560_
360docimg_561_一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,
360docimg_562_也可以建立多字段的组合索引和基于函数的索引
360docimg_563_
360docimg_564_ORACLE8.1.7字符串可以索引的最大长度为1578 单字节
360docimg_565_ORACLE8.0.6字符串可以索引的最大长度为758 单字节
360docimg_566_
360docimg_567_2 .视图 ( VIEW )
360docimg_568_
360docimg_569_CREATE VIEW 视图名AS SELECT …. FROM …..;
360docimg_570_ALTER VIEW视图名 COMPILE;
360docimg_571_
360docimg_572_视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.
360docimg_573_
360docimg_574_3 .同义词 (SYNONMY)
360docimg_575_CREATE SYNONYM同义词名FOR 表名;
360docimg_576_CREATE SYNONYM同义词名FOR 表名 @数据库链接名 ;
360docimg_577_
360docimg_578_4 .数据库链接 ( DATABASE LINK)
360docimg_579_CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;
360docimg_580_
360docimg_581_数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.
360docimg_582_
360docimg_583_数据库参数global_name = true时要求数据库链接名称跟远端数据库名称一样
360docimg_584_
360docimg_585_数据库全局名称可以用以下命令查出
360docimg_586_SELECT * FROM GLOBAL_NAME;
360docimg_587_
360docimg_588_查询远端数据库里的表
360docimg_589_SELECT …… FROM 表名 @数据库链接名 ;
360docimg_590_
360docimg_591_五.权限管理 (DCL) 语句
360docimg_592_
360docimg_593_1 . GRANT 赋于权限
360docimg_594_常用的系统权限集合有以下三个:
360docimg_595_CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
360docimg_596_常用的数据对象权限有以下五个:
360docimg_597_ALL ON 数据对象名, SELECT ON 数据对象名, UPDATE ON 数据对象名,
360docimg_598_DELETE ON 数据对象名, INSERT ON 数据对象名, ALTER ON 数据对象名
360docimg_599_
360docimg_600_GRANT CONNECT, RESOURCE TO 用户名;
360docimg_601_GRANT SELECT ON 表名 TO 用户名;
360docimg_602_GRANT SELECT , INSERT , DELETE ON表名 TO 用户名1, 用户名2;
360docimg_603_
360docimg_604_2 . REVOKE 回收权限
360docimg_605_
360docimg_606_REVOKE CONNECT, RESOURCE FROM 用户名;
360docimg_607_REVOKE SELECT ON 表名 FROM 用户名;
360docimg_608_REVOKE SELECT , INSERT , DELETE ON表名 FROM 用户名1, 用户名2;
360docimg_609_
360docimg_610_
360docimg_611_查询数据库中第63号错误:
360docimg_612_select orgaddr,destaddr from sm_histable0116 where error_code = ' 63 ' ;
360docimg_613_
360docimg_614_查询数据库中开户用户最大提交和最大下发数: select MSISDN,TCOS,OCOS from ms_usertable;
360docimg_615_
360docimg_616_
360docimg_617_查询数据库中各种错误代码的总和:
360docimg_618_select error_code, count ( * ) from sm_histable0513 group by error_code order
360docimg_619_by error_code;
360docimg_620_
360docimg_621_查询报表数据库中话单统计种类查询。
360docimg_622_select sum (Successcount) from tbl_MiddleMt0411 where ServiceType2 = 111
360docimg_623_select sum (successcount),servicetype from tbl_middlemt0411 group by servicetype
以下转自:WWW.LOVEUNIX.COM MADE BY SDAWNYJ
360docimg_624_1、查看表空间的名称及大小
360docimg_625_select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
360docimg_626_from dba_tablespaces t, dba_data_files d
360docimg_627_where t.tablespace_name = d.tablespace_name
360docimg_628_group by t.tablespace_name;
360docimg_629_2、查看表空间物理文件的名称及大小
360docimg_630_select tablespace_name, file_id, file_name,
360docimg_631_round(bytes/(1024*1024),0) total_space
360docimg_632_from dba_data_files
360docimg_633_order by tablespace_name;
360docimg_634_3、查看回滚段名称及大小
360docimg_635_select segment_name, tablespace_name, r.status,
360docimg_636_(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
360docimg_637_max_extents, v.curext CurExtent
360docimg_638_From dba_rollback_segs r, v$rollstat v
360docimg_639_Where r.segment_id = v.usn(+)
360docimg_640_order by segment_name ;
360docimg_641_4、查看控制文件
360docimg_642_select name from v$controlfile;
360docimg_643_5、查看日志文件
360docimg_644_select member from v$logfile;
360docimg_645_6、查看表空间的使用情况
360docimg_646_select sum(bytes)/(1024*1024) as free_space,tablespace_name
360docimg_647_from dba_free_space
360docimg_648_group by tablespace_name;
360docimg_649_
360docimg_650_SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
360docimg_651_(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
360docimg_652_FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
360docimg_653_WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
360docimg_654_7、查看数据库库对象
360docimg_655_select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
360docimg_656_8、查看数据库的版本
360docimg_657_Select version FROM Product_component_version
360docimg_658_Where SUBSTR(PRODUCT,1,6)='Oracle';
360docimg_659_9、查看数据库的创建日期和归档方式
360docimg_660_Select Created, Log_Mode, Log_Mode From V$Database;
360docimg_661_10、捕捉运行很久的SQL
360docimg_662_column username format a12
360docimg_663_column opname format a16
360docimg_664_column progress format a8
360docimg_665_
360docimg_666_select username,sid,opname,
360docimg_667_round(sofar*100 / totalwork,0) || '%' as progress,
360docimg_668_time_remaining,sql_text
360docimg_669_from v$session_longops , v$sql
360docimg_670_where time_remaining <> 0
360docimg_671_and sql_address = address
360docimg_672_and sql_hash_value = hash_value
360docimg_673_/
360docimg_674_11。查看数据表的参数信息
360docimg_675_SELECT partition_name, high_value, high_value_length, tablespace_name,
360docimg_676_pct_free, pct_used, ini_trans, max_trans, initial_extent,
360docimg_677_next_extent, min_extent, max_extent, pct_increase, FREELISTS,
360docimg_678_freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
360docimg_679_empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
360docimg_680_last_analyzed
360docimg_681_FROM dba_tab_partitions
360docimg_682_--WHERE table_name = :tname AND table_owner = :towner
360docimg_683_ORDER BY partition_position
360docimg_684_
360docimg_685_12.查看还没提交的事务
360docimg_686_select * from v$locked_object;
360docimg_687_select * from v$transaction;
360docimg_688_13。查找object为哪些进程所用
360docimg_689_select
360docimg_690_p.spid,
360docimg_691_s.sid,
360docimg_692_s.serial# serial_num,
360docimg_693_s.username user_name,
360docimg_694_a.type object_type,
360docimg_695_s.osuser os_user_name,
360docimg_696_a.owner,
360docimg_697_a.object object_name,
360docimg_698_decode(sign(48 - command),
360docimg_699_1,
360docimg_700_to_char(command), 'Action Code #' || to_char(command) ) action,
360docimg_701_p.program oracle_process,
360docimg_702_s.terminal terminal,
360docimg_703_s.program program,
360docimg_704_s.status session_status
360docimg_705_from v$session s, v$access a, v$process p
360docimg_706_where s.paddr = p.addr and
360docimg_707_s.type = 'USER' and
360docimg_708_a.sid = s.sid and
360docimg_709_a.object='SUBSCRIBER_ATTR'
360docimg_710_order by s.username, s.osuser
360docimg_711_
360docimg_712_14。回滚段查看
360docimg_713_select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
360docimg_714_Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
360docimg_715_v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
360docimg_716_sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
360docimg_717_v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
360docimg_718_v$rollstat.usn (+) = v$rollname.usn order by rownum
360docimg_719_
360docimg_720_15。耗资源的进程(top session)
360docimg_721_select s.schemaname schema_name, decode(sign(48 - command), 1,
360docimg_722_to_char(command), 'Action Code #' || to_char(command) ) action, status
360docimg_723_session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,
360docimg_724_nvl(s.username, '[Oracle process]') user_name, s.terminal terminal,
360docimg_725_s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p
360docimg_726_where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL' = 'ALL'
360docimg_727_or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc
360docimg_728_
360docimg_729_16。查看锁(lock)情况
360docimg_730_select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
360docimg_731_decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX',
360docimg_732_'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type,
360docimg_733_o.object_name object, decode(ls.lmode, 1, null, 2, 'Row Share', 3,
360docimg_734_'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null)
360docimg_735_lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
360docimg_736_from sys.dba_objects o, ( select s.osuser, s.username, l.type,
360docimg_737_l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s,
360docimg_738_v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner
360docimg_739_<> 'SYS' order by o.owner, o.object_name
360docimg_740_
360docimg_741_17。查看等待(wait)情况
360docimg_742_SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
360docimg_743_FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
360docimg_744_'consistent gets') group by v$waitstat.class, v$waitstat.count
360docimg_745_
360docimg_746_18。查看sga情况
360docimg_747_SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
360docimg_748_
360docimg_749_19。查看catched object
360docimg_750_SELECT owner, name, db_link, namespace,
360docimg_751_type, sharable_mem, loads, executions,
360docimg_752_locks, pins, kept FROM v$db_object_cache
360docimg_753_
360docimg_754_20。查看V$SQLAREA
360docimg_755_SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
360docimg_756_VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
360docimg_757_USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
360docimg_758_BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA
360docimg_759_
360docimg_760_21。查看object分类数量
360docimg_761_select decode (o.type#,1,'INDEX' , 2,'TABLE' , 3 , 'CLUSTER' , 4, 'VIEW' , 5 ,
360docimg_762_'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) object_type , count(*) quantity from
360docimg_763_sys.obj$ o where o.type# > 1 group by decode (o.type#,1,'INDEX' , 2,'TABLE' , 3
360docimg_764_, 'CLUSTER' , 4, 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) union select
360docimg_765_'COLUMN' , count(*) from sys.col$ union select 'DB LINK' , count(*) from
360docimg_766_
360docimg_767_22。按用户查看object种类
360docimg_768_select u.name schema, sum(decode(o.type#, 1, 1, NULL)) indexes,
360docimg_769_sum(decode(o.type#, 2, 1, NULL)) tables, sum(decode(o.type#, 3, 1, NULL))
360docimg_770_clusters, sum(decode(o.type#, 4, 1, NULL)) views, sum(decode(o.type#, 5, 1,
360docimg_771_NULL)) synonyms, sum(decode(o.type#, 6, 1, NULL)) sequences,
360docimg_772_sum(decode(o.type#, 1, NULL, 2, NULL, 3, NULL, 4, NULL, 5, NULL, 6, NULL, 1))
360docimg_773_others from sys.obj$ o, sys.user$ u where o.type# >= 1 and u.user# =
360docimg_774_o.owner# and u.name <> 'PUBLIC' group by u.name order by
360docimg_775_sys.link$ union select 'CONSTRAINT' , count(*) from sys.con$
360docimg_776_
360docimg_777_23。有关connection的相关信息
360docimg_778_1)查看有哪些用户连接
360docimg_779_select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),
360docimg_780_'Action Code #' || to_char(command) ) action, p.program oracle_process,
360docimg_781_status session_status, s.terminal terminal, s.program program,
360docimg_782_s.username user_name, s.fixed_table_sequence activity_meter, '' query,
360docimg_783_0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num
360docimg_784_from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'
360docimg_785_order by s.username, s.osuser
360docimg_786_2)根据v.sid查看对应连接的资源占用等情况
360docimg_787_select n.name,
360docimg_788_v.value,
360docimg_789_n.class,
360docimg_790_n.statistic#
360docimg_791_from v$statname n,
360docimg_792_v$sesstat v
360docimg_793_where v.sid = 71 and
360docimg_794_v.statistic# = n.statistic#
360docimg_795_order by n.class, n.statistic#
360docimg_796_3)根据sid查看对应连接正在运行的sql
360docimg_797_select /*+ PUSH_SUBQ */
360docimg_798_command_type,
360docimg_799_sql_text,
360docimg_800_sharable_mem,
360docimg_801_persistent_mem,
360docimg_802_runtime_mem,
360docimg_803_sorts,
360docimg_804_version_count,
360docimg_805_loaded_versions,
360docimg_806_open_versions,
360docimg_807_users_opening,
360docimg_808_executions,
360docimg_809_users_executing,
360docimg_810_loads,
360docimg_811_first_load_time,
360docimg_812_invalidations,
360docimg_813_parse_calls,
360docimg_814_disk_reads,
360docimg_815_buffer_gets,
360docimg_816_rows_processed,
360docimg_817_sysdate start_time,
360docimg_818_sysdate finish_time,
360docimg_819_'>' || address sql_address,
360docimg_820_'N' status
360docimg_821_from v$sqlarea
360docimg_822_where address = (select sql_address from v$session where sid = 71)
360docimg_823_
360docimg_824_24.查询表空间使用情况select a.tablespace_name "表空间名称",
360docimg_825_100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",
360docimg_826_round(a.bytes_alloc/1024/1024,2) "容量(M)",
360docimg_827_round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",
360docimg_828_round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",
360docimg_829_Largest "最大扩展段(M)",
360docimg_830_to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"
360docimg_831_from (select f.tablespace_name,
360docimg_832_sum(f.bytes) bytes_alloc,
360docimg_833_sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes
360docimg_834_from dba_data_files f
360docimg_835_group by tablespace_name) a,
360docimg_836_(select f.tablespace_name,
360docimg_837_sum(f.bytes) bytes_free
360docimg_838_from dba_free_space f
360docimg_839_group by tablespace_name) b,
360docimg_840_(select round(max(ff.length)*16/1024,2) Largest,
360docimg_841_ts.name tablespace_name
360docimg_842_from sys.fet$ ff, sys.file$ tf,sys.ts$ ts
360docimg_843_where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#
360docimg_844_group by ts.name, tf.blocks) c
360docimg_845_where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name
360docimg_846_
360docimg_847_25. 查询表空间的碎片程度
360docimg_848_
360docimg_849_select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
360docimg_850_having count(tablespace_name)>10;
360docimg_851_
360docimg_852_alter tablespace name coalesce;
360docimg_853_alter table name deallocate unused;
360docimg_854_
360docimg_855_create or replace view ts_blocks_v as
360docimg_856_select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
360docimg_857_union all
360docimg_858_select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
360docimg_859_
360docimg_860_select * from ts_blocks_v;
360docimg_861_
360docimg_862_select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
360docimg_863_group by tablespace_name;
360docimg_864_
360docimg_865_26.查看有哪些实例在运行:
360docimg_866_
360docimg_867_select * from v$active_instances;