DDL (Data Definition Language) CREATE, ALTER, DROP
1. TABLESPACE 관리
1) TABLESPACE 생성
CREATE TABLESPACE tablespace_name
DATAFILE '경로'
SIZE 100M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 2M -- 유니폼 사이즈를 주면 STORAGE 옵션은 의미없다
SEGMENT SPACE MANAGEMENT AUTO;
2) DATAFILE 추가
ALTER TABLESPACE tablespace_name
ADD DATAFILE '경로' ;
3) DATAFILE 삭제
ALTER TABLESPACE tablespace_name
DROP DATAFILE '경로' ;
4) DATAFILE 자동 증가
ALTER DATABASE
DATAFILE '경로' AUTOEXTEND ON;
5) DATAFILE 사이즈 증설
ALTER DATABASE
DATAFILE '경로' RESIZE 100M ;
6) DATAFILE 명 변경
ALTER DATABASE RENAME
FILE '경로'
TO '경로' ;
7) TABLESPACE 삭제
DROP TABLESPACE tablespace_name (including contents) ; --데이터가 있어도 삭제
2. USER 관리
1) USER 생성
CREATE USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp ;
2) 권한 부여
GRANT RESOURCE, CONNECT TO user_name;
GRANT DBA TO user_name;
GRANT SELECT ON schema.table_name TO user_name ;
INSERT
UPDATE
3) USER 변경
ALTER USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
TEMPORARY TABLESPACE temp ;
4) USER 잠금 해제
ALTER USER user_name ACCOUNT UNLOCK ;
5) USER 삭제
DROP USER user_name (CASCADE) ;
3. TABLE 관리
1) TABLE 생성
CREATE TABLE table_name
(
column, datatype(length),
column, datatype(length)
)
TABLESPACE tablespace_name
STORAGE
(
INITIAL 1M
NEXT 1M
);
constraint
2) TABLE 컬럼 추가
ALTER TABLE table_name
ADD column_name datatype;
3) TABLE 컬럼 삭제
ALTER TABLE table_name
DROP COLUMN column_name;
4) TABLE DATATYPE 변경
ALTER TABLE table_name
MODIFY column_name datatype(value); -- 데이터타입 길이 변경
5) TABLE 이름 변경
RENAME table_name TO table_name
6) TABLE 삭제
DROP TABLE table_name (PURGE) ;
4. INDEX 관리
1) INDEX 생성
CREATE INDEX index_name
ON table_name (column_name)
TABLESPACE tablespace_name
STORAGE(initial 1M, next 1M) ;
2) 데이터 양이 많을 경우 PARALLEL 로 만든 후
create table abc (column_name, data_type) ;
create unique index abc_idx on abc (abc) ;
alter table abc add primary key abc_pk ;
DML (Data Manipulation Language) INSERT, UPDATE, DELETE
INSERT INTO table_name (column_name, column_name) VALUES (value, value) ;
생략 가능
UPDATE table_name SET column_name = value WHERE 조건 ;
DELETE FROM table_name WHERE 조건 ;
VIEW
SYNONYM
DBLINK
PROCEDURE
PACKAGE
TRIGGER
FUNCTION
listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
tnsnames.ora
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)