Oracle 사용자추가 부터 권한부여(테이블스페이스 지정포함)

출처 : http://ilus.tistory.com/93

-- 테이블스페이스 삭제 -- 테이블스페이스가 오프라인으로 상태에서 명령을 내리길 권장 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 '부과대장키 '; -------------------------------------------------------------------------------------