Oracle笔记

1、查看表空间空间大小；

SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')||'' "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')||'G'  "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')||'G'  "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')||'%'  "比例"
FROM   (SELECT A.TABLESPACE_NAME 
               TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

2、查询表空间路径；

SELECT B.FILE_ID　　文件ID,
	B.TABLESPACE_NAME　　表空间,
	B.FILE_NAME　　　　　物理文件名,
	B.BYTES　　　　　　　总字节数,
	(B.BYTES-SUM(NVL(A.BYTES,0)))　　　已使用,
	SUM(NVL(A.BYTES,0))　　　　　　　　剩余,
	SUM(NVL(A.BYTES,0))/(B.BYTES)*100　剩余百分比
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.FILE_ID,B.BYTES
ORDER BY B.TABLESPACE_NAME

3、设置表空间大小

ALTER DATABASE DATAFILE 'D:\ORADATA\BSOFT_DATA.DBF' 
AUTOEXTEND ON NEXT 300M MAXSIZE 30720M; 

ALTER TABLESPACE BSOFT_DATA ADD DATAFILE
'D:\ORADATA\BSOFT_DATA.DBF' SIZE 30720M;

4、临时表空间

SELECT TABLESPACE_NAME,FILE_NAME,USER_BYTES/BYTES,
BYTES / 1024 / 1024 "FILE_SIZE(M)",AUTOEXTENSIBLE
FROM DBA_TEMP_FILES T
WHERE T.TABLESPACE_NAME = 'BSOFT_TEMP';

SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

调整临时表空间大小

ALTER TABLESPACE TEMP  ADD TEMPFILE 'D:\ORADATA\BSOFT_TEMP.DBF' SIZE 2048M;

alter tablespace temp
add
TEMPFILE 'D:\ORADATA\BSOFT_TEMP.DBF'
size 1024m
autoextend on
next 1024m maxsize 20480m;


alter database tempfile 'D:\ORADATA\BSOFT_TEMP.DBF' resize 4G;

 Oracle解决锁表问题

SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
WHERE AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = SESS.SID

ALTER SYSTEM KILL SESSION '166,154';