测试过程中常用的sql命令

2.1 锁信息
在测试过程中会发现查询某张表时查询速度很慢，查看了下对应的表数据量不大、相关的索引也存在且不涉及全表扫面，此时我们可以看下是否有锁表的现象，如果非法操作我们可以直接将被锁表解锁。具体被锁信息、被锁sql、杀死相关进程sql如下：
—查询被锁信息
select t2.username,
t2.sid,
t2.serial#,
t3.object_name,
t2.OSUSER,
t2.MACHINE,
t2.PROGRAM,
t2.COMMAND,
t2.LAST_CALL_ET
from v$locke{d}_{o}bjectt1,v$session t2, dba_objects t3
where t1.session_id = t2.sid
and t1.object_id = t3.object_id
order by t2.logon_time;
—查看锁的具体信息
select ls.osuser os_user_name,
ls.username 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.id2
from sys.dba_objects o,
(select s.osuser,
s.username,
l.type,
l.lmode,
s.sid,
s.serial#,
l.id1,
l.id2
from v$sessions,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_name;
—查询被锁的sql
select sql_text
from v$sqlwherehas{h}_{v}aluein(selectsq{l}_{h}as{h}_{v}aluefromv$session
where sid in (select session_id from v$locked_object));

–关闭sid、serial#对应进程
alter system kill session ‘sid,serial#’;

2.2 用户被锁
测试时经常出现某用户频繁登录且输入的密码错误次数过多导致用户被锁了，那么解锁的sql如下：
–用户解锁
alter user tdap account unlock;

2.3 数据库连接查询
在做性能测试时经常会根据数据库最大连接数来设置应用程序中的最大连接数，查看数据库连接数以及数据库的并发数、会话相关sql如下：
–查看数据库进程最大连接值
select count() from v$process;\ast \ast --查看数据库允许连接的最大数\ast \ast selectvaluefromv$parameter where name =‘processes’;
–查看数据库的并发数
select count() from v$sessionwherestatus{=}^{\prime }ACTIV{E}^{\prime };select\ast fromal{l}_{i}ndexesswheres.tabl{e}_{n}ame{=}^{\prime }S{B}_{Z}NJQ{C}^{\prime };--表空间名\ast \ast --查看数据库当前会话连接数\ast \ast selectcount(\ast )fromv$session;

2.4 性能相关
—计算1个process耗费多少内存
select (select sum(pga_used_mem) / 1024 / 1024 from v$process)/(selectcount(\ast )fromv$process)
from dual;
—查询数据库中每个用户最后使用的 SQL 查询
SELECT S.USERNAME || ‘(’ || s.sid || ‘)-’ || s.osuser UNAME,
s.program || ‘-’ || s.terminal || ‘(’ || s.machine || ‘)’ PROG,
s.sid || ‘/’ || s.serial# sid,
s.status “Status”,
p.spid,
sql_text sqltext
FROM v$sqltex{t}_{w}it{h}_{n}ewlinest,V$SESSION s, v$processpWHEREt.address=s.sq{l}_{a}ddressANDp.addr=s.paddr(+)ANDt.has{h}_{v}alue=s.sq{l}_{h}as{h}_{v}alueORDERBYs.sid,t.piece;\ast \ast ---查询用户CPU的使用率\ast \ast SELECTss.username,se.SID,VALUE/100cp{u}_{u}sag{e}_{s}econdsFROMv$session ss, v$sesstatse,v$statname sn
WHERE se.STATISTIC# = sn.STATISTIC#
AND NAME LIKE ‘%CPU used by this session%’
AND se.SID = ss.SID
AND ss.status = ‘ACTIVE’
AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

2.5 表空间有关
—查看所有表空间存放位置
select tablespace_name, file_name from dba_data_files;
—查看表空间使用情况
select Upper(F.TABLESPACE_NAME) “表空间名”,
D.TOT_GROOTTE_MB “表空间大小”,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES “已使用空间(M)”,
To_char(Round((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
‘990.99’) || ‘%’ “使用比”,
F.TOTAL_BYTES “空闲空间(M)”,
F.MAX_BYTES “最大块(M)”
from (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
—查看所有表空间大小
select t.tablespace_name, round(sum(bytes / (1024 * 1024)), 0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2.6 影响性能sql积累
—查看运行很久的SQL
select username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || ‘%’ as progress,
time_remaining,
sql_text
from v$sessio{n}_{l}ongops,v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;
—查看耗资源的进程
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(s.username, ‘[Oracle process]’) user_name,
s.terminal terminal,
s.program program,
st.value criteria_value
from v$sesstatst,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, s.username asc, s.osuser asc;

2.7系统用户相关sql—增加数据库用户
create user test<用户名> identified by test <密码> default tablespace users Temporary TABLESPACE Temp;
—用户授权
grant connect,resource,dba to test11;
grant sysdba to test11;
commit;
—更改数据库用户的密码：(将sys与system的密码改为test.)
alter user sys indentified by test;
alter user system indentified by test;

2.8 查看用户下索引
select * from all_indexes d where d.table_name=‘SJCK’;
LINUX常用下硬件指标查看命令

查看
CPUcat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c–
查看磁盘
df -h–
查看内存
cat /proc/meminfo

2.9 数据备份
此处只说下简单的逻辑备份（完全备份、用户、表备份）
10g以后导出用户下所有对象、用户下表
–导出用户所有对象
expdp tdap/tdap directory=DATA_PUMP_DIR schemas=tdap（被导入方的用户）
dumpfile=tdapdl.dmp parallel=4 REMAP_SCHEMA=tdap:tdap;

–导出用户下表
expdp DB_NSXYGL_BZB/DB_NSXYGL_BZB rows=n directory=DATA_PUMP_DIR dumpfile=nsxygl_mdb.dmp logfile=nsxygl_mdb.log tables=‘NSXY_MDB’
rows=n:为只导出表结构
10g
以后导入特定的表、特定用户
–导入特定表
Impdp DB_NSXYGL_SJB/DB_NSXYGL_SJB DIRECTORY=DATA_PUMP_DIR DUMPFILE=nsxygl_mdb.dmp TABLES=DB_NSXYGL_SJB.nsxygl_mdb REMAP_SCHEMA=DB_NSXYGL_BZB:DB_NSXYGL_SJB;
–导入特定用户
impdp DB_NSXYGL_SJB/DB_NSXYGL_SJB remap_schema=(DB_NSXYGL_BZB:DB_NSXYGL_SJB) dumpfile=SSHS_NSRJCCBFKXX.dmp remap_tablespace=(TS_DAT_SJCK:TS_DAT_NSXYGL)
9I
以前导入导出
—导出
exp db_nsxygl_sjb/db_nsxygl_sjb@sjck_108 file=d:\daochu.dmp full=y
exp db_nsxygl_sjb/db_nsxygl_sjb@sjck_108 file=d:\daochu.dmp owner=(‘db_nsxygl_sjb’)
exp db_nsxygl_sjb/db_nsxygl_sjb@sjck_108 file=d:\daochu.dmp tables=（‘NSXY_MDB’）
–导入
imp db_nsxygl_sjb/db_nsxygl_sjb file=/…/dpdump/daochu.dmp fromuser=db_nsxygl_bzb touser=db_nsxygl_sjb ignore=y commit=y buffer=40960000;
数据误删

恢复
利用scn找到删除的记录，再将删除的记录恢复到对应表里
select dbms_flashback.get_system_change_number from dual;
select * from test_del as of scn 14189015030110;
insert into test_del select * from test_del as of scn 14189015030110;

找回特定时间点的存储过程
SELECT *
FROM DBA_SOURCE AS OF TIMESTAMP to_date(‘2019-11-26 09:53:30’, ‘yyyy-MM-dd hh24:mi:ss’)
WHERE OWNER = ‘SJJS_BZ’
AND NAME = ‘P_TEST_TASK_LOG’
ORDER BY LINE;

数据导入：
1)
导入前先指定实例
export ORACLE_SID=&qpos;sjfx&qpos;2)
将文件导入到指定目录
select * from dba_directories
建议放置在：
DATA_PUMP_DIR3
）更改文件属性

最大属性（可读、可写、可执行）
4
）获取导出命令并写下导入命令

impdp sjck/sjck@fxglsjyh file=/opt/app/oracle/admin/fxglsjyh/dpdump/np_nszczxqkqrb.dmp ignore=y commit=y fromuser=sjzbq；

impdp tdap/tdap@hbglfzjc DIRECTORY=DATA_PUMP_DIR DUMPFILE=tdap.dmp SCHEMAS=tdap

数据导出
expdp ydgl/ydgl@hbglfzjc directory=DATA_PUMP_DIR schemas=ydgl dumpfile=ydgldl.dmp parallel=4

impdp yonghuming/mima@tns file=tdap.dmp directory=DATA_PUMP_DIR fromuser=tdap touser=tdap

impdp SYSTEM/oracle@hbglfzjc DIRECTORY=DATA_PUMP_DIR DUMPFILE=fxglall_20170104_bak.dmp

export ORACLE_SID=gxdjecho $ORACLE_SIDimpdp system/oracle123（数据库管理员的用户名和密码）
DIRECTORY=DATA_PUMP_DIR DUMPFILE=ydgldl.dmp fromuser=ydgl
touser=ydgl logfile=import.log

2.10 删除用户下面所有的表、序列、存储过程等
在终端依次输入以下命令
sqlplus
–输入需要删除的用户名
–输入密码
SET HEAD OFF
SPOOL /tmp/database/drop_tables.sql
–chr(13) ASCII中=换行 chr(10) ASCII中=回车
–delete tables
select ‘drop table ’ || table_name ||’;’||chr(13)||chr(10) from user_tables;
–delete views
select ‘drop view ’ || view_name||’;’||chr(13)||chr(10) from user_views;
–delete seqs
select ‘drop sequence ’ || sequence_name||’;’||chr(13)||chr(10) from user_sequences;
–delete functions
select ‘drop function ’ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=‘FUNCTION’;
–delete procedure
select ‘drop procedure ’ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=‘PROCEDURE’;
–delete package
select ‘drop package ’ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=‘PACKAGE’;
–delete triggerSELECT ‘drop TRIGGER "’ ||SYS_CONTEXT(‘USERENV’,‘CURRENT_USER’)||’"."’|| TRIGGER_NAME ||’";’ ||CHR(13) ||CHR(10)FROM USER_TRIGGERS
spool off;
@/tmp/database/drop_tables.sql;
purge recyclebin; --清空Oracle的回收站

方法2–**********************
1　select Drop table ||table_name||;
　　from all_tables　　where owner=要删除的用户名（注意要大写）;　　2、　　删除所有表　　以用户test为例　　for example:　　declare　　cursor cur1 is select table_name from dba_tables where owner=TEST;　　begin　　for cur2 in cur1 loop　　execute immediate drop table test.||cur2.table_name;　　end loop;　　end;　　3、这个删除当前用户的所有对象(表、视图、触发器、存储过程、函数)　　DECLARE　　TYPE name_list IS TABLE OF VARCHAR2(40);　　TYPE type_list IS TABLE OF VARCHAR2(20);　　Tab_name name_list:=name_list();　　Tab_type type_list:=type_list();　　sql_str VARCHAR2(500);　　BEGIN　　sql_str := select uo.object_name,uo.object_type from user_objects uo where uo.object_type not in(INDEX,LOB) order by uo.object_type desc;　　EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;　　FOR i IN Tab_name.FIRST… Tab_name.LAST LOOP　　sql_str := DROP || Tab_type(i) || || Tab_name(i);　　EXECUTE IMMEDIATE sql_str;　　END LOOP;　　END;
–新建linux目录
进入到某个实例 export ORACLE_SID=实例名
Sqlplus
Create or replace directory 目录名 as ‘路径’

–查看目录
select * from dba_directories

–删除数据库下的某个用户以及该用户下级联对象
方法一：
1、删除用户 然后重建，这样最快：
1、在cmd中输入sqlplus / as sysdba2、删除用户A，级所有和用户A关联的数据drop user a cascade;3、重建用户Acreate user A identified by 密码;grant connect,resource to A;
方法二：
–删除某个用户下的对象
set
heading
off
;
set
feedback
off
;
spool c:\dropobj.sql;
prompt --Drop constraint
select
‘alter table ‘||table_name||’ drop constraint ‘||constraint_name||’ ;’
from
user_constraints
where
constraint_type=‘R’;
prompt --Drop tables
select
‘drop table ‘||table_name ||’;’
from
user_tables;
prompt --Drop view
select
‘drop view ’ ||view_name||’;’
from
user_views;
prompt --Drop sequence
select
‘drop sequence ’ ||sequence_name||’;’
from
user_sequences;
prompt --Drop function
select
‘drop function ’ ||object_name||’;’
from
user_objects
where
object_type=‘FUNCTION’;
prompt --Drop procedure
select
‘drop procedure ‘||object_name||’;’
from
user_objects
where
object_type=‘PROCEDURE’;
prompt --Drop package
prompt --Drop package body
select
‘drop package ‘|| object_name||’;’
from
user_objects
where
object_type=‘PACKAGE’;
prompt --Drop database link
select
‘drop database link ‘|| object_name||’;’
from
user_objects
where
object_type=‘DATABASE LINK’;
spool
off
;
set
heading
on
;
set
feedback
on
;
@@c:\dropobj.sql;
host del c:\dropobj.sql;
//数据泵导入导出文章
http://blog.sina.com.cn/s/blog_62defbef0101ny6t.html

impdp system/oracle123 DIRECTORY=KYU DUMPFILE=alldata.dmp

1、PL/SQL Developer记住登陆密码 在使用PL/SQL Developer时，为了工作方便希望PL/SQL Developer记住登录Oracle的用户名和密码； 设置方法：PL/SQL Developer 7.1.2 ->tools->Preferences->Oracle->Logon History，“Store history”是默认勾选的，勾上“Store with password” 即可，重新登录在输入一次密码则记住了。2、登录后默认自动选中My Objects设置方法：Tools菜单－－Brower Filters会打开Brower Filters的定单窗口，把“My Objects”设为默认即可。同理，可以在Tools菜单－－Brower Filters中把你经常点的几个目录（比如：tables Views Seq Functions Procedures）移得靠上一点，并加上颜色区分，这样你的平均寻表时间会大大缩短，试试看。3、执行单条SQL语句 在使用PL/SQL Developer的SQL Window时，按F8键，PL/SQL Developer默认是执行该窗口的所有SQL语句，需要设置为鼠标所在的那条SQL语句，即执行当前SQL语句；设置方法：PL/SQL Developer 7.1.2 -->tools->Preferences–>Window types ，勾上“AutoSelect Statement” 即可。 4、双击即显示表数据设置方法：Preferences－－User Interface，在右侧，为不同的Object type绑定双击和拖放操作。5、格式化SQL语句 在使用PL/SQL Developer的SQL Window时，有时候输入的SQL语句太长或太乱，希望能用比较通用的写法格式话一下，这样看起来会好看些，也好分析； 使用方法：选中需要格式化的SQL语句，然后点击工具栏的PL/SQL beautifier按钮即可. 6、查看执行计划 在使用PL/SQL Developer的SQL Window时，有时候输入的SQL语句执行的效率，分析下表结构，如何可以提高查询的效率，可以通过查看Oracle提供的执行计划； 使用方法：选中需要分析的SQL语句，然后点击工具栏的Explain plan按钮（即执行计划），或者直接按F5即可。 7、调试存储过程 在使用PL/SQL Developer操作Oracle时，有时候调用某些存储过程，或者调试存储过程； 调 用存储过程的方法：首先，在PL/SQL Developer左边的Browser中选择Procedures，查找需要调用的存储过程；然后，选中调试的存储过程，点击右键，选择Test，在弹 出来的Test scrīpt窗口中，对于定义为in类型的参数，需要给该参数的Value输入值；最后点击上面的条数按钮：Start debugger 或者按F9；最后点击：RUN 或者Ctrl+R 8、左下角显示window list 点击菜单 tools -> window list, 将弹出的小窗口拖到左下角合适位置，然后点击菜单 window->save layout 9、防止登录超时 tools->Preferences–>Oracle->Connection 选择 “check connection” 10、不备份sql文件 tools->Preferences->Files->backup,页面中backup files中选择 disabled11、特殊Copy在SQL Window里写好的SQL语句通常需要放到Java或者别的语言内，就需要转成字符串并上加上相应的连字符，这一个事不需要再重复做了，在写好的SQL上点右键，使用特殊Copy即OK！12、SQL Window中根据光标位置自动选择语句设置方法：Preferences–Window Types–SQL Window，将AutoSelect statement选中即可。注意，每条语句后面要加分号。13、选中行高亮Preferences–User Interface–Editor–other–Highlight edit line不可否认工具在很多时候带给我们工作和学习很大便利。同时，安全性也不容忽视，如记住密码功能，对于生产库要慎用，防止误操作。