본문 바로가기

DBMS/Oracle

 (6)
[Oracle] 오라클 테이블 & 컬럼 Comment Comment 설정 --테이블 Comment 설정 COMMENT ON TABLE [테이블명] IS [Comment]; COMMENT ON TABLE EX_TABLE IS '예제 테이블'; --컬럼 Comment 설정 COMMENT ON COLUMN [테이블명].[컬럼명] IS '[Comment]'; COMMENT ON COLUMN EX_TABLE.DEPT IS '부서'; COMMENT ON COLUMN EX_TABLE.TP IS '유형'; Comment 확인 --테이블 전체 comment 조회 SELECT table_name, table_type, comments FROM USER_TAB_COMMENTS WHERE comments IS NOT NULL; --컬럼 전체 comment 조회 SELECT ta..
오라클 character set 확인 및 변경 현재 설정된 캐릭터셋 확인 및 변경 방법 Character set 확인 select * from nls_database_parameters where parameter like '%CHAR%'; Character set 변경 SQL>Shutdown immediate; SQL>Startup Mount; SQL>Alter system enable restricted session; SQL>Alter system set JOB_QUEUE_PROCESSES=0; SQL>Alter system set AQ_TM_PROCESSES=0; SQL>Alter database Open; SQL>Alter database character set KO16MSWIN949; 주의 사항 KO16MSWIN949는 KO16KSC56..
ORA-12547: TNS:lost contact ORA-12547: TNS:lost contact When I tried to login into oracle I have received below error sapec4:ec4adm 61> sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 29 14:00:17 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> connect / as sysdba ERROR: ORA-12547: TNS:lost contact Here is the simple solution for that Solution1:- 1.Check “oracle:dba” should have ownership ..
테이블 스페이스 조회 테이블 스페이스별 목록 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,ma..
oracle dblink 생성 # 전제 조건 DB Link를 생성 권한이 있어야 함. # 링크 생성 CREATE PUBLIC DATABASE LINK LINK_NAME CONNECT TO IDENTIFIED BY USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) )' ; # 사용법 SELECT COUNT(*) FROM TABLE_NAME@DATA_LINK Trouble Shooting # 생성시 권한 오류 SYSDBA 계정으로 권한 추가 : PUBLIC 명시 여부 체크 -- PUBLIC DB LINK 생성 권한 SYS@ORACLE11> GR..
sqlplus 쿼리 일괄 수행 방법 SQL> @/home/data/import.sql

티스토리툴바