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

Size: px

Start display at page:

Download "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"

서규 여
6 years ago
Views:

2 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

19 : ts_cre_bonsa.sql : (TS001, IS001) : : 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 IS (IS001) DATAFILE 'D:\TEST\DB1\DF001.DBF01' SIZE 30M DEFAULT STORAGE ( INITIAL 512K NEXT 256K MAXEXTENTS 128 PCTINCREASE 0); SPOOL OFF

20 SQL> connect SQL>....

21 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.

23 : user_cre_bonsa.sql : (BONSA01) : : SPOOL user_cre_bonsa.log CREATE USER BONSA01 IDENTIFIED BY BONSA01 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; SPOOL OFF

24 SQL> connect SQL>....

25 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

26 : role_cre_bonsa.sql : (BONSA01) : : CREATE ROLE BONSA_ROLE NOT IDENTIFIED; GRANT CREATE SESSION, CREATE TABLE,CREATE VIEW, ALTER ROLLBACK SEGMENT TO BONSA_ROLE;

27 SQL> connect SQL>.....

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

29 : role_grant_jisa.sql : (JISA01) : : GRANT JISA_ROLE TO JISA01;

30 SQL> connect SQL>....

31 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

33 : tab_cre_bonsa.sql : (BONSA01) : : 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

34 SQL> connect

35 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 TS001 EXTENTS TABLESPACE

36 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

37 : index_cre_bonsa.sql : (BONSA01) : : SPOOL index_cre_bonsa.log CREATE INDEX I_EMP01 ON EMP (DEPTNO ASC) TABLESPACE IS001;; SPOOL OFF

38 SQL> connect

39 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 TS001

40 : view_cre_bonsa.sql : (BONSA01) : : SPOOL view_cre_bonsa.log CREATE VIEW V_EMP AS SELECT EMPNO, EMPNM, DEPTNO FROM EMP; SPOOL OFF

41 SQL> connect

42 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

45 C:\>lsnrctl start BONSA has 1

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

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

48 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) (20/24) <-- REFRESH AS SELECT * FROM EMP@BONSA; <--

MS-SQL SERVER 대비 기능

MS-SQL SERVER 대비 기능 Business! ORACLE MS - SQL ORACLE MS - SQL Clustering A-Z A-F G-L M-R S-Z T-Z Microsoft EE : Works for benchmarks only CREATE VIEW Customers AS SELECT * FROM Server1.TableOwner.Customers_33 UNION ALL SELECT