계정생성..

관리 툴 없이 TABLESPACE부터 생성하려니 몇 번의 명령어 오류를 경험하게 되었다.

CREATE TABLESPACE TS_DEV DATAFILE 'D:\TOOLS\ORACLESTD1\ORADATA\ORA1\TS_DEV01.DBF' SIZE 500M AUTOEXTEND ON NEXT 100M;

CREATE TABLESPACE IX_DEV DATAFILE 'D:\TOOLS\ORACLESTD1\ORADATA\ORA1\IX_DEV01.DBF' SIZE 200M AUTOEXTEND ON NEXT 100M;

CREATE USER DEV IDENTIFIED BY ****** DEFAULT TABLESPACE TS_DEV TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON TS_DEV;

GRANT CONNECT,RESOURCE,DBA TO DEV;

CREATE TABLE DEV.TEST01 (
	USER_ID VARCHAR2(30)
) ;

INSERT INTO TEST01 SELECT 'USER'||LPAD(CEIL(DBMS_RANDOM.VALUE(1,1000)),4,'0') FROM DUAL CONNECT BY LEVEL <= 500;

SELECT * FROM TEST01;

CREATE OR REPLACE FORCE VIEW V_TEST01 AS SELECT * FROM TEST01 WHERE MOD(TO_NUMBER(SUBSTR(USER_ID,5)),2) = 0;

SELECT * FROM V_TEST01;