oracle常用sql

原创 2004年06月28日 20:51:00

--删除表a中bm重复的记录
delete from a a where a.rowid !=(select max(rowid) from a b where a.bm=b.bm);

--查找表a中有表b中没有的记录
select * from a where not exists (select 'x' from b where a.bm=b.bm);

--删除表a中有表b中没有的记录
delete from a where not exists (select 'x' from b where a.bm=b.bm);


--查找指定记录数的分页查询
select * from (select njc_nbdw.*,rownum rr  from njc_nbdw where rownum<5) b where b.rr >2;


--查看数据文件是否自动扩展
--使用系统表:
--v$datafile:存储数据库中数据文件的信息
--v$filestat:存储系统中访问数据文件的统计信息


select file_id,file_name,tablespace_name,autoextensible from dba_data_files order by file_id;

--看Oracle版本及安装了哪些选项
col PARAMETER format a60
col VALUE format a10
select * from sys.v_$option;

--查看SGA区剩余可用内存
select pool,name,
      sgasize/1024/1024        "Allocated(M)",
      bytes/1024            "自由空间(K)",
      round(bytes/sgasize*100, 2)   "自由空间百分比(%)"
   from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
   where  f.name = 'free memory'

 

--SGA区总和
select sum(bytes) sgasize from sys.v_$sgastat; 

--查看哪些用户拥有SYSDBA、SYSOPER
select * from v$PWFILE_USERS;

--查询表里的约束
select * from user_constraints where table_name='NJC_NBDW';

--重建索引

alter index 索引名
rebuild
tablespace 索引表空间名
storage(initial 初始值 next 扩展值)
nologging


--the high-level structure of a top-n analysis query is :
select [column_list],rownum
from (select [colum_list]
      from table
      order by top_n_colum)
where rownum <=n;
--example of top_n analysis:
--to display the top three earner names and sararies from the emp table
select name,salary ,rownum
from (select name,salary
      from emp
      order by salary desc)
where rownum <=3;

 

--使用external table
--不能使用varchar,否则查询时报错,奇怪
--1,创建一个目录
create or replace directory emp_dir as 'e:/flat_files';
--2,创建external table
create table oldemp (
empno number,empname varchar2(20),birthdate date)
organization external
(type oracle_loader
default directory emp_dir
access parameters
(records delimited by newLine
badfile 'bad emp'
logfile 'log_emp'
fields terminated by ','
(empno char,
empname char,
birthdate char date_format date mask "dd-mon-yyyy" ))
location ('emp1.txt')
)
parallel 5
reject limit 200;


--create index with create table
create table new_emp
{employee_id number primary key using index
        (create index emp_id_index on new_emp(employee_id)),
         first_name varchar2(30),
         last_name varchar2(30)
         )
)        

--find whether the database is archiving mode
select archiver from v$instance;

--如何在Oracle服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context('userenv','ip_address') from dual;

--给表、列加注释
comment on table 表 is '表注释';
comment on column 表.列 is '列注释';

--如何在字符串里加回车
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;

--如何跟踪用户执行的sql语句
alter session set sql_trace=true;
select username, sid, serial# from v$session where username ='TESGE';
EXECUTE dbms_system.set_sql_trace_in_session(&SID,&SERIALNUM,TRUE);

在cmd中使用tkprof ****.trc newName.trc; 来格式化监控到的sql

--取当前条,上一条,下一条
select * from  njc_nbdw where dw_id in (
(select max(dw_id) from njc_nbdw where dw_id<18)
union all
(select min(dw_id) from njc_nbdw where dw_id>18)
union all select 18 dw_id from dual
)

--查找dw_id为22的记录的上一条,下一条和该条记录。
select * from
njc_nbdw
where dw_id>=
(select dw_id from
(select dw_id from njc_nbdw where dw_id < 22 order by dw_id desc)
where rownum < 2
)
and rownum < 4

--查询oracle采用的字符集
select * from V$NLS_PARAMETERS;


--查询db blocks get:
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets', 'physical reads');
--查询命中率,要求在90%以上。
Hit Ratio = 1 - ( physical reads / (db block gets + consistent gets) )

--Buffer Pool Hit Ratios
SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
      1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
 FROM V$BUFFER_POOL_STATISTICS;


--  查询用户执行的sql性能
select area.* from v$sqlarea area,v$session  s where area.HASH_VALUE=s.SQL_HASH_VALUE
and area.ADDRESS = s.SQL_ADDRESS and s.sid=&sId;
--该sId可以用下面语句得到
select sid from v$session where username ='TESGE';


--从数据库级查找客户端发出的sql
select sql_text
from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.sid='&sid'
)
order by piece asc

--查询sharepool剩余的空间
SELECT FREE_SPACE,FREE_COUNT,REQUEST_FAILURES,REQUEST_MISSES,LAST_FAILURE_SIZE FROM V$SHARED_POOL_RESERVED;


--强制使用棒定变量,修改会话
alter session set cursor_sharing=force

--修改系统
ALTER SYSTEM SET CURSOR_SHARING=FORCE;

 

查ROLE具有的系统权限,试试如下
SQL> conn t/t
Connected.
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
RESOURCE

SQL> select privilege
2 from role_sys_privs
3 where role='RESOURCE';


--查看数据库链路
select * from SYS.link$;
--查看数据库当前全局实例名
select * from  global_name;


--查询表中有多少字段
select count(column_name) from user_tab_columns where table_name='S_EMP'

--在将这个表移到新的表空间的同时,这个表中的数据也被移到了新的表空间的数据文件中了
alter table table_name move tablespace tablespace_name;

 

DB_CACHE_HIT_RATIO.sql
/* The database buffer cache hit ratio should be greater than 90% on an OLTP
system. */
/* Otherwise increase the size of DB_CACHE_SIZE within SGA_MAX_SIZE
boundary.    */

select 1-(PHY.VALUE - LOB.VALUE - DIR.VALUE)/SES.VALUE "CACHE HIT RATIO"
FROM V$SYSSTAT SES, V$SYSSTAT LOB, V$SYSSTAT DIR, V$SYSSTAT PHY
WHERE SES.NAME ='session logical reads'
and DIR.NAME ='physical reads direct'
and LOB.NAME ='physical reads direct (lob)'
and PHY.NAME ='physical reads'
/


LIBRARY_CACHE_HIT_RATIO.sql
/* The overall library cache hit ratio should be greater than 99%
*/
/* If the RELOADS to PINS ratio is great than 1%, increase SHARED_POOL_SIZE.
*/

SELECT SUM(PINS-RELOADS)/SUM(PINS)*100 "Library Cache Hit Ratio"
FROM V$LIBRARYCACHE
/
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "MISSES", SUM(RELOADS)/SUM(PINS)
"RELOAD RATIO"
FROM V$LIBRARYCACHE
/


DICTIONARY_CACHE_HIT_RATIO.sql
/* The overall dictionary cache hit ratio should be greater than 85%. */

SELECT (SUM(GETS-GETMISSES))/SUM(GETS) "Dictionary Cache Hit Ratio"
FROM V$ROWCACHE
/
------------------------------------------------------------------------------

Calculate the cache hit ratio for the library cache with the following query:

Select sum(pinhits) / sum(pins) "Hit Ratio",
    sum(reloads) / sum(pins) "Reload percent"
From v$librarycache
Where namespace in
('SQL AREA', 'TABLE/PROCEDURE', 'BODY', 'TRIGGER');

The hit ratio should be 85% (i.e. 0.85). The reload percent should be very low, 2% (i.e. 0.02) or less. If this is not the case, increase the initialisation parameter SHARED_POOL_SIZE. Although less likely, the init.ora parameter OPEN_CURSORS may also need to increased.

 

---------------------------------------------------------------------------------
从数据库级查找客户端发出的sql
1.select sid,username from v$session;  从中找到你要查找的客户端的session id。

select sql_text
 from v$sqltext a
 where a.hash_value = (
 select sql_hash_value from v$session b
 where b.sid='&sid'
)
 order by piece asc
/
--------------------------------------------------------------------------------------
UNIX多进程Server,通过ps命令可以看到pid

select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.paddr = (
select addr from v$process c
where c.spid = '&spid'
)
)
order by piece asc
/

 

Oracle常用sql操作总结

一、选择行 1. 简单的SELECT 语句 SELECT 字段名1 [AS] '字段名1 解释' FROM table; 2. 处理NULL NVL函数可把NULL...
  • memoryzxj
  • memoryzxj
  • 2012年03月16日 16:27
  • 3214

Oracle SQL常用内置函数总结

Oracle SQL常用内置函数总结:数字计算函数、字符处理函数、日期时间函数、转换函数、聚合分析函数...
  • yihuiworld
  • yihuiworld
  • 2015年03月28日 15:17
  • 10707

Oracle sql常用语法

PL/Sql循序渐进全面学习教程课程 一 PL/SQL 基本查询与排序 本课重点:    1、写SELECT语句进行数据库查询    2、进行数学运算    3、处理空值    4、使用别名ALIAS...
  • long102
  • long102
  • 2007年05月31日 13:12
  • 2060

EBS系统管理常用SQL语句整理汇总(参考网上资料&其他人博客)

--1查找系统用户基本信息 SELECT user_id, user_name, description, ...
  • caixingyun
  • caixingyun
  • 2013年12月04日 23:07
  • 1427

oracle常用的SQL PLUS命令详解

dba基础课程:oracle常用的SQL PLUS命令详解 Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中,可以运行sql*plus命令与sql*plu...
  • oraclestudyroad
  • oraclestudyroad
  • 2016年08月03日 15:40
  • 680

ORACLE—PL/SQL命令大全

第一章:日志管理  1.forcing log switches sql> alter system switch logfile; 2.forcing checkpoints sql> alter ...
  • javagarden
  • javagarden
  • 2009年03月23日 19:12
  • 1709

Oracle常用经典SQL查询(一)

注:本文的查询测试以oracle11.2.0.4.0企业版作为查询测试,截图只是为了证明SQL的可用性及正确性,查询结果因测试环境不同各有差异。1、查看表空间的名称及大小select t.tables...
  • jHstGeWWubw
  • jHstGeWWubw
  • 2018年01月27日 00:00
  • 37

常用sql语句(oracle 监控)

 1.   监控事例的等待           select   event,sum(decode(wait_Time,0,0,1))   "Prev",       sum(decode(wait_...
  • wenbingcai
  • wenbingcai
  • 2008年05月19日 10:32
  • 346

oracle和sql server哪个工作(公司)里用的比较多?哪个实用点?

楼上的,可能你中的微软病毒太深了,可也不能忽悠初学者啊!单调的SQL Server数据库能和功能强大的ORACLE数据库相比吗?! oracle数据库是现阶段、未来的占市场率最高的数据库。你现在问问,...
  • weibinxiaoyi
  • weibinxiaoyi
  • 2007年04月17日 01:34
  • 1474

Oracle维护常用SQL语句(查询系统表和视图)

Oracle维护常用SQL语句(查询系统表和视图) 提要: 1、查看表空间的名称及大小 2、查看表空间物理文件的名称及大小 3、查看回滚段名称及大小 4、查看控制文件 5、查看日志...
  • Hu199055
  • Hu199055
  • 2016年01月10日 14:48
  • 869
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle常用sql
举报原因:
原因补充:

(最多只允许输入30个字)