我在接触一个以前没有接触没有碰过的数据库时,
先学会以下几条命令
1:查死锁表SQL的方法.
select SID,SERIAL# from
v$locked_object a, v$session b where a.SESSION_ID = b.SID
2)杀线程
alter system kill session '77,13299' --[SID,SERIAL#]
3)查执行游标
select SID,SERIAL# from
v$open_curcos a, v$session b where a.SID = b.SID
4)杀执行游标
alter system kill session '77,13299' --[SID,SERIAL#]
5)查全部索引
select * from all_indexes where TABLE_NAME like '%TaskTable%'
6)从查询出的结果中将数据加到指定表
insert into testTable select * from testTable2
7)生成和某个表相同的数据和结构
create table TestTable
as select * from TestTable2
8)加外键约束
alter table TestTable
add constraint pfk_tt foreign key (acptsite_id)
references tt_2(acptsite_id)
9)删除重复纪录
--按num分组后找出表中num列重复,即出现次数大于一次
delete from test_subscrb b where exists (
select max(a.sub_name) from xxx a --查找表中num列重复的,列出重复的记录数,并列出他的name属性
where a.acct=b.acct group by a.acct
having count(a.acct) >1)
10)建立序列
CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2;
11)20和10之间的行
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
12) 后台登陆
sqlplus /nolog
connect user/pw as sysDBA
//执行自己想写的SQL
13)启动ORACLE
shutdown;关闭数据库;
STARTUP;打开数据库;
如果侦听器没启动则退出sql>exit用lsnrctl start