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