DataBase
[Oracle] 오라클 명령어
izen8
2011. 10. 13. 10:47
반응형
자주 사용하는 명령어 정리
-- 테이블 명 변경
ALTER TABLE schema.table_name RENAME TO new_table_name;
-- 테이블 로그 관련
ALTER TABLE schema.table_name LOGGING;
ALTER TABLE schema.table_name NOLOGGING;
-- 컬럼 추가
ALTER TABLE schema.table_name ADD (column_properties);
-- 컬럼 속성 변경
ALTER TABLE schema.table_name MODIFY (column_properties);
-- 컬럼 삭제
ALTER TABLE schema.table_name DROP COLUMN column_name;
-- 컬럼명 변경
ALTER TABLE schema.table_name RENAME COLUMN old_name TO new_name;
-- 제약 조건명 변경
ALTER TABLE schema.table_name RENAME CONSTRAINT old_name TO new_name;
-- 제약 조건 삭제
ALTER TABLE schema.table_name DROP CONSTRAINT constraint_name;
-- 테이블의 테이블스페이스 변경
ALTER TABLE schema.table_name MOVE TABLESPACE tablespace_name;
-- 테이블의 통계정보 업데이트
ANALYZE TABLE table_name COMPUTE STATISTICS;
ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 30 PERCENT;
ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 1000 ROWS;
-- 테이블의 통계 락 해제
EXECUTE DBMS_STATS.UNLOCK_SCHEMA_STATS('schema_name');
-- 스키마 소유의 오브젝트에 대한 통계 생성
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('schema_name', 'COMPUTE');
-- PK 추가
CREATE UNIQUE INDEX schema.index_name ON schema.table_name (columns);
ALTER TABLE schema.table_name ADD CONSTRAINT constraint_name PRIMARY KEY (columns);
-- 인덱스명 변경
ALTER INDEX schema.old_index_name RENAME TO new_index_name;
-- 인덱스의 테이블스페이스 변경
ALTER INDEX schema.index_name REBUILD TABLESPACE tablespace_name;
-- 테이블스페이스의 데이터 파일 이동 및 이름변경
ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
OS COPY: data file
ALTER TABLESPACE tablepsace_name RENAME DATAFILE 'diskb:data_file.dat' TO 'diska:data_file.dat';
ALTER TABLESPACE tablespace_name ONLINE;
-- SYNONYM 생성
CREATE SYNONYM schema.synonym_name FOR schema.object_name;
CREATE SYNONYM schema.synonym_name FOR schema.object_name@db_link_name;
-- COMMENT 생성
COMMENT ON TABLE schema.table_name IS 'comment_text';
COMMENT ON COLUMN schema.table_name.column_name IS 'comment_text';
-- DB LINK 생성
CREATE /* PUBLIC */ DATABASE LINK db_link_name
CONNECT TO user_id IDENTIFIED BY password USING 'tns_name';
-- 유저 생성
CREATE USER TEST IDENTIFIED BY TEST
-- 권한 주기
GRANT connect, resource TO TEST
*설명 : TEST 사용자에게 CREATE SESSION 권한을 부여
GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION
*설명 : scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고,
scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게 되며,
만약 사용자가 WITH ADMIN OPTION과 같이 역할을 부여 받는다면 부여된 역할은 그 사용자에 의해 변경 또는 삭제 될 수 있습니다.
WITH ADMIN OPTION을 사용하여 시스템 권한 취소
WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했어도 시스템 권한을 취소 할 때는 연쇄적으로 취소 되지 않습니다.
시나리오
1. DBA가 STORM에게 WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템 권한을 부여 합니다.
2. STORM이 테이블을 생성 합니다.
3. STORM이 CREATE TABLE 시스템 권한을 SCOTT에게 부여 합니다.
4. SCOTT가 테이블을 생성 합니다.
5. DBA가 STORM에게 부여한 CREATE TABLE 시스템 권한을 취소 합니다.
결과
- STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없습니다.
- SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있습니다.
-- 권한 제거
REVOKE CREATE USER, ALTER USER, DROP USER FROM scott
-- 비밀번호 변경
ALTER USER scott IDENTIFIED BY lion
1. Shared Pool의 오버플로 문제의 임시 해결법
ALTER SYSTEM FLUSH SHARED_POOL
2. 테이블스페이스를 ALTER한다.
ALTER TABLESPACE EBPP ADD DATAFILE
'/dev2/oracle/oradata/ORA8/test02.dbf' SIZE 50M
AUTOEXTEND ON NEXT 1M;
3. 테이블 생성
CREATE TABLE IMSI (name NUMBER(3), age DATE);
4. 테이블 변경(추가,변경,컬럼삭제)
ALTER TABLE IMSI ADD(tel VARCHAR2(14));
ALTER TABLE IMSI MODIFY tel VARCHAR2(14) NOT NULL;
ALTER TABLE IMSI DROP COLUMN tel;
5. 테이블 변경(수정, PRIMARY KEY로 속성 부여)
ALTER TABLE IMSI ADD CONSTRAINT IMSI_PK PRIMARY KEY(name);
6. 테이블 변경(수정, CHECK 속성 부여)
ALTER TABLE IMSI ADD CONSTRAINT IMSI_CK CHECK(pay BETWEEN 1000 AND 5000);
7. 테이블 변경(수정, DISABLE설정)
ALTER TABLE IMSI DISABLE CONSTRAINT IMSI_CK;
8. 테이블 변경(수정, ENABLE설정)
ALTER TABLE IMSI ENABLE CONSTRAINT IMSI_CK;
9. 테이블 변경(삭제, CONSTRAINT 삭제)
ALTER TABLE IMSI DROP CONSTRAINT IMSI_CK;
10. TEST_LK라는 이름으로 SCOTT/TIGER 로 링크 만들기
CREATE PUBLIC DATABASE LINK TEST_LK
CONNECT TO SCOTT IDENTIFIED BY TIGER USING '[TNS NAME]';
--링크 삭제
DROP PUBLIC DATABASE LINK TEST_LK;
11. global name을 사용하지 않기 위해 다음과 같이 설정한다.
ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
12. 인덱스 생성
CREATE INDEX IDX_T_PERF_F_INT ON T_PERF(F_INT,F_VARCHAR2)
TABLESPACE PERF PCTFREE 5
STORAGE (
INITIAL 30M
NEXT 5M
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 10)
13. 외부 c라이브러리 콜을 위한 library를 생성한다.
CREATE OR REPLACE LIBRARY libname IS '/dev1/ohbs/lib/libname.so';
14. 롤백 세그먼트 생성
CREATE PUBLIC(공용) ROLLBACK SEGMENT 세그먼트명 TABLESPACE 테이블스페이스명
STORAGE
( INITIAL 50M
NEXT 1M
MINEXTENTS 2
MAXEXTENTS UNLIMITED )
ALTER ROLLBACK SEGMENT 세그먼트명 ONLINE
15. 시퀀스를 생성한다.
CREATE SEQUENCE 시퀀스명
INCREMENT BY 1 ==> 증가치
START WITH 1 MAXVALUE 99999999999 MINVALUE 1 ==> 시작값과 최대값, 최소값
NOCYCLE NOORDER ==> 사이클, 순서
CACHE 20; ==> 캐쉬지정
16. 시노님을 생성한다.
CREATE SYSNONYM getTable
FOR aaa@bbb@ccc;
17. 테이블스페이스를 생성한다.
CREATE TABLESPACE ohbs
DATAFILE '/www/oracle/data1/oradata/ORA8/ohbs01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M
DEFAULT STORAGE (
INITIAL 5M
NEXT 1M
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0) ONLINE(OFFLILE) PERMANENT(TEMPORARY)
18. 오라클 유저 생성 및 수정
CREATE USER OHBS IDENTIFIED BY QUICK
DEFAULT TABLESPACE OHBS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO OHBS;
GRANT RESOURCE TO OHBS;
ALTER USER OHBS IDENTIFIED BY QUICK
DEFAULT TABLESPACE OHBS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
19. TEMP 테이블 스페이스 생성
CREATE TEMPORARY TABLESPACE "TEMP01"
TEMPFILE '/user1/oracle/app/oracle/oradata/I3SHOPDW/TEMP01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP01"
20. 오브젝트를 grant한다.
GRANT EXECUTE(GRANT할 권한) ON SYS.UTL_FILE(오브젝트) TO EBPP(오라클유저);
(WITH GRANT OPTION ==> GRANT 업션을 줄경우)
GRANT DELETE(GRANT할 권한) ON EBPP.TEST(오브젝트) TO EBPP(오라클유저);
(WITH GRANT OPTION ==> GRANT 옵션을 줄경우)
GRANT CREATE LIBRARY TO EBPP
21. 오브젝트를 revoke한다.
REVOKE EXECUTE(REVOKE할 권한) ON SYS.UTL_FILE(오브젝트) FROM EBPP(오라클유저);
ALTER TABLE schema.table_name RENAME TO new_table_name;
-- 테이블 로그 관련
ALTER TABLE schema.table_name LOGGING;
ALTER TABLE schema.table_name NOLOGGING;
-- 컬럼 추가
ALTER TABLE schema.table_name ADD (column_properties);
-- 컬럼 속성 변경
ALTER TABLE schema.table_name MODIFY (column_properties);
-- 컬럼 삭제
ALTER TABLE schema.table_name DROP COLUMN column_name;
-- 컬럼명 변경
ALTER TABLE schema.table_name RENAME COLUMN old_name TO new_name;
-- 제약 조건명 변경
ALTER TABLE schema.table_name RENAME CONSTRAINT old_name TO new_name;
-- 제약 조건 삭제
ALTER TABLE schema.table_name DROP CONSTRAINT constraint_name;
-- 테이블의 테이블스페이스 변경
ALTER TABLE schema.table_name MOVE TABLESPACE tablespace_name;
-- 테이블의 통계정보 업데이트
ANALYZE TABLE table_name COMPUTE STATISTICS;
ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 30 PERCENT;
ANALYZE TABLE table_name ESTIMATE STATISTICS SAMPLE 1000 ROWS;
-- 테이블의 통계 락 해제
EXECUTE DBMS_STATS.UNLOCK_SCHEMA_STATS('schema_name');
-- 스키마 소유의 오브젝트에 대한 통계 생성
EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('schema_name', 'COMPUTE');
-- PK 추가
CREATE UNIQUE INDEX schema.index_name ON schema.table_name (columns);
ALTER TABLE schema.table_name ADD CONSTRAINT constraint_name PRIMARY KEY (columns);
-- 인덱스명 변경
ALTER INDEX schema.old_index_name RENAME TO new_index_name;
-- 인덱스의 테이블스페이스 변경
ALTER INDEX schema.index_name REBUILD TABLESPACE tablespace_name;
-- 테이블스페이스의 데이터 파일 이동 및 이름변경
ALTER TABLESPACE tablespace_name OFFLINE NORMAL;
OS COPY: data file
ALTER TABLESPACE tablepsace_name RENAME DATAFILE 'diskb:data_file.dat' TO 'diska:data_file.dat';
ALTER TABLESPACE tablespace_name ONLINE;
-- SYNONYM 생성
CREATE SYNONYM schema.synonym_name FOR schema.object_name;
CREATE SYNONYM schema.synonym_name FOR schema.object_name@db_link_name;
-- COMMENT 생성
COMMENT ON TABLE schema.table_name IS 'comment_text';
COMMENT ON COLUMN schema.table_name.column_name IS 'comment_text';
-- DB LINK 생성
CREATE /* PUBLIC */ DATABASE LINK db_link_name
CONNECT TO user_id IDENTIFIED BY password USING 'tns_name';
-- 모든 유저 보기
SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS;
CREATE USER TEST IDENTIFIED BY TEST
-- 권한 주기
GRANT connect, resource TO TEST
*설명 : TEST 사용자에게 CREATE SESSION 권한을 부여
GRANT CREATE USER, ALTER USER, DROP USER TO scott WITH ADMIN OPTION
*설명 : scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고,
scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있습니다.
WITH ADMIN OPTION : 권한을 부여 받은 사용자도 부여 받은 권한을 다른 사용자 또는 역할로 부여할 수 있게 되며,
만약 사용자가 WITH ADMIN OPTION과 같이 역할을 부여 받는다면 부여된 역할은 그 사용자에 의해 변경 또는 삭제 될 수 있습니다.
WITH ADMIN OPTION을 사용하여 시스템 권한 취소
WITH ADMIN OPTION을 사용하여 시스템 권한을 부여했어도 시스템 권한을 취소 할 때는 연쇄적으로 취소 되지 않습니다.
시나리오
1. DBA가 STORM에게 WITH ADMIN OPTION을 사용하여 CREATE TABLE 시스템 권한을 부여 합니다.
2. STORM이 테이블을 생성 합니다.
3. STORM이 CREATE TABLE 시스템 권한을 SCOTT에게 부여 합니다.
4. SCOTT가 테이블을 생성 합니다.
5. DBA가 STORM에게 부여한 CREATE TABLE 시스템 권한을 취소 합니다.
결과
- STORM의 테이블은 여전히 존재하지만 새 테이블을 생성할 수 있는 권한은 없습니다.
- SCOTT는 여전히 테이블과 새로운 테이블을 생성 할 수 있는 CREATE TABLE권한을 가지고 있습니다.
-- 권한 제거
REVOKE CREATE USER, ALTER USER, DROP USER FROM scott
-- 비밀번호 변경
ALTER USER scott IDENTIFIED BY lion
1. Shared Pool의 오버플로 문제의 임시 해결법
ALTER SYSTEM FLUSH SHARED_POOL
2. 테이블스페이스를 ALTER한다.
ALTER TABLESPACE EBPP ADD DATAFILE
'/dev2/oracle/oradata/ORA8/test02.dbf' SIZE 50M
AUTOEXTEND ON NEXT 1M;
3. 테이블 생성
CREATE TABLE IMSI (name NUMBER(3), age DATE);
4. 테이블 변경(추가,변경,컬럼삭제)
ALTER TABLE IMSI ADD(tel VARCHAR2(14));
ALTER TABLE IMSI MODIFY tel VARCHAR2(14) NOT NULL;
ALTER TABLE IMSI DROP COLUMN tel;
5. 테이블 변경(수정, PRIMARY KEY로 속성 부여)
ALTER TABLE IMSI ADD CONSTRAINT IMSI_PK PRIMARY KEY(name);
6. 테이블 변경(수정, CHECK 속성 부여)
ALTER TABLE IMSI ADD CONSTRAINT IMSI_CK CHECK(pay BETWEEN 1000 AND 5000);
7. 테이블 변경(수정, DISABLE설정)
ALTER TABLE IMSI DISABLE CONSTRAINT IMSI_CK;
8. 테이블 변경(수정, ENABLE설정)
ALTER TABLE IMSI ENABLE CONSTRAINT IMSI_CK;
9. 테이블 변경(삭제, CONSTRAINT 삭제)
ALTER TABLE IMSI DROP CONSTRAINT IMSI_CK;
10. TEST_LK라는 이름으로 SCOTT/TIGER 로 링크 만들기
CREATE PUBLIC DATABASE LINK TEST_LK
CONNECT TO SCOTT IDENTIFIED BY TIGER USING '[TNS NAME]';
--링크 삭제
DROP PUBLIC DATABASE LINK TEST_LK;
11. global name을 사용하지 않기 위해 다음과 같이 설정한다.
ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
12. 인덱스 생성
CREATE INDEX IDX_T_PERF_F_INT ON T_PERF(F_INT,F_VARCHAR2)
TABLESPACE PERF PCTFREE 5
STORAGE (
INITIAL 30M
NEXT 5M
MINEXTENTS 2
MAXEXTENTS UNLIMITED
PCTINCREASE 10)
13. 외부 c라이브러리 콜을 위한 library를 생성한다.
CREATE OR REPLACE LIBRARY libname IS '/dev1/ohbs/lib/libname.so';
14. 롤백 세그먼트 생성
CREATE PUBLIC(공용) ROLLBACK SEGMENT 세그먼트명 TABLESPACE 테이블스페이스명
STORAGE
( INITIAL 50M
NEXT 1M
MINEXTENTS 2
MAXEXTENTS UNLIMITED )
ALTER ROLLBACK SEGMENT 세그먼트명 ONLINE
15. 시퀀스를 생성한다.
CREATE SEQUENCE 시퀀스명
INCREMENT BY 1 ==> 증가치
START WITH 1 MAXVALUE 99999999999 MINVALUE 1 ==> 시작값과 최대값, 최소값
NOCYCLE NOORDER ==> 사이클, 순서
CACHE 20; ==> 캐쉬지정
16. 시노님을 생성한다.
CREATE SYSNONYM getTable
FOR aaa@bbb@ccc;
17. 테이블스페이스를 생성한다.
CREATE TABLESPACE ohbs
DATAFILE '/www/oracle/data1/oradata/ORA8/ohbs01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M
DEFAULT STORAGE (
INITIAL 5M
NEXT 1M
MINEXTENTS 2
MAXEXTENTS 50
PCTINCREASE 0) ONLINE(OFFLILE) PERMANENT(TEMPORARY)
18. 오라클 유저 생성 및 수정
CREATE USER OHBS IDENTIFIED BY QUICK
DEFAULT TABLESPACE OHBS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO OHBS;
GRANT RESOURCE TO OHBS;
ALTER USER OHBS IDENTIFIED BY QUICK
DEFAULT TABLESPACE OHBS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
19. TEMP 테이블 스페이스 생성
CREATE TEMPORARY TABLESPACE "TEMP01"
TEMPFILE '/user1/oracle/app/oracle/oradata/I3SHOPDW/TEMP01.dbf' SIZE 100M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP01"
20. 오브젝트를 grant한다.
GRANT EXECUTE(GRANT할 권한) ON SYS.UTL_FILE(오브젝트) TO EBPP(오라클유저);
(WITH GRANT OPTION ==> GRANT 업션을 줄경우)
GRANT DELETE(GRANT할 권한) ON EBPP.TEST(오브젝트) TO EBPP(오라클유저);
(WITH GRANT OPTION ==> GRANT 옵션을 줄경우)
GRANT CREATE LIBRARY TO EBPP
21. 오브젝트를 revoke한다.
REVOKE EXECUTE(REVOKE할 권한) ON SYS.UTL_FILE(오브젝트) FROM EBPP(오라클유저);
반응형