Oracle 관리용 SQL문

● 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;

● 테이블스페이스별 현황 확인 쿼리문(MB 단위)

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes  FROM  (            SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT            FROM DBA_DATA_FILES E, DBA_FREE_SPACE F            WHERE E.FILE_ID = F.FILE_ID            GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES            ) A;

● 테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)

SELECT  A.TABLESPACE_NAME,            A.FILE_NAME,            (A.BYTES - B.FREE)    AS USED_BYTES,            B.FREE                   AS FREE_BYTES,            A.BYTES                   AS TOTAL_BYTES,            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%'     AS FREE_PERCENT FROM            (             SELECT FILE_ID,                       TABLESPACE_NAME,                       FILE_NAME,                       SUBSTR(FILE_NAME,1,200) FILE_NM,                       SUM(BYTES) BYTES             FROM DBA_DATA_FILES             GROUP BY FILE_ID,TABLESPACE_NAME,FILE_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;

● 테이블 용량 조회

SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB FROM  (            SELECT segment_name table_name, owner, bytes            FROM dba_segments            WHERE segment_type in  ('TABLE','TABLE PARTITION')            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 in ('INDEX','INDEX PARTITION')            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 IN ('LOBSEGMENT','LOB PARTITION')            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

● 테이블스페이스 생성

CREATE TABLESPACE TS_TABLESPACE_NAME             DATAFILE '/data/oradata/TS_TABLESPACE_NAME07.DBF'             SIZE 500M            DEFAULT STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 80);

● 테이블스페이스 Data File 추가 확장

ALTER TABLESPACE TS_TABLESPACE_NAME           ADD DATAFILE '/data/oradata/TS_TABLESPACE_NAME07.DBF'           SIZE 500M;

● 테이블스페이스 삭제

DROP TABLESPACE TS_TABLESPACE_NAME INCLUDING CONTENTS AND DATAFILES;

● 사용자 계정 & 테이블스페이스 확인

select username, default_tablespace, temporary_tablespace from DBA_USERS;

● 사용자 계정 생성

CREATE USER [user_name]             IDENTIFIED BY [password]            DEFAULT TABLESPACE [tablespace_name]            TEMPORARY TABLESPACE TEMP            QUOTA UNLIMITED ON USERS;

● 사용자에 권한 주기

ALTER USER 유저명 QUOTA UNLIMITED ON 테이블스페이스; GRANT CONNECT, DBA, RESOURCE TO 유저명; (모든 권한 주기) GRANT CREATE SESSION TO 유저명;         // 데이터베이스에 접근할 수 있는 권한 GRANT CREATE DATABASE LINK TO 유저명; GRANT CREATE MATERIALIZED VIEW TO 유저명; GRANT CREATE PROCEDURE TO 유저명; GRANT CREATE PUBLIC SYNONYM TO 유저명; GRANT CREATE ROLE TO 유저명; GRANT CREATE SEQUENCE TO 유저명; GRANT CREATE SYNONYM TO 유저명; GRANT CREATE TABLE TO 유저명;             // 테이블을 생성할 수 있는 권한 GRANT DROP ANY TABLE TO 유저명;         // 테이블을 제거할 수 있는 권한 GRANT CREATE TRIGGER TO 유저명;  GRANT CREATE TYPE TO 유저명;  GRANT CREATE VIEW TO 유저명; GRANT IMP_FULL_DATABASE TO 유저명; GRANT EXP_FULL_DATABASE TO 유저명;

● 사용자 삭제하기

drop user 사용자계정 cascade;

사용자 drop 중 오류가 발생하면

SQL> DROP USER [USER_ID] CASCADE;  DROP USER ROVIET CASCADE  * ERROR at line 1: ORA-28014: cannot drop administrative users SQL> ALTER SESSION SET "_oracle_script" = true; Session altered. SQL> DROP USER [USER_ID] CASCADE; User dropped.

● sqlplus 컬럼 사이즈 & 라인 폭 지정

SQL> COLUMN COLUMN_NAME FORMAT A30; SQL> SET LINESIZE 300;

●CHARACTER SET  확인

select * from props$ where name in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

● IMPORT 시 오류 및 조치사항

# imp <ID>/<PWD> COMMIT=Y IGNORE=Y FROMUSER=<BACKUP_ID> BUFFER=10000000 FILE=<FILE_NAME> ==>  조치사항         IGNORE=Y : 경고 또는 오류 발생시 무시하고 작업 진행         BUFFER=10000000 : SQL문이 버퍼 길이를 초과, 익스포트 파일의 인식할 수 없는 명령문         COMMIT=Y : SQL문이 버퍼 길이를 초과, 익스포트 파일의 인식할 수 없는 명령문 SQL> ALTER USER  QUOTA UNLIMITED ON <TABLESPACE_NAME>;  ==> 조치사항         테이블스페이스 'USERS' 에 대한 권한이 없습니다.