oracle 常用查询语句,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,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