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