oracle（2）

6、查询正在运行的对象
查看oracle数据库中正在执行的sql语句：
select a.program,b.spid,c.sql_text from v$session a,v$process b,v$sqlarea c where a.paddr=b.addr and a.sql_hash_value=c.hash_value and a.username is not null;

查看正在运行的存储过程
select name,locks,pins
from v$db_object_cache
where locks > 0 and pins > 0 and type='PROCEDURE';

SELECT * FROM V$ACCESS WHERE OWNER='PORTAL' AND V$ACCESS.OBJECT='TEST_GET_SERVICENUMDEV_DATE';

SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='280';
ALTER SYSTEM KILL SESSION '280,44207';
select a.program, b.spid, c.sql_text
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;

7、查询当前锁
SELECT p.spid,
a.serial#,
c.object_name,
b.session_id,
b.oracle_username,
b.os_user_name
FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr
AND a.process = b.process
AND c.object_id = b.object_id

8、执行计划
插入SQL的执行计划（插入plan_table表）
explain plan for + SQL

查询对应的执行计划
select object_name, operation, options from plan_table order by id;

可以根据执行计划对SQL进行优化

也可以使用set autotrace on;开启执行计划模式，然后直接执行sql即可同时显示出执行计划

9、数据量大时，sql语句避免使用distinct、in等关键字

10、delete可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据
操作 回滚 高水线 空间 效率
Truncate 不能 下降 回收 快
delete 可以 不变 不回收 慢

11、
alter database open;

recover datafile 5;
alter database datafile 5 online;

ALTER DATABASE datafile 5 resize 2048m;


shutdown immediate;
select * from dba_data_files

dbv file=/dev/raw/raw1 blocksize=8192

sqlplus /nolog
connect sys/123 as sysdba;

alter database rename file '/oracle/app/oradata/ora11g/data.dbf' to '/dev/raw/raw1';

drop tablespace ts_index including contents and datafiles;

alter tablespace m400idx add datafile '/home/oracle/datafile/m400idx01.dbf' size 256m REUSE AUTOEXTEND ON NEXT 256m MAXSIZE 4096m;

drop table test3;
create table test3(
test number(38),
rest date GENERATED ALWAYS as (to_date(test, 'yyyymmddhh24miss')) VIRTUAL
)
partition by range (rest) interval (numtoyminterval(1,'month'))
(
partition test_001 values less than (to_date('201301', 'yyyymm'))
);

insert into test3 (test) values (20130423000000);
insert into test3 (test) values (20130523000000);
insert into test3 (test) values (20130623000000);
insert into test3 (test) values (20130823000000);
insert into test3 (test) values (20130923000000);
insert into test3 (test) values (20131023000000);
insert into test3 (test) values (20131123000000);
insert into test3 (test) values (20131223000000);
commit;