HooNim

--1. 해당 데이터파일의 크기를 재조정 
	ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\KIRIO1\A1_CLON\A1.DBF' RESIZE 1100M; 
--2. 해당 테이블 스페이스에 새로운 데이터파일 추가 
	ALTER TABLESPACE DATA ADD DATAFILE 'd:\oracle\oradata\kirio1\data01.dbf' SIZE 100M; 
--3. 해당 데이터파일에 AUTOEXTEND ON 설정 
	ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\KIRIO1\A1_CLON\A1.DBF' AUTOEXTEND ON; 

-- 테이블스페이스 삭제
-- 테이블스페이스가 오프라인으로 상태에서 명령을 내리길 권장
DROP TABLESPACE unicorn_temp
INCLUDING CONTENTS CASCADE CONSTRAINTS;

-- DATA TABLESPACE 생성
CREATE TABLESPACE unicorn_data
DATAFILE 'D:\oracle\oradata\unicorn_data.TBL' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
DEFAULT STORAGE
(INITIAL 4K
NEXT 128K
MINEXTENTS 1
PCTINCREASE 0);

-- INDEX TABLESPACE 생성
CREATE TABLESPACE unicorn_index
DATAFILE 'D:\oracle\oradata\unicorn_index.TBL' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
DEFAULT STORAGE
(INITIAL 4K
NEXT 128K
MINEXTENTS 1
PCTINCREASE 0);

-- TEMP TALBESPACE 생성
CREATE TEMPORARY TABLESPACE unicorn_temp
TEMPFILE 'D:\oracle\oradata\unicorn_temp.TBL' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;


-- 사용자 생성 
CREATE USER unicorn IDENTIFIED BY unicorn
DEFAULT TABLESPACE unicorn_data
TEMPORARY TABLESPACE unicorn_temp;

-- 사용자 비밀번호 변경
ALTER USER unicorn IDENTIFIED BY unicorn;

-- 권한 설정
-- 생성한 사용자에게 자신의 schema에서 테이블등을 만들 권한과 자원을 사용할 권한을 준다.
-- GRANT RESOURCE, CONNECT TO MIDAN; 
-- DBA 권한을 준다.
GRANT DBA TO unicorn;

-- datafile 추가
ALTER TABLESPACE unicorn_data
ADD DATAFILE 'D:\oracle\oradata\unicorn\unicorn_dat02.dbf' SIZE 2048M;
/*
alter tablespace unicorn_data
add datafile 'D:\oracle\oradata\unicorn\unicorn_dat02.dbf' size 50M
autoextend on next 20M maxsize 100M 

추후에 datafile에 자동증가만 추가하려면
alter database datafile 'D:\oracle\oradata\unicorn\unicorn_dat02.dbf' 
       autoextend on next 20M maxsize 100M 
계속 증가하도록 하려면 100M 대신 unlimited 사용 (32G이상 확장되지 않음) 
*/

-- datafile 확인
select * from DBA_DATA_FILES;

-- 등록되어 있는 사용자보기
SELECT * FROM ALL_USERS;


-- 사용자 삭제
-- ※ CASCADE를 사용하게 되면 사용자 이름과 관련된 모든 데이터베이스 스키마가 데이터 사전으로부터 
-- 삭제되며 모든 스키마 객체들 또한 물리적으로 삭제 됩니다. 
DROP USER scott CASCADE;


--IMPORT, EXPORT 권한을 준다.
GRANT EXP_FULL_DATABASE, IMP_FULL_DATABASE TO unicorn;

/*
--시스템 권한 부여
GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION.
--*설명 : scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고,
-- scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.

--시스템 권한 박탈
REVOKE CREATE USER, ALTER USER, DROP USER FROM scott
--*설명 : scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수합니다,
*/

--사용자조회
SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS;
SELECT * FROM ALL_USERS;

-- 테이블스페이스조회
SELECT * FROM DBA_TABLESPACES; 


% 그외 TIP



-------------------------------------------------------------------------------------
※ ORACLE SID 확인

SELECT INSTANCE
FROM V$THREAD;
-------------------------------------------------------------------------------------
※ ORACLE DB_NAME 확인

SELECT NAME 
FROM V$DATABASE;
-------------------------------------------------------------------------------------
※ ORACLE VERSION 확인

SELECT *
FROM V$VERSION;
-------------------------------------------------------------------------------------
※ ORACLE USER 확인

SELECT * 
FROM ALL_USERS;
-------------------------------------------------------------------------------------
※ 등록된 USER 목록 보기

SELECT USERNAME, USER_ID 
FROM DBA_USERS 
ORDER BY 
USERNAME;
-------------------------------------------------------------------------------------
※ USER가 소유한 모든 테이블 보기

SELECT TABLE_NAME 
FROM USER_TABLES;
-------------------------------------------------------------------------------------
※ 사용자 정보 

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE 
FROM DBA_USERS;
-------------------------------------------------------------------------------------
※ 오브젝트 조회

SELECT * 
FROM ALL_OBJECTS 
WHERE OBJECT_NAME LIKE '명';
-------------------------------------------------------------------------------------
※ 테이블 조회

SELECT * 
FROM ALL_TABLES 
WHERE TABLE_NAME LIKE '명';
-------------------------------------------------------------------------------------
※ 시퀀스 정보 

SELECT * 
FROM USER_SEQUENCES;
-------------------------------------------------------------------------------------
※ 시노님 조회

SELECT * 
FROM ALL_SYNONYMS 
WHERE SYNONYM_NAME='명';
-------------------------------------------------------------------------------------
※ 테이블 인덱스 정보 조회

SELECT * 
FROM ALL_IND_COLUMNS 
WHERE TABLE_NAME='테이블명';
-------------------------------------------------------------------------------------
※ 테이블의 컬럼 정보 조회

SELECT * 
FROM ALL_TAB_COLUMNS 
WHERE TABLE_NAME='테이블명';
-------------------------------------------------------------------------------------
※ TABLE COMMENT 쿼리

SELECT * 
FROM ALL_TAB_COMMENTS 
WHERE TABLE_NAME='테이블명';
-------------------------------------------------------------------------------------
※ COLUMN COMMENT 쿼리

SELECT * 
FROM ALL_COL_COMMENTS 
WHERE TABLE_NAME='테이블명';
-------------------------------------------------------------------------------------
※ 콘트롤파일의 데이타파일 정보와 테이블스페이스 정보 

SELECT status,enabled, t.name,d.name 
FROM v$datafile d, v$tablespace t 
WHERE t.ts#=d.ts#;
-------------------------------------------------------------------------------------
※ 테이블스페이스의 데이터파일과 테이블스페이스의 크기 확인
※ DBA_DATA_FILES 데이터 사전을 이용 하면 됩니다. 

SELECT file_name, tablespace_name, bytes, blocks, status, user_bytes, user_blocks
FROM DBA_DATA_FILES;
-------------------------------------------------------------------------------------
※ 테이블 스페이스별 사용 가능한 공간의 확인
※ DBA_FREE_SPACE 데이터 사전
※ SUM을 사용한 이유는하나의 테이블 스페이스에 분산되어 있는 여유공간을 합한 것이며
※ MAX를 사용한 이유는 여유 공간중 가장 큰 공간의 SIZE를 의미 합니다.

SELECT tablespace_name, SUM(bytes), MAX(bytes) 
FROM DBA_FREE_SPACE 
GROUP BY tablespace_name;
-------------------------------------------------------------------------------------
※데이타 화일에 대한 총 크기와 남아있는 공간, 사용한 용량, 남은 %율
※DBA_FREE_SPACE, DBA_DATA_FILES 데이터 사전 

SELECT b.file_name "FILE_NAME", -- DataFile Name 
b.tablespace_name "TABLESPACE_NAME", -- TableSpace Name 
b.bytes / 1024 "TOTAL SIZE(KB)", -- 총 Bytes 
((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)", -- 사용한 용량 
(sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)", -- 남은 용량 
ROUND((sum(nvl(a.bytes,0)) / (b.bytes)) * 100, 2) "FREE %" -- 남은 % 
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b 
WHERE a.file_id(+) = b.file_id 
GROUP BY b.tablespace_name, b.file_name, b.bytes ORDER BY b.tablespace_name
-------------------------------------------------------------------------------------
※오라클 권한 준거 확인 (테이블 권한)
SELECT *
FROM USER_TAB_PRIVS;
-------------------------------------------------------------------------------------
※오라클 VIEW Table 조회
SELECT OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OWNER = 'NIS'
AND OBJECT_TYPE = 'VIEW';

SELECT *
FROM TAB;
-------------------------------------------------------------------------------------
※오라클 비밀번호 강제 변경

sqlplus "/as sysdba"

ALTER USER 아이디 IDENTIFIED BY 암호;

-------------------------------------------------------------------------------------
※락 잡힌거 죽이기

--락확인 법
SELECT A.SESSION_ID SID,
B.SERIAL# SERIAL_NO,
A.OS_USER_NAME OS_USER_NAME,
A.ORACLE_USERNAME ORACLE_USERNAME,
B.STATUS STATUS
FROM V$LOCKED_OBJECT A, V$SESSION B
WHERE A.SESSION_ID = B.SID;

SELECT *
FROM V$LOCKED_OBJECT;

ALTER SYSTEM KILL SESSION 'SID, SERIAL_ID';

-------------------------------------------------------------------------------------
권한주기
GRANT SELECT,DELETE,UPDATE ON SMSVNJNVIEW TO SMSVIEW WITH GRANT OPTION;

-------------------------------------------------------------------------------------
아이디생성
CREATE USER SMSVIEW PROFILE DEFAULT IDENTIFIED BY SMSVIEW;

GRANT CONNECT, RESOURCE TO SMSVIEW;
-------------------------------------------------------------------------------------
시노님 생성

CREATE PUBLIC SYNONYM SMSVNJNVIEW FOR NIS.SMSVNJNVIEW;
-------------------------------------------------------------------------------------
디비링크 거는법

1. tnsnames.ora 파일 정보추가

tns =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
)
(CONNECT_DATA =
(SID = ora92)
)
)

2. 연결

CREATE DATABASE LINK 링크명
CONNECT TO 아이디
IDENTIFIED BY 비번
USING 'tns'
-------------------------------------------------------------------------------------
pk 생성

ALTER TABLE 테이블 ADD (
CONSTRAINT pk명 PRIMARY KEY (컬럼, 컬럼));
-------------------------------------------------------------------------------------
인덱스 생성
CREATE INDEX 인덱스명 ON 테이블 (컬럼, 컬럼);

CREATE UNIQUE INDEX 인덱스명 ON 테이블 (컬럼, 컬럼);
------------------------------------------------------

-------------------------------
테이블 설명
COMMENT ON TABLE 테이블명 IS '부과대장';
COMMENT ON COLUMN 테이블명.컬럼명 IS '부과대장키 ';
-------------------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER 트리거명
	AFTER INSERT OR UPDATE OR DELETE OF 선택컬럼명 ON 테이블명
FOR EACH ROW
	BEGIN
		IF (INSERTING) THEN
			INSERT INTO 테이블(선택컬럼) VALUES(:NEW.선택컬럼);
		ELSIF (UPDATING) THEN
			UPDATE 테이블	SET 선택컬럼=:NEW.선택컬럼 WHERE 선택컬럼=:OLD.선택컬럼;
		ELSIF (DELETING) THEN
			DELETE FROM 테이블 WHERE 선택컬럼 = :OLD.선택컬럼;
		END IF;
	END;

SELECT	SUBSTR(
				WDATA, 
				INSTR(WDATA, '|', 1, LEVEL) + 1, 
				INSTR(WDATA, '|', 1, LEVEL + 1) - INSTR(WDATA, '|', 1, LEVEL) - 1
			) NAME 
FROM 	( 
		 	SELECT	'|aaa|bbb|ccc|ddd|eee|'  AS WDATA
			FROM	dual
		) 
CONNECT BY LEVEL <= LENGTH(WDATA) - LENGTH(REPLACE(WDATA, '|')) - 1 

exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure


-- 예제 1)
select * from 
OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;Database=절대경로;HDR=NO','select * from 파일명')

-- 예제 2)
select * from
OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=절대경로;','select * from 파일명')