bedbmsguru

pangate.com/647

SELECT /*+ RULE */ D.JOB, V.SID, V.SERIAL#, STATUS, LOG_USER USERNAME, WHAT, DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') RUNNING, D.FAILURES, 'alter system kill session ' || '''' || V.SID || ', ' || V.SERIAL# || '''' || ' immediate;' KILL_SQL FROM DBA_JOBS_RUNNING D, V$SESSION V, DBA_JOBS J WHERE V.SID = D.SID AND D.JOB = J.JOB;

http://www.runningoracle.com/product_info.php?products_id=34

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;

SELECT a.username

, a.sid

, a.serial#

, a.osuser

, b.tablespace

, (b.blocks * (select block_size from  v$buffer_pool) /1024/1024) USED_SIZE_MB

, c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address= a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.blocks DESC

--출처

https://positivemh.tistory.com/260

SELECT level,

LPAD('         ', (level-1)*2, '        ') || NVL(s.username, '(oracle)') AS username,

s.osuser,

s.sid,

s.serial#,

s.lockwait,

s.status,

s.module,

s.machine,

s.program,

TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM v$session s

WHERE level > 1

OR     EXISTS (SELECT 1

                    FROM v$session

                    WHERE blocking_session = s.sid)

CONNECT BY PRIOR s.sid = s.blocking_session

START WITH s.blocking_session IS NULL;

--출처

https://oracle-base.com/dba/script?category=10g&file=lock_tree.sql

1.undo 사용량 확인

SELECT TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#) sid_serial,
         NVL(s.username, 'None') orauser,
         s.program,
         r.name undoseg,
         ROUND(t.used_ublk * TO_NUMBER(x.value)/1024/1024, 2)||'M' "Undo"
FROM   sys.v_$rollname    r,
        sys.v_$session     s,
        sys.v_$transaction t,
        sys.v_$parameter   x
WHERE s.taddr = t.addr
AND r.usn   = t.xidusn(+)
AND x.name  = 'db_block_size'
ORDER BY (t.used_ublk * TO_NUMBER(x.value/1024)) DESC

2. 현재 Rollback 진행률 확인

select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING, ELAPSED_SECONDS

from v$session_longops where username='SCOTT';

--출처

https://positivemh.tistory.com/181

--1 NOLOGGIN QUERY 만들기 (FUNCTION BASE INDEX 가 있을경우 진행불가능)
SELECT 'ALTER TABLE OWNER명.테이블명 NOLOGGING;' FROM DUAL
UNION ALL
SELECT 'ALTER INDEX ' || OWNER ||'.' || INDEX_NAME || ' NOLOGGING;' FROM all_indexes WHERE table_name='테이블명' AND owner='OWNER명'
UNION ALL
SELECT 'FUNCTION BASE INDEX 있음' FROM all_indexes WHERE table_name='테이블명' AND owner='OWNER명' AND INDEX_TYPE LIKE 'FUNCTION%'


--2 ROW MOVEMENT ENABLE  만들기
SELECT 'ALTER TABLE OWNER명.테이블명 ENABLE ROW MOVEMENT;' FROM DUAL

ALTER TABLE OWNER명.테이블명 ENABLE ROW MOVEMENT;



-- 3-1 SHRINK SPACE CASCADE QUERY 만들기
SELECT 'ALTER TABLE OWNER명.테이블명 SHRINK SPACE CASCADE;' FROM DUAL

ALTER TABLE OWNER명.테이블명 SHRINK SPACE CASCADE;

-- 3-2  Shrink Space INDEX Table 각각 별도로 할 경우

SELECT 'ALTER TABLE OWNER명.테이블명 SHRINK SPACE;' FROM DUAL
UNION ALL
SELECT 'ALTER INDEX ' || OWNER ||'.' || INDEX_NAME || ' SHRINK SPACE;' FROM all_indexes WHERE table_name='테이블명' AND owner='OWNER명'




--4 ROW MOVEMENT DISABLE  만들기
SELECT 'ALTER TABLE OWNER명.테이블명 DISABLE ROW MOVEMENT;' FROM DUAL


ALTER TABLE OWNER명.테이블명 DISABLE ROW MOVEMENT;

--5 LOGGIN QUERY 만들기 
SELECT 'ALTER TABLE OWNER명.테이블명 LOGGING;' FROM DUAL
UNION ALL
SELECT 'ALTER INDEX ' || OWNER ||'.' || INDEX_NAME || ' LOGGING;' FROM all_indexes WHERE table_name='테이블명' AND owner='OWNER명'



--6 원상복귀 확인  
SELECT INDEX_NAME, LOGGING, '' FROM all_indexes WHERE table_name='테이블명' AND owner='OWNER명'
UNION ALL
SELECT TABLE_NAME, LOGGING, ROW_MOVEMENT FROM all_TABLEs WHERE table_name='테이블명' AND owner='OWNER명'

http://blog.naver.com/itisksc/30079446285

1. DBA 로그인

2. 링크 생성 권한 부여
grant create database link to 아이디;

3. 오라클 서버 TNS 등록
cd %ORACLE_HOME/admin/
cp tnsnames.ora tnsnames.ora.20181217
vi tnsnames.ora

4. 아이디 로그인

5. 링크 생성
CREATE DATABASE LINK 디비링크이름
connect to 타겟아이디 identified by “암호”
using ''tns등록이름''

6. 테스트
Select * from dual@디비링크이름;

7. 권한회수
revoke create database link from 아이디;

WITH sessions AS

 (SELECT /*+materialize*/

 sid, blocking_session, row_wait_obj#, sql_id

 FROM v$session) 

SELECT LPAD(' ', LEVEL ) || sid sid, object_name, 

substr(sql_text,1,40) sql_text 

FROM sessions s 

LEFT OUTER JOIN dba_objects 

ON (object_id = row_wait_obj#) 

LEFT OUTER JOIN v$sql 

USING (sql_id) 

WHERE sid IN (SELECT blocking_session FROM sessions) 

OR blocking_session IS NOT NULL 

CONNECT BY PRIOR sid = blocking_session 

START WITH blocking_session IS NULL;

https://oracle2amar.wordpress.com/2010/11/29/script-to-find-the-blocking-sqls-during-the-deadlock-situations/

--blockin 확인 SQL

SELECT 
       holding_session bsession_id, 
      waiting_session wsession_id, 
      b.username busername, 
      a.username wusername, 
      c.lock_type type, 
      mode_held, mode_requested,
      lock_id1, lock_id2 
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a 
WHERE
c.holding_session=b.sid and
c.waiting_session=a.sid

http://www.dba-oracle.com/t_find_blocking_sessions.htm

SELECT owner, table_name, TRUNC(sum(bytes) / 1024 / 1024) Meg
  FROM (SELECT segment_name table_name, owner, bytes
          FROM dba_segments
         WHERE segment_type = 'TABLE'
        UNION ALL
        SELECT i.table_name, i.owner, s.bytes
          FROM dba_indexes i, dba_segments s
         WHERE s.segment_name = i.index_name
           AND s.owner = i.owner
           AND s.segment_type = 'INDEX'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.segment_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBSEGMENT'
        UNION ALL
        SELECT l.table_name, l.owner, s.bytes
          FROM dba_lobs l, dba_segments s
         WHERE s.segment_name = l.index_name
           AND s.owner = l.owner
           AND s.segment_type = 'LOBINDEX')
 WHERE owner in UPPER('&owner')
 GROUP BY table_name, owner
HAVING SUM(bytes) / 1024 / 1024 > 10 /* Ignore really small tables */
 ORDER BY SUM(bytes) desc;

출처: http://a2zakir.blogspot.com/2012/02/script-for-getting-oracle-table-size.html