Oracle常用命令

select name from v$datefile;

select member from v$logfile;

select name from v$controlfile;

 

create table newtablename as select * from oldtablename where datetime < to_date ('20111001','yyyymmdd');

 

drop table tablename;

 

查看进程进度：

select sid,sofar/totalwork from v$session_longops where time_remaining != 0;

 

查看锁表：
select session_id,object_id,oracle_username from v$locked_object;
select object_id,object_name  FROM dba_objects where object_id = '&object_id';
ALTER SYSTEM KILL SESSION 'sid,serial#';

查看SQL:
select b.sid,a.sql_text
from v$sqltext a, v$session b
where b.sid='&sid'
and a.hash_value=decode(b.sql_hash_value,0,b.prev_hash_value,b.sql_hash_value)
order by a.piece;

通过操作系统的PID查询SQL

SELECT sql_text
  FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
       (SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
               DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
          FROM v$session b
         WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = '&pid'))
ORDER BY piece ASC

 

 

 

ALTER DATABASE DATAFILE '/oradata/oral/undotbs01.dbf' RESIZE  1024M