==========================死锁查询及解锁======================= 1 检查死锁的表 select substr(v$lock.sid,1,4) sid, substr(username,1,12) username, substr(object_name,1,25) objectname, v$lock.type locktype, decode(rtrim(substr(lmode,
==========================死锁查询及解锁=======================
1 检查死锁的表
select substr(v$lock.sid,1,4) sid,
substr(username,1,12) username,
substr(object_name,1,25) objectname,
v$lock.type locktype,
decode(rtrim(substr(lmode,1,4)),
'2','row-s (ss)','3','row-x (sx)',
'4','share', '5','s/row-x (ssx)',
'6','exclusive', 'other' ) lockmode,
substr(v$session.program,1,25) programname,
v$session.serial#,
'alter system kill session ' || v$session.serial#
from v$lock,sys.dba_objects,v$session
where (object_id = v$lock.id1
and v$lock.sid = v$session.sid
and username is not null
and username not in ('sys','system')
and serial# != 1)
2 找到表对应的session
select 'alter system kill session ''' || a.sid || ',' || b.serial# || ''';'
from v$lock a, all_objects c, v$session b
where a.sid > 8
and a.id1 = c.object_id
and a.sid = b.sid and (a.sid= '773' or a.sid= '775')
3 杀掉相应的进程
alter system kill session 'sid,serial#';
=================================================================
--详细的列出每个表的占用情况
select a.tablespace_name ,
round(sum(a.bytes)/1024/1024/1024,2) gb,
round((sum(a.bytes)-sum(nvl(b.bytes,0)))/1024/1024/1024,2) used,
round(sum(nvl(b.bytes,0))/1024/1024/1024,2) free
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
and a.tablespace_name=b.tablespace_name
group by a.tablespace_name
order by a.tablespace_name
---空间使用比
select a.tablespace_name,round(a.bytes/1024/1024/1024) sum gb,round((a.bytes-b.bytes)/1024/1024/1024) used gb,round(b.bytes/1024/1024/1024) free gb,
round(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc
===============================sql语句用法=====================
1 修改某个表的字段的数据类型
alter table tb_b_dm_cdma_dev_prf modify brand_type_id number(10);
2 清空指定分区记录
alter table tb_b_ft_day_charge_200811 truncate partition platn_551
3 增加约束
alter table tb_jtsc_data_comp add constraint supplier_unique unique (supplier_id);
4 alter table tb_cw_auto_file_config modify comp_tab not null ; table altered
5 alter table tb_cw_auto_file_config add constraint p_rule_id primary key(rule_id);
6 alter table tb_b_ft_ofr_rev_dm_200904 add sys_user_type_id number(6);
-- add comments to the columns
comment on column tb_b_ft_ofr_rev_dm_200904.sys_user_type_id
is '系统用户标识 tb_b_dim_sys_user_type ';
7 增加分区
alter table tb_b_ft_bill_busi_month add partition p200903 values less than (200904) tablespace tbs_bil_other;
截断分区:alter table sales truncate partiton sales1999_q2;
8 给表建立索引
create index ind_jtsc_edp_21 on tb_jtsc_edp_21_mid (acct_item_type_id) tablespace tbs_ind_acct_item_550 ;
======================增加表空间==================
1 lsvg -l 找到有空余的空间 必须是close的
2
alter tablespace tbs_bss_rev_month_00 add datafile '/dev/rdw_sett_795' reuse ; 里面要加个r
========================oracle的记录删除恢复========
1 将删除的表给 恢复了
flashback table tablename to before drop；但是oracle可能会提示在oracle的recyclebin中没有此表的 错误提示信息
2 闪回查询
首先 执行alter table table_name enable row movement（必须保证该表row movement）;
然后闪回到某个时间点上
flashback table test_flashback to timestamp
to_timestamp('2008-10-13 16:47:00','yyyy-mm-dd hh24:mi:ss')
也可以
select * from test_flashback as of timestamp
to_timestamp('2008-10-13 16:47:00','yyyy-mm-dd hh24:mi:ss')
col scn for 9999999999999
select timestamp_to_scn('14-10月 -08 10.40.00.000000000 上午') from dual;
select scn_to_timestamp(9450968440129) scn from dual;
insert into tb_evt_cust_order_tr_551 select * from tb_evt_cust_order_tr_551 as of timestamp scn_to_timestamp(9450968440129) where order_id ='32620094';
select timestamp_to_scn(to_timestamp('2008-10-30 13:46:04 ','yyyy-mm-dd hh24:mi:ss'))
from dual;
select timestamp_to_scn(to_timestamp('2008-11-04 16:50:00','yyyy-mm-dd hh24:mi:ss')) from dual;
select * from tb_check_e6e8_prf_bak_e8 as of
scn 9459341319432
用以下语句可以查询到每个操作的时间 versions_starttime 字段
select versions_starttime,
versions_endtime,
versions_xid,
versions_operation
from 被删除的表名 versions between timestamp minvalue and maxvalue
order by versions_starttime;
3 如果误删除了记录，将其恢复步骤如下
1 alter table tb_check_e6e8_prf_bak_e8 enable row movement;（这个命令的作用是，允许oracle 修改分配给 行的rowid。在oracle 中，插入一行时就会为它分配一个rowid，而且这一行永远拥有这个rowid。闪回表处理会对emp 完成delete，并且重新插入行，这样就会为这些行分配一个新的rowid。要支持闪回就必须允许oracle 执行这个操作）
2 flashback table tb_check_e6e8_prf_bak_e8
to timestamp to_timestamp('2008-11-04 15:00:00','yyyy-mm-dd hh24:mi:ss')
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
ora-01555: snapshot too old: rollback segment number 21 with name _syssmu21$ too small
报1555错误说明已经不能闪回了,undo空间里面的信息已经被新的undo覆盖了
ora-08180: no snapshot found based on specified time
4 在回收站里面可以看到删除的表
select * from dba_recyclebin