Oracle锁表与解锁

--锁表查询SQL
SELECT object_name, machine, s.sid, s.serial#,s.*
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　 = o.object_id
AND l.session_id = s.sid;

select * FROM dba_ddl_locks where name =upper('P_BALANCE_DELETE'); -- 查看存储过程被锁
select t.sid,t.serial# from v$session t
where t.sid=&session_id;

--释放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '1584, 23357';
select * from v$parameter where name like '%cpu%';

 

--用语句查询谁在使用临时段

SELECT se.username,
sid,
serial#,
sql_address,
machine,
program,
tablespace,
segtype,
contents
FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;
Alter system kill session 'sid,serial#';
/*select tablespace_name,
CURRENT_USERS,
TOTAL_BLOCKS,
USED_BLOCKS,
FREE_BLOCKS
from v$sort_segment;*/

 

/*select * from dba_tablespaces where tablespace_name = 'TEMP';

alter tablespace TEMP default storage(pctincrease 0);*/

-- 清理表空间
select ts#, name from sys.ts$ ;

alter session set events 'immediate trace name DROP_SEGMENTS level 3' ;

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
-- 查看表空间
select *
from (Select a.tablespace_name,
to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
'99,999.999') use_bytes,
to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name)
order by tablespace_name;

-- --索引表空间
/*CREATE TABLESPACE AFCINDEX
DATAFILE '/u01/app/oracle/oradata/accdb1/AFCINDEX.dbf' size 3072M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; */

-- 修改表空间大小
alter database datafile '/u01/app/oracle/oradata/accdb1/SYSTEM.dbf' resize 2048M;
-- 查看表空间路劲
select * from dba_data_files ;
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
-- 删表空间文件
--alter tablespace USERS drop datafile '/u01/app/oracle/oradata/accdb1/users02.dbf';

 

select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id
--找出所有照成锁的会话
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

ALTER system kill session '2, 58561';

select value from v$parameter where name = 'processes';

--Oracle的show processlist
SELECT a.username,a.machine, b.sql_id, b.SQL_TEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
AND a.SQL_HASH_VALUE = b.HASH_VALUE;

--Oracle show full processlist
SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
AND a.SQL_HASH_VALUE = b.HASH_VALUE;
SELECT *
FROM dba_indexes
WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYS', 'SYSTEM')

--增加表空间大小的四种方法
--Meathod1：给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

--Meathod2：新增数据文件，并且允许数据文件自动增长
ALTER TABLESPACE AFCINDEX ADD DATAFILE
'/u01/app/oracle/oradata/accdb1/AFCINDEX01.dbf' SIZE 32766M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

--Meathod3：允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF'
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

--Meathod4：手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/accdb1/users01.dbf'
RESIZE 100M;

--扩展表空间的3种方法

--1手动增加数据文件大小

alter database datafile '/home/oracle/ts01.dbf' resize 100m;

--2把表空间设置为自动扩展

alter database datafile '/home/oracle/ts01.dbf' autoextend on next 5m maxsize unlimited;

--3 往表空间增加数据文件

alter tablespace ts01 add datafile '/home/oracle/ts02.dbf' size 2m;

--1.查看总消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

--2.查看CPU消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

--3.查看消耗磁盘读取最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

select a.sql_text,
a.sql_fulltext,
a.cpu_time,
b.sid,
b.serial#,
b.username,
b.machine,
b.terminal,
b.program,
c.block_gets,
c.consistent_gets,
c.physical_reads,
c.block_changes,
c.consistent_changes
from v$sql a,
v$session b,
v$sess_io c
where a.address=b.prev_sql_addr
and b.sid=c.sid
order by a.CPU_TIME desc;