Oracle常用命令

sqlplus 设置

set timing on
set feedback 1

--创建表空间

    CREATE TABLESPACE css
    LOGGING 
    DATAFILE 'C:\oracle\product\10.2.0\oradata\orcl\css.dbf' SIZE 20M 
    AUTOEXTEND 
    ON NEXT  20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT AUTO;
--创建用户
CREATE USER css IDENTIFIED BY css 
temporary tablespace temp DEFAULT TABLESPACE css ACCOUNT UNLOCK;
--授权

GRANT connect,resource TO css;

grant create view to rmis;
grant unlimited tablespace to rmis;

alter user rmis quota unlimited on rmis;

导入

imp SINFO_SZDX/guanliyuan@ORCL fromuser=SINFO_SZDX touser=SINFO_SZDX file=SINFO_SZDX.dmp log=import.log

导出：

exp rmis/rmis  BUFFER=64000 FILE=C:\rmis.dmp OWNER=rmis

查看当前用户的session id

select userenv('sid') from dual;

杀掉某session      
 Alter system kill session 'SID,SERIAL#'

查看当前用户的sid和serial#:
select sid, serial#, status from v$session where audsid=userenv('sessionid');

查看当前用户的spid:
select spid from v$process p, v$session s where s.audsid=userenv('sessionid') and s.paddr=p.addr;
select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv('sessionid');

查看当前用户的trace file路径:
select p.value || '\' || t.instance || '_ora_' || ltrim(to_char(p.spid,'fm99999')) || '.trc'
 from v$process p, v$session s, v$parameter p, v$thread t
 where p.addr = s.paddr and s.audsid = userenv('sessionid') and p.name = 'user_dump_dest';

已知spid，查看当前正在执行或最近一次执行的语句:
select /*+ ordered */ sql_text from v$sqltext sql
 where (sql.hash_value, sql.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 s where s.paddr = (select addr from v$process p where p.spid = to_number('&pid')))
 order by piece asc;

查看锁和等待:
col user_name format a10
col owner format a10
col object_name format a15
col sid format 999999
col serial# format 999999
col spid format a6
select /*+ rule */ lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,
 o.owner, o.object_name, o.object_type, s.sid, s.serial#, p.spid
 from v$locked_object l, dba_objects o, v$session s, v$process p
 where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr
 order by o.object_id, xidusn desc;