I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

-------------------------------------------------------------------- -- 1. : ts_cre_bonsa.sql -- 2. : (TS001, IS001) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- SPOOL ts_cre_bonsa.log -- (TS001) CREATE TABLESPACE TS001 DATAFILE 'D:\TEST\DB1\DF001.DBF01' SIZE 55M DEFAULT STORAGE ( INITIAL 1024K NEXT 512K MAXEXTENTS 128 PCTINCREASE 0); CREATE TABLESPACE IS001 -- (IS001) DATAFILE 'D:\TEST\DB1\DF001.DBF01' SIZE 30M DEFAULT STORAGE ( INITIAL 512K NEXT 256K MAXEXTENTS 128 PCTINCREASE 0); SPOOL OFF

SQL> connect bonsa01/bonsa01@bonsa SQL> @ts_cre_bonsa.sql SQL>....

ROMPT =================================== PROMPT PROMPT =================================== COL TABLESPACE_NAME FORMAT A10 COL DATAFILE FORMAT A50 COL SZ_KB FORMAT 9,999,999 SELECT TABLESPACE_NAME, FILE_NAME AS DATAFILE, (BYTES/1024) AS SZ_KB FROM DBA_DATA_FILES ; TABLESPACE_NAME DATAFILE SZ_KB ----------------- ------------------------ --------... TS001 D:\TEST\DB1\DF101.DBF01 56,320 IS001 D:\TEST\DB1\DF001.DBF01 30,720 8.

-------------------------------------------------------------------- -- 1. : user_cre_bonsa.sql -- 2. : (BONSA01) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- SPOOL user_cre_bonsa.log CREATE USER BONSA01 IDENTIFIED BY BONSA01 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; SPOOL OFF

SQL> connect system/manager@bonsa SQL> @user_cre_bonsa.sql SQL>....

PROMPT =================================== PROMPT PROMPT =================================== COL USERNAME FORMAT A15 COL DEF_TBS FORMAT A15 COL TMP_TBS FORMAT A15 SELECT USERNAME,DEFAULT_TABLESPACE DEF_TBS, TEMPORARY_TABLESPACE TMP_TBS, PROFILE FROM DBA_USERS; SERNAME DEF_TBS TMP_TBS PROFILE --------- --------- --------- ---------... BONSA01 USERS TEMP DEFAULT

-------------------------------------------------------------------- -- 1. : role_cre_bonsa.sql -- 2. : (BONSA01) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- CREATE ROLE BONSA_ROLE NOT IDENTIFIED; GRANT CREATE SESSION, CREATE TABLE,CREATE VIEW, ALTER ROLLBACK SEGMENT TO BONSA_ROLE;

SQL> connect system/manager@bonsa SQL> @role_cre_bonsa.sql SQL>.....

PROMPT =================================== PROMPT PROMPT =================================== COL ROLE FORMAT A35 SELECT ROLE FROM DBA_ROLES; SELECT ROLE FROM DBA_ROLES; ROLE -----------------------------------... BONSA_ROLE 23.

-------------------------------------------------------------------- -- 1. : role_grant_jisa.sql -- 2. : (JISA01) -- 3. :200271 -- 4. : -------------------------------------------------------------------- GRANT JISA_ROLE TO JISA01;

SQL> connect system/manager@jisa SQL> @role_grant_jisa.sql SQL>....

PROMPT =================================== PROMPT PROMPT =================================== COL GRANTEE FORMAT A10 COL GRANTED_ROLE FORMAT A30 SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS; GRANTEE GRANTED_ROLE ---------- -------------------... BONSA01 BONSA_ROLE

------------------------------------------------------------------- -- 1. : tab_cre_bonsa.sql -- 2. : (BONSA01) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- SPOOL tab_cre_bonsa.log CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL, DEPTNM VARCHAR2(14) NOT NULL, LOC VARCHAR2(14) NULL, CHARGE VARCHAR2(20) NULL ) TABLESPACE TS001 STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0); ALTER TABLE DEPT ADD CONSTRAINT DEPT_PK PRIMARY KEY( DEPTNO) ; ALTER TABLE ORDER ADD CONSTRAINT ORDER_DELIVERY_FK01 FOREIGN KEY(DELIEVNO) REFERENCES DELIVERY ; SPOOL OFF

SQL> connect bonsa01/bonsa01@bonsa SQL> @tab_cre_bonsa.sql........?

PROMPT =================================== PROMPT PROMPT =================================== COL SEGMENT_NAME FORMAT A15 COL TABLESPACE_NAME FORMAT A10 SELECT SEGMENT_NAME, ROUND(INITIAL_EXTENT/1024,0) INI_KB, ROUND(NEXT_EXTENT/1024,0) NXT_KB, ROUND(BYTES/1024,0) TOT_KB, PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE'; SEGMENT_NAME INI_KB NXT_KB TOT_KB PCT ------------ ------- ------- ------- ---- ---------- ---------- DEPT 1024 1024 1040 0 1 TS001 EXTENTS TABLESPACE

PROMPT =================================== PROMPT FOREIGN KEY PROMPT =================================== COL CONSTRAINT_NAME FOR A20 COL R_CONSTRAINT_NAME FOR A20 SELECT CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS; CONSTRAINT_NAME R_CONSTRAINT_NAME STATUS -------------------- -------------------- -------- EMP_DEPT_FK01 DEPT_PK ENABLED DEPT_SUPPLER_FK01 SUPPLER_PK ENABLED

-------------------------------------------------------------------- -- 1. : index_cre_bonsa.sql -- 2. : (BONSA01) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- SPOOL index_cre_bonsa.log CREATE INDEX I_EMP01 ON EMP (DEPTNO ASC) TABLESPACE IS001;; SPOOL OFF

SQL> connect bonsa01/bonsa01@bonsa SQL> @index_cre_bonsa.sql....

PROMPT =================================== PROMPT PROMPT =================================== COL SEGMENT_NAME FORMAT A15 COL TABLESPACE_NAME FORMAT A10 SELECT SEGMENT_NAME,ROUND(INITIAL_EXTENT/1024,0) INI_KB, ROUND(NEXT_EXTENT/1024,0) NXT_KB, ROUND(BYTES/1024,0) TOT_KB, PCT_INCREASE PCT, EXTENTS, TABLESPACE_NAME FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'; SEGMENT_NAME INI_KB NXT_KB TOT_KB PCT EXTENTS TABLESPACE --------------- ---------- ---------- ---------- ---------- ---------- ----------... I_EMP 1024 512 1040 0 1 TS001

-------------------------------------------------------------------- -- 1. : view_cre_bonsa.sql -- 2. : (BONSA01) -- 3. : 200271 -- 4. : -------------------------------------------------------------------- SPOOL view_cre_bonsa.log CREATE VIEW V_EMP AS SELECT EMPNO, EMPNM, DEPTNO FROM EMP; SPOOL OFF

SQL> connect bonsa01/bonsa01@bonsa SQL> @view_cre_bonsa.sql....

PROMPT =================================== PROMPT PROMPT =================================== COL OBJECT_NAME FORMAT A30 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_TYPE = 'VIEW'; OBJECT_NAME OBJECT_TYPE STATUS --------------- ------------------ ------- V_EMP VIEW VALID

C:\>lsnrctl start...... BONSA has 1

BONSA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = BONSA) ))

SQL> CREATE PUBLIC DATABASE LINK H2U1 CONNECT TO BONSA01 IDENTIFIED BY BONSA01 USING 'BONSA'; <-- Tnsnames.ora

CREATE SNAPSHOT LOG ON EMP TABLESPACE TS001 STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0); CREATE SNAPSHOT EMP TABLESPACE TS002 STORAGE (MINEXTENTS 1 PCTINCREASE 0) USING INDEX TABLESPACE IS002 STORAGE (MINEXTENTS 1 PCTINCREASE 0) REFRESH FAST START WITH SYSDATE NEXT TRUNC(SYSDATE) + 1 + (20/24) <-- REFRESH AS SELECT * FROM EMP@BONSA; <--