테이블 변경 중인 락 확인

-- 락 확인

SELECT CC.SID,CC.SERIAL#
     , BB.OWNER,BB.OBJECT_NAME
     , CC.MACHINE
     , CC.PROGRAM
     , CC.TERMINAL
     , DD.SPID BG_PID
     , CC.PROCESS FG_PID
  FROM V$LOCKED_OBJECT AA
     , ALL_OBJECTS BB
     , V$SESSION CC
     , V$PROCESS DD
 WHERE BB.OBJECT_ID = AA.OBJECT_ID
   AND CC.SID = AA.SESSION_ID
   AND CC.PADDR = DD.ADDR
;
-- KILL

ALTER SYSTEM KILL SESSION '921,3217';

SELECT * FROM V$SESSION WHERE SID='921';

-- 롤백시 남은 레코드및 블럭수
--USED_UBLK(사용된 언두 블럭) USED_UREC(사용된 언두 레코드)
SELECT S.SID
     , S.MACHINE
     , R.NAME ROLLNAME
     , T.USED_UBLK USED_UBLK
     , T.USED_UREC
  FROM V$SESSION S
     , V$ROLLNAME R
     , V$TRANSACTION T
 WHERE S.SADDR=T.SES_ADDR
   AND T.XIDUSN=R.USN
 --AND USED_UBLK > 10
 --AND R.NAME IN ('R18')
   AND S.SID = '921'
 ORDER BY USED_UBLK
;

오라클 테이블스페이스 확인

-- 테이블스페이스 용량 확인

SELECT A.TABLESPACE_NAME
     , ROUND(SUM(A.BYTES) / (1024 * 1024 * 1024), 2) || 'G' "전체"
     , ROUND(SUM(B.FREES) / (1024 * 1024 * 1024), 2) || 'G' "여유"
  FROM ( SELECT FILE_ID, TABLESPACE_NAME, SUM(BYTES) BYTES
            FROM DBA_DATA_FILES
          GROUP BY FILE_ID, TABLESPACE_NAME
       ) A
     , ( SELECT TABLESPACE_NAME, FILE_ID, SUM(BYTES) FREES
           FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME, FILE_ID
       ) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
--   AND A.TABLESPACE_NAME LIKE 'SP_FMC%'
 GROUP BY A.TABLESPACE_NAME;
;

-- 테이블스페이스 데이터 파일 용량 확인

SELECT A.TABLESPACE_NAME                                    "테이블스페이스명"
     , ROUND((A.BYTES - B.FREE) / (1024 * 1024 * 1024), 2)  "사용공간"
     , ROUND(B.FREE / (1024 * 1024 * 1024),2)               "여유 공간"
     , ROUND(A.BYTES / (1024 * 1024 * 1024),2)              "총크기"
     , TO_CHAR((B.FREE / A.BYTES * 100) , '999.99')||'%'    "여유공간"
  FROM ( SELECT FILE_ID,
                TABLESPACE_NAME,
                SUBSTR(FILE_NAME,1,200) FILE_NM,
                SUM(BYTES) BYTES
           FROM DBA_DATA_FILES
          GROUP BY FILE_ID,TABLESPACE_NAME,SUBSTR(FILE_NAME,1,200)
       ) A,
       ( SELECT TABLESPACE_NAME,
                FILE_ID,
                SUM(NVL(BYTES,0)) FREE
           FROM DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME,FILE_ID
       ) B
 WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
   AND A.FILE_ID = B.FILE_ID
   AND A.TABLESPACE_NAME = 'SP_AS_AORM_DB'
;