
P132
7.1
CONN scott/tiger@orcl
DROP SEQUENCE order_seq;
CREATE SEQUENCE order_seq
  INCREMENT BY 1
  START WITH 10000000
  CACHE 500
  NOCYCLE;
COL sequence_name FORMAT A13
SELECT sequence_name, min_value, max_value, 
       increment_by, last_number
  FROM user_sequences;
SELECT order_seq.CURRVAL FROM DUAL;
SELECT order_seq.NEXTVAL FROM DUAL;
SELECT order_seq.CURRVAL FROM DUAL;
SELECT order_seq.NEXTVAL FROM DUAL;
SELECT order_seq.CURRVAL FROM DUAL;

P134
7.2 
CONN scott/tiger@orcl
DROP TABLE order_header ;
CREATE TABLE order_header (
  order_id VARCHAR2(14),
  customer_id VARCHAR2(12),
  order_value NUMBER(10,2),
  submit_time TIMESTAMP);
INSERT INTO order_header VALUES(
        to_char(SYSDATE,'yyyymm')||order_seq.nextval,
        '1000',3750,SYSTIMESTAMP);
COMMIT;
COL submit_time FORMAT A33
COL order_value FORMAT L0999
SELECT * FROM order_header;
 
P135
7.3
CEMERP_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = cemerp)
    )
  )
CONN sys/syspwd@orcl AS SYSDBA
GRANT CREATE DATABASE LINK TO scott;
CONN scott/tiger@orcl
CREATE DATABASE LINK orcl_cemerp_dblink
  CONNECT TO scott
  IDENTIFIED BY tiger USING 'cemerp_db';
CONN scott/tiger@orcl
SELECT * FROM dept@orcl_cemerp_dblink;

P137
7.4
(1)ݿִ
CONNECT scott/tiger@orcl
SELECT owner,table_name,constraint_type
  FROM user_constraints
  WHERE owner='SCOTT' AND table_name='DEPT'
  ORDER BY constraint_type;
ALTER TABLE dept ADD(PRIMARY KEY(deptno));
DROP MATERIALIZED VIEW LOG ON dept;
CREATE MATERIALIZED VIEW LOG ON dept
  WITH PRIMARY KEY
  INCLUDING NEW VALUES;
SELECT * FROM tab ORDER BY tname;
DESC MLOG$_DEPT
(2)ݿCEMERPִ
CONNECT scott/tiger@cemerp
SELECT * FROM user_sys_privs WHERE username='SCOTT';
CONNECT system/systempwd@cemerp
GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;
CONNECT scott/tiger@cemerp
COL username FORMAT A8
SELECT * FROM user_sys_privs WHERE username='SCOTT';
ORCL_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )
DROP DATABASE LINK cemerp_orcl_dblink;
CREATE DATABASE LINK cemerp_orcl_dblink
  CONNECT TO scott
  IDENTIFIED BY tiger USING 'orcl_db';
DROP MATERIALIZED VIEW mv_dept;
CREATE MATERIALIZED VIEW mv_dept
  BUILD IMMEDIATE
  REFRESH FAST
  NEXT SYSDATE+5 / 1440
  AS SELECT *
     FROM dept@cemerp_orcl_dblink;
SELECT * FROM tab;
DESC mv_dept
(3)
CONNECT scott/tiger@orcl
INSERT INTO dept(deptno,dname,loc) VALUES(1,'1','ص1');
INSERT INTO dept(deptno,dname,loc) VALUES(2,'2','ص2');
COMMIT;
COL CHANGE_VECTOR$$ FORMAT A20
SELECT * FROM MLOG$_DEPT;
CONNECT scott/tiger@cemerp
SELECT * FROM mv_dept ;

