当前位置：文档之家 › oracle常用经典SQL查询

oracle常用经典SQL查询

  • 格式：doc
  • 大小：159.50 KB
  • 文档页数：50

oracle常用经典SQL查询常用SQL查询：1、查看表空间的名称及大小select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = n(+)order by segment_name;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select sum(bytes)/(1024*1024) as free_space,tablespace_namefrom dba_free_spacegroup by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"FROM SYS.***$TS_***AIL A,SYS.***$TS_USED B,SYS.***$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ANDA.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看数据库库对象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type,status;8、查看数据库的版本Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=''Oracle'';9、查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode From V$Database;10、捕捉运行很久的SQLcolumn username format a12column opname format a16column progress format a8select username,sid,opname,round(sofar*100 / totalwork,0) || ''%'' as progress,time_remaining,sql_textfrom v$session_longops , v$sqlwhere time_remaining <> 0and sql_address = addressand sql_hash_value = hash_value/11。

查看数据表的参数信息SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pct_increase, FREELISTS,freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,empty_blocks, ***g_space, chain_cnt, ***g_row_len, sample_size,last_analyzedFROM dba_tab_partitions--WHERE table_name = :tname AND table_owner = :townerORDER BY partition_position12.查看还没提交的事务select * from v$locked_object;select * from v$transaction;13。

查找object为哪些进程所用selectp.spid,s.sid,s.serial# serial_num,ername user_name,a.type object_type,s.osuser os_user_name,a.owner,a.object object_name,decode(sign(48 - command),1,to_char(command), ''Action Code #'' || to_char(command) ) action,p.program oracle_process,s.terminal terminal,s.program program,s.status session_statusfrom v$session s, v$access a, v$process pwhere s.paddr = p.addr ands.type = ''USER'' anda.sid = s.sid anda.object=''SUBSCRIBER_ATTR''order by ername, s.osuser14。

回滚段查看select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes, sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs, v$rollname where v$(+) = sys.dba_rollback_segs.segment_name and v$n (+) = v$n order by rownum15。

耗资源的进程（top session）select s.schemaname schema_name, decode(sign(48 - command), 1,to_char(command), ''Action Code #'' || to_char(command) ) action, status session_status, s.osuser os_user_name, s.sid, p.spid , s.serial# serial_num,nvl(ername, ''[Oracle process]'') user_name, s.terminal terminal,s.program program, st.value criteria_value from v$sesstat st, v$session s , v$process p where st.sid = s.sid and st.statistic# = to_number(''38'') and (''ALL'' = ''ALL''or s.status = ''ALL'') and p.addr = s.paddr order by st.value desc, p.spid asc, ername asc,s.osuser asc16。

查看锁（lock）情况select /*+ RULE */ ls.osuser os_user_name, ername user_name, decode(ls.type, ''RW'', ''Row wait enqueue lock'', ''TM'', ''DML enqueue lock'', ''TX'', ''Transaction enqueue lock'', ''UL'', ''User supplied lock'') lock_type,o.object_name object, decode(ls.lmode, 1, null, 2, ''Row Share'', 3, ''Row Exclusive'', 4, ''Share'', 5, ''Share Row Exclusive'', 6, ''Exclusive'', null)lock_mode, o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2from sys.dba_objects o, ( select s.osuser, ername, l.type,l.lmode, s.sid, s.serial#, l.id1, l.id2 from v$session s, v$lock l where s.sid = l.sid ) ls where o.object_id = ls.id1 and o.owner<> ''SYS'' order by o.owner, o.object_name17。

合集下载

Oracle中常用查询用户、角色、权限等SQL

Oracle中常用查询用户、角色、权限等SQL

Oracle中常⽤查询⽤户、⾓⾊、权限等SQL连带表间的外键关系⼀起删除所有某个⽤户下的所有表：SELECT 'DROP TABLE '||TNAME||' CASCADE CONSTRAINT ;' FROM DBA_OBJECTS FROM DBA_OBJECTS WHEREOWNER='YOUR_USER' AND OBJECT_TYPE='TABLE'然后将选出的结果粘贴到SQLPLUS⾥执⾏1.查看所有⽤户：select * from dba_users;select * from all_users;select * from user_users;2.查看⽤户或⾓⾊系统权限(直接赋值给⽤户或⾓⾊的系统权限)：select * from dba_sys_privs;select * from user_sys_privs; (查看当前⽤户所拥有的权限)3.查看⾓⾊(只能查看登陆⽤户拥有的⾓⾊)所包含的权限sql>select * from role_sys_privs;4.查看⽤户对象权限：select * from dba_tab_privs;select * from all_tab_privs;select * from user_tab_privs;5.查看所有⾓⾊： select * from dba_roles;6.查看⽤户或⾓⾊所拥有的⾓⾊：select * from dba_role_privs;select * from user_role_privs;7.查看哪些⽤户有sysdba或sysoper系统权限(查询时需要相应权限)select * from V$PWFILE_USERS8.SqlPlus中查看⼀个⽤户所拥有权限SQL>select * from dba_sys_privs where grantee='username'; 其中的username即⽤户名要⼤写才⾏。

oracle sql并发查询语句

oracle sql并发查询语句

oracle sql并发查询语句Oracle SQL是一种强大的关系型数据库管理系统，支持并发查询，可以同时处理多个查询请求。

下面列举了十个符合题目要求的Oracle SQL并发查询语句。

1. 查询所有员工的姓名和工资```sqlSELECT 姓名, 工资 FROM 员工表;```2. 查询员工工资大于5000元的部门名称和平均工资```sqlSELECT 部门名称, AVG(工资) AS 平均工资FROM 员工表WHERE 工资 > 5000GROUP BY 部门名称;```3. 查询销售额最高的产品名称和销售额```sqlSELECT 产品名称, MAX(销售额) AS 最高销售额FROM 销售表GROUP BY 产品名称ORDER BY 最高销售额 DESCFETCH FIRST ROW ONLY;```4. 查询在某个日期范围内有销售记录的员工姓名和销售数量```sqlSELECT 姓名, COUNT(*) AS 销售数量FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID WHERE 销售日期BETWEEN TO_DATE('2021-01-01', 'YYYY-MM-DD') AND TO_DATE('2021-12-31', 'YYYY-MM-DD') GROUP BY 姓名;```5. 查询每个部门的员工数量和平均工资```sqlSELECT 部门名称, COUNT(*) AS 员工数量, AVG(工资) AS 平均工资FROM 员工表GROUP BY 部门名称;```6. 查询没有销售记录的产品名称和库存数量```sqlSELECT 产品名称, 库存数量FROM 产品表WHERE 产品ID NOT IN (SELECT 产品ID FROM 销售表);```7. 查询员工工资排名前10的姓名和工资```sqlSELECT 姓名, 工资FROM (SELECT 姓名, 工资, RANK() OVER (ORDER BY 工资 DESC) AS 排名FROM 员工表)WHERE 排名 <= 10;```8. 查询每个部门的销售总额和销售数量```sqlSELECT 部门名称, SUM(销售额) AS 销售总额, COUNT(*) AS 销售数量FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID GROUP BY 部门名称;```9. 查询在某个日期之后有销售记录的产品名称和最早销售日期```sqlSELECT 产品名称, MIN(销售日期) AS 最早销售日期FROM 产品表INNER JOIN 销售表 ON 产品表.产品ID = 销售表.产品ID WHERE 销售日期 > TO_DATE('2021-01-01', 'YYYY-MM-DD') GROUP BY 产品名称;```10. 查询销售额排名前5的员工姓名和销售额```sqlSELECT 姓名, 销售额FROM (SELECT 姓名, 销售额, RANK() OVER (ORDER BY 销售额DESC) AS 排名FROM 员工表INNER JOIN 销售表 ON 员工表.员工ID = 销售表.员工ID) WHERE 排名 <= 5;```以上是十个符合题目要求的Oracle SQL并发查询语句。

oracle查询表结构sql语句

oracle查询表结构sql语句

oracle查询表结构sql语句在Oracle数据库中，查询表结构的SQL语句可以通过查询数据库的系统表来实现。

以下是一些常用的查询表结构的SQL语句。

1. 查询表的所有列名和数据类型：```SELECT column_name, data_typeFROM all_tab_columnsWHERE table_name = '表名';```这条SQL语句会返回指定表的所有列名和对应的数据类型。

2. 查询表的主键列：```SELECT constraint_name, column_nameFROM all_cons_columnsWHERE table_name = '表名' AND constraint_name = 'PK_表名';```这条SQL语句会返回指定表的主键列名。

3. 查询表的外键列：```SELECT constraint_name, column_name, r_constraint_name, r_table_nameFROM all_cons_columnsWHERE table_name = '表名' AND constraint_name LIKE 'FK_%';```这条SQL语句会返回指定表的外键列名、相关联的表名和外键约束名。

4. 查询表的索引：```SELECT index_name, column_nameFROM all_ind_columnsWHERE table_name = '表名';```这条SQL语句会返回指定表的索引名和对应的列名。

5. 查询表的约束：```SELECT constraint_name, constraint_typeFROM all_constraintsWHERE table_name = '表名' AND constraint_type IN ('P', 'U', 'R', 'C');```这条SQL语句会返回指定表的主键约束、唯一约束、外键约束和检查约束。

oracle索引查询语句

oracle索引查询语句

oracle索引查询语句Oracle索引是一种用于加快查询速度的数据结构，它能够提高数据库的性能和效率。

索引查询语句是使用索引来搜索和获取数据的查询语句。

下面列举了一些常用的Oracle索引查询语句。

1. 查询表中的所有索引```sqlSELECT index_name, table_nameFROM user_indexes;```2. 查询索引的定义和属性```sqlSELECT index_name, table_name, column_name, uniqueness FROM user_ind_columnsWHERE table_name = '表名';```3. 查询表中某个索引的使用情况```sqlSELECT index_name, table_name, statusFROM all_indexesWHERE table_name = '表名' AND index_name = '索引名';```4. 查询使用了索引的SQL语句```sqlSELECT sql_textFROM v$sqlWHERE sql_text LIKE '%索引名%';```5. 查询索引的大小和碎片情况```sqlSELECT index_name, leaf_blocks, num_rows, clustering_factor FROM user_indexesWHERE table_name = '表名';```6. 查询索引的统计信息```sqlSELECT index_name, distinct_keys, num_rows, clustering_factorFROM user_indexesWHERE table_name = '表名';``````sqlEXPLAIN PLAN FORSELECT * FROM 表名WHERE 索引列 = 值;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);```8. 查询索引的创建时间和最后修改时间```sqlSELECT index_name, created, last_ddl_timeFROM all_indexesWHERE table_name = '表名';```9. 查询索引的统计信息和优化建议```sqlSELECT index_name, table_name, column_name, num_distinct, density, histogramFROM dba_optstat_indexesWHERE table_name = '表名';``````sqlSELECT index_name, table_name, column_name, usage, effectivenessFROM v$index_usage;```以上是一些常用的Oracle索引查询语句，可以帮助我们了解和优化索引的使用情况，提高数据库的性能和效率。