Kamsi's Blog

Tablespace 용량 확인

select   substr(a.tablespace_name,1,30) tablespace,

         round(sum(a.total1)/1024/1024,1) "TotalMB",

         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",

         round(sum(a.sum1)/1024/1024,1) "FreeMB",

         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"

from

         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt

          from     dba_free_space

          group by tablespace_name

          union

          select   tablespace_name,sum(bytes) total1,0,0,0

          from     dba_data_files

          group by tablespace_name) a

group by a.tablespace_name

order by tablespace;

Tablespace 용량 늘리기

alter DATABASE datafile '<경로>/<파일명>.dbf' resize 1024M;

SELECT *

FROM 

    DEPT_INFO

WHERE  

    CONNECT_BY_ISLEAF  = 1

    AND CONNECT_BY_ISCYCLE = 0

START WITH PARNTS_DEPT_CD = '00000000'

CONNECT BY NOCYCLE PRIOR DEPT_CD = PARNTS_DEPT_CD;

UPDATE TABLE1 T1 

SET (NAME, DESC) = ( SELECT T2.NAME, T2.DESC FROM TABLE2 T2 WHERE T1.ID = T2.ID )

WHERE EXISTS ( SELECT 1 FROM TABLE2 T2 WHERE T1.ID = T2.ID );

1. 테이블 목록조회

SELECT *

FROM all_all_tables;

SELECT *

FROM dba_tables;

SELECT *

FROM ALL_OBJECTS

WHERE OBJECT_TYPE = 'TABLE'

-- 접속한 계정의 테이블 목록보기

SELECT *

FROM tabs;

SELECT *

FROM USER_OBJECTS 

WHERE OBJECT_TYPE = 'TABLE';

SELECT *

FROM USER_TABLES;

-- 테이블 코멘트 보기

SELECT *

FROM ALL_TAB_COMMENTS

WHERE TABLE_NAME = '테이블명'

SELECT *

FROM USER_TAB_COMMENTS;

2. 컬럼 목록조회

SELECT *

FROM COLS;

WHERE TABLE_NAME = '테이블명';

SELECT *

FROM ALL_TAB_COLUMNS

WHERE TABLE_NAME = '테이블명';

SELECT *

FROM USER_TAB_COLUMNS;

-- 컬럼 코멘트 보기

SELECT *

FROM USER_COL_COMMENTS;

원인 : DB에 접속하는 커넥션 프로세서 수의 오버

> sqlplus "/as sysdba"

> ALTER system SET processes=1000 scope=spfile;

> shutdown immediate;

> startup

사용자 삭제

drop user 아이디 CASCADE;

사용자 생성

create user 아이디 identified by 비밀번호;

권한주기

grant connect,resource, DBA to 아이디;

grant CREATE DATABASE LINK, CREATE PUBLIC DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW to 아이디;

select
    machine "hostname",
    username "username",
    osuser "OS User",
    program "Program",
    sid||','||serial# "ID",
    last_call_et "Last Activity",
    status
from v$session
where
    username is not null
order by status desc, last_call_et desc;

select
    b.machine "hostname",
    b.username "username",
    b.osuser "OS User",
    b.program "Program",
    a.tablespace_name "TableSpace",
    row_wait_file# "File Number",
    row_wait_block# "Block Number",
    c.owner,
    c.segment_name,
    c.segment_type
from
    dba_data_files a,
    v$session b,
    dba_extents c
where
    b.row_wait_file# = a.file_id
    and c.file_id = row_wait_file#
    and row_wait_block# between c.block_id and c.block_id + c.blocks -1
    and row_wait_file# <> 0
    and type = 'USER'

CREATE OR REPLACE FUNCTION UHSOFT.F_MD5_CRIPT( V_PASSWORD VARCHAR2 )
RETURN sys.dbms_obfuscation_toolkit.varchar2_checksum AS
BEGIN
    RETURN LOWER(RAWTOHEX(UTL_RAW.CAST_TO_RAW(sys.dbms_obfuscation_toolkit.md5( input_string => V_PASSWORD ))));
END;

ORA-01940: 현재 접속되어 있는 사용자는 삭제할 수 없습니다

사용자 확인

select sid,serial#,username,status from v$session where schemaname = '사용자아이디(대문자)';

프로세스 죽이기

alter system kill session 'sid,serial#';

SELECT
   TRUNC(EndDate - StartDate) as "일",
      TRUNC(((EndDate - StartDate) - TRUNC(EndDate - StartDate)) * 24) as "시간",
     FLOOR(((((EndDate - StartDate) -TRUNC(EndDate - StartDate)) * 24)
      - TRUNC(((EndDate - StartDate)-TRUNC(EndDate - StartDate)) * 24)) * 60) as "분"
FROM (              
   SELECT TO_DATE('2013-08-07','yyyy-mm-dd') AS STARTDATE,  -- 시작일
        TO_DATE('2013-12-30','yyyy-mm-dd') AS ENDDATE    -- 종료일
  FROM DUAL
);

select
    b.machine "hostname",
    b.username "username",
    b.osuser "OS User",
    b.program "Program",
    a.tablespace_name "TableSpace",
    row_wait_file# "File Number",
    row_wait_block# "Block Number",
    c.owner,
    c.segment_name,
    c.segment_type
from
    dba_data_files a,
    v$session b,
    dba_extents c
where
    b.row_wait_file# = a.file_id
    and c.file_id = row_wait_file#
    and row_wait_block# between c.block_id and c.block_id + c.blocks -1
    and row_wait_file# <> 0
    and type = 'USER'

테이블 삭제

Begin
for c in (select table_name from user_tables) loop
  execute immediate 'drop table '||c.table_name||' cascade constraints';
end loop;
End;

시퀀스 삭제

Begin
for c in (SELECT * FROM all_sequences WHERE SEQUENCE_OWNER='DBUSER') loop
  execute immediate 'drop SEQUENCE '||c.sequence_name;
end loop;
End;

declare
  ret VARCHAR2(30);
BEGIN
  GET_DOCUMENT_CD_P(ret, 'TBL_MEMO');
  dbms_output.put_line('ret = ' || ret);
END;