ORACLE常用的查询语句

==========================死锁查询及解锁======================= 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