oracle常用sql

--删除表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
/