第 5章.txt

oracle操作基础基础基础基础基础基础基础基础基础基础基础基础

例5.1 
DROP SEQUENCE my_seq;
CREATE SEQUENCE my_seq
   INCREMENT BY 10
   START WITH 10
   NOCACHE
   NOCYCLE;
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;
SELECT my_seq.NEXTVAL FROM DUAL;
SELECT my_seq.CURRVAL FROM DUAL;

例5.2
conn scott/tiger@oradb
CREATE DATABASE LINK testlink
CONNECT TO scott
IDENTIFIED BY tiger USING 'oemrep_db';
SELECT empno,ename,sal,comm FROM emp@testlink;

例5.3
CONNECT scott/tiger@oradb
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;
CONNECT scott/tiger@oemrep
select * from USER_SYS_PRIVS where USERNAME='SCOTT';
CONNECT system/system@oemrep
GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;
CONNECT scott/tiger@oemrep
select * from USER_SYS_PRIVS where USERNAME='SCOTT';
DROP DATABASE LINK oemrep_oradb;
CREATE DATABASE LINK oemrep_oradb CONNECT TO scott
  IDENTIFIED BY tiger USING 'oradb_db';
DROP MATERIALIZED VIEW dept_mv;
CREATE MATERIALIZED VIEW dept_mv
   BUILD IMMEDIATE
   REFRESH FAST
   NEXT SYSDATE+5 / 1440
   AS SELECT *
      FROM dept@oemrep_oradb;

CONNECT scott/tiger@oradb
INSERT INTO dept(deptno,dname,loc) VALUES(1,'部门1','地点1');
INSERT INTO dept(deptno,dname,loc) VALUES(2,'部门2','地点2');
commit;
CONNECT scott/tiger@oemrep
SELECT * FROM dept_mv ;